compare text box input to data in table

[murray83]

Have the following database attached and it keeps throwing up an error on the update of the input text box

help welcomed and appreciated

edit forgot to add my code soz

If Me.txtInput.Value = [Table1.RefNumber] Then
Me.Received.Value = "Yes"
Me.txtInput.Value = ""
Else
Me.Received.Value = ""

End If

 

[isladogs]

Your code actually says:

If Me.txtInput.Value = [Table[B][COLOR="Red"]2[/COLOR][/B].RefNumber] Then
Me.Received.Value = "Yes"
Me.txtInput.Value = ""
Else
Me.Received.Value = ""

End If

You don't have a Table2 hence the error
Change it to Table1 and all is fine

 

[murray83]

i did and still faults with same message

 

[murray83]

anybody else any ideas as still get same silly error

 

[isladogs]

i did and still faults with same message

It worked when I made that change

 

[murray83]

could you attach your version and yes i know all you did was change one thing

ta

 

[isladogs]

Well that's odd, it didn't work when I tried it again...

However, this code does work:

'check if the ref number typed or scanned in matches in the table
Private Sub txtInput_AfterUpdate()

If Me.txtInput.Value = Me.RefNumber Then
    Me.Received.Value = "Yes"
    Me.ScanTime = Now()
    Me.txtInput.Value = ""
Else
    Me.Received.Value = ""
End If

Me.Refresh

End Sub

However if you re-enter the same scan code twice, it updates the scan time field.
Perhaps you should add code to prevent this...?

Also I moved the Me.txtInput.Value = "" line into the If statement so it only clears if its a correct value
Suggest you add a MsgBox in the Else section to say its an incorrect value

Modified DB attached as requested

 

[missinglinq]

You can't, in VBA code, reference a value in a Table, using

If Me.txtInput.Value = [Table1.RefNumber] Then

that's SQL syntax. If Table1 is the RecordSource of the current Form, then ridders

If Me.txtInput.Value = Me.RefNumber Then

will do it.

If the Table being referenced is not the RecordSource of the current Form, you'd have to use DLookup() to reference/pull the value.

Linq ;0)>