How populate a 3rd form with the value typed into a combobox on a sub-form?

I have a main form that has a subform that has a combobox.  The properties of the combobox are... Limit to List = Yes, Allow Value List Edits = Yes.  And then I have another form, the third form, that opens when the user answers Yes to adding a record.

When that 3rd form opens, I want a field on it to be populated with the value I typed in the combobox on the subform.

Can this be done?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
I forgot to mention, the subform is a continuous form so the value to be populated has to be from the record on the continuous form that is being edited.
0
SheilsCommented:
Short answer is yes it can be done.  And there are a number of ways to do it.

One way would be to run a query to add the new data to the tables relating to the third form. This works best when the third form is still closed but you can add a little bit of extra coding to update the form if it is already opened.

Assuming that changing of combobox value is your trigger, I suggest using the onchange event of your combobox.

The code will be something like

Private Sub ComboBoxName_Change()

Dim lngID as Long
Dim strSQL as String

StrSQL="INSERT INTO TableA (field1, field2, field3) "
StrSQL=StrSQL & "VALUES (" & chr(34) & Me.fleild1 & chr(34) & "," &  chr(34) & "," & chr(34) & Me.fleild2 & chr(34) & "," & chr(34) & "," & Me.fleild3 & ") "

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I'm sure I'm doing something wrong because this isn't working.

Is there a way to do this with TempVars?  Set the value as a temp var and use it to populate the field in the 3rd form?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SheilsCommented:
Yes there is.

On the change event of the combobox you can insert the following codes

In a module declare the variables as global eg:

Global Var1 as string
Global Var2 as Long
Global Var3 as Double

Var1=field1
Var2=field2
Var3=field3

Then you can use some other even to add these value to your third form
0
SheilsCommented:
posting a sample of your db with some dummy data will allow me to better assist you
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Use the NotInList event to do this. The NIL event has a "DataAdded" parameter, which is the value that was not found in the list, and you can insert that into the form.

Sub YourCombo_NotInList(NewData As String, Response As Integer)
  '/ open the second form in dialog mode
  DoCmd.OpenForm "YourFormName", , , , , acDialog, NewData
  Response = acDataErrAdded
End Sub

Now in the Open event of the form:

Me.YourControl = Me.OpenArgs

When the user closes the form, the next line will fire (the "Response" line), which tells Access to requery the combo.

These articles might help you to understand better:

https://msdn.microsoft.com/en-us/library/bb243765(v=office.12).aspx
https://msdn.microsoft.com/en-us/library/bb237500(v=office.12).aspx
0
SteveL13Author Commented:
Scott,

Isn't working.  I have this in the notinlist of the combobox:

Sub cboItemN_NotInList(NewData As String, Response As Integer)
   
   '/ open the second form in dialog mode
   DoCmd.OpenForm "frmWorkOrderItemsAddMode", , , , , acDialog, NewData
   
   Response = acDataErrAdded

End Sub

Open in new window


And this in the onopen event of the 3rd form:

Private Sub Form_Open(Cancel As Integer)

    Me.txtDescription = Me.OpenArgs

    DoCmd.Restore

End Sub

Open in new window


I also tried:  Me.Description = Me.OpenArgs  instead of the field name.  Is the control name.
0
SteveL13Author Commented:
Ok.  I do have the combobox adding the new record to the table.  And I do have the 3rd form opening with NewData in the first field which is perfect.  But when I add data to the other fields on the 3rd form this other data is not being added to the new record created by the combobox.  

??
0
Jeffrey CoachmanMIS LiasonCommented:
Why not just skip the whole, "Add a new record/value from a combobox", step?

...In this case, ...this seems to be more trouble than it is worth...
;-)

Just create the new value with the combo box on the first form, ... then just "Open" the second form, ...then select the newly refreshed value from a combobox in the second form...
(perhaps just needing a "requery" command on the combobox)

For example, on the Customer form,  a customer is not in the combobox list,
Use the NIL event to add the customer, ...
Then just open the Order form to a new record, and select this new customer from a combobox on the order form.

But to be fair, ...lets see if someone posts a solution to your direct question.

Going even deeper...
Many developers just simply do not allow "Adding a record through a combobox".
This is because this logic presumes that every user knows that the record "should" be added.
For example, if a customer does not exist, how would the "Order" data entry person know all the information needed to really validate a customer?
(for example, a user adding a "sound-alike" duplicate customer by mistake)

So quite simply, you could let the process flow normally.
For example,  someone calls up to place an order.
You open the Oder form and see that they are not in the customer dropdown.
You ask them if they are a customer, ..if they say no, ...then you *close* the Order form, and open the Customer form.
Then add them as a customer in the customer form.
Then close the customer form and re-open the order form, and the new customer should now be on the list.
Done, ...simple, ...no complex code is required...

I realize that the automated "add to the list" functionality is slick, ...but it is only practical for the simplest of systems.
Where a small trusted staff would be using the system.
But in larger, more complex systems, ...again, ...sometimes tying to automating all of this is more trouble than it is worth...

But again, ...lets see if an expert can answer you direct question as posted...


JeffCoachman
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
But when I add data to the other fields on the 3rd form this other data is not being added to the new record created by the combobox.  
That's not really what you asked. The NotInList event is used to add a value to the Combobox that raises that event. If your intent is to add an entirely new RECORD, then I'd suspect you're going about this the wrong way.

But your question is too "scatter-shot" to really understand, so you'll have to simplify things for us.

Do you want to add a new record the the COMBO?

Or do you want to add a new record, and include a value typed into the COMBO?
0
SteveL13Author Commented:
I want to type a value in the combo and if the record exists use it as usual.  If it doesn't exist ask the user if the want to add it and if they say Yes, open a form that allows them to enter the details of the new record.  When they close that form, return them to the form with the combo, requery the come with the added record, and go on from there.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Then the NotInList event should work. That's exactly what it's designed for - adding a new record to the Recordsource of the COMBO. It will not add a new record to the Form on which the combo lives, however.

You have to be sure to dot all the I's and cross all the T's with this method. For example, on your 3rd form, you must be sure that you're saving all the data correctly, and that it's being saved to the same recordsource as your combo is using, and if the data is "child" data, that you're properly relating the Child data to the Parent.

You also have to be sure to set the Response value correctly.
0
SheilsCommented:
In your original question you said "When that 3rd form opens, I want a field on it to be populated with the value I typed in the combobox on the subform."

The notinlist codes will add the new data to you combobox row source but not to the third form record source. To do this you still need to run the insert into query which I referred to previously

Private Sub ComboBoxName_Change()

Dim lngID as Long
Dim strSQL as String

StrSQL="INSERT INTO TableA (field1, field2, field3) "
StrSQL=StrSQL & "VALUES (" & chr(34) & Me.fleild1 & chr(34) & "," &  chr(34) & "," & chr(34) & Me.fleild2 & chr(34) & "," & chr(34) & "," & Me.fleild3 & ") "

Currentdb.Execute strSQL

End Sub

I believe that the onchange event occurs after the notinlist (our expert please correct me if this is wrong) so it should work even when the notinlist is triggered.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To do this you still need to run the insert into query which I referred to previously
Or, create a standard Bound Access form. That would add a new record to a table (or tables), but would have no affect on the combo. You still have to be sure to insert values into the appropriate tables to insure the new value shows up in your combo.

The Change event fires at every keystroke, so I'm not sure it would be appropriate for use with this process.
0
SteveL13Author Commented:
To all that offered support...  I found this web site and used solution #2.  I'm sure you all suggested a solution muc hlike this...

https://support.microsoft.com/en-us/kb/197526
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.