query parameter criteria-Choose fm drop down list

[Yu2008]

Hi, can anyone help me make a query so that the criteria for the parameters are a drop down list of the values in that field?

For example, if the field is "Product model", the user can choose from the available models via a drop down
list rather than type the specific model number like "TV6779D" in the criteria box.

Or if the field is "Date Range", the user can choose the date range from the small calendar next to the criteria box in stead of having to type the date.

I've been spending hours trying to figure this out. but no clue. Your help will be much appreciated!

Yu

 

[boblarson]

Check out this sample on my website:
http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[Yu2008]

Thanks! Bob.

 

[Yu2008]

Hi Bob. How come there is no result comes out when I hit the "generate report" on the sample form that downloaded from your website. Can you give me some light again? thanks.

Yu

 

[boblarson]

What version of Access are you using?

 

[Yu2008]

Oh, I found what the problem is. What I use is Access 2007, everytime when I open the database, there is always a message bar with a security warning which I ignore it every time. But just now, I get in there, and choose "Enable this content". And then I run your form again. It works! Thanks Bob : )

 

[boblarson]

You can avoid that message by adding the database location to your trusted locations. See here for how:

http://www.btabdevelopment.com/main...owtoenablecodeandmacros/tabid/57/Default.aspx

 

[Yu2008]

Okay! got it ^_^

 

[Yu2008]

Bob, I imitate yours and create my own form but when I hit the Generate Report button, an error message pop up showing "Data type mismatch in criteria expression." What is this problem maybe? May I have your comments again? Thanks!

 

[boblarson]

Perhaps you are trying to open with text criteria and have to include single quotes, or something. What actual code are you using?

 

[Yu2008]

hey, thanks Bob. Here is the code I use. If not select model but only date range, the report runs well and generate correct. But the error message will pop up if I select the model number.

Private Sub cmdGenerateReport_Click()
    On Error GoTo Err_cmdGenerateReport_Click
    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
        If Not IsNull(Me.cboSelectModel) Then
            stWhere =[COLOR=red] "[Model]=" &[/COLOR] Me.cboSelectModel & " And "
            blnTrim = True
        End If
        If IsNull(Me.TxtFrom) And Me.TxtFrom = "" Then
            If Not IsNull(Me.TxtTo) And Me.TxtTo <> "" Then
                stWhere = stWhere & "[Dates]  <=" & Me.TxtTo & "#"
                blnTrim = False
            End If
        Else
            If IsNull(Me.TxtTo) And Me.TxtTo = "" Then
                If Not IsNull(Me.TxtFrom) And Me.TxtFrom <> "" Then
                    stWhere = stWhere & "[Dates]>=" & Me.TxtFrom
                    blnTrim = False
                End If
            Else
                If (Not IsNull(Me.TxtFrom) And Me.TxtFrom <> "") And (Not IsNull(Me.TxtTo) Or Me.TxtTo <> "") Then
                stWhere = stWhere & "[Dates] Between #" & Me.TxtFrom & "# And #" & Me.TxtTo & "#"
                blnTrim = False
                End If
            End If
        End If
        If blnTrim Then
            stWhere = Left(stWhere, Len(stWhere) - 5)
        End If
    stDocName = "ModelActivity_Rpt"
    DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_cmdGenerateReport_Click:
    Exit Sub
Err_cmdGenerateReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdGenerateReport_Click
End Sub

 

[boblarson]

Is Model a text value? If so, it needs to be:

...
stWhere = "[Model]=[size=5][color=red][b]'[/b][/color][/size]" & Me.cboSelectModel & "[size=5][color=red][b]'[/b][/color][/size] And "
...

 

[Yu2008]

yeah, you are right. it is text value. I changed the code as you said, this time the report runs. But new problem appear, after I select the model number, the report that it generate is blank. hmm... Will it because the report that I set link in this form? cause in this report, I had sort it by the model number just like attachment you see. thanks! Bob.

 

[boblarson]

Not sure why it would be blank unless there is no data for that model. I guess we're to the point that it would be useful to see your database, if you can post it:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Yu2008]

Thanks Bob. attached is the database.

Yu

 

[boblarson]

Thanks Bob. attached is the database.

Yu

It didn't seem to work. Care to try again? Make sure to compact and repair first and then zip it using winzip or something like it.

 

[Yu2008]

Oop, the attachment is not there. do again.

 

[boblarson]

Okay - got it for you ...

1. First, your combo box to select for the search should NOT include the ID because you are using it to search on the model NAME field and not the model ID field. So, I changed that.

2. Second, get rid of the lookups at table level (http://www.mvps.org/access/lookupfields.htm )

 

[Yu2008]

Oh : ) Thanks a lot! Bob. You are so great!
Got a flu that my eyes are hard to open at this time. But with your help, I believe I'll get this puzzle resolved tomorrow!
Have a good night.

Yu

 

[Yu2008]

Bob, can you help again check what is wrong with my form where I add on another inquiry criteria. (attachment) It just doesn't work

Thanks a lot!

Yu