Form criteria using a list box

astarbyfar

Registered User.
Local time
Today, 05:09
Joined
Apr 27, 2003
Messages
93
Hi, I have a table called tblSettings which has the primary keys

Line number and Part number

Now I want to produce a list box which matches a certain critera (which Ive managed to do!). However I want to be able to click on the tuple from the list which inturn opens the details corresponding to the selected primary keys. I know this has been discussed a number of times in the forum, I cant find a way for using two search criteria. Here is the code I have upto now which is giving me a "data mismatch" error.

Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box

DoCmd.OpenForm "Viewtonnage", , , _
"[tblSettings.Line_number]=" & "'" & Me.lstSearch.Column(0) & "'" AND
"[tblSettings.Part_number]=" & "'" & Me.lstSearch.Column(1) & "'"

'Close the dialog box
DoCmd.Close acForm, "frmListBoxSearch"

End Sub


As you can see the name of the form I wish to view the information in is Viewtonnage which has a record source of a query with no criteria specified. Id also like it to be multiselection if its possible i.e. pick a number of results at a time to view via the Viewtonnage form.

I know its been discussed in earlier posts but I cant find anything that uses two search critera.

Regards
 
Star,

If they're strings:

Code:
DoCmd.OpenForm "Viewtonnage", , , _
   "[tblSettings.Line_number] = '" &  Me.lstSearch.Column(0) & "' AND " & _
   "[tblSettings.Part_number] = '" &  Me.lstSearch.Column(1) & "'"

If they're numbers:

Code:
DoCmd.OpenForm "Viewtonnage", , , _
   "[tblSettings.Line_number] = " &  Me.lstSearch.Column(0) & " AND " & _
   "[tblSettings.Part_number] = " &  Me.lstSearch.Column(1)

Wayne
 
Wayne there seems to be a problem with the "_" that is used in the below code i.e. the code that is highlighted

DoCmd.OpenForm "Viewtonnage", , , _
"[tblSettings.Line_number] = '" & Me.lstSearch.Column(0) & "' AND " & _
"[tblSettings.Part_number] = '" & Me.lstSearch.Column(1) & "'"
 
try this,
"[tblSettings.Line_number] = '" & Me.lstSearch.Column(0) & "' AND " _
& "[tblSettings.Part_number] = '" & Me.lstSearch.Column(1) & "'"
 
Thanks for your replys. Ive already tried using the last code myself and when I do it I have an error saying invlaid use of brackets where the tables and attributes are declared i.e. [tblSettings.Part_number]. Any suggestions?
 

Users who are viewing this thread

Back
Top Bottom