Make a query from a form

[Nav4]

Can this be done?

I would like to genreate a query from a form. So when I want to run a monthly report for a month I can pick a mont or a date range and a criteria for the form such as crew meetings attended, paperwork in on time or up to seven different points of interest for a particular person.

I can do this relative simple in parameter query, but he users want a more efficient input form. Or maybe the word i "quicker". And if they use a drop down box from a data table to start the form/query, there won't be as many input errors.

THANKS,

Nav4

 

[boblarson]

Check out my sample here as it SHOULD give you an idea of how you can do that. While this sample is for a report, it allows you to select one, or more, criteria including from a combo box and/or dates then it opens up based on a dynamic WHERE clause that is built via code.

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[Nav4]

That looks exactly what I am wanting. I'll play with it and see if I can get it to work like I want it to and give you an update tomorrow.

Again,

THANKS!

Nav4

 

[Nav4]

Bob,

After several attempts............

I can almost get it working. It comes up with a dialog box, if I simply enter, it returns a blank report. If I add the info it comes back with another parameter entry. If I leave this balnk it agains returns a blank report. If I enter the name twice.........It Works as it is supposed to.

I pretty much plagerized your code and changed the input names. I just used one variable and in between dates.

Nav4

 

[Nav4]

Code Added

Private Sub CmdGenRepSM_Click()
On Error GoTo Err_CmdGenRepSM_Click


Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.CmbCl) Then
stWhere = "[Crew Leader]=" & Me.CmbCl & " And "
blnTrim = True
End If


If IsNull(Me.txtstartdate) And Me.txtstartdate = "" Then
If Not IsNull(Me.txtenddate) And Me.txtenddate <> "" Then
stWhere = stWhere & "[indate] <=" & Me.txtstartdate & "#"
blnTrim = False
End If
Else
If IsNull(Me.txtenddate) And Me.txtenddate = "" Then
If Not IsNull(Me.txtstartdate) And Me.txtstartdate <> "" Then
stWhere = stWhere & "[indate]>=" & Me.txtstartdate
blnTrim = False
End If
Else
If (Not IsNull(Me.txtstartdate) And Me.txtstartdate <> "") And (Not IsNull(Me.txtenddate) Or Me.txtenddate <> "") Then
stWhere = stWhere & "[indate] Between #" & Me.txtstartdate & "# And #" & Me.txtenddate & "#"
blnTrim = False
End If
End If
End If

If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If
stDocName = "quesafetymeeting"
DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_CmdGenRepSM_Click:
Exit Sub

Err_CmdGenRepSM_Click:
MsgBox Err.Description
Resume Exit_CmdGenRepSM_Click




End Sub

 

[boblarson]

The problem looks like you aren't doing it correctly with the AND's. You need the AND in EVERY Where clause and the blnTrim needs to be set blnTrim=True in every place you have said bln=False:

stWhere = stWhere & "[indate] <=" & Me.txtstartdate & "# AND"

 

[Nav4]

OK, I give,...Below is the code. When I add the last And, I get a error message on the query, indicating it doesn't like the last And. If I do not put it in,it shows the parameter query box with the Value from the drop down box. If you put the same value back in, it runs like it is supposed to.

Private Sub CmdGenRepSM_Click()
On Error GoTo Err_CmdGenRepSM_Click


Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.CmbCl) Then
stWhere = "[Crew Leader]=" & Me.CmbCl & " And "
blnTrim = True
End If


If IsNull(Me.txtstartdate) And Me.txtstartdate = "" Then
If Not IsNull(Me.txtenddate) And Me.txtenddate <> "" Then
stWhere = stWhere & "[indate] <=" & Me.txtstartdate & "# and"
blnTrim = True
End If
Else
If IsNull(Me.txtenddate) And Me.txtenddate = "" Then
If Not IsNull(Me.txtstartdate) And Me.txtstartdate <> "" Then
stWhere = stWhere & "[indate]>=" & Me.txtstartdate & "# and "
blnTrim = True

End If
Else
If (Not IsNull(Me.txtstartdate) And Me.txtstartdate <> "") And (Not IsNull(Me.txtenddate) Or Me.txtenddate <> "") Then
stWhere = stWhere & "[indate] Between # " & Me.txtstartdate & "# And #" & Me.txtenddate & " # And "
blnTrim = Ytue
End If
End If
End If

If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If
stDocName = "quesafetymeeting"
DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_CmdGenRepSM_Click:
Exit Sub

Err_CmdGenRepSM_Click:
MsgBox Err.Description
Resume Exit_CmdGenRepSM_Click