The value you entered isn't valid for this field

[CarolThompson]

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.

 

[JHB]

Could you please post your code using the Code tags - click the "#", because what you show is a mess.
Place a Debug.Print strSource in your code, then copy the result from the Immediate window into a new query, then you can better find out which field(s) get a wrong value type, (I'm guessing it is the date field, [End Date]).

...
Debug.Print strSource
Me.lstSearchResults.RowSource = strSource
..

 

[gemma-the-husky]

I think you might find that later versions return a string from a cbobox or listbox, rather than a number - or maybe more correctly that they do not automatically resolve the result to the correct type.

you may find this sort of thing fixes it

intRecord = clng(Me!lstSearchResults.Column(0))

 

[CarolThompson]

Could you please post your code using the Code tags - click the "#", because what you show is a mess.
Place a Debug.Print strSource in your code, then copy the result from the Immediate window into a new query, then you can better find out which field(s) get a wrong value type, (I'm guessing it is the date field, [End Date]).

...
Debug.Print strSource
Me.lstSearchResults.RowSource = strSource
..

Thank you so much for your reply. It is the first time I've posted so I just cut and pasted. I've used the # this time. I removed the date but still received the above error message. Here is the code again:

Search text box -

Private Sub txtCustomerSearch_Change()
On Error GoTo Err_txtCustomerSearch_Change
Dim strSource As String
strSource = "SELECT 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 & "*' "
Debug.Print strSource
Me.lstSearchResults.RowSource = strSource
Exit_txtCustomerSearch_Change: Exit Sub
Err_txtCustomerSearch_Change: MsgBox Err.Number & " " & Err.Description
Resume Exit_txtCustomerSearch_Change
End Sub

List box where the results appear and are selected:

Private Sub lstSearchResults_Click()
Dim intRecord As Long
intRecord = Me!lstSearchResults.Column(0)

DoCmd.Close

DoCmd.OpenForm "CUSTOMER"
Forms![Customer].SetFocus
DoCmd.GoToControl "CustomerNo"
DoCmd.FindRecord intRecord

Exit Sub

End Sub

 

[CarolThompson]

I think you might find that later versions return a string from a cbobox or listbox, rather than a number - or maybe more correctly that they do not automatically resolve the result to the correct type.

you may find this sort of thing fixes it

intRecord = clng(Me!lstSearchResults.Column(0))

Thank you very much for your suggestion gemma-the-husky. I changed the code to the above, but unfortunately that didn't work. I also tried it with cInt just to see if it needed to match the field in the table.

 

[CarolThompson]

Could you please post your code using the Code tags - click the "#", because what you show is a mess.
Place a Debug.Print strSource in your code, then copy the result from the Immediate window into a new query, then you can better find out which field(s) get a wrong value type, (I'm guessing it is the date field, [End Date]).

...
Debug.Print strSource
Me.lstSearchResults.RowSource = strSource
..

Hi JHB
I've found out what was wrong. The bound column on the list box was set to 1 (I think by default). I set it to 0 and it works fine now. Thank you for your help, your advise has been most helpful.

 

[CarolThompson]

Thank you very much for your suggestion gemma-the-husky. I changed the code to the above, but unfortunately that didn't work. I also tried it with cInt just to see if it needed to match the field in the table.

Hi Jemma-The-Husky
I've found out what was wrong. The bound column on the list box was set to 1 (I think by default). I set it to 0 and it works fine now. Thank you for your help, your advise has been most helpful and it made me search a little wider for more answers.