queries + Visual basic

J

justin_vasko

i am trying to create a user-friendly GUI and am trying to figure out how to
create queries based on what the user enters in text fields, in this GUI.

does anyone know of a website that offers assistance with the VB and
database components/coding required to do this, with a walkthrough or FAq on
the subject?
 
S

Sprinks

Hi, Justin.

I don't know any websites, although you might search for Jeff Conrad's very
comprehensive post on Access websites. There is also a discussion in
Litwin/Getz/Gilbert's Access Developer's Handbook. The general strategy is
to provide combo boxes where possible, and textboxes for user entry and to
build an SQL search string. I build it in an invisible textbox on the form.
The procedure WriteFilterString is called by the AfterUpdate event of each
form control. It loops through all of the form's controls and writes
appropriate SQL for each non-null entry.

For simplicity, I've omitted other code that handles ranges, but it is
similar. For these cases, useful for example for currency fields, I provide
an option group consisting of >, <, =, or Between. If any of the first three
are selected, the user enters the value in an adjoining text box. If Between
is selected, a second textbox is enabled and made visible to permit a range
to be entered. After validating the data (both non-null, the 2nd greater
than the 1st), the SQL is written.

Command buttons are provided to preview the report or clear out all
criteria. The code is included below.

Hope that helps.
Sprinks

Private Sub WriteFilterString()
‘ Called by each criteria control’s AfterUpdate event
Dim intindex As Integer
Dim ctl As Control
Dim ctlValFld1 As Control, ctlValFld2 As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "


End If

End Select

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Private Sub cmdClearSelection_Click()
' Resets all controls to Null
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Or _
ctl.ControlType = acOptionGroup) Then
ctl.Value = Null
End If
Next ctl
End Sub

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim strFilter As String

strDocName = "rptBidReturn"
strFilter = ""

' If no criteria selected, preview entire report
If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub
 
J

justin_vasko

well, i gave that a try, but i'm not sure i understand completely...

i've revised my approach, and am now using command buttons to bring up input
boxes for the search criteria. it is a search through a database for a menu,
with cmd buttons for Category (cmdCat), supplier (cmdSupply), and item name
(cmdItem). they all bring up input boxes, and i am wondeirng how to use
this information obtained from the user to tailor a query upon these
variables, and find any instances of 1, 2, or 3 whichever applies.

to display the results of this search i hope to use a subsummary
(subInventory) on this search form (frmSearch)

so some tips on tailoring queries in this way would be much appreciated,
along with any referrals elsewhere also and thankyou again in advance!
 
S

Sprinks

Hi, Justin.

I omitted a couple of important points in explaining my code. Once you
understand what's going on, I think you'll find this approach much more
straightforward than the series of command buttons you're thinking of--the
user simply fills in controls on a form.

- Firstly, "txtFilterString" is the name of a textbox on the form in which
the filter string will be built, i.e., the value of the SQL string will be
assigned to the textbox.

- I use a naming convention for each control that consists of a 3-digit
prefix (txt for textbox, cbo for combo box, etc.) plus the name of the table
field that corresponds to this data. All controls on the form are *unbound*.

- Using this naming convention, for each control, if it has a non-null
value, a snippet of filter string is written: first an open bracket, then
the name of the field (snipping off the first 3 characters of the control
name), then a close bracket, an equal sign, and finally the value the user
has selected. So, for example, if the user entered a value of 4 in a combo
box called cboCategory, the code generated would be:

[Category] = 4 AND

The For loop loops through all controls in the form's controls collection,
adding similar snippets for each non-null value entered, resulting in, say:

[Category] = 4 AND [Item] = 623 AND [Region] = 2 AND

Then after the loop the " AND " is snipped off, resulting in the final string:

[Category] = 4 AND [Item] = 623 AND [Region] = 2

So, in your case, three combo boxes--cboCategory, cboSupplier, and
cboItem--would enable the user to select one or more of these values in any
combination.

To display the filtered records on a form, use the Openform method, using
the value of Me!txtFilterString as the link critieria:

Private Sub YourCommandButton_Click()
On Error GoTo Err_YourCommandButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"
stLinkCriteria = Me!txtFilterString
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_YourCommandButton_Click:
Exit Sub

ErrYourCommandButton_Click:
MsgBox Err.Description
Resume Exit_YourCommandButton_Click

End Sub

Hope that helps.

Sprinks
 
Top