I wonder if anyone can help. I don’t know VBA and only have managed with what I have found on this forum.
My Database is related to cattle and samples being tested in a lab. In my order form I have a Combo Box that uses the Not in List Event to check if that Farmers (372 number) is in the list and if not then open a new customer form to fill in. All this is working fine.
I have created a form with 2 text boxes and used the following code to convert the scanned number into the correct 372 number =iif(Left([ScannedNo],3)="372",(left([ScannedNo],10)),("372" &LEFT([ScannedNo],7)))
This works – I have then tried to pass the “answer” for example 3722241765 into the ComboBox on the order form. This I cannot do. I am able to pass the number into a TextBox but not the ComboBox - I don't know if this is even possible?
Any help would be appreciated.
My Database is related to cattle and samples being tested in a lab. In my order form I have a Combo Box that uses the Not in List Event to check if that Farmers (372 number) is in the list and if not then open a new customer form to fill in. All this is working fine.
My problem now is my client does not want staff typing in the 372 number (example 3722241765) due to human error. When the samples arrive, there is a scan code on them, but different manufacturers of the samples show these differently. One version would look like this 37222417651234 – the last 4 digits relates to the calf and the first 3 digits are always 372, and next 7 digits relate to the herd/farmer. Another manufacture sends the code like this 22417651234 – the 372 is missing from the beginning – I need to add the 372 and remove the 1234 to get the actual number 3722241765.Private Sub cbo372No_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("The 372 No " & Chr(34) & NewData & _
Chr(34) & " is not currently in Database." & vbCrLf & _
"Would you like to add New Customer now?" _
, vbQuestion + vbYesNo, "Invalid: Please check you entered a Valid Number ")
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmNewCustomer", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
I have created a form with 2 text boxes and used the following code to convert the scanned number into the correct 372 number =iif(Left([ScannedNo],3)="372",(left([ScannedNo],10)),("372" &LEFT([ScannedNo],7)))
This works – I have then tried to pass the “answer” for example 3722241765 into the ComboBox on the order form. This I cannot do. I am able to pass the number into a TextBox but not the ComboBox - I don't know if this is even possible?
Any help would be appreciated.