set form recordsource filtered by date and other variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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?
 
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...
 
Back
Top