on notinlist error for combo box 1

[installer69]

Another question:

I have a combo box that populates with serial numbers based on a query. It goes through a Me.refresh before the next one can be input. When a duplicate entry is attempted it triggers the notinlist error. You click ok but then you have to delete the shown data before trying again. As we use a barcode scanner is there any way of setting it so that the incorrect entry is highlighted and ready to be typed over? Thanks

I have used the following:

If DLookup("serial", "duplicate_query", "serial ='" & Me.Serial & "'") = Serial Then
MsgBox "This box is already scanned to us"
Cancel = True
Me.Serial.SelStart = 0
Me.Serial.SelLength = Len(Me.Serial)
Else
End If

to do a similar thing on another form but in this case I have no query to refer to just the notinlist function

 

[RickSpr]

I don't see the problem. The combo box also has SelStart and SelLength properties. Just change Me.Serial to Me.<combo box name> and put the code in the NotInList event procedure. You also need to set the 'response' argument to acDataErrContinue or acDataErrDisplay instead of using 'Cancel = True'.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[installer69]

Thanks Rick, I got to this part but I'm not sure where to put the response bit:

Private Sub Serial_NotInList(NewData As String, Response As Integer)

Me.Serial.SelStart = 0
Me.Serial.SelLength = Len(Me.Serial)

End Sub

Using just the above gives the error 'Run time error '94'
invalid use of Null

 

[RickSpr]

Apparently, the text box part of Serial was empty. Try this.

Private Sub Serial_NotInList(NewData As String, Response As Integer)
    Me.Serial.SelStart = 0
    Me.Serial.SelLength = Len(Nz(Me.Serial, ""))
    Response = acDataErrDisplay
End Sub

Rick Sprague
Want the best answers? See faq181-2886
[COLOR=blue]To write a program from scratch, first create the universe.[/color] - Paraphrased from Albert Einstein

 

[installer69]

I was just about to say it didn't work when I realised that as I am still in the testing stage I hadn't enter a 'required' input mask (I haven't got the barcode scanner with me and couldnt be bothered to type in 17 digit made up numbers!!)

Can I set my own text box error message instead of the default notinlist one?

 

[RickSpr]

You can't 'set' it, but you can issue a MsgBox message and change 'acDataErrDisplay' to 'acDataErrContinue' to tell Access not to display its own message.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[installer69]

Thanks Rick.

Here's the final code I used:

Private Sub Serial_NotInList(NewData As String, Response As Integer)
    Me.Serial.SelStart = 0
    Me.Serial.SelLength = Len(Nz(Me.Serial, ""))
    MsgBox "This box is not showing in the warehouse!"
    Response = acDataErrContinue
End Sub

I wish other people would do that bit so that we can see how they solved a problem with you guys help.