LP,

I'm not that familiar with DAO (usually use ADO though I know there are some
advantages to using DAO with Access).

In ADO (same in DAO?) you can get the value of the id field (if it's an
autonumber) by reading it back from the recordset after running an insert.

So, if you had a table:
id (autonumber)
otherfield (eg. string)

you can do something like:

'*******************
dim pk

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)
rs1.AddNew
rs1.Fields("otherfield").value="Blah"
rs1.Update
pk = rs1.Fields("id").value 'should now be populated with the "autonumber"
key
'*******************

Reading the key by running another select on the table is generally not a
great idea, since you can't guarantee another user hasn't inserted a record
and you're then reading *their* key value. I'm not certain this applies in
this case since I'm not that clear on record vs. table locking in Access,
but you can see it would be something best avoided....

Tim




"Little Penny" wrote in message
...
Ok I have started from scratch by rebuilding the tables and code. I
found some information about the (Dmax) function to get the primary
key to add records to the related tables. However I use the function:

pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")


I get an error:

Complie error:
Sub or Function not defined

But if I assgn pk to the next primary key value

pk = 25


The code works find. I'm stuck any help would be appricated


My New code is below

Thanks - Little Penny


****************Start Code******************


Sub AccessUpdate()



Dim db As Database, rs1 As Recordset, r As Long, ur As Long
Dim rs2 As Recordset, rs3 As Recordset
Dim pk As Long



MsgBox "Running Update!!!", vbExclamation + vbInformation, "Running
Update!!!"



Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb")

' open the database
Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)
Set rs2 = db.OpenRecordset("tbl_JobGrandTotals", dbOpenTable)
Set rs3 = db.OpenRecordset("tbl_JobBatches", dbOpenTable)

ur = Range("K2").Value

With rs1

.AddNew ' create a new record
' add values to each field in the record

.Fields("JobName") = Range("D2").Value
.Fields("IPWJobName") = Range("D3").Value
.Fields("JobType") = Range("D4").Value
.Fields("IPWNumber") = Range("H3").Value
.Fields("Region") = Range("H4").Value
.Fields("Shift") = Range("J2").Value
.Fields("Machine") = Range("J3").Value
.Fields("InsertDate") = Range("N2").Value
.Fields("MailDate") = Range("N3").Value
.Fields("TradeDate") = Range("N4").Value
.Fields("Comments1") = Range("D22").Value
.Fields("Comments2") = Range("B23").Value
.Update ' stores the new record

End With



pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData") + 1

'pk = 25 ' next key value

With rs2




.AddNew ' create a new record
' add values to each field in the record

.Fields("OpLogJobDataID") = pk
'.Fields("OpLogJobDataID") = ur
.Fields("TotalM_Count") = Range("G20").Value
.Fields("TotalRetypes") = Range("H20").Value
.Fields("TotalMissPull") = Range("I20").Value
.Fields("GrandTotalEnv") = Range("J20").Value
.Fields("ShipVendor") = Range("M20").Value
.Fields("ShipNumber") = Range("N20").Value
.Update ' stores the new record

End With


With rs3

r = 9

Do While r <= 18


.AddNew

.Fields("OpLogJobDataID") = pk
.Fields("BatchNumber") = Range("B" & r).Value
.Fields("BatchStrSeq") = Range("C" & r).Value
.Fields("BatchEndseq") = Range("D" & r).Value
.Fields("BatchTotalenv") = Range("F" & r).Value
.Fields("BatchMeterCt") = Range("G" & r).Value
.Fields("BatchRetypes") = Range("H" & r).Value
.Fields("BatchMiss_Pull") = Range("I" & r).Value
.Fields("BatchEnvTotal") = Range("J" & r).Value
.Fields("BatchOPname") = Range("K" & r).Value
.Fields("BatchOPid") = Range("M" & r).Value
.Fields("BatchQCverify") = Range("N" & r).Value
.Fields("BatchQCDtTime") = Range("O" & r).Value
.Update ' stores the new record

r = r + 1
If r = 19 Then Exit Do
Loop




End With


rs1.Close
rs2.Close
rs3.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
db.Close
Set db = Nothing

End Sub


****************End Code******************

















On Sat, 05 Dec 2009 21:03:14 -0500, Little Penny
wrote:



I'm somewhat familiar with updating from Excel to Access via VBA. How
can I update to multiple tables in Access that have a one to many
relationship using VBA. Table are linked via key.

tbl_One is one Many with tbl_Two via Foreign Key
tbl_One is one Many with tbl_Three via Foreign Key


Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb")

' open the database
Set rs = db.OpenRecordset("tbl_One", dbOpenTable)

With rs

.AddNew ' create a new record
' add values to each field in the record
.Fields("TableFieldName2") = Range("A1").Value
.Fields("TableFieldName2") = Range("B1").Value
.Fields("TableFieldName2") = Range("C1").Value

Here I what to updated data to tbl_Two which linked to tbl_One

Here I what to updated data to tbl_Three which linked to tbl_One


.Update ' stores the new record
End With


Thanks


Little Penny