Passing Value from one form to ComboBox in another (1 Viewer)

lucylyn

New member
Local time
Today, 19:02
Joined
Jul 21, 2018
Messages
6
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.


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
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.


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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:02
Joined
Jan 23, 2006
Messages
15,364
You may find this video tutorial interesting a tutorial on combo on form Edit List Item

I would look at bringing incoming codes to a standard.
How many different formats of the incoming "code" do you have?
Do the people entering data know which "codes" have to be manipulated to make them conform to a "standard code"?

If typeAcode then do process A..
If typeBcode then do process B..

where each process manipulates incoming code to produce a standard code. And a standard code is whatever you/your organization decides.

Good luck with your project.
 

lucylyn

New member
Local time
Today, 19:02
Joined
Jul 21, 2018
Messages
6
Thanks for your reply and the link to the video. This is something I did not know and is certainly useful, however I'm not sure it will help with this problem.


I have a combo box for the 372 number which looks up the customer and fills in all the other boxes like name, category etc. Since writing my first post I have now moved the Converted Number into my order form (see below). What I am trying to achieve is to automatically or with a click of a button copy the number from Converted 372 Number to 372 Number combo box






This is the form in Design View
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:02
Joined
Jan 23, 2006
Messages
15,364
Why does it have to be a combo?
It seems that once you have converted a number, you could record that with your Customer table in some manner. Then that number would be available in future from a combo based on Customer info. I don't know all details of your set up so may be missing something basic.

The video was just showing some automation from M$oft re Not in list.

Can you post a copy of the database with a few records (zip format) to show the issue?
 
Last edited:

lucylyn

New member
Local time
Today, 19:02
Joined
Jul 21, 2018
Messages
6
I will need a bit of time to get the database ready as it has lots of entries. I will get that done shortly.



To answer your question
Why does it have to be a combo?
It seems that once you have converted a number, you could record that with your Customer table in some manner.
The reason I have the Combo Box is that the 372 number ALREADY EXISTS in the database stored in the Customer Table. When a new order is being created we need to look up the customer and that is done (up to now) by either entering their 372 number or their Herd Number. These are typed in to the combo box and once entered it looks for that customer and if there populates all the information, if not in the combo box list then as mentioned earlier I have used the NotInList event to open a new customer form so the customer can be added so we can then create the order.


My problem now is they are using a scanned number but unfortunately it is NOT the 372 number but a variation of it which I have to calculate into the 372 number first before I can look it up.
 

lucylyn

New member
Local time
Today, 19:02
Joined
Jul 21, 2018
Messages
6
Sorry for not coming back, my customer has now said there might be another number I need to take into consideration which changes things. However the person with the answer is on holidays. I will have to put this on hold for now.
 

Users who are viewing this thread

Top Bottom