DAO Querydef - Returning values

  • Thread starter Thread starter Chris Strug
  • Start date Start date
C

Chris Strug

Hi,

Its been a while since I worked with DAO (its all ADO now...) so please
excuse me if this is a daft question...

I'm after an efficient way to insert a row into a local table in an unbound
form and return the ID (autonumber) of the new record.

How on earth do I do it?

I've been looking for samples but seem to be unable to get anything working.

I'm looking at using a querydef but I'm unsure as to how to achieve it.

For example,

Dim DAOqd as dao.querydef

set DAOqd = new DAO.querydef

And of course assuming that I do get it working, I'm not sure how to return
the ID of the newly inserted field.

I realise that this is a bit vague for a simple reply, but I'd be most
grateful if anyone could point me in the right direction / provide a link /
etc.

Any and all assistance is gratefully received.

Thanks

Chris.
 
Chris Strug said:
Hi,

Its been a while since I worked with DAO (its all ADO now...) so please
excuse me if this is a daft question...

I'm after an efficient way to insert a row into a local table in an unbound
form and return the ID (autonumber) of the new record.

How on earth do I do it?

I've been looking for samples but seem to be unable to get anything working.

I'm looking at using a querydef but I'm unsure as to how to achieve it.

For example,

Dim DAOqd as dao.querydef

set DAOqd = new DAO.querydef

And of course assuming that I do get it working, I'm not sure how to return
the ID of the newly inserted field.

I realise that this is a bit vague for a simple reply, but I'd be most
grateful if anyone could point me in the right direction / provide a link /
etc.

Any and all assistance is gratefully received.

Thanks

Chris.
Click to expand...

Open a recordset on the table (use the dbAppendOnly option so it doesn't
bother returning records), and use the AddNew and Update methods to write
the new record. Then, before you destroy the recordset, you can get the
autonumber value from the appropriate field in the recordset.
 
If there is a unique index on another field or combination of fields other
than the AutoNumber field, you can simply open a recordset using those
fields in the criteria ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngNewID As Long

Set db = CurrentDb
'code to insert record here ...

'Unique index on combination of 'FirstField' and 'SecondField' ...
Set rst = db.OpenRecordset("SELECT IDField FROM TableName WHERE FirstField =
" & Me!FirstControl & " AND SecondField = " & Me!SecondControl)
lngNewID = rst("IDField")
rst.Close

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Chris Strug said:
Hi,

Its been a while since I worked with DAO (its all ADO now...) so please
excuse me if this is a daft question...

I'm after an efficient way to insert a row into a local table in an
unbound
form and return the ID (autonumber) of the new record.

How on earth do I do it?

I've been looking for samples but seem to be unable to get anything
working.

I'm looking at using a querydef but I'm unsure as to how to achieve it.

For example,

Dim DAOqd as dao.querydef

set DAOqd = new DAO.querydef

And of course assuming that I do get it working, I'm not sure how to
return
the ID of the newly inserted field.

I realise that this is a bit vague for a simple reply, but I'd be most
grateful if anyone could point me in the right direction / provide a link
/
etc.

Any and all assistance is gratefully received.

Thanks

Chris.
Click to expand...

If you need to use DAO, then something like this gives you great
flexibility. The example function returns the ID of a newly-created contact
for whom you have provided first and last names, unless an error occurs in
which case the function returns zero. You should be able to adapt it for
your needs:


Public Function NewID(strFirstName As String, _
strLastName As String) As Long

On Error GoTo Err_Handler

Dim lngReturn As Long
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblContacts", dbOpenDynaset, dbAppendOnly)

rst.AddNew
rst!ConFirstName = strFirstName
rst!ConLastName = strLastName
lngReturn = rst!ConID
rst.Update

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

NewID = lngReturn

Exit Function

Err_Handler:
lngReturn = 0
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
 
Just a quick thanks for your help.

As I said, my DAO is a bit rusty since I took up ADO.... :)

Thanks again,

Chris.
 
Back
Top