set variable to field value in different table

[mcgraw]

How would I set a variable to a field that is in a table not controlled by the current form?

The form I'm trying update is controlled by the Issues table, and I need to grab tblWithGroup.ID as string GID

Problem is, the Issues table also has a field called ID...so when I set the variable to ID it is automatically pulling the Issues.ID, not the tblWithGroup.ID

Private Sub with_group_AfterUpdate()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblWithGroup")
With rs

If Me!group_ID = Null Then
Me!group_ID = Null
Else
If Me!group_ID = ID Then
.Edit
!End_Date = Date
End If
End If

Dim GID As String
GID = ID 'Needs to pull ID from tblWithGroup.ID

.AddNew
!Issue_ID = Me.ID
!Old_Group = OldGroup
!Group = Me.with_group
!Start_Date = Date
Me!group_ID = GID
.Update
End With
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
End Sub

When I put a stop and watch what is happening, I see it update the group_ID with the ID from the Issues table, so the code is working (I think), just not how I want it to!

 

[boblarson]

Check out the DLookup function.

 

[mcgraw]

Check out the DLookup function.

Thanks...I am looking into that, and tried this:

Dim GID As String
GID = DLookup("ID", "tblWithGroup", "tblWithGroup.Issue_ID = ID")

But I get Invalid use of Null?

 

[boblarson]

If you are looking for the ID on the form you would need:

GID = DLookup("ID", "tblWithGroup", "[Issue_ID] =" & Me!ID)

And usually it is good to use the NZ function in case it really doesn't find it:

GID = Nz(DLookup("ID", "tblWithGroup", "[Issue_ID] =" & Me!ID),0)

But I think you are not looking up the right thing. If you already have the ID why are you trying to look it up?

 

[mcgraw]

But I think you are not looking up the right thing. If you already have the ID why are you trying to look it up?

What I am trying to do is find the last record in tblWithGroup that corresponds to the Issues table to put an end date on it.

So the steps that this function as a whole should be are:
1: put an end date on the last record.
2: Add a new record to tblWithGroup that will
a: Set the Issue_ID in tblWithGroup to Issues.ID
b: take the old group and log it
c: Set the New Group
d: set the start date to Date()
e: Update the Issues.Group_ID field to the tblWithGroup.ID

So, I need to find the tblWithGroup.ID and set it to the Group_ID.