VBA - Inline code using variable to receive result of Select Info

E

Ed H

Not sure if I duplicated this question as I cannot find the original one.
Within a form, I select a value from a combo box. I then want to go look up
another value through a somewhat complex query and place the result in
another field on the screen.

I'm very new at this and have cobbled together something. SQL seems to run
fine, but receiving field is never populated.

Private Sub FLD_VehicleLicence_Exit(Cancel As Integer)
Dim STR_VehicleDriver As String
Dim STR_VehicleLicence 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


End Sub
 
T

Tom Wickerath

Hi Ed,

I assume that the receiving field is SFLD_DriverName, based on the last line
of code in your procedure:

Me.SFLD_DriverName = STR_VehicleDriver

You initialized the string variable STR_VehicleDriver as follows (ie. a
single space):

STR_VehicleDriver = " "

but that's all that happens before you attempt to assign this value to
SFLD_DriverName. I would remove the line of code shown above that initializes
STR_VehicleDriver, since it will be zero length (ie. "") until assigned a
value.

Not that it's related to the problem at hand, but just out of curiousity, is
FLD_VehicleLicence a date / time datatype? That's what you are indicating by
including the # sign delimitors.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Not sure if I duplicated this question as I cannot find the original one.
Within a form, I select a value from a combo box. I then want to go look up
another value through a somewhat complex query and place the result in
another field on the screen.

I'm very new at this and have cobbled together something. SQL seems to run
fine, but receiving field is never populated.

Private Sub FLD_VehicleLicence_Exit(Cancel As Integer)
Dim STR_VehicleDriver As String
Dim STR_VehicleLicence 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


End Sub
 
E

Ed H

It is a text field. Hence the newbie title. I used an example in the Help
and assumed that any variable that had to be parsed in to the SQL statement
had to have the # around it.

I basically want to execute a sql statement one way or another and put the
result in a variable that I can show on the form, in this case
SFLD_DriverName.

Is there another special character or none needed at all?
 
T

Tom Wickerath

Hi Ed,
Is there another special character or none needed at all?
Click to expand...

For text data type, you need to enclose the value in either single quotes,
or Chr(34). I prefer using Chr(34) myself. For example:

"WHERE TAB_Patrols.FLD_VehicleLicence = '" & Me.FLD_VehicleLicence & "';"
or
"WHERE TAB_Patrols.FLD_VehicleLicence = " _
& Chr(34) & Me.FLD_VehicleLicence & Chr(34)

You can get away without including the semicolon, a special character that
marks the end of the SQL statement.

I basically want to execute a sql statement one way or another and
put the result in a variable that I can show on the form, in this case
SFLD_DriverName.
Click to expand...

The SQL statement that you used is considered an append query, ie.
SELECT ..... INTO.

This type of statement does not return a value--it just adds (appends) a
record to a table. It sounds like you need to open a recordset first, where
you base the recordset on a SELECT query. Without knowing more about what you
are trying to do, it is difficult to help much.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

It is a text field. Hence the newbie title. I used an example in the Help
and assumed that any variable that had to be parsed in to the SQL statement
had to have the # around it.

I basically want to execute a sql statement one way or another and put the
result in a variable that I can show on the form, in this case
SFLD_DriverName.

Is there another special character or none needed at all?
 
E

Ed H

Thanks Tom. The other thing I noticed is that I was selecting 3 fields and
receiving 1. I'm surprised the statement didn't return an error. I'm going
to try your suggestion. Another suggestion that was made was to use DLOOKUP.
It does work well, but it appears it can only return one field at a time.
So for the three fields, I need to make 3 DLOOKUP calls, one for each field.
 
T

Tom Wickerath

Hi Ed,
The other thing I noticed is that I was selecting 3 fields and receiving 1.
Click to expand...

Unless you changed your SQL statement from the time you first posted it, it
appears to me as if you were selecting just one field ("FLD_LastName" from
the "TAB_Members" table) for appending it into another table
(STR_VehicleDriver), ie:

STR_SQL = "SELECT DISTINCTROW TAB_Members.FLD_LastName " & _
"INTO [STR_VehicleDriver]" & _
"FROM ......

