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.RowSource = strSQL
Me!lstSearch.Requery

End Function

Sub ClearCaptions()

'Clear captions of asc and desc symbols

Me!cmdOrderFNameDesc.Caption = "Order by First Name"
Me!cmdOrderFName.Caption = "Order by First Name"
Me!cmdOrderLNameDesc.Caption = "Order by Last Name"
Me!cmdOrderLName.Caption = "Order by Last Name"
Me!cmdOrderPCNumberDesc.Caption = "Order by PCNumber"
Me!cmdOrderPCnumber.Caption = "Order by PCNumber"

End Sub

Private Sub cmdOrderFName_Click()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("AssetFirstName", "asc")
Me!cmdOrderFNameDesc.Visible = True
Me!cmdOrderFNameDesc.Caption = "v Order by First Name v"
Me!cmdOrderFNameDesc.SetFocus
Me!cmdOrderFName.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderFNameDesc_Click()
'Set First Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("AssetFirstName", "DESC")
Me!cmdOrderFName.Visible = True
Me!cmdOrderFName.Caption = "^ Order by First Name ^"
Me!cmdOrderFName.SetFocus
Me!cmdOrderFNameDesc.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderLName_Click()
'Set Last Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("AssetLastName", "asc")
Me!cmdOrderLNameDesc.Visible = True
Me!cmdOrderLNameDesc.Caption = "v Order by First Name v"
Me!cmdOrderLNameDesc.SetFocus
Me!cmdOrderLName.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderLNameDesc_Click()
'Set Last Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("AssetLastName", "DESC")
Me!cmdOrderLName.Visible = True
Me!cmdOrderLName.Caption = "^ Order by Last Name ^"
Me!cmdOrderLName.SetFocus
Me!cmdOrderLNameDesc.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderPCNumber_Click()
'Set PN order in ascending order and apply captions
Dim response As Integer
response = basOrderby("PCNumber", "asc")
Me!cmdOrderPCNumberDesc.Visible = True
Me!cmdOrderPCNumberDesc.Caption = "v Order by PCNumber v"
Me!cmdOrderPCNumberDesc.SetFocus
Me!cmdOrderPCnumber.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderPCNumberDesc_Click()
'Set PN order in descending order and apply captions
Dim response As Integer
response = basOrderby("PCNumber", "DESC")
Me!cmdOrderPCnumber.Visible = True
Me!cmdOrderPCnumber.Caption = "^ Order by PCNumber ^"
Me!cmdOrderPCnumber.SetFocus
Me!cmdOrderPCNumberDesc.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enabled = True
End Sub

Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearch) Then
ShowRecord_Click
End If
End Sub

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

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

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

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

DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

[MMcCarthy]

This is your problem line I think.

DoCmd.OpenForm "Assets", , , _
"Assets.AssetID=" & "'" & Me.lstSearch.Column(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.RowSource = strSQL
Me!lstSearch.Requery

End Function

Sub ClearCaptions()

'Clear captions of asc and desc symbols

Me!cmdOrderFNameDesc.Caption = "Order by First Name"
Me!cmdOrderFName.Caption = "Order by First Name"
Me!cmdOrderLNameDesc.Caption = "Order by Last Name"
Me!cmdOrderLName.Caption = "Order by Last Name"
Me!cmdOrderPCNumberDesc.Caption = "Order by PCNumber"
Me!cmdOrderPCnumber.Caption = "Order by PCNumber"

End Sub

Private Sub cmdOrderFName_Click()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("AssetFirstName", "asc")
Me!cmdOrderFNameDesc.Visible = True
Me!cmdOrderFNameDesc.Caption = "v Order by First Name v"
Me!cmdOrderFNameDesc.SetFocus
Me!cmdOrderFName.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderFNameDesc_Click()
'Set First Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("AssetFirstName", "DESC")
Me!cmdOrderFName.Visible = True
Me!cmdOrderFName.Caption = "^ Order by First Name ^"
Me!cmdOrderFName.SetFocus
Me!cmdOrderFNameDesc.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderLName_Click()
'Set Last Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("AssetLastName", "asc")
Me!cmdOrderLNameDesc.Visible = True
Me!cmdOrderLNameDesc.Caption = "v Order by First Name v"
Me!cmdOrderLNameDesc.SetFocus
Me!cmdOrderLName.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderLNameDesc_Click()
'Set Last Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("AssetLastName", "DESC")
Me!cmdOrderLName.Visible = True
Me!cmdOrderLName.Caption = "^ Order by Last Name ^"
Me!cmdOrderLName.SetFocus
Me!cmdOrderLNameDesc.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderPCNumber_Click()
'Set PN order in ascending order and apply captions
Dim response As Integer
response = basOrderby("PCNumber", "asc")
Me!cmdOrderPCNumberDesc.Visible = True
Me!cmdOrderPCNumberDesc.Caption = "v Order by PCNumber v"
Me!cmdOrderPCNumberDesc.SetFocus
Me!cmdOrderPCnumber.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub cmdOrderPCNumberDesc_Click()
'Set PN order in descending order and apply captions
Dim response As Integer
response = basOrderby("PCNumber", "DESC")
Me!cmdOrderPCnumber.Visible = True
Me!cmdOrderPCnumber.Caption = "^ Order by PCNumber ^"
Me!cmdOrderPCnumber.SetFocus
Me!cmdOrderPCNumberDesc.Visible = False
Me!lstSearch.SetFocus

End Sub

Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enabled = True
End Sub

Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearch) Then
ShowRecord_Click
End If
End Sub

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

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

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

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

DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub