SQL or VBA??? Please help

[Wong]

Hello
I have created a cmb on a form which should activate a report eventually.

I have tried to do SQL & VBA, but neither seem to work. I know the selection from the text box is getting to the report, but when the report is open I get all of the records in the db, not what I have asked in my text box.

eg The text box is to select an activity, then when I open the report I should only see people who are interested in this activity,

Can anyone tell me what is best to do SQL or VBA

Sorry I am quite new at databases, but very keen to learn more.

 

[RuralGuy]

Pass the data in the WhereCondition of the OpenReport.

 

[ScottGem]

SQL/VBA is not an either/or choice. SQL is used to query tables. VBA is a procedural language to process tasks. You can run SQL queries within a VBA module.

If you want to filter a report based on a value in a control on a form, there are a number of ways to do it. One is to use a Where clause or Filter in the OpenReport method. My preference is to base the report on a query and set the criteria inthe query to:

=Forms!formname!controlname

 

[Wong]

Hi ScottGem and RuralGuys

Thank you for the info, can you please give a bit more details - Sorry

I have attached the vba and sql I am using. I know that the text box is getting to the report because the Title is correct, but it's not filtering to the Activity Title.

If you wouldn't mind letting me know where I am going wrong it would be really appreciated.

Thanks
Sue

 

[RuralGuy]

Hi Sue,
Try this code:

Private Sub cmdActivityDetails_Click()

Dim strReportName As String
Dim strCriteria As String

strReportName = "rptActivityDetails"

'open rptActivityDetails passing in the activity
'type you want to check bookings for.

If Not IsNull(Me.cmbActivityTypes) Then
   [COLOR=DarkOrange]'-- If IDType is numeric
   strCriteria = "[IDType]= " & Me.cmbActivityTypes[/COLOR]
   [COLOR=DarkGreen]'-- If IDType is text
'   strCriteria = "[IDType]= '" & Me.cmbActivityTypes & "'"[/COLOR]
   DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
Else
   'Don't run report if there's no activity type selected
   MsgBox "You must select an activity type before proceeding to check the report.", _
            vbOKOnly, "No Activity Type Selected"
End If

Exit_cmdrptActivityDetails_Click:
    Exit Sub

Err_cmdrptActivityDetails_Click:
    MsgBox Err.Description
    Resume Exit_cmdrptActivityDetails_Click

End Sub

In the future you can just include code directly in the post just as I have done. No need to mess with attachments.

 

[ScottGem]

As I said, there are different ways to do this. One is what RuralGuy showed you. Again, my preference is to put the criteria in the query using:

=Forms!formname!controlname

where formname is the name of your form and controlname the name of the control storing the value (in this case Me.cmbActivityTypes)

 

[RuralGuy]

Hi ScottGem,
IMHO using the WhereCondition is far more flexible than referencing a control on a form. If you can use a generic query in the report then the WhereCondition parameter can be a complex as you want to limit the selection. It can also involve *any* field in the query and has the added benefit of being able to close the form it you choose. It is far more than a personal preference for me. Just my $0.02