I'm going to try your suggestion. Another suggestion that was made
was to use DLOOKUP.
Click to expand...

DLOOKUP should work, but be aware that it will only return the first
matching value. Here is a simplified example of using a recordset, however, I
wonder if you really need to get this complex in order to achieve your
objective:

Private Sub txtCompanyName_AfterUpdate()
On Error GoTo ProcError

' Requires a reference set to the "Microsoft DAO 3.x Object Library"
' For Access 97, x = 3.51; for Access 2000/2002/2003, x = 3.6

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT ContactName, City, Country FROM Customers " _
& "WHERE CustomerID = " & Chr(34) & Me.CustomerID & Chr(34)

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Populate unbound textboxes:
Me.txtContactName = rs("ContactName")
Me.txtCity = rs("City")
Me.txtCountry = rs("Country")

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure txtCompanyName_AfterUpdate..."
Resume ExitProc
End Sub


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks Tom. The other thing I noticed is that I was selecting 3 fields and
receiving 1. I'm surprised the statement didn't return an error. I'm going
to try your suggestion. Another suggestion that was made was to use DLOOKUP.
It does work well, but it appears it can only return one field at a time.
So for the three fields, I need to make 3 DLOOKUP calls, one for each field.
 
E

Ed H

Hi Tom.

