p2p.wrox.com Forums - using debug.print

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)

- Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)

- - using debug.print (http://p2p.wrox.com/showthread.php?t=59456)

Vince_421

June 26th, 2007 08:31 AM

using debug.print

This might seem stupid, but I never used this command.

What I would like to do is that when I create a new record with DAO, I can get the autonumber of the created field to be put into a variable? I need this because data will be inputted in another table using the pk as fk.

According to what I know, I need the debug.print to 'catch' the autonumber, however, I don't know how to do this...

Can anyone help me on this? Just need to know how to retrieve the primary key of the created record...

Thanks

robzyc

June 26th, 2007 09:16 AM

Hi Vince,

Debug.Print does is not used for "catching" variables, is merely outputs a value/formula result/text string to the "Immediate" pane in the VBE. When in the VBA Editor, press "Ctrl + G" and the Immediate pane will appear.

The Debug.Print command is extremely (almost required in some cases)
for debugging applications as you can output information, but keep it from the
users view.

In terms of getting the New ID number for a newly created field, it can be
awkward with Access.
I found the best way is to:[list][*]Open up a Recordset.[*]Add a New Record.[*]The cursor should now be at the new record, so querying the "ID" fields value will give you the new ID.[*]Assign the rest of the values to the record.[*]Close the Recordset.

I hope this helps,
Best Regards
Rob

Vince_421

June 26th, 2007 09:55 AM

ok,

but how does step 3 work? I can do the others, but don't know how to do the thirth. Do you need more info to do this or not?

Thanks so far...

robzyc

June 26th, 2007 10:17 AM

Vince,

No problem, its can seem a little odd at first, heres some sample code:

Code:

'Test Code for Vince_421.

'Create a DAO Recordset and Add a new Record and return the ID.



Public Function AddAndGetAutoNumber() As Long

On Error GoTo AddAndGetAutoNumber_Err

'Creates a new Record and returns its ID number.



    Dim newID As Long



    'Create a DAO Recordset.

    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Test]")



    rs.AddNew                   'This adds a new Record to the Recordset.

    newID = rs("ID")            'Get the new ID from the Recordset

    rs("Field 1") = "BLAH"

    rs("Field 2") = 1

    rs.Update                   'Update the Recordset.



    Debug.Print "New Record ID is: " & newID    'Debug.Print in Action!



    AddAndGetAutoNumber = newID



AddAndGetAutoNumber_Exit:

    Exit Function



AddAndGetAutoNumber_Err:

    MsgBox "Error: " & Err.Number & " - " & Err.Description

End Function

Hope this helps/points you in the right direction.

Best Regards,
Rob

Vince_421

June 29th, 2007 10:27 AM

Sorry for the late response, but I was really busy with another problem to solve...

Your thing works great, thanks very much for your help.

robzyc

July 2nd, 2007 03:00 AM

No problem, your welcome Vince.

Glad I could be of help!

All times are GMT -4. The time now is 05:01 AM.