set form recordsource filtered by date and other variable

M

Melanie O

I'm trying to set a form's recordsource in VBA. This code seems to work:

WhereSQL = "tbl_TimeTracker.SystemID = Forms!subfrm_onesub!txt_name "

WhereSQL = WhereSQL & " AND tbl_TimeTracker.Date =
Forms!subfrm_onesub!txt_monday "

orderbySQL = "ORDER BY Date, Activity, SubActivity, Task "

Me.RecordSource = "SELECT tbl_TimeTracker.SystemID,
tbl_TimeTracker.Date, tbl_Activity.Activity, tbl_SubActivity.SubActivity,
tbl_Tasks.Task " & _
"FROM ((tbl_TimeTracker INNER JOIN tbl_Activity ON
tbl_TimeTracker.ActivityID = tbl_Activity.ActivityID) " & _
"INNER JOIN tbl_SubActivity ON tbl_TimeTracker.SubActivityID =
tbl_SubActivity.SubActivityID) " & _
"INNER JOIN tbl_Tasks ON tbl_TimeTracker.TaskID = tbl_Tasks.TaskID "
& _
"Where True and " & _
WhereSQL & orderbySQL

The problem is when I try to assign the variable CurrentDate and UserID to
text box values on the form instead of the references above as follows:

UserID = txt_name.Value
CurrentDate = txt_Monday.Value

I then substitute the appropriate Forms!form_name!field_name with the
variable name in the where statement. When the form opens, I am prompted for
the UserID, and the date restriction makes no records appear. I think the
date problem is related to the data type and/or lack of '#' around the date,
but I'm clueless about the UserID prompt. Any help on either issue would be
appreciated.
 
K

Ken Snell [MVP]

You need to concatenate the values of those variables outside the string
that you build:

WhereSQL = "tbl_TimeTracker.SystemID = " & UserID

WhereSQL = WhereSQL & " AND tbl_TimeTracker.Date = #" & _
Format(CurrentDate, "m/d/yyyy") & "#"

Also, I note in your code that your first "WHERE" expression is just True.
Is that correct?
"Where True and " & _
WhereSQL & orderbySQL
 
M

Melanie O

Ken,

Thanks for the help. I had originally coded the UserID statement you
suggest below, but when the form loads, I receive an "Enter Parameter Value"
message box with the value of the UserID showing and a text box for me to
enter the parameter (which is not what I want at all). If I enter the value
of the UserID, then the correct records populate the form. Here's the code
I'm using:

Dim UserID As String
UserID = ""
UserID = txt_name.Value
WhereSQL = "tbl_TimeTracker.SystemID = " & UserID

Everything looks correct in the debug window, but I can't figure it out.

You were right on with the date. Thanks.

And yes, the "where true" statement is in the code on purpose in case I want
to make the where statements conditional and I don't have a where clause.

Melanie
 
K

Ken Snell [MVP]

UserId is a text variable, so we need to modify the code slightly to delimit
its value with ' characters (that tells Jet database engine that the value
is a string):

WhereSQL = "tbl_TimeTracker.SystemID = '" & UserID & "'"

Also, you indicate that the query is asking for a UserID value. In your
code, you're using the UserID value for the SystemID field. Is that what you
intended?
 
M

Melanie O

Ken,

That worked. Thank you very much!


Ken Snell said:
UserId is a text variable, so we need to modify the code slightly to delimit
its value with ' characters (that tells Jet database engine that the value
is a string):

WhereSQL = "tbl_TimeTracker.SystemID = '" & UserID & "'"

Also, you indicate that the query is asking for a UserID value. In your
code, you're using the UserID value for the SystemID field. Is that what you
intended?
Click to expand...
 
Top