I got some further help that solved this problem. The suggestion was to use
the recordset DAO objects. My SQL as you pointed out was okay for what I was
trying to do (minus the #). Anyway, it is working now. Next I have to
figure out how to get the field LIntDriverMemberId that is returned from the
query in to TAB_Patrols (TAB_Patrols.FLD_DriverMemberId). It is not part of
the form, but is part of the record. Not sure how to do this one, perhaps
using a similar method.

Thanks for your help by the way.

Dim LIntDriverMemberId As Long
Dim STR_LastName As String
Dim STR_FirstName As String

Dim STR_VehicleDriver As String
Dim dbs As Database, rst As Recordset
Dim STR_SQL As String
Dim retValue As Variant

Rem *****
Rem * Get the drivers name and id. Display the name
Rem * Store the id in TAB_Patrols
Rem *****

Set dbs = CurrentDb

STR_SQL = "SELECT DISTINCTROW TAB_Members.FLD_LastName, " & _
"TAB_Members.FLD_FirstName, TAB_Members.FLD_MemberId " & _
"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 & "'));"
Rem retValue = MsgBox(STR_SQL, vbOKCancel)
Set rst = dbs.OpenRecordset(STR_SQL, dbOpenSnapshot, dbReadOnly)
If rst.RecordCount > 0 Then
rst.MoveFirst
STR_LastName = rst("FLD_LastName")
STR_FirstName = rst("FLD_FirstName")
LIntDriverMemberId = rst("FLD_MemberId")
Me.SFLD_DriverName = STR_LastName & ", " & STR_FirstName
Else
rst.Close
Set dbs = Nothing
Exit Sub
End If

rst.Close
Set dbs = Nothing

End Sub

Tom Wickerath said:
Hi Ed,
The other thing I noticed is that I was selecting 3 fields and receiving 1.
Click to expand...

Unless you changed your SQL statement from the time you first posted it, it
appears to me as if you were selecting just one field ("FLD_LastName" from
the "TAB_Members" table) for appending it into another table
(STR_VehicleDriver), ie:

STR_SQL = "SELECT DISTINCTROW TAB_Members.FLD_LastName " & _
"INTO [STR_VehicleDriver]" & _
"FROM ......

I'm going to try your suggestion. Another suggestion that was made
was to use DLOOKUP.
Click to expand...

DLOOKUP should work, but be aware that it will only return the first
matching value. Here is a simplified example of using a recordset, however, I
wonder if you really need to get this complex in order to achieve your
objective:

Private Sub txtCompanyName_AfterUpdate()
On Error GoTo ProcError

' Requires a reference set to the "Microsoft DAO 3.x Object Library"
' For Access 97, x = 3.51; for Access 2000/2002/2003, x = 3.6

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT ContactName, City, Country FROM Customers " _
& "WHERE CustomerID = " & Chr(34) & Me.CustomerID & Chr(34)

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Populate unbound textboxes:
Me.txtContactName = rs("ContactName")
Me.txtCity = rs("City")
Me.txtCountry = rs("Country")

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure txtCompanyName_AfterUpdate..."
Resume ExitProc
End Sub


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks Tom. The other thing I noticed is that I was selecting 3 fields and
receiving 1. I'm surprised the statement didn't return an error. I'm going
to try your suggestion. Another suggestion that was made was to use DLOOKUP.
It does work well, but it appears it can only return one field at a time.
So for the three fields, I need to make 3 DLOOKUP calls, one for each field.
Click to expand...
 
T

Tom Wickerath

Hi Ed,

A couple of suggestions. First, on the line that reads:

Dim dbs As Database, rst As Recordset

I recommend changing this to:

Dim dbs As Database, rst As DAO.Recordset

While your code may compile without an error, adding the explicit library
reference can help prevent a run-time error (Error 13: type mismatch). Here's
why:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html

I would also move the statements that are used to close and destroy the
recordset and database object variables to an ExitProc: label, similar to
what I showed in my previous example. This way, you can be sure that these
objects will always be closed. If your code bombs out half way for any
reason, these objects will be left in memory.
Next I have to figure out how to get the field LIntDriverMemberId...
Click to expand...

It looks like you are doing it right. Hard to say without being able to look
at your database.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Tom.

I got some further help that solved this problem. The suggestion was to use
the recordset DAO objects. My SQL as you pointed out was okay for what I was
trying to do (minus the #). Anyway, it is working now. Next I have to
figure out how to get the field LIntDriverMemberId that is returned from the
query in to TAB_Patrols (TAB_Patrols.FLD_DriverMemberId). It is not part of
the form, but is part of the record. Not sure how to do this one, perhaps
using a similar method.

Thanks for your help by the way.

Dim LIntDriverMemberId As Long
Dim STR_LastName As String
Dim STR_FirstName As String

Dim STR_VehicleDriver As String
Dim dbs As Database, rst As Recordset
Dim STR_SQL As String
Dim retValue As Variant

Rem *****
Rem * Get the drivers name and id. Display the name
Rem * Store the id in TAB_Patrols
Rem *****

Set dbs = CurrentDb

STR_SQL = "SELECT DISTINCTROW TAB_Members.FLD_LastName, " & _
"TAB_Members.FLD_FirstName, TAB_Members.FLD_MemberId " & _
"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 & "'));"
Rem retValue = MsgBox(STR_SQL, vbOKCancel)
Set rst = dbs.OpenRecordset(STR_SQL, dbOpenSnapshot, dbReadOnly)
If rst.RecordCount > 0 Then
rst.MoveFirst
STR_LastName = rst("FLD_LastName")
STR_FirstName = rst("FLD_FirstName")
LIntDriverMemberId = rst("FLD_MemberId")
Me.SFLD_DriverName = STR_LastName & ", " & STR_FirstName
Else
rst.Close
Set dbs = Nothing
Exit Sub
End If

rst.Close
Set dbs = Nothing

End Sub
 
E

Ed H

Thanks very much for your help on this and the additional suggestions.
I'm always looking to best practices and I appreciate you pointing me to your
website.

Ed
 
T

Tom Wickerath

Hi Ed,

Actually, the web site is owned by a friend of mine. Here is another link
regarding best practices that I like to share with people:

Always Use Option Explicit
http://www.access.qbuilt.com/html/VBEOptions

Good luck on your project!


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks very much for your help on this and the additional suggestions.
I'm always looking to best practices and I appreciate you pointing me to your
website.

Ed
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inline VBA - SELECT ... INTO variableName WHERE ... 2
VBA Code Help 3
return without gosub error 0
return without gosub error 0
MS Access to Excel Code Review 2
Exporting multiple files to a template instead of a new file! 5
error loading DLL 3
Export multiple versions of a query to separate Excel files 21

Top