Updating a table

[PC User]

I'm trying to update a record in one table from data in a record from another table using a common field (ChemicalID). I don't get any errors, but the code is not udating the target table. Can someone help?

Public Function UpdateInventory()
'On Error Resume Next
On Error GoTo Whoops
    Dim Db As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Set Db = CurrentDb()
    Set rst1 = Db.OpenRecordset("tblChemicalInventory", dbOpenDynaset) 'From insert data notification
    Set rst2 = Db.OpenRecordset("tblChemicalProperties", dbOpenDynaset) 'From insert data notification

        rst2.FindFirst [ChemicalID] = gintChemicalID
        
            With rst1
            .Edit
                ![CAS] = rst2!CAS
                ![Fire Code Hazard Classes] = rst2![Fire Code Hazard Classes]
                ![Hazardous Material Type] = rst2![Hazardous Material Type]
                ![Physical State] = rst2![Physical State]
                ![Storage Pressure] = rst2![Storage Pressure]
                ![Storage Temperature] = rst2![Storage Temperature]
                ![Units] = rst2![Units]
                ![Storage Container] = rst2![Storage Container]
                ![Fed Hazard Catagory] = rst2![Fed Hazard Catagory]
                ![DOT No] = rst2![DOT No]
                ![Hazard Class/Division] = rst2![Hazard Class/Division]
                ![Usage Purpose] = rst2![Usage Purpose]
                ![NFPA ID Health] = rst2![NFPA ID Health]
                ![NFPA ID Reactivity] = rst2![NFPA ID Reactivity]
                ![NFPA ID Flamability] = rst2![NFPA ID Flamability]
                ![NFPA ID Special Hazard] = rst2![NFPA ID Special Hazard]
            .Update
        End With
rst2.Close
Set rst2 = Nothing
rst1.Close
Set rst1 = Nothing
Db.Close
Set Db = Nothing

OffRamp:
    Exit Function
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
    
End Function

Thanks,
PC

 

[Moniker]

Three things:

1) Just write an update query to accomplish this. Writing queries in code is a great way to get yourself in trouble down the road.

2) For the time being, put a breakpoint on this line:

rst2.FindFirst [ChemicalID] = gintChemicalID

Now, get the value of gintChemicalID and manually search in tblChemicalProperties for this value. Does it exist?

3) By storing the same value in multiple tables, you're breaking the rules of normalization. If you change the value of [Usage Purpose] in one table, then the other table has the wrong value. If these are already lookups, then you can ignore this part, but it doesn't look like they are.

 

[PC User]

You're rignt about normalization. I'm trying to make a quick fix to a db that I've already started adding data. My program manager asked for this change and I'm under deadline pressure. Originally, I had the two tables tblChemicalInventory and tblChemicalProperties. When I wanted to add a chemical to the inventory, I would insert it into the inventory table from the master chemical list tblChemicalProperties. However, if I made a change to the tblChemicalProperties table, I would have to manually change what I had aluredy put into the tblChemicalInventory table.

My manager asked to make this automatic for all chemicals in the inventory; so I tried to make a relationship between the two tables using a common field ChemicalID. However, the data is not displaying on the form even though I set the control source for the appropriate fields to get their data from the tblChemicalProperties table as made available through a joint query between tables.

I hope I explained this clearly. Any suggestions or examples? Unfortunately, my db is too large for posting to show you the issue.

Thanks,
PC

 

[s1police]

It seems that your coding cannot MoveNext....

Is it?!