Need Help With Double Click Event

[KLahvic01]

Hello, I have a search field where the list box is populated off of a query, I want to be able to double click an item in the list box and have it bring up the details view of the record. I have the following code on the double click event based on a thread I found here, but I cannot seem to get it to work, it opens the details form but with a blank, new record.

Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "Customer Details", , , "[CustomerID] = " & Me![SearchResults]
End Sub

Any help would be greatly appreciated, I assume it is something simple, but I have banged my head enough, time for help.

thanks,

KJ

 

[pbaldy]

Make sure the bound column of the listbox is the expected CustomerID. If CustomerID isn't numeric, the syntax is slightly different:

http://www.baldyweb.com/wherecondition.htm

 

[VilaRestal]

Customer ID is a number field?
SearchResults has two columns, one for the ID - hidden (zero width), one for the display text?

If so use Me.SearchResults.Column(0) instead of Me![SearchResults]

 

[KLahvic01]

I have changed the bound column to 0, which is the column CustomerID is in the Customers table, and I have tried changing the code to include

Me.SearchResults.Column(0) instead of Me![SearchResults]

And it still comes up with a blank form...I feel liek it is something so simple, but I am at a loss here...hopefully I am missing something simple.

KJ

 

[pbaldy]

Do you know how to set a breakpoint and check the value coming from the listbox? Also, the Data Entry property of the form being opened isn't Yes, is it?

 

[KLahvic01]

I do not know how to set a breakpoint.

The form that I am trying to open here is the customer details screen, which is the same screen that is used to enter a new customer as well. It would have the Data Entry option set to Yes because of its entry use. I just want a simple way to search my customers by multiple fields and have teh search be able to open the details about the customer. I have th esearch working where I can search by several fields but the double click isnt working like I would want it to.

 

[VilaRestal]

Try

Private Sub SearchResults_DblClick(Cancel As Integer)
MsgBox Me.SearchResults.Column(0)
DoCmd.OpenForm "Customer Details", , , "[CustomerID] = " & Me.SearchResults.Column(0)
End Sub

 

[VilaRestal]

It would have the Data Entry option set to Yes because of its entry use.

Ah then that's your problem. A data entry form will never show existing records. Turn that off or make a copy without it.

 

[KLahvic01]

Ok, I did that and a message box popped up saying '3' with an OK button. Once I clicked ok, it brought me to my Customer Details form but still a blank entry...

Frustrating...

KJ

 

[VilaRestal]

Cool, so that bits working. It just the data entry of the form that's messing it up.

 

[KLahvic01]

Ok, so if the form needs to have DataEntry as No, is there a way to have it when it is accessed through the search to have it as No, but to have it Yes when trying to enter a new customer?

KJ

 

[pbaldy]

Just change the appropriate argument of OpenForm.

 

[VilaRestal]

Yeah have it as not data entry by default. When you open it to add a new customer use the acFormAdd argument:

DoCmd.OpenForm "Customer Details", , , , acFormAdd

 

[KLahvic01]

Thank you very much, this works like a charm. I knew it had to be something simple that was holding me back...

KJ

 

[KLahvic01]

I have another similar issue with a search form for a different table, this time it is my employees form that I would like to populate the information based on a list boxes dbl click event. I have the Employee Details form Data Entry as No, and the appropriate column bound (is it the column of the list box, the query that populates the list box, or the table the query comes out of?).

I am not sure what is happening here, but I need some help.

Thanks,

KJ

 

[VilaRestal]

What is happening? The same as before: form opens but with no records?

Could you post the code that opens the form please?

 

[KLahvic01]

Yeah, the same thing is happening, only this time I have the form with the Data Entry control as 'No'. The form has Tabs on it with different sections and the second tab is fields from a different table which is in a one to one relationship with the ID field (dont know if that makes a difference). Here is the code I am using, it is pretty much a mirror of the code you provided for the Customers Form and Search:

Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "Employee Details", , , "[ID] = " & Me.SearchResults.Column(0)
End Sub

Hope this is enough info, please let me know if you need anything else...

Thanks again,

KJ

 

[VilaRestal]

The problem will be that the filter
"[ID] = " & Me.SearchResults.Column(0)
returns no records. No record has that ID.

Check that Column 1 (zero indexed = Column(0)) is the ID column in the list box.

If it is then open the form directly (not via this method) and try filtering it manually for an ID. Even temporarily put a textbox on the form that shows the ID so you can see what IDs are used.

Put MsgBox Me.SearchResults.Column(0) in the code above so you can see exactly what ID is being used as the filter. Check that is a number and an ID that a record actually has. Perhaps there's a problem with the recordsource of the form.

 

[KLahvic01]

Thank you, I think I have found the issue. You had mentioned the Filter and so I looked at the entry in that line of the property and it was something like [ID] = "13" or something, well the database doesnt have that ID, once I removed it and the code in the on open that called for a new record and now it works. Thank you for your assistance in this. Please watch for future posts from me...

Thanks again,

KJ

 

[VilaRestal]

One thing to watch out for is that if a form is opened with a where clause and then you shift into design view it will put that where clause as a filter that can then be saved permanently for the form.

It's a very annoying habit that has lead me to never alter the design of a form by moving from it's form view to design view. I'll always close and reopen in design view.

And that includes debugging code. If I'm making changes to code at runtime, whilst stepping through it, when I close the form and it asks to save I'll click No. Then I go back into it in design view and make the changes again.

Having an unintentional filter appear like that without any notification that it has can cause serious problems in a serious database.

I expect that's what's happened here.