Have combo box on form save to table on new record subform

[Anthony.DG]

Hi still pretty new with Access.

I want to have the info from my combo boxes and text boxes to carry over to new records (on tbl "New_MainData"(temporary name)) that I create on the subform as to be a little more efficient. (request of the person I'm making this for.)

Form:

and in case it helps, my relationships

Can someone tell me how I could go about doing this or if it's possible? I've been searching on how to do this but it either can't be or I lack the terminology to search for it properly. Thank you in advance.

 

[theDBguy]

Hi. Have you tried setting the Default Value of your Textboxes to refer to the Comboboxes? Just a thought...

 

[June7]

If you want to carry over data entry to next record, use VBA to set each control's DefaultValue property to the value just entered. However, at last one control must receive user input to initiate a new record.

Fairly common topic. For one discussion, review https://www.accessforums.net/showthread.php?t=76444&highlight=defaultvalue

Advise CustomerID should not be in New_MainData. Customer is associated with Job and Job is associated with MainData.

BTW, tonnage is spelled with 2 n's.

 

[Anthony.DG]

If you want to carry over data entry to next record, use VBA to set each control's DefaultValue property to the value just entered. However, at last one control must receive user input to initiate a new record.

Fairly common topic. For one discussion, review

Advise CustomerID should not be in New_MainData. Customer is associated with Job and Job is associated with MainData.

BTW, tonnage is spelled with 2 n's.

The CustomerID is present there because the Job field isnt always required but the CustomerID is. Maybe its still redundant idk.

And just to explain a little further I want the info on the form to carry over with the data entered on the subform to the "New_MainData" table.

That might just be exactly what you trying to tell me how to do. I'm looking into your advise rn and thank you.

 

[theDBguy]

Hi. Did you try what I suggested? Just wondering...

 

[Anthony.DG]

Hi. Did you try what I suggested? Just wondering...

I havent yet. Im not sure what you mean. How would I go about doing that? I do appreciate the help ty btw.

 

[theDBguy]

I havent yet. Im not sure what you mean. How would I go about doing that? I do appreciate the help ty btw.

Go to the Design View of your Form and select the Textbox you want to contain the value from your Combobox and enter something like:


Forms!FormName.ComboboxName


Hope that helps...

 

[June7]

Trying to set subform control DefaultValue with reference to main form control has been tried and I have never seen it work as desired. Subform loads before main form therefore main form controls are not available for reference by subform.

Is main form bound? Why duplicate parent record data into dependent record? Something is wrong with data structure if you are doing that.

 

[theDBguy]

Trying to set subform control DefaultValue with reference to main form control has been tried and I have never seen it work as desired. Subform loads before main form therefore main form controls are not available for reference by subform.

Here's a quick test db... It's not perfect, but should be doable.

 

[Pat Hartman]

Take a look at the picture you drew that shows how the fields map. This is a good reflection of the underlying problem. The table needs to be split into two tables. The "header" data goes in the parent table and that is bound to the parent form and the "repeading" data goes into the child table and that is bound to the subform. You do not want to save duplicate data in multiple rows when with proper design, you can have the data saved only once.

 

[Anthony.DG]

Here's a quick test db... It's not perfect, but should be doable.

This is definitely helping me move forward ty very much. I am wondering though if you could explain a couple of things to me.

Why on event "After Update" you do you use ".Recalc" and not ".Requery"?
What is event "On Got Focus" and why use ".Requery"?

Just trying to understand better.

 

[theDBguy]

This is definitely helping me move forward ty very much. I am wondering though if you could explain a couple of things to me.

Why on event "After Update" you do you use ".Recalc" and not ".Requery"?
What is event "On Got Focus" and why use ".Requery"?

Just trying to understand better.

Hi. Glad to hear the demo is helping you a little bit. The code between the AfterUpdate and GotFocus have slight differences. If you'll notice, in the AfterUpdate event, I am using "Me" only. But in the GotFocus event, I am using the name of the Combobox. So, in the AfterUpdate event, I am trying to "recalculate" the whole form; whereas in the GotFocus event, I am only requerying the Combobox. There is also a difference between Requery and Recalc. I am using Recalc because I am not trying to retrieve any new records from the table, just trying to assign the selected item to the default value in the subform. Hope that makes sense...

 

[Pat Hartman]

@Anthony,
Even though it is not specifically what you asked for, most experts would agree that normalizing the schema so that you don't need to duplicate data is probably a better solution. Let me know if you want to pursue that option. Otherwise, I'll just bow out.