I posted a reply to one of your other posts outlining how you could do this.
Tim


'%%%%%%%%%%%%%%%%%%%%%%%%%%
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 be populated with the "autonumber" key
'*******************






"Little Penny" wrote in message
...

Understood there will only be one. If I want to chanage to handle
multi users in the future what should I read up on?



On Thu, 10 Dec 2009 20:36:47 -0800, "Tim Williams"
wrote:

No problem, but remember this is *not* a good way to get the key for a
just-inserted record unless there's only ever one user accessing the
database at any time....

Tim

"Little Penny" wrote in message
. ..
Thanks Tim that did it.....




On Wed, 9 Dec 2009 19:22:52 -0800, "Tim Williams"
wrote:

Set rs1 = db.OpenRecordset( _
"select max(OpLogJobDataID) from tbl_OperatorLogJobData")

pk = rs1(0).value

http://www.databasedev.co.uk/access_max_function.html

Tim



"Little Penny" wrote in message
m...


Sorry typo




Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

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

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID field in
tbl_OperatorLogJobData table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub










On Wed, 09 Dec 2009 08:11:35 -0500, Little Penny
wrote:




Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?




tbl_OperatorLogJobData is a table in my access database and
OpLogJobDataID is the primary key field.


My code

Sub GetMaxKey()

Dim db As Database
Dim rs1 As Recordset
Dim pk As Long

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

Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable)

'Get max key in from OpLogJobDataID table
pk = Application.WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])

rs1.Close

Set rs1 = Nothing

db.Close
Set db = Nothing

Range("A1").Value = pk
End Sub









On Wed, 09 Dec 2009 17:29:29 +1300, Rob van Gelder
wrote:


Check that you have named ranges called tbl_OperatorLogJobData and
OpLogJobDataID?

Cheers,
Rob



Little Penny wrote:
Thanks for your reply. I tried the need line of code and now I'm
getting

Runtime error 424

Object required

Any idea?


Little Penny












On Tue, 08 Dec 2009 19:37:54 +1300, Rob van Gelder
wrote:

You need to prefix it with WorksheetFunction.

WorksheetFunction.DMax(..., ..., ...)

You'll note that the function requires 3 arguments, not 2 as
you've
supplied.

For example:
pk = WorksheetFunction.DMax([tbl_OperatorLogJobData],
"OpLogJobDataID", [OpLogJobDataID])


Cheers,
Rob


Little Penny wrote:
Can any one help me determine why I'm getting a compile error
when
using the dmax function?


Dim pk As Long


The line of code
pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData")



Do I need to install a reference or add a public function?


Any help would be appreciated