Query by Form

[Guest]

I set the criteria for a query in a roundabout way. I wonder if there is a
more direct approach.

I have about 50 text boxes on a form called Main_Form. When Main_Form
opens, the underlying query runs 50 times. After the query runs the first
time, text box 1 displays the contents of a field called Furnish (from the
query). Then the query criteria are reset, the query runs a second time and
text box 2 displays the contents of the field, Furnish. When the query has
run 50 times, the 50 text boxes display the various contents of the field
Furnish that results from the query.

The query criteria refers to a text box in Main_Form called RowNum. In a
Private Sub that runs when the form opens, VBA code sets the contents of
RowNum to the 1st criteria (call it criteria #1). Then the code runs Requery
and sets the contents of text box 1 to the field, Furnish. Then the sequence
repeats for criteria #2.

It seems that I am setting the criteria for the query in an indirect way
(VBA code places the criteria in the text box RowNum and then the query gets
the criteria from the text box). Is there a more direct way to set the
criteria.

 

[Guest]

Perhaps I am not getting a very clear picture here, but one thing you might
consider is setting calculated control sources for your various text boxes
instead of running and re-running a query. The ControlSource entry for
TextBox1 might look something like this (where DataX is the data you are
currently pushing into RowNum via VBA):

=dlookup("[Furnish]","[Table1]","[DataX] = 1")

For TextBox2, like this:

=dlookup("[Furnish]","[Table1]","[DataX] = 2")

etc., etc., etc.

Basically, you can just insert the SQL code of your query into the
ControlSource of the text box and either hard-code the where statement or
find a way to have it look up the criteria from a table. How you use this
depends on whether the data going into RowNum is dynamic or static. If it is
a static sequence (1, 2, 3, etc) , you could simply hard-code the criteria
into the Where clause of each control's ControlSource as above.