Q:

Insert values from two different tables into another different table with concatenate

I have a button which uses two separate queries that pull data from two tables and insert into another table:
Dim lngID As Long
Dim lngIDCallout As Long
Dim strSQL1 As String

lngID = CalloutAttendance_MultiSelect.Value
lngIDCallout = Forms![Callouts].[CalloutID].Value

strSQL1 = "INSERT INTO Members_Callouts(MemberID) SELECT MemberID FROM Members WHERE MemberID=" & lngID    
strSQL2 = "INSERT INTO Members_Callouts(CalloutID) SELECT CalloutID FROM Callouts WHERE CalloutID=" & lngIDCallout

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2
CalloutAttendance_MultiSelect.Requery

And whilst it almost does what I want it to do, it inserts the the two values as two separate new records, whereas I'd like it to insert it into ONE new record. I've had a go, but I either get syntax errors, or in the case below, I got a 3067 runtime error "Query input must contain at least one table or query"
strSQL1 = "INSERT INTO Members_Callouts(MemberID, CalloutID) SELECT
          (SELECT MemberID FROM Members WHERE MemberID=" & lngID & "),
          (SELECT CalloutID FROM Callouts WHERE CalloutID=" & lngIDCallout & ")"

Anyone know where I might be going wrong?
Thanks :-)

A:

In this case you're just inserting the key values so all you need to do is
strSQL1 = _
        "INSERT INTO Members_Callouts (MemberID, CalloutID) " & _
            "VALUES (" & lngID & ", " & lngIDCallout & ")"

In other words, you don't need to bother with something like...
"(SELECT MemberID FROM Members WHERE MemberID=" & lngID & ")"

...since the value it returns is just lngId (assuming that the value exists in the [Members] table).

