Query in VB/Passing it to a form/query

W

WatEva

A Query figures out what paint has what particle size.
Switchboard has a bunch of check boxes with particle size ranges and people
can select and the querry is suppose to return another form with the list of
paints that match the particle size range. I am not sure how to relate the
check boxes to the query and then do another query to match the values and
then how to return that information to the form? I am really confused and i
need to finish this by today. I have no idea what to do.
 
O

OssieMac

This might be a bit late but somehow I don't think you were ever going to
finish in a day when you obviously still have so much to learn but anyway
give this a try and see how you go with it. I have tried to keep the steps as
simple as possible and it might be overkill. However, get back to me if you
still have more questions. I developed a sample in Access 2002/2003. I also
have Access 2007 so if you have more questions please indicate what version
you are using and I will answer accordingly.

You do all this on one form (Continuous form). Step by step instructions
follow.

Under forms: Double click ‘Create form in Design view.
Save the form. (My example saves as DisplayData.)
Right click in the square top left of the form where the forms horizontal
and vertical ruler lines meet. (If not already there, small square black dot
appears indicating you are working with the form and not controls within the
form.)
Select properties from the dialog box.

Select Format tab and set following properties.
Default view: Continuous forms
Record Selectors: No
Navigation buttons: No

Drag edges of design frame and then the form itself out to a reasonable
size. (Can re-adjust later if required)

Click on menu item View and select Form Header/Footer (Ensure FORM and not
the Page Header/Footer)

Scroll down to the bottom of the form (scroll bars on right of Form Design
frame). Make the space below the bar with Form Footer nothing by dragging the
bottom of the space up to the bar. You should now have two areas of space on
the form. Form header and Detail. (The Form Footer bar still displays but
there should be no form space below it.)

You can make the form header larger by dragging the Detail bar down. (Move
cursor over the bar until you see a cross.) The header needs to be reasonably
large and the Detail section just enough for one line of data.

In the header, insert your check boxes and check box labels. (Probably best
kept as column to left). Then for each checkbox, insert 2 text boxes side by
side over to the right of them. (These can be hidden later so the user can’t
see them but don’t do so until you have finished testing.)

In my example, I inserted 5 check boxes and named them Check1, Check2 .....
Check5.

The text boxes I named RngLow1 and RngHigh1, RngLow2 and RngHigh2........
RngLow5 and RngHigh5.

For each of these text boxes, open properties, Data tab and set Default
value to 0 (zero).

For each of the check boxes, open properties and create an after update
event in code. Now for each of the checkboxes, the following code is
inserted. The example has hypothetical particle numbers so substitute real
ones for your code. Basically the RngLow textboxes have the minimum number of
particles for the checkbox selected and RngHigh has the maximum. If the check
box is not selected, both RngLow and RngHigh is set to 0 (Zero).

Private Sub Check1_AfterUpdate()
If Me.Check1 = True Then
Me.RngLow1 = 1001
Me.RngHigh1 = 2000
Else
Me.RngLow1 = 0
Me.RngHigh1 = 0
End If

End Sub

Private Sub Check2_AfterUpdate()
If Me.Check2 = True Then
Me.RngLow2 = 2001
Me.RngHigh2 = 3000
Else
Me.RngLow2 = 0
Me.RngHigh2 = 0
End If

End Sub

Private Sub Check3_AfterUpdate()
If Me.Check3 = True Then
Me.RngLow3 = 3001
Me.RngHigh3 = 4000
Else
Me.RngLow3 = 0
Me.RngHigh3 = 0
End If

End Sub

Private Sub Check4_AfterUpdate()
If Me.Check4 = True Then
Me.RngLow4 = 4001
Me.RngHigh4 = 5000
Else
Me.RngLow4 = 0
Me.RngHigh4 = 0
End If

End Sub

Private Sub Check5_AfterUpdate()
If Me.Check5 = True Then
Me.RngLow5 = 5001
Me.RngHigh5 = 6000
Else
Me.RngLow5 = 0
Me.RngHigh5 = 0
End If

End Sub


Now select the form again (right click the square where the ruler lines meet
as described above and select properties.)
Select Data tab.

Click the button with three dots at the end of the Record source. This opens
the query builder for the form. Select the required table and Add it and then
close the Show table dialog box.

Populate the fields for the query builder.

Drag the bottom of the Criteria matrix so that you have 6 or more criteria
lines showing.

In the first criteria line under the particle size insert
=[Forms]![DisplayData]![RngLow1] And <=[Forms]![DisplayData]![RngHigh1]

In the following lines of the criteria insert (or’s)
=[Forms]![DisplayData]![RngLow2] And <=[Forms]![DisplayData]![RngHigh2]
=[Forms]![DisplayData]![RngLow3] And <=[Forms]![DisplayData]![RngHigh3]
=[Forms]![DisplayData]![RngLow4] And <=[Forms]![DisplayData]![RngHigh4]
=[Forms]![DisplayData]![RngLow5] And <=[Forms]![DisplayData]![RngHigh5]

Close and save the query builder and the query is now attached to the actual
form.

Close the properties dialog box.

In the Detail part of the form, insert textboxes side by side across the
line for each of the fields you wish to display. (Delete the label associated
with each text box.)
Use Properties, Data tab and set the Control source to the field of the
query you wish to display. (Click the Control source field and then click
the drop down at end of field to select.)
Insert column labels for each text box directly above the text boxes but in
the Form header and as close as practicable to the Detail bar.

Now insert a control button somewhere in the form header and call it Up Date
Data. (You click this after selecting the required checkboxes). You can
cancel the dialog box that asks what you want to do with the control button).
Right click the control button and select properties and then Event tab.
Click in the field for On click and then click the button with the three dots
and select code. In between the sub and end sub insert the following.

Me.Requery

Now the height used for each row of data is the height between the Detail
bar and the Form footer bar. The text boxes in the Detail section should be
as close as possible (can touch) to the Detail bar and the Form footer bar as
close as possible to the bottom of the text boxes. (again can touch).

Save the form and test.

If Ok then open in design mode again and hide the text boxes in the Form
header.
 

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

Top