Help with combo boxes

jonu61 · Oct 30, 2003

Hi.

I building a database to track and log documents. I have four combo boxes in the database. The combo boxes are used to select the information that you choose to view. The first combo box is to select the type of media (ie doc, cd, floppy, * (All), etc). The next combo box is to select the status of the document (ie active, void, *(All), etc). The third combo box is used to select the owner of the document (ie John Doe, *(All), etc). The fourth is used to select the location of the document (ie 8-9, *(All), etc). I then have a command button below that is used to open the trigger the query to open the report based on this criteria. I seem to be having trouble with my status combo box. It will only trigger active reports. When I select void it will open a blank report. What do I need to do.

Here is the row source for my status combo box:

SELECT DISTINCT [DOCUMENTS LOG].[STATUS] FROM [DOCUMENTS LOG] Union SELECT "*" FROM [DOCUMENTS LOG]

I have this placed in the criteria line of my query:
Like [Forms]![Select Document]![Combo98]

Here is the code for my command button:

Private Sub Command100_Click()

Dim strWhere As String
Dim strSQL As String

If IsNull(Me.Combo96) Then
strSQL = strSQL & vbNullString
Else
strSQL = strSQL & " And TYPE = " & Me!Combo96 & "'"
End If

If IsNull(Me.Combo98) Then
strSQL = strSQL & vbNullString
Else
strSQL = strSQL & " And STATUS = " & Me!Combo98 & "'"
End If
If strSQL <> vbNullString Then
strSQL = Mid(strSQL, 6) & ";"
End If

If IsNull(Me.Combo109) Then
strSQL = strSQL & vbNullString
Else
strSQL = strSQL & " And [OWNER] = " & Me!Combo109 & "'"
End If
If strSQL <> vbNullString Then
strSQL = Mid(strSQL, 6) & ";"
End If

If IsNull(Me.Combo113) Then
strSQL = strSQL & vbNullString
Else
strSQL = strSQL & " And [LOCATION] = " & Me!Combo113 & "'"
End If
If strSQL <> vbNullString Then
strSQL = Mid(strSQL, 6) & ";"
End If

strWhere = "SELECT * FROM [Select] WHERE" & strSQL
DoCmd.OpenReport "Select", acViewPreview, strSQL

End Sub

After doing a little bit more research I changed the Command100 button code to.
Private Sub Command100_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

On Error Resume Next
db.querydefs.Delete ("Status&quot

On Error GoTo 0

where = Null
where = where & " AND [TYPE] '" + Me![Combo96] + "'"
where = where & " AND [STATUS] '" + Me![Combo98] + "'"
where = where & " AND [OWNER] '" + Me![Combo109] + "'"
where = where & " AND [LOCATION] '" + Me![Combo113] + "'"

strWhere = "SELECT * FROM [Select] WHERE" & strSQL
DoCmd.OpenReport "Select", acViewPreview, strSQL

End Sub
I am not sure if I need to add more to it or if what to do next.

Please help me if you can. Thank you very much in advance. I really appreciate all of the help.

Thanks,
Jon

docmeizie · Oct 30, 2003

All that code really is not necessary. Try this on for size.

Now what I am about to do works with a list box to view the selected data and comboboxes to select the information criteria for the listbox.

1. Create a query for the list box that has the data you want viewed in the list box and the IDnumbers of the four Comboboxes. Make the ID's invisible to the query, and then build a criteria for each ID that points to it's corresponding combo box. So like mediaID would point to the combo box that has the media criteria.

2. Make your four comboboxes and tack on the queries for each one. Make sure the combo boxes are in the order in which you are going to make the selection.

3. OPTIONAL. Besides the first combo box you can add the previous controls ID to the query of the other three comboboxes so that when you make a selection in the first combo box it will filter the next combo box and so on.

4. In the after update of the first combobox you want to put this code:
Me![combobox2] = null
Me![combobox2].requery
Me![combobox3] = null
Me![combobox3].requery
Me![combobox4] = null
Me![combobox4].requery
Me![listbox1] = null
Me![listbox1].requery
For the next three comboboxes, use the same code minus that combobox and the previous comboboxes (thus order is important)

5. That should get you started with something way shorter than what you have now.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv [pc1]

jonu61 · Oct 31, 2003

I still can't get it to work. Here is the latest code I have tried.

Private Sub Command100_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

On Error Resume Next
db.querydefs.Delete ("Status&quot

On Error GoTo 0

where = Null
where = where & " AND [TYPE] '" + Me![Combo96] + "'"
where = where & " AND [STATUS] '" + Me![Combo98] + "'"
where = where & " AND [OWNER] '" + Me![Combo109] + "'"
where = where & " AND [LOCATION] '" + Me![Combo113] + "'"

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

If Left(Me![Status], 1) = "*" Or Right(Me![Combo98], 1) = "*" Then
where = where & " AND [STATUS] like '" + Me![Combo98] + "'"
Else
where = where & " AND [STATUS] = '" + Me![Combo98] + "'"
End If

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

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

Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from TYPE " & (" where " + Mid(where, 6) & ";&quot

)

'strWhere = "SELECT * FROM [Select] WHERE" & strSQL
DoCmd.OpenReport "Select", acViewPreview

End Sub
Thanks,
Jon

docmeizie · Oct 31, 2003

What part are you having trouble with in the code that I posted. The Optional part of the post from before is actually needed for that to work right.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv [pc1]

PeteJohnston · Oct 31, 2003

You seem to have a few unnecessary Nulls and conditions? Why do you need to set the selection to Null before you start? How about just

Dim Where As String

Code:

  Set db = CurrentDb()
   
  Where = &quot;&quot;
  If IsNull([Combo96]) = False Then
    Where = Where & &quot; AND [TYPE] like '*&quot; & [Combo96] & &quot;*'&quot;
  End If
    
  If IsNull([Combo98]) = False Then
    Where = Where & &quot; AND [STATUS] like '*&quot; & [Combo98] & &quot;*'&quot;
  End If
    
  If IsNull([Combo109]) = False Then
    Where = Where & &quot; AND [OWNER] like '*&quot; & [Combo109] & &quot;*'&quot;
  End If
    
  If IsNull([Combo113]) = False Then
    Where = Where & &quot; AND [LOCATION] like '*&quot; & [Combo113] & &quot;*'&quot;
  End If
  
  If Len(Where) > 0 Then
     Where = Mid(Where, 6)
  End If

  DoCmd.OpenReport &quot;Select&quot;, acViewPreview, , Where

so that it opens the "Select" report in preview mode with filter "Where" applied? The Len(Where) > 0 bit just drops the leftmost " AND " if required.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with combo boxes

jonu61

Technical User

docmeizie

Programmer

jonu61

Technical User

docmeizie

Programmer

PeteJohnston

Programmer

Similar threads

Part and Inventory Search

Sponsor