Combo Box Autopopulation with an import button across databases

[Vita]

A subform/subreport is created by installing a Subform/SubReport Container Control on a form or report. The container control has a Source Object property. This has the name of form or report the container holds.

Yes I have this all already setup.





This is my current issue:

The box labeled heat has a control source of [Heat]

This is the append query the button uses:

[mike60smart]

Can you upload the actual Database?

[Vita]

Can you upload the actual Database?

I cannot. I do not have permission to do so from my employer. It would probably cause confusion anyways since there are roughly 25 tables, 48ish queries, and 15 forms that I am trying to weed through.
I am learning this database myself. Can you tell me what you are looking for and I can try to supply it? Everything else functions as intended. I am only trying to add this feature because it was requested from my employer.

[mike60smart]

Have you set the Link Master / Link Child Fields correctly ?

[June7]

Assign names to controls that will be used for criteria, such as tbxHeat. Then try this code:

CurrentDb.Execute "INSERT INTO [Chemistry Junction](Heat, C) SELECT HEAT, C FROM [Heats-Master1] WHERE Heat='" & Me.tbxHeat & "'"

This way don't have to deal with getting subform path reference correct in query object and code works even if form is not used as subform.

If Heat is a number type field, remove apostrophe delimiters.

If purpose of form is to select a Heat for import to [Chemistry Junction], I don't see need for form/subform arrangement. An unbound form with a combobox listing Heats from [Heats-Master1] should be sufficient. Then after import if you need to do further data entry/edit on records, open a bound form filtered to that Heat.

[Micron]

If you mean actually creating relationships in Access I know of no vids. Have you researched and do you grasp db normalization? Creating relationships depends on that.
Perhaps these will help a bit.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

[Vita]

Assign names to controls that will be used for criteria, such as tbxHeat. Then try this code:

CurrentDb.Execute "INSERT INTO [Chemistry Junction](Heat, C) SELECT HEAT, C FROM [Heats-Master1] WHERE Heat='" & Me.tbxHeat & "'"

This way don't have to deal with getting subform path reference correct in query object and code works even if form is not used as subform.

If Heat is a number type field, remove apostrophe delimiters.

If purpose of form is to select a Heat for import to [Chemistry Junction], I don't see need for form/subform arrangement. An unbound form with a combobox listing Heats from [Heats-Master1] should be sufficient. Then after import if you need to do further data entry/edit on records, open a bound form filtered to that Heat.

The purpose is for the chemistry junction to autopopulate the chemistry data for the matching heat selected from the combobox (Which links to the MetalDB) and then a button to import that data to the WorkBE. To eliminate typos/user error.
Side note: I wanted to skip the junction and have it autopopulate the main form(Lots) but the people who use this also need to be able to manually enter into the same field so a subform with append query seemed the best option.

Also I assume that code is for the append query? Can you explain to me what tbx stands for? it was not on the naming convention site I looked at.
(And maybe break down the changes you think I should make a bit more. I think I get it but would appreciate it being broken down a bit more)
I also don't understand why I need to put "CurrentDb.Execute" If the button is on the current DB.

[Vita]

I believe so but I am not familiar with the terms. How would I check/know?
Edit: They are both set to the [Heat] field. So I believe that is correct.

[Vita]

I will give these a look! I don't mind reading!
I understand normalization. I use it when organizing things but was unaware of the name.
I am going to attempt to follow the normalizations that you have linked but its a bit hard as 2 people before me had their own styles and I have to figure out how everything works. In time hopefully I will have this database under control but right now its a bit like a bad hair day.

[June7]

"tbx" is a naming convention prefix I use. I give controls names different from fields: tbx is for a textbox, cbx a combobox, lbx a listbox. Use whatever you want, if any.

CurrentDb.Execute is what it is. That is command to run an action SQL statement.

Or use DoCmd.RunSQL, but I prefer CurrentDb.Execute.

Do research on both.

Controls used to select a value for criteria should be UNBOUND, otherwise you change data in table. So if you want to use form bound to [Chemistry Junction] with bound controls that users can enter data into table, probably need other controls UNBOUND that can be used just for selecting criteria for the action SQL. Might find this tutorial of interest http://allenbrowne.com/ser-62.html which uses UNBOUND controls to build criteria to filter records on form.

[Vita]

"tbx" is a naming convention prefix I use. I give controls names different from fields: tbx is for a textbox, cbx a combobox, lbx a listbox. Use whatever you want, if any.

CurrentDb.Execute is what it is. That is command to run an action SQL statement.

Or use DoCmd.RunSQL, but I prefer CurrentDb.Execute.

Do research on both.

Controls used to select a value for criteria should be UNBOUND, otherwise you change data in table. So if you want to use form bound to [Chemistry Junction] with bound controls that users can enter data into table, probably need other controls UNBOUND that can be used just for selecting criteria for the action SQL. Might find this tutorial of interest http://allenbrowne.com/ser-62.html which uses UNBOUND controls to build criteria to filter records on form.

So I currently have the combobox inside of ChemJuncF use a regular query (Which I believe is called a lookup. correct me if I'm wrong) to display all the Heats from the MetalDB.
all the chemistry data inside ChemJuncF has its own fields (Ex: [C], [Co], etc) so I guess they are bound?
So you are saying to delete the chemistry data fields inside ChemJuncF so that they are unbound?
The Main form [LOTS] also has these fields, which are bound appropriately. The button was then gonna use the append query to take the autopopulated data and append it to the main table [Lots](I know its the same name as the form but in lowercase but I cant change it without screwing up everything else.) This would allow them to type directly into the main form[Lots] or use the subform[ChemJuncF] to import data from MetalDB.

[Vita]

I also changed the query to import heats from [TblChemJunc] to the main table and it still gave me the primary key error.

It also would not let me use the "CurrentDb.Execute" so this is what I entered. (Note all fields are text but it gave me syntax error with the quotes. probably due to older version of Access)

INSERT INTO [TblChemJunc](Heat, C) SELECT HEAT, C FROM [TblAttHeats] WHERE Heat= Me.FsubCboImHeat

[June7]

Query in post #4 is appending to [Chemistry Junction] table, not [Lots].

Nature of a junction table normally requires records to exist in other two tables first then create a record in junction to associate records from each of the other two tables. Common simplified example is CustomerOrders. Tables of Customers, Products, CustomerOrders. CustomerOrders associates existing customer and product records (one product per order in this case). Adding new customer and/or product on-the-fly during data entry requires code.

If you want to pull data from [Heats-Master1] and append to [Lots], exactly what purpose does [Chemistry Junction] table serve?

More about CurrentDb.Execute. CurrentDb can be substituted with an object variable. But that's a topic for another discussion, not really relevant to the current issue.

Your table names are changing. This is getting more and more confusing. I think without providing a mockup of your database, I cannot help.

You entered that SQL where? It is not valid for a query object nor VBA.

[Vita]

Query in post #4 is appending to [Chemistry Junction] table, not [Lots].

Nature of a junction table normally requires records to exist in other two tables first then create a record in junction to associate records from each of the other two tables. Common simplified example is CustomerOrders. Tables of Customers, Products, CustomerOrders. CustomerOrders associates existing customer and product records (one product per order in this case). Adding new customer and/or product on-the-fly during data entry requires code.

If you want to pull data from [Heats-Master1] and append to [Lots], exactly what purpose does [Chemistry Junction] table serve?

More about CurrentDb.Execute. CurrentDb can be substituted with an object variable. But that's a topic for another discussion, not really relevant to the current issue.

Your table names are changing. This is getting more and more confusing. I think without providing a mockup of your database, I cannot help.

You entered that SQL where? It is not value for a query object nor VBA.

Apologies I changed the table names after your advice.
Heats-Master -> TblMetalDBHeats
Chemistry Junction (Table) -> TblChemJunc
Import Heat Query Button -> QappImHeatBttn
Import Heat Combobox Query -> QlkpImHeatCbo
Combobox for selecting heat(I believe its called a control) -> FsubCboImHeat
Chemistry Junction (form) -> ChemistryJunctionF
Main table -> [Lots] (Lowercase. Can't change)
Main form -> [LOTS] (Uppercase. Can't change)


The junction made sense at an earlier time but I think you are right and I should get rid of it.

I misunderstood where the SQL you posted was intended for so I copied the QappImHeatBttn renaming it to QappImHeatBttn2 and put it in there.

So with your point brought up I think I should delete TblChemJunc and ChemistryJunctionF.
Then I should have a combobox on the form [LOTS] that selects the heat and a button next to it to append the chemistry relating to that selection.

I apologize for making things difficult and thank you very much for your time. I won't make any changes until we agree that sounds like a good idea.

[June7]

Cannot use Me. reference in query objects. It is valid only in VBA. It is shorthand for the form or report name the code sits behind. The code I suggested would be in an event procedure such as a button Click or combobox AfterUpdate.

Still unclear to me how these tables should work together - what data needs to be pulled from where and saved where. Maybe a junction table is not need because data is not in a many-to-many relationship but master and child tables might be needed for a one-to-many relationship. Really should provide sample data even if you can't provide a db file - can build tables directly here in post. Go to the Advanced post editor and you will have some table building tools available. Can also copy/paste from Access table then edit with the table tools.

I don't think it was me who advised to change table naming but I agree with it.