Hi, I wonder if anyone can help. This worked in Access 2003 but not in Access 2007, 2010 or 2013.
I have created a text box for people to enter search criteria, e.g. customer surname. The results appear in a list box underneath. When I select the record it should open up the customer's details but in Access 2013 it produces an error "The value you entered isn't valid for this field". Can anyone spot why the code no longer works?
The first sub-routine pulls the search data into a Row Source:
Private Sub txtCustomerSearch_Change()
On Error GoTo Err_txtCustomerSearch_Change Dim strSource As String strSource =
"SELECT DistinctRow CustomerNo, [End Date], Surname, FirstName, Address1,
Address2,Town, HomeTel,Mobile" & _ " FROM CUSTOMER " & _ "Where CustomerNo Like
'*" & Me.txtCustomerSearch.Text & "*' " _ & "Or [End Date] Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Surname Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or FirstName Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Address1 Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Address2 Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Town Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or HomeTel Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Mobile Like '*" &
Me.txtCustomerSearch.Text & "*' "
Me.lstSearchResults.RowSource = strSource
Exit_txtCustomerSearch_Change: Exit Sub Err_txtCustomerSearch_Change: MsgBox
Err.Number & " " & Err.Description Resume Exit_txtCustomerSearch_Change
End Sub
'Sub routine responding to the On Click event. When the result row is clicked 'it should open the customer form and subforms for the customer number selected. This is when I get the error -
Private Sub lstSearchResults_Click() 'List box.
Dim intRecord As Integer
intRecord = Me!lstSearchResults.Column(0)
DoCmd.Close
DoCmd.OpenForm "CUSTOMER"
Forms![Customer].SetFocus
DoCmd.GoToControl "CustomerNo"
DoCmd.FindRecord intRecord
Exit Sub
End Sub
Hope someone can help or I will have to re-write it another way.
I have created a text box for people to enter search criteria, e.g. customer surname. The results appear in a list box underneath. When I select the record it should open up the customer's details but in Access 2013 it produces an error "The value you entered isn't valid for this field". Can anyone spot why the code no longer works?
The first sub-routine pulls the search data into a Row Source:
Private Sub txtCustomerSearch_Change()
On Error GoTo Err_txtCustomerSearch_Change Dim strSource As String strSource =
"SELECT DistinctRow CustomerNo, [End Date], Surname, FirstName, Address1,
Address2,Town, HomeTel,Mobile" & _ " FROM CUSTOMER " & _ "Where CustomerNo Like
'*" & Me.txtCustomerSearch.Text & "*' " _ & "Or [End Date] Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Surname Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or FirstName Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Address1 Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Address2 Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Town Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or HomeTel Like '*" &
Me.txtCustomerSearch.Text & "*' " _ & "Or Mobile Like '*" &
Me.txtCustomerSearch.Text & "*' "
Me.lstSearchResults.RowSource = strSource
Exit_txtCustomerSearch_Change: Exit Sub Err_txtCustomerSearch_Change: MsgBox
Err.Number & " " & Err.Description Resume Exit_txtCustomerSearch_Change
End Sub
'Sub routine responding to the On Click event. When the result row is clicked 'it should open the customer form and subforms for the customer number selected. This is when I get the error -
Private Sub lstSearchResults_Click() 'List box.
Dim intRecord As Integer
intRecord = Me!lstSearchResults.Column(0)
DoCmd.Close
DoCmd.OpenForm "CUSTOMER"
Forms![Customer].SetFocus
DoCmd.GoToControl "CustomerNo"
DoCmd.FindRecord intRecord
Exit Sub
End Sub
Hope someone can help or I will have to re-write it another way.
The value you entered isn't valid for this field
