How to refer to a field value in a Table's Specific Record

Bill Bisco · Mar 27, 2009

I have 2 tables, tblProcesses and tblElements. Each Process has many Elements. I want to add a new Record to tblProcesses, and then add a new Record to tblElements that belongs to the new Record I just added to tblProcesses.

Here is my code. I used Do.Cmd to go to the record I just created, but I know that strFieldName = Me![Process ID] is the wrong way to reference it.

So, how do I reference the the field [Process ID] from the record I just selected from Do.Cmd?

Code:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTable As String
    Dim strFieldName As String
    Dim lngrecordnum As Long
       
    strTable = "tblProcesses"
    strFieldName = Me![tblProcesses.StationID]
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    With rst
        .AddNew
        .Fields("StationID") = strFieldName
        .Fields("Process Name") = "New Process"
        .Update
    End With
    
    DoCmd.OpenTable ("tblProcesses")
    DoCmd.GoToRecord acDataTable, "tblProcesses", acLast
    strFieldName = Me![Process ID]
    DoCmd.Close acTable, "tblProcesses", acSaveYes

    strTable = "tblElements"
    Set rst = dbs.OpenRecordset(strTable)
    With rst
        .AddNew
        .Fields("Process ID") = strFieldName
        .Fields("Element Name") = "New Element"
        .Update
    End With
    
    Me.Parent.Refresh

    End Sub

Any Help is appreciated.

Sincerely,
Bill

pbaldy · Mar 27, 2009

You could use a DMax to get the number, but it would be unreliable in a multiuser environment. Search here on @@Identity, which is a reliable method.

wazz · Mar 27, 2009

another common way is:

Bill Bisco said:

Code:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTable As String
    Dim strFieldName As String
    Dim lngrecordnum As Long
    [COLOR=red]Dim lngNewID As Long[/COLOR]
 
    strTable = "tblProcesses"
    strFieldName = Me![tblProcesses.StationID]
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    With rst
        .AddNew
        .Fields("StationID") = strFieldName
        .Fields("Process Name") = "New Process"
        .Update
        [COLOR=red].Bookmark = .LastModified[/COLOR]
[COLOR=red]        lngNewID = !StationID[/COLOR]
    End With
etc...

pbaldy · Mar 27, 2009

Good point Wazz. I work with SQL Server so much I forgot about that one.

wazz · Mar 27, 2009

i had a feeling.

@@
----

Bill Bisco · Mar 30, 2009

Wazz and pbaldy thanks a lot for responding. Your solution worked great. My knowledge has increased.

Sincerely,
Bill

How to refer to a field value in a Table's Specific Record

Bill Bisco

Custom User Title

pbaldy

Wino Moderator

wazz

Super Moderator