Searching records using a list box.

[dlowry]

I'm currently using access 97. I'm relatively new to all this, so have attempted to create a company Asset Register when i've come across a problem im unsure of. I'm trying to create some sort of search finder that enables me to navigate to the fields i require quickly.
I've done this using a list box with FirstName, LastName and PCNumber as the search headings. Above that are a set of command buttons that search both ascending and descending. When highlighting the required user and hitting search the following comes up 'Enter Parameter Value' Assets.AssetID. It's more complicated than what i've put so is there a way i can attach the DB ?

[MMcCarthy]

Post the code behind the search buttons.

I'm currently using access 97. I'm relatively new to all this, so have attempted to create a company Asset Register when i've come across a problem im unsure of. I'm trying to create some sort of search finder that enables me to navigate to the fields i require quickly.
I've done this using a list box with FirstName, LastName and PCNumber as the search headings. Above that are a set of command buttons that search both ascending and descending. When highlighting the required user and hitting search the following comes up 'Enter Parameter Value' Assets.AssetID. It's more complicated than what i've put so is there a way i can attach the DB ?

[dlowry]

Post the code behind the search buttons.

Option Compare Database
Option Explicit
Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String

'Clear captions from command buttons
ClearCaptions

'Set row source for list box
strSQL = "SELECT DISTINCTROW AssetID, AssetsFirstName , AssetsLastName, PCNumber "
strSQL = strSQL & "FROM Assets "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!lstSearch.Ro wSource = strSQL
Me!lstSearch.Re query

End Function

Sub ClearCaptions()

'Clear captions of asc and desc symbols

Me!cmdOrderFNam eDesc.Caption = "Order by First Name"
Me!cmdOrderFNam e.Caption = "Order by First Name"
Me!cmdOrderLNam eDesc.Caption = "Order by Last Name"
Me!cmdOrderLNam e.Caption = "Order by Last Name"
Me!cmdOrderPCNu mberDesc.Captio n = "Order by PCNumber"
Me!cmdOrderPCnu mber.Caption = "Order by PCNumber"

End Sub

Private Sub cmdOrderFName_C lick()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Ass etFirstName", "asc")
Me!cmdOrderFNam eDesc.Visible = True
Me!cmdOrderFNam eDesc.Caption = "v Order by First Name v"
Me!cmdOrderFNam eDesc.SetFocus
Me!cmdOrderFNam e.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderFNameDe sc_Click()
'Set First Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Ass etFirstName", "DESC")
Me!cmdOrderFNam e.Visible = True
Me!cmdOrderFNam e.Caption = "^ Order by First Name ^"
Me!cmdOrderFNam e.SetFocus
Me!cmdOrderFNam eDesc.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderLName_C lick()
'Set Last Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Ass etLastName", "asc")
Me!cmdOrderLNam eDesc.Visible = True
Me!cmdOrderLNam eDesc.Caption = "v Order by First Name v"
Me!cmdOrderLNam eDesc.SetFocus
Me!cmdOrderLNam e.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderLNameDe sc_Click()
'Set Last Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Ass etLastName", "DESC")
Me!cmdOrderLNam e.Visible = True
Me!cmdOrderLNam e.Caption = "^ Order by Last Name ^"
Me!cmdOrderLNam e.SetFocus
Me!cmdOrderLNam eDesc.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderPCNumbe r_Click()
'Set PN order in ascending order and apply captions
Dim response As Integer
response = basOrderby("PCN umber", "asc")
Me!cmdOrderPCNu mberDesc.Visibl e = True
Me!cmdOrderPCNu mberDesc.Captio n = "v Order by PCNumber v"
Me!cmdOrderPCNu mberDesc.SetFoc us
Me!cmdOrderPCnu mber.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderPCNumbe rDesc_Click()
'Set PN order in descending order and apply captions
Dim response As Integer
response = basOrderby("PCN umber", "DESC")
Me!cmdOrderPCnu mber.Visible = True
Me!cmdOrderPCnu mber.Caption = "^ Order by PCNumber ^"
Me!cmdOrderPCnu mber.SetFocus
Me!cmdOrderPCNu mberDesc.Visibl e = False
Me!lstSearch.Se tFocus

End Sub

Private Sub lstSearch_After Update()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enab led = True
End Sub

Private Sub lstSearch_DblCl ick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearc h) Then
ShowRecord_Clic k
End If
End Sub

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

DoCmd.OpenForm "Assets", , , _
"Assets.AssetID =" & "'" & Me.lstSearch.Co lumn(0) & "'"

'Close the dialog box
DoCmd.Close acForm, "frmListBoxSear ch"

End Sub
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Clic k
'Cancel and close the form

DoCmd.Close

Exit_Cancel_Cli ck:
Exit Sub

Err_Cancel_Clic k:
MsgBox Err.Description
Resume Exit_Cancel_Cli ck

End Sub

[MMcCarthy]

This is your problem line I think.

DoCmd.OpenForm "Assets", , , _
"Assets.AssetID =" & "'" & Me.lstSearch.Co lumn(0) & "'"

Go to the Assets form and in design view go to the properties of the control you think is AssetID. Look under the other tab for the system name of the control. I think you will find it is not Assets.AssetID.

Option Compare Database
Option Explicit
Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String

'Clear captions from command buttons
ClearCaptions

'Set row source for list box
strSQL = "SELECT DISTINCTROW AssetID, AssetsFirstName , AssetsLastName, PCNumber "
strSQL = strSQL & "FROM Assets "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!lstSearch.Ro wSource = strSQL
Me!lstSearch.Re query

End Function

Sub ClearCaptions()

'Clear captions of asc and desc symbols

Me!cmdOrderFNam eDesc.Caption = "Order by First Name"
Me!cmdOrderFNam e.Caption = "Order by First Name"
Me!cmdOrderLNam eDesc.Caption = "Order by Last Name"
Me!cmdOrderLNam e.Caption = "Order by Last Name"
Me!cmdOrderPCNu mberDesc.Captio n = "Order by PCNumber"
Me!cmdOrderPCnu mber.Caption = "Order by PCNumber"

End Sub

Private Sub cmdOrderFName_C lick()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Ass etFirstName", "asc")
Me!cmdOrderFNam eDesc.Visible = True
Me!cmdOrderFNam eDesc.Caption = "v Order by First Name v"
Me!cmdOrderFNam eDesc.SetFocus
Me!cmdOrderFNam e.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderFNameDe sc_Click()
'Set First Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Ass etFirstName", "DESC")
Me!cmdOrderFNam e.Visible = True
Me!cmdOrderFNam e.Caption = "^ Order by First Name ^"
Me!cmdOrderFNam e.SetFocus
Me!cmdOrderFNam eDesc.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderLName_C lick()
'Set Last Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Ass etLastName", "asc")
Me!cmdOrderLNam eDesc.Visible = True
Me!cmdOrderLNam eDesc.Caption = "v Order by First Name v"
Me!cmdOrderLNam eDesc.SetFocus
Me!cmdOrderLNam e.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderLNameDe sc_Click()
'Set Last Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Ass etLastName", "DESC")
Me!cmdOrderLNam e.Visible = True
Me!cmdOrderLNam e.Caption = "^ Order by Last Name ^"
Me!cmdOrderLNam e.SetFocus
Me!cmdOrderLNam eDesc.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderPCNumbe r_Click()
'Set PN order in ascending order and apply captions
Dim response As Integer
response = basOrderby("PCN umber", "asc")
Me!cmdOrderPCNu mberDesc.Visibl e = True
Me!cmdOrderPCNu mberDesc.Captio n = "v Order by PCNumber v"
Me!cmdOrderPCNu mberDesc.SetFoc us
Me!cmdOrderPCnu mber.Visible = False
Me!lstSearch.Se tFocus

End Sub

Private Sub cmdOrderPCNumbe rDesc_Click()
'Set PN order in descending order and apply captions
Dim response As Integer
response = basOrderby("PCN umber", "DESC")
Me!cmdOrderPCnu mber.Visible = True
Me!cmdOrderPCnu mber.Caption = "^ Order by PCNumber ^"
Me!cmdOrderPCnu mber.SetFocus
Me!cmdOrderPCNu mberDesc.Visibl e = False
Me!lstSearch.Se tFocus

End Sub

Private Sub lstSearch_After Update()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enab led = True
End Sub

Private Sub lstSearch_DblCl ick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearc h) Then
ShowRecord_Clic k
End If
End Sub

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

DoCmd.OpenForm "Assets", , , _
"Assets.AssetID =" & "'" & Me.lstSearch.Co lumn(0) & "'"

'Close the dialog box
DoCmd.Close acForm, "frmListBoxSear ch"

End Sub
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Clic k
'Cancel and close the form

DoCmd.Close

Exit_Cancel_Cli ck:
Exit Sub

Err_Cancel_Clic k:
MsgBox Err.Description
Resume Exit_Cancel_Cli ck

End Sub