listbox selection not updating query/form

[stormin_norm]

I have a simple form with text box and list box called frmCustSelect.
User enters last name in text box, and the list box is populated with matching user records (using like [Forms]...&"*" in lookup query to get all jon* records when user keys in 'jon')
This works great.

Now user scrolls through the list box and double clicks on the entry jones, mary. And a new form pops up called frmCustomer.
If I go back to frmCustSelect and doubleclick on a different entry, the underlying query is never updated, and therefore frmCustomer is not changed.

If I close frmCustomer, and then doublick on a listbox entry, the frmCustomer opens with the new record. Is this because a new query thread is started each time???


Simple code ex:
-------------------
frmCustSelect

Private Sub txtLastName_AfterUpdate()
lbCustomerSelection = Null
lbCustomerSelection.Requery
End Sub

Private Sub lbCustomerSelection_DblClick(Cancel As Integer)
DoCmd.OpenForm ("frmCustomer")
End Sub
------------------------

----------------------
frmCustomer

RecordSource = qryCust
Misc fields from qryCust
------------------------

-----------------------
qryCust

SELECT Customer.*, Customer.CustID
FROM Customer
WHERE (((Customer.CustID)=[Forms]![frmCustSelect]![lbCustomerSelection]));

-----------------------

 

[Jack Cowley]

Bind the list box to CustID then change your code to look like this:

DoCmd.OpenForm "frmCustomer", , ,"[CustID] = " & Me.ListBoxName

You don't need the query, just base 'frmCustomer' on the customer table...

hth,
Jack

 

[stormin_norm]

This works great! Thanks for the tip. I'm now hunting through the OpenForm options.
The key was the fourth parameter of the openform method:

DoCmd.OpenForm "frmCustomer", , ,"[CustID] = " & Me.ListBoxName

Quick add on question--->
Is there a good way to use this more generic?

Let's say you have four forms
frmCustomer
frmCustomerSales
frmCustomerInvoices
frmCustomerShipping

but you want to use the same pop=up form to initiate the customer selection process, thing is, the pop-up form needs to know who called him.

 

[Jack Cowley]

Take a look at the OpenArgs method of OpenForm object... I think you will find your answer there.

hth,
Jack