Add Quantity of Chemicals on Form

[dlab85]

Hi,
I have the following form for logging chemicals into a chemical database. I also have the following tables and relationships for reference.



The "Catalog Number:" combo box at the top allows users to select a previously logged in chemical and fill in the Chemical Info fills (the left most fields), because that information is the same for every catalog number for a chemical. Then there is the "Chemical ID:" field located in the middle portion, which is the PK of tblChemicalID, which is autogenerated when the user enters the quantity, received date, etc fields on the form. I would like to repeat all of the middle fields and right side fields, EXCEPT CREATE a new ChemicalID, depending on the value entered into the "Quantity" field. SO, a user chooses the catalog number for Isopropyl Alcohol, starts filling in the middle and right side fields, including 5 for the Quantity (because there are 5 bottles), and I need Isopropyl Alcohol added to tblChemicalID 5 times, but a new ChemicalID for each of the records. It would make chemical database entry FAR easier for my personnel. Thanks in advance.

[burrina]

This sounds like a case for a subform with multiple records using the same ID. Example: you have a payroll module that needs to pay multiple employees for the same job. You use a subform and link by ID. Hope this suggestion helps.

[dlab85]

Burrina and others,
Ignore the attached image, and instead refer to the embedded Log In form image, for clarification, this is how the log in form is set up:
1. It's an unbound form, with catalog Number at the top as an unbound combo box.
2. All of the fields below is actually a subform with the following arrangement=
A.The left hand side information are fields from tblChemicalInfo, which is the main form in this "subform"
B.The information in the middle and right hand side is actually a subform of the information from tblChemicalID (and tblLocation)

So I already have a form/subform relationship with tblChemicalInfo and tblChemicalID respectively, which is how I am able to have the left hand fields fill in the information for a user when a catalog number is selected. BUT, I would like to have the subform information (the received date, expiration date, room location, etc) apply, to say a Quantity of 5 chemicals, but each of those five chemicals receives a separate ChemicalID. Would your suggestion be another subform within the current subform? I am not sure how I would accomplish that.

[burrina]

One idea would be to use code like this and have it increment your value: This would be used on the before insert event.
MyCounter = Nz(DMax("MyCounter", "YourTableNameHere","PrimaryKey=" & Forms!YourFormName!PrimaryKey)) + 1

[dlab85]

I am trying to get the quantity option to work with an append query. The problem I am having is that my append query is based on the tblChemicalID and making the quantity to every sample I have in the table, rather than just the data I am entering on the form. Is there a way to focus the query on the form rather than the table? Or do I have to query out the specific record I want to make that many quantities for?

[burrina]

[Forms]![frmEvent]![EventID] Something like this in your query grid. Of course replace with your criteria. But, this makes it specific to your form for the append. Hope this helps!

[dlab85]

I am not having any luck on where to place information to point the append query in to the form? Also, my field ChemicalID is an autonumber PK generated when a new chemical is logged in, and when I run the append query it starts autonumbering +1 from the number on the form (so instead of say ChemicalID 15, the append query starts at 16), causing a continual skipping of one chemicalID everytime a new chemical is logged in.

[burrina]

Back to the original issue, counting with a new value! Is this NOT what you want, a unique ID for all of those 5 records? If so, use the DMax function on your form! Of course no doubt you could accomplish the same thing with another subform and create a Fake Key to link and just use your new Primary Key as a counter!

[dlab85]

I have become mentally entangled in our discussion here, so let's step back and start over. On my log in form, there is the field Chemical ID, which is autogenerated for a specific chemical (i.e. Isopropyl), when a user starts entering the received date, etc. Once the user has all the information entered for that specific record, they would put a quantity into the quantity box, i.e. 5. I want ALL of the information they entered to be repeated 5 times, with a new Chemical ID for each of those 5 records (i.e 15, 16, 17, 18, 19). I have been attempting to accomplish this via an append query, due to it being the simpliest for me to use. Now, I have the Chemical ID currently being generated on the log in form, but when the append query runs, it assigns the Chemical ID's one ahead of the value on the form, so instead of 15, 16, 17, 18, 19 it enters 16, 17, 18, 19, 20. If I use the DMax+1 function, that should take the generated ID from the log in form, and add one to that, maintaining the appropriate numbering sequence, correct? Secondly, will the next generated Chemical ID be affected by the DMax in the append query, such that instead of generating 20 for the next sample logged in, it would generate 16 because the database thinks that is next number? Or, since the query is appending the data into the tblChemicalID, the database will insert 20? Lastly, what is the DMax +1 function, and I put that in a separate column that is acting on the ChemicalID field? Thanks for bearing with me!