Select Query from within a Form

  • Thread starter Thread starter Gavin Webb
  • Start date Start date
G

Gavin Webb

I am trying to run a select query using a value from a
field within a Form.
I have a working query, which displays the results in a
grid.
The problem is that I want to use the first value
displayed in the future. I want to store it as a string
variable within the form.

What do I need to do?

This is what I have in the form:

Private Sub Cbo_ReportedBy_Change()
strReportedBy = Cbo_ReportedBy.Value
DoCmd.OpenQuery ("Query1")
End Sub

and this is the query I run:

SELECT [tbl_Users].[UserDept]
FROM tbl_Users
WHERE ((([tbl_Users].[UserID])=[Forms].[Frm_Main].
[strReportedBy]));


What do I need to change?
 
Gavin,

You don't need to, and in fact you can't, use the variable in the query
in this way. Just do it like this...

Private Sub Cbo_ReportedBy_AfterUpdate()
DoCmd.OpenQuery "Query1"
End Sub

Query1:
SELECT [tbl_Users].[UserDept]
FROM tbl_Users
WHERE (([tbl_Users].[UserID])=[Forms]![Frm_Main]![cbo_ReportedBy])

By the way, I think the AfterUpdate event of the combobox is better than
Change.

By the way again, OpenQuery would normally only be used in code to run
an Action Query, not to simply display the datasheet of a Select Query.
Normally, if you want to see a set of data displayed, you would have a
form or report based on Query1, and use an OpenForm or OpenReport method.
 
Back
Top