Linked Table Append Query Problem.

[dlab85]

Hi,
I am trying to run an append query that adds records to tlbChemicalID, duplicating common information from my form fmqryChemicalInfo1, but increasing the tblChemicalID's PK ChemicalID by autonumber. The problem is ChemicalID is also the FK in tblLocation, via a 1-to-1 relationship, and I need the fields on frmqryChemicalInfo1 to also be appended to tblLocation, based on the PK/FK relationship. I continue to pound my head in frustration on how to add that quantity of records to both tblChemicalID and tblLocation; I even tried running two append queries using information relevant to tblChemicalID and tblLocation. But as my frustration builds, I also wonder if I even need tblChemicalID and tlbLocation to have a 1-to-1 relationship, when I could just store the few fields from tblLocation in tblChemicalID. If anyone has ideas on the query suggestion, or after having viewed my hopefully successful attempt at attaching a zip file, if someone agrees or disagrees on removing the relationship and solving my problem entirely ...I am nearing the latter suggestion anyhow! Thanks for the helpl

[June7]

You have separate tblLocation because not every record in tblChemicalID will have location data? If they will then definitely should just be one table (as long as not more than 255 fields). If they won't then can still be one table and tolerate few empty fields. It is a balancing act between normalization and ease of data entry/output.

Why do you need append for tblChemicalID anyway? Are you duplicating data between tables? (Have not looked at your db yet).

[dlab85]

June7,
Thanks for the speedy reply. I am going to just combine my fields from tblLocation in to tblChemicalID; I can't originally recall why I thought it was a good idea to split the information, because every ChemicalID will have only 1 Location in tblLocation.
As to the appending, see the image below, or look at frmLogIn in my database for the layout:

So I have it set up where a user uses the first Catalog Number combo box (unbound, control source set to a query to pull in all catalog numbers) to select the catalog number of the chemical they are logging in after receiving. This "pre-fills" the fields on the left, allowing the user to see the relevant information regarding that catalog number. Then the user starts filling in the fields on the right, starting with Received Date. When they fill in the fields, the field ChemicalID autogenerates the next PK ChemicalID for tblChemicalID. However, there are instances where we receive multiple bottles, all having the same information on the left and right side of the log in form. So the user would put the Quantity, and I am trying to use an append query to add all the same information in the fields, but change the only variable field of ChemicalID, and then put that into tblChemicalID. I succeeded in creating an Append query that does this, however, it is taking the quantity and adding it to the displayed ChemicalID on the log in form. So if I received 5 bottles of the chemical, and the chemicalID starts with 50, if I put 5 as the quantity, it actually adds 6 samples to the tblChemicalID, so I would have to make sure criteria box states "How many additional containers" rather than "How many containers total", and adding 4 more records, in addition to the one visible on the log in form. Make sense? Just a minor hiccup that I am trying to figure out. Thanks!

[June7]

Rats! You are using only macros. I don't use macros, only VBA.

So the issue of adding records to tblLocation is eliminated by combining tables. Should this also apply to tblVirologyChemicalInfo which also has a 1-to-1 relationship?

Your idea for the quantity entry sounds reasonable.

Really have too many form levels for the login form. The controls on the first (main) form can be in the header and footer of frmqryChemicalInfo. Then by eliminating the Location and Virology forms, gets down to 2 levels instead of 4.

[dlab85]

Yes, I also combined tblVirologyChemicalInfo into tblChemicalID. I redid the main form (frmLogIn) with the screen shot from post #3, where I have an unbound form, with unbound control of combo box "Catalog Number", and then a subform of tblChemicalInfo, which also houses a subform of tblChemicalID, thereby reaching the 2 levels (I believe) that you were suggesting.
I thought the append query was a reasonable idea, I just wish I could figure out how to make the query act the way I want! . I will have to keep plugging away and searching as to what my solution should be, or see if my users are going to be confused by adding on how many containers, thereby excluding one from their total count, since the record on the form is counted as record 1. Thanks for your input.