Selecting record in List Box and opening that record in another form

[jobrien4]

I created a search form that searches for Customer Number, Part Number, and Revision Number. The results are displayed in a List Box.



I want the user to be able to make a selection on the List Box and click an "Open Selected" button that launches that record in another form. How is this possible?

[dreday]

The value of the listbox will be whatever they selected so when you click the "Open Selected" button you can either pass the value of the listbox as parameter to the new form if it's bound to a parametrized query or open the new form and set the filter option to filter for the record selected.

[jobrien4]

Ok but I have three values in each row of the list box. How do I pass along each value to the filter?

This is what I'm trying right now:

Dim SelectedPartNumber As String
Dim SelectedRevisionNumber As String
Dim SelectedCustomerNumber As Integer

SelectedCustomerNumber = Me.SearchResults.Column(0)
SelectedPartNumber = Me.SearchResults.Column(1)
SelectedRevisionNumber = Me.SearchResults.Column(2)
DoCmd.OpenForm "frm_PartDatabase", acNormal, , "Forms![frm_PartDatabase]![PartNumber]=SelectedPartNumber AND Forms![frm_PartDatabase]![RevisionNumber]=SelectedRevisionNumber AND Forms![frm_PartDatabase]![CustomerNumber]=SelectedCustomerNumber"


When I debug, I'm getting the correct numbers as the variables, when it opens the new form, it's asking for the values as if it were a paramter query

[dreday]

You need to assemble the string differently so all your variables can compute:

DoCmd.OpenForm "frm_PartDatabase", acNormal, ,
"Forms![frm_PartDatabase]![PartNumber]=" & SelectedPartNumber & " AND
Forms![frm_PartDatabase]![RevisionNumber]=" & SelectedRevisionNumber & " AND
Forms![frm_PartDatabase]![CustomerNumber]=" & SelectedCustomerNumber

When they're stuck in the quotes you're actually passing whatever text is in the string instead of the values they represent. Try assembling the filter code in a string ahead of the open form action and check out the difference between the original filter and the one I provided

[jobrien4]

When I try this code, it opens the form with the header but nothing in the detail section

If I remove the filter all together, it opens the form on the first record

[dreday]

Try using just the field names without Forms![frm_PartDatabase] part and enclose the whole statement in parenthesis

[jobrien4]

It's now giving me the following error:

Data type mismatch in criteria expression

I checked and made sure PartNumber was set up as Text, RevisionNumber was set up as text, and CustomerNumber was set up as Long Interger

[dreday]

It's now giving me the following error:

Data type mismatch in criteria expression

I checked and made sure PartNumber was set up as Text, RevisionNumber was set up as text, and CustomerNumber was set up as Long Interger

I bet you need extra quotes around the vars in the filter string to force the new form to treat them like text for PartNumber and RevisionNumber

try this:
DoCmd.OpenForm "frm_PartDatabase", acNormal, ,
"[PartNumber]=""" & SelectedPartNumber & """ AND
[RevisionNumber]=""" & SelectedRevisionNumber & """ AND
[CustomerNumber]=" & SelectedCustomerNumber

by putting an extra "" before and after the var it will insert a " into the actual string so it will come out looking like:

[PartNumber] = "1234" AND [RevisionNumber] = "4556" AND [CustomerNumber] = 2343

Since customer number is a Long we don't want to put quotes around it or we'll force another data mismatch

[jobrien4]

Thanks for you patience...that did it! Now I won't be trying to figure this out all weekend.

[dreday]

Happy to help