Inline VBA - SELECT ... INTO variableName WHERE ...

[Ed H]

I'm very new at this and have been putting bits and pieces together to try to
make this work. I have created a query (somewhat complex) and copied the SQL
into inline VBA code. My issue is the variable is never filled with the
result. Even when I substitute a very simple query. User enters a licence
number on the form from a combo box and I want to populate the next field on
the form with their name. TAB_Patrols
(FLD_VehicleLicence)->TAB_MemberVehicles (FLD_VehicleLicence,
FLD_MemberID)->TAB_Members(FLD_MemberId, FLD_MemberLastName)

There may be a much more appropriate way to do this, but I'm learning. The
fields going in to the query appear to contain the correct information.

Private Sub FLD_VehicleLicence_Exit(Cancel As Integer)
Dim STR_VehicleDriver As String
STR_VehicleDriver = " "

Dim DBS As Database
Dim QDF As QueryDef
Dim STR_SQL As String

Set DBS = CurrentDb
For Each QDF In DBS.QueryDefs
If QDF.Name = "TMP_QRYNameByLicence" Then
DBS.QueryDefs.Delete QDF.Name
End If
Next QDF

STR_SQL = "SELECT DISTINCTROW TAB_Members.FLD_LastName " & _
"INTO [STR_VehicleDriver]" & _
"FROM (TAB_Members INNER JOIN TAB_MemberVehicles ON " & _
"TAB_Members.FLD_MemberId = TAB_MemberVehicles.FLD_MemberId) "
& _
"INNER JOIN TAB_Patrols ON
TAB_MemberVehicles.FLD_VehicleLicence = " & _
"TAB_Patrols.FLD_VehicleLicence " & _
"WHERE TAB_Patrols.FLD_VehicleLicence = #" &
Me.FLD_VehicleLicence & "#;"
Set QDF = DBS.CreateQueryDef("TMP_QRYNameByLicence", STR_SQL)
Me.SFLD_DriverName = STR_VehicleDriver

 

[HKComputer]

Delete all your code and use DLookup. I'm not accustomed to your naming
convention. It makes it difficult for me to interpret your code. I'll use
my own here. Textboxes on forms should start with txt and combos with cbo.
Table names should start with tbl. Variables that hold alphanumeric should
start with str. For a more complete description of DCount / DLookup goto:


Dim strMemberID as String
strMemberID = DLookup("MemberID", "tblMemberVehicles", "[Vehicle License]= '"
& txtVehicleLicense & "'")
Me.txtDriverName = DLookup("DriverName", "tblMembers", "[MemberID]= '" &
strMemberID & "'")

 

[HKComputer]

Sorry, I forgot to put in the link on that detailed explanation. I'm having
trouble finding it now and not sure if it will work to past an accessmonster
URL in here. -HK