Form to select report content

[fraser_lindsay]

Hello,

I have a query set up as the record set for a form containing combo boxes.

The form has four combo boxes with various drop downs. I have linked these combo boxes to the query using this syntax in the query:

[Forms]![frmToolStats]![cboModel_Number]

I have a preview button report on the form to load my report template to be populated with the combo selected info.

If I fill all four combo boxes with specfic info then my report displays all data records related as you would expect. For example I can select A 'Tool Type', 'Manufacturer', 'Model Number' and 'Tool Condition' and my reports will show me three test records relating to these specific parameters.

How do I set the code to allow me to complete only some of the boxes and return the records i.e. only 'Tool Type' and 'Model Number'.


I am trying to get my report to lift the data for test records for each specific tool by model number and tool condition and then do some calculations. This forms the full report.

I have tried this code:

If Not IsNull(Me![cboToolGroup]) Then
where = where & " AND [cboToolGroup] Like '*" & Me![cboToolGroup] & "*'"
End If


Can anyone help?

 

[Jerry Stoner]

This worked for me.

If Me![cboToolGroup] <> "" Then
where = where & " AND [cboToolGroup] Like '*" & Me![cboToolGroup] & "*'"
End If

 

[fraser_lindsay]

I have tried your code and replicated it for the other combo boxes but it doesn't allow me to only select some combos instead of all.

I therefore still have to fill all four combos with exact info to return records for an exact tool group, manufacturer, model and condition.

Any other suggestions?

F

 

[DBL]

This is what I use. I could probably send you a sample if that helped.

Private Sub btnPrint_Click()
On Error GoTo ErrorHandler

Dim stdresponse As String

Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim where As Variant
Dim F, T As Variant

Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.
' TO DO: Turn normal error handler on when this condition is finished.
On Error Resume Next

F = MakeUSDate([DateFrom])
T = MakeUSDate([DateTo])

'If IsNull(DLookup("IautoNo", "qrySalesDaybook", "[IDate]" & " Between " & F & " And " & T & "")) Then
'MsgBox "There were no details to print"
'Exit Sub
'End If

' DoCmd.OpenReport "rptSalesDaybook", acViewPreview, , "[IDate]" & " Between " & F & " And " & T & ""


db.QueryDefs.Delete ("Dynamic_Masterfile")
' TO DO: Turn normal error handler on when this condition is finished.
'On Error GoTo 0

where = Null

If Left(Me![DateFrom], 1) = "*" Or Right(Me![DateFrom], 1) = "*" Then
If Left(Me![DateTo], 1) = "*" Or Right(Me![DateTo], 1) = "*" Then
where = where & " and [IDate]" & " Between " & F & " And " & T & ""
End If
End If


If Left(Me![cboSelectSuppler].Column(1), 1) = "*" Or Right(Me![cboSelectSupplier].Column(1), 1) = "*" Then
where = where & " AND [supplier] Like " + Me![cboSelectSupplier].Column(1)
Else
where = where & " AND [supplier]=" + Me![cboSelectSupplier].Column(1)
End If

If Left(Me![cboSelectCustomer].Column(0), 1) = "*" Or Right(Me![cboSelectSpecies].Column(0), 1) = "*" Then
where = where & " AND [mcid] Like " + Me![cboSelectCustomer].Column(0)
Else
where = where & " AND [MCID]=" + Me![cboSelectCustomer].Column(0)
End If

If Left(Me![cboSelectPart].Column(0), 1) = "*" Or Right(Me![cboSelectSpecies].Column(0), 1) = "*" Then
where = where & " AND [PartID] Like " + Me![cboSelectPart].Column(0)
Else
where = where & " AND [PartID]=" + Me![cboSelectPart].Column(0)
End If

If Left(Me![cboSelectStatus].Column(0), 1) = "*" Or Right(Me![cboSelectStatus].Column(0), 1) = "*" Then
where = where & " AND [odstatus] Like " + Me![cboSelectStatus].Column(0)
Else
where = where & " AND [odstatus]=" + Me![cboSelectStatus].Column(0)
End If

'If Left(Me![Other], 1) = "*" Or Right(Me![Other], 1) = "*" Then
' where = where & " AND [Other] Like '" + Me![Other] + "'"
'Else
' where = where & " AND [Other]='" + Me![Other] + "'"
'End If

Set QD = db.CreateQueryDef("Dynamic_OrderStatus", _
"Select * from qryOrderStatus " & (" where " + Mid(where, 6) & ";"))
'DoCmd.OpenQuery "Dynamic_Query"
'DoCmd.OpenReport "RptDetailedAnimalInfo2", acViewPreview
If (DCount("*", "Dynamic_OrderStatus") = 0) Then
MsgBox "There are no records to print for the selection made"
Exit Sub
End If

DoCmd.OpenReport "rptOrderStatus", acViewPreview

'DoCmd.SendObject acSendReport, "rptDetailedAnimalInfo2", "rich text format", Forms!frmDetailedRptDialog!cboSelectUser.Column(3)

ExitHere:
Exit Sub

errorhandlerexit:
Exit Sub

ErrorHandler:
If Err.Number = 2501 Then
stdresponse = acDataErrContinue
Exit Sub
MsgBox Err.Description
Resume errorhandlerexit

Exit Sub
End If

End Sub

 

[DBL]

Sorry that probably isn't much use. I could put together a demo but not at the moment. If you can't make sense of the above let me know and I'll see what I can do.

 

[fraser_lindsay]

Hello,

Thanks for this, I see this is full code for a QBF as well. I tried the QBF a couple of times but never managed to get it to work. I tried sample databases and then replicated these and tailored to match my DB. This method has helped me learn a lot with Access.

Unfortunately I couldn't get it to work and the end result was using a standard query and basic code above. It works, sort of. I've been 'winging' it somewhat, perhaps I should buy a VBA for Access book.

If you have a demo or could make a demo to solve my problem above that would be fantastic.

 

[fraser_lindsay]

I'll have an attempt with some of your code first though!

 

[DBL]

Here's a sample. Open the form. You can filter by any of the options, leave blank it prints everying. The status field you can select more than one status. Put in a date range too. Hope this makes sense. All down to the hard work of the wonderful Mr Jack Cowley.

 

[fraser_lindsay]

Thanks a million. I'll have a play with this and see if I can work it into my DB, I'll let you know how I get on tomorrow.

Thanks very much for taking the time to do that, very much appreciated.

Fraser

 

[fraser_lindsay]

Ok, modified my DB to include code from your sample, replaced the relevant fields and references and tried it out. When I press my preview button, which the code is assigned to nothing happens. Not even an error message.

I got this far with my last attempt at a QBF. I don't know what I'm doing wrong but I just can't these to work.

 

[DBL]

Have you tried stepping through the code to see where it might be falling down? If you can zip and upload your database I'll take a look.

 

[fraser_lindsay]

Hello,

I've attached my DB - I must be missing something obvious but would be grateful if you or somebody else could have a quick look and tell what I have done wrong.

I just can't get this query by form technique to work and I'm pulling my hair out.

Thanks very much

 

[DBL]

Hi Fraser

I've got some suggestions about your database and I can see where you're hitting some problems. There's probably too much to post on the forum and I notice you're local to me. I've sent you a private message with a contact number and if you want to get in touch I'd be happy to discuss it with you. We can then post some comments which would hopefully help others.

 

[DBL]

Hi Fraser, here's your database with a few amendments. The combo boxes compare to the fields in the query that the report is based on rather than the lookup tables. Once I changed the field names to the corresponding names in the base query the code worked perfectly.

Any problems, give me a shout.

Dawn

 

[Brianwarnock]

I'm probably missing the point but isn't the simple solution to this type of selection to change each criteria to
eg Like "*" & [forms]![formname]![comboname]

Then if the selection from a combo is blank it doesn't figure in the selection filtering as all entries meet the criteria.

Just a thought

Brian