Combo Box Search Criteria

[nebula53]

I have Combo box Titled ( Contract Status) Contains 4 values ( Active, Closed, Canceled, released)

I need when I chose form the Drop down list one of the status to run report with all contracts with that specific status.

All the Data are coming from one table contains 9 columns one of them are the ( Status)

[jimatqsi]

Nebbie,
I assume you have a button to launch the report. In the click event of that button you will have code like this:

dim strCriteria as string
dim strReportName as string
 
strcriteria = "[Status]='" & me!ContractStatus & "'"
strReportName = "name of the report"
 
docmd.openreport strReportName , acPreview , strCriteria
or
docmd.openreport strReportName , acViewNormal
 

That should do it.

Jim

[nebula53]

Unfortunately I tried it but it’s not working

[colintis]

Have you correctly set up the report's control source?

[nebula53]

Private Sub Command141_Click()

Dim strCriteria As String
Dim strReportName As String

strCriteria = "[Status]='" & Me!ContractStatus & "'"
strReportName = "PMCONTRACTS"

DoCmd.OpenReport PMCONTRACTS, acViewNormal

End Sub

the error is ( 2465 : Microsoft office can't find the field ' ContractsStstus' referred to in your expression

[colintis]

Check back and see what's your combo box actually named in the form. Or try use the Me.ContractStatus instead of Me!ContractStatus

[semanticnotion]

whats this problem is.....
i cant post my question.

[semanticnotion]

sorry for reply here
but what this problem tells when i post my question "The message you have entered is too short. Please lengthen your message to at least 1"

i cant post my question even cant edit the above reply. its a test post.

[nebula53]

The Combo box name is correct, when I changed the Me! To ME.
I received this Error message (Method or Data member not found)

[mshmyob]

Use the little reply button on the message - not the normal reply button. For some reason using the normal reply button doesn't work.

[colintis]

Have you tried using this one?

docmd.openreport strReportName , acPreview , strCriteria

As I see it should be somewhere typed incorrect but not the code you posted.

[jimatqsi]

Did you copy and paste this error message, or retype it?
the error is ( 2465 : Microsoft office can't find the field ' ContractsStstus' referred to in your expression

I ask, because I see the spelling of the object name is different in the error message from what you said it was.
One is ContractsStstus, with status spelled incorrectly, the other is spelled correctly. Maybe you've just got a typo.

Jim

[nebula53]

Jim there where Typo error when I copied the message.

[nebula53]

Please find the attached to understand the concept

[mshmyob]

Very sloppy programming practices. The message is simple: Access cannot find a field or control named "ContractStatus". Simply because it does not exist. Your control name is [Contract Status] - with a space. You therefore need to wrap the control name or field name in square brackets.

Making a few minor changes to Jimatqsi code you should have:

Dim strCriteria As String
Dim strReportName As String
 
strCriteria = "[Contract Status]='" & Me![Contract Status] & "'"
strReportName = "PMCONTRACTS"
 
DoCmd.OpenReport strReportName, acPreview, , strCriteria
 

Note:
1. The wrapping of the control name in square brackets to take into account the bad habit of using spaces.
2. Changing [Status]= to [Contract Status]= since there is no Status control or field in your report.
3. I added an extra comma in the openreport line since strCriteria is a SQL expression not a filter name.


cheers,

[nebula53]

mshmyob

thank you for your help I really appreciate it

[nebula53]

There is one thing I need to add

I need to set the Combo box to (Select) as default and If I didn’t chose any of the other criteria ( Active, inactive) then the report should show all of the information in the table

Dim strCriteria As String
Dim strReportName As String
 
strCriteria = "[Contract Status]='" & Me![Contract Status] & "'"
strReportName = "PMCONTRACTS"
 
DoCmd.OpenReport strReportName, acPreview, , strCriteria
 
' Reset Form
[Contract Status] = "SELECT"

[colintis]

For setting defaults, apply the value of the combo box to the form_load event, which it will set the [Contract Status] to "SELECT" when the form is open initially.

Private Sub Form_Load()
 
    [Contract Status].value = "SELECT" 
 
End Sub
 

Then in your button click event we have earlier, simply place a if else statement to open the report in specific line.

Dim strCriteria As String 
Dim strReportName As String 
 
strCriteria = "[Contract Status]='" & Me![Contract Status] & "'" 
strReportName = "PMCONTRACTS" 
 
If [Contract Status].Value = "SELECT" Then
    DoCmd.OpenReport strReportName, acPreview
Else
    DoCmd.OpenReport strReportName, acPreview, , strCriteria 
 

[NeoPa]

To set the default of a form control use the .DefaultValue property. Setting the .Value property has quite a different effect. It works for only the first record and it dirties the buffer.

[nebula53]

colintis

I appreciate your help its working fine. Thanks

[nebula53]

NeoPa
Thank you for the explanation, its helps to understand more.