Trying to get vba code to enter in data depening on the value of another field

[gmann]

Trying to get vba code to enter in repetive data.

Basically there is two tables that this deals with, Chemical and Chemical Details Table.

Chemical has this field called Method, which determines what chemicals are tested, hence the parameters field in chemical details, which has a record for each chemical.

Since the methods always use the same chemicals, i want the vba code to generate the records with the paramaters automatically depending on which method is chosen.

The problem is getting the data entered in the ChemDetails tables so it corresponds with Chemical Table, meaning the primary key doesn't match up, Chemical_DataID, which is a autonumber.

I tried setting the Chemical_DataID in the details table to what the Chemical table would have, but that doesn't work, it doesn't seem to let u change the number.

Any Ideas?
Thanks Greg

Private Sub Method_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tempID As Long
Dim rstDet As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chemical", dbOpenDynaset)
Set rstD = dbs.OpenRecordset("Chemical_Details", dbOpenDynaset)

rst.MoveLast
rst.AddNew
tempID = rst![Chemical_DataID] - 1
If rst![Method] = "EPA 602" Then
rst.MoveLast
rstD.AddNew
rstD![Chemical_DataID] = tempID
rstD!Parameter = "t butylmethylether"
rstD.Update
rstD.AddNew
rstD!Parameter = "Benzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Toluene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Ethylbenzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "m + p Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "o-Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
End If
End Sub

 

[rockman]

First thought is to make sure rstD![Chemical_DataID] is declared as a long integer field and not autonumber.

Next if that is not the answer I would eliminate any relationships involving these two fields and see if that helps.

Let us know what works.

 

[Alexandre]

To my opinion, you should not do that this way. You should just create a reference table listing the correspondances. You've got a list of chemicals on one hand, and a list of methods on the other hand. And you have 'business rules' specifying how your chemicals and methods are related. This calls for a table, no code.
If one chemical always relates to one method and vice-versa, you only need 1 table:

Tbl
- ID (autonumber PK)
- Chemical
- Method

You need two tables with a one to many relationship if one method can correspond to various chemicals (OR the contrary):
Ex (case One chemical can correspond to Many chemicals):
Tbl1
- ID_Chemical (autonumber PK)
- Chemical
- ID_Method (foreign key)

Tbl2
- ID_Method (primarykey)
- Method


You need three tables and two one to many relationships if one method can be related to various chemicals AND on chemical to various talbes:
TblChemicals
- ID_Chemical(primarykey)
- Chemical

TblMethods
- ID_Method (primarykey)
- Method

Tbl3Correspondances
- ID_Method (primarykey)
- ID_Chemical(primarykey)
(+ eventually other field(s) that chracterise/describe the association)

Relationships:
TblChemicals: One___Many :Tbl3Correspondances: Many____One :TblMethods



Then you do not need neither code, nor to strore ChemicalIDs/MethodIDs ever again: Instead whenever you want to retrieve/display one chemicals related to one method (or the contrary) ou just use a query (or Dlookup, etc.).


Hope this helps

 

[gmann]

First rstD.ChemicalDataID is a Long

Secondly about making a table seperate for methods and chemicals that would be ideal if there wasn't like 2000 chemicals and 50 some odd methods(tests), which would have a lot of repetive data because many of the test use some of the same chemicalls.
Thanks for the ideas
Still searching for an easy way to do this, without redesigning the DB.

Greg

 

[Alexandre]

I don't understand, gmann
You seem ready to CODE these correspondances anynway. So why not make (a) table(s). Seems to be less work and far more flexible to update.
Am I missing something?

 

[gmann]

Alright so I created another table called MethodChemical, it has two fields method and parameter. Ok so how do i get it to automatically add records. The whole idea is so people who are inputing data don't have to add these chemicals. The thing is that each paramater has other fields corresponding to it like result and limit, etc., because of this the parametes are records not collumms.

Greg

 

[Alexandre]

I suggest that you zip and send me your DB so I have a clearer idea of what you are dealing with. Alternatively, attach an image of your relationships to a post. Also, provide a concrete complete example of what ou are after.

 

[gmann]

Hey Alexandre,

I attached an image of the related relationships. The tables that it deals with are Chemical, Chemical Details, and MethodChemical. Basically what i want is when the method entered in the chemical table, new records are created in the chemical detail table based on the MethodChemical table. Sorry i can't send u the db, hopefully the relationship will be enough for u to understand.

Thanks For All your Help
Greg

 

[gmann]

The main problem i'm having is referencing the newly created record, if I use .Last its the last record before the newly created one. Do u know how i can reference the new record?

Thanks Greg

 

[gmann]

Nermind Alex

I got it to work, just needed to use
DoCmd.RunCommand acCmdSaveRecord

Thanks for your help