Search form not opening records properly...

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

ukmonkeyman

Thread Starter
Joined
Nov 19, 2011
Messages
17
Basicly I have been given the task of building a system to manage accounts and log calls and I have most of what to do already mapped out and having done similar things in parts with Access before I thought it would be a doddle but a few steps in and I have already come up against a brick wall. I have a form with several search fields and that works fine, no hitches at all and when you click on a button next to the top record it opens the customers details in a new form for editing, that works fine also. The problem I am having occurs when I get more than one result from the search and I try to click the button next to any record except the first and the problem is that the form I want to load is not loading anything, its just a blank form. No text boxes, no labels, nothing but the colours and the picture in the corner. I have put the code on the form to show me what data is going through and when following through with the first record it is showing me the correct data was transfered but when clicking any other record I am getting nothing. I have tried hunting for a solution for this but find it hard to know what to type to get the right information for my problem so I am sorry if this has been resolved before. Also forgive me for my patchy knowledge but I have taught myself everything I have done so far so it is probably just a step I have missed somewhere along the line.

Any Ideas?

Code:
'In Search Form on button click
    Dim strWhere As String
    If IsNull(Me.OrderRef) Then
    Else
        strWhere = "[OrderRef] = " & Me.OrderRef
        DoCmd.OpenForm "fsOpenOrder", WhereCondition:=strWhere
    End If

'In Order Form on load
    If Me.Status = "Closed" Then
        MsgBox "This order has been closed and you are no longer able to  edit any information. If you need to re-open this account then please  contact the database admin.", vbInformation, "Closed Order."
        Me.Price.Locked = True
        Me.Items.Locked = True
        Me.Notes.Locked = True
        Me.Dispatched.Locked = True
        Me.DispatchDate.Locked = True
        Me.Status.Enabled = False
        Me.Payment.Locked = True
        Me.PaymentCleared.Locked = True
        Me.ID.Locked = True
    Else
        Me.Price.Locked = False
        Me.Items.Locked = False
        Me.Notes.Locked = False
        Me.Dispatched.Locked = False
        Me.DispatchDate.Locked = False
        Me.Status.Enabled = True
        Me.Payment.Locked = False
        Me.PaymentCleared.Locked = False
        Me.ID.Locked = False
    End If
 

OBP

Joined
Mar 8, 2005
Messages
19,896
2 questions, what type of form is the search form, is it in "Continuous Forms mode" or Single form mode?
Where is the Button on the Form?
 

ukmonkeyman

Thread Starter
Joined
Nov 19, 2011
Messages
17
Continuous forms mode and the button is on the details section in line with the data, i also tried putting the code on the double click function of the details section and still no luck.
 

ukmonkeyman

Thread Starter
Joined
Nov 19, 2011
Messages
17
Something new I found. It is showing only the first record in my table, if my search omits my tables first record it is not opening the first search result in the new form as with my original post. So this is something to do with it pulling data from the table right?
 

OBP

Joined
Mar 8, 2005
Messages
19,896
OK, it is a pity we are so far apart timewise as this is taking longer than it should.
I would suggest that you add this line to your code after
strWhere = "[OrderRef] = " & Me.OrderRef
msgbox "strwhere = " & strWhere " OrderRef = " & Me.OrderRef

This will check that you are getting data and what the data actually is. My concern is that the button is not interacting with the Record in the Continuous form.

Can you post this database with some dummy data in Access 2000-2003 format?
I have used this form opening technique quite a bit and the Syntax is also quite important, so did you get the Command Button Wizard to create it?
You could try strWhere = "[OrderRef] = '" & Me.OrderRef & "' "
 

ukmonkeyman

Thread Starter
Joined
Nov 19, 2011
Messages
17
yes, give me 30 mins, just finishing up something else then will put some dummy data in. thank you so much for your help so far. it is greatly appreciated.
 

ukmonkeyman

Thread Starter
Joined
Nov 19, 2011
Messages
17
ok, attached is what I have got so far. I am sure you will see the problem straight away but I am coming to the conclusion that it is an issue with the relationship with the table. I am going to keep trying things my end until I hear back from you and thanks again.
 

Attachments

ukmonkeyman

Thread Starter
Joined
Nov 19, 2011
Messages
17
OK, I think I have found the mistake myself and I think my solution to the task originally was the wrong way of going about it. Maybe I should be passing the info across via openargs and filtering rather than opening the form with a where condition?
 

OBP

Joined
Mar 8, 2005
Messages
19,896
I don't think so, your main problem is the Orders form does not show any data at all when you open it.
I have disbaled the VBA and it still doesn't show any records
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Take a look at this version.
I have changed the Form's record source to a Query, it was using an SQL with a where clause.
 

Attachments

ukmonkeyman

Thread Starter
Joined
Nov 19, 2011
Messages
17
and thank you very much for your help, makes sense now im looking at it done properly. I owe you a beer. :)
 

OBP

Joined
Mar 8, 2005
Messages
19,896
I always use queries for forms and reports, you can check the records, order them filter them if necessary and then create the form or report and recheck the records.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top