Filtered form and combo box

[tonycl69]

Hi everyone, I know this is in similar realms to cascading combos, but still can't see to get my head round it. I have a form based on a qry, I have 3 unbound combo boxes based on 3 tables, one for each, just lists supplying the combos, I have a filter button so that once anyone combination is selected in the combo boxes, filters the form. This works fine, I also have on the form an unbound combo based on the same query as the form that loosk up records on the form, this works fine. What I would like to do is whichever of the other 3 combos have been selected and the filter button clicked change the listing in the loookup combo to reflect the filtered form. Can you please point me in the right direction. I am not that clued up with VBA so instructions would be great or point me to somehwere where there maybe an example to study. Cheers.

 

[Beetle]

If you are filtering your form's record source by building a SQL string based on the values in the combo boxes where selections have been made, you should be able to use that same SQL string (or a slightly altered version of it) as the Row Source of the "lookup" combo box.

 

[tonycl69]

Hi thanks for your reply, but not sure how to integrate the 2.

This is what is behind the filter button:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#dd\/mm\/yyyy\#" 'The format expected for dates in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "([location] = """ & Me.cboLocation & """) AND "
End If
'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "([category] Like ""*" & Me.cboCategory & "*"") AND "
End If
If Not IsNull(Me.cboStatus) Then
strWhere = strWhere & "([status] Like ""*" & Me.cboStatus & "*"") AND "
End If
'Number field example. Do not add the extra quotes.
'If Not IsNull(Me.cboFilterLevel) Then
'strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ") AND "
'End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
'If Me.cboFilterIsCorporate = -1 Then
'strWhere = strWhere & "([IsCorporate] = True) AND "
'ElseIf Me.cboFilterIsCorporate = 0 Then
'strWhere = strWhere & "([IsCorporate] = False) AND "
'End If

'Date field example. Use the format string to add the # delimiters and get the right international format.
'If Not IsNull(Me.txtstartdate) Then
'strWhere = strWhere & "([EnteredOn] >= " & Format(Me.txtstartdate, conJetDate) & ") AND "
'End If

'Another date field example. Use "less than the next day" since this field has times as well as dates.
'If Not IsNull(Me.txtenddate) Then 'Less than the next day.
'strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtenddate + 1, conJetDate) & ") AND "
'End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
Me.Combo72.Requery
End If
Me.cboLocation = Null
Me.cboCategory = Null
Me.cboStatus = Null

This is what is behind the the lookup combo after update:

comboFlag = True

Dim rs As DAO.Recordset
If Not IsNull(Me.Combo72) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[unit] = " & "'" & Me.Combo72 & "'"
If rs.NoMatch Then
MsgBox "File not found: Please Click 'Show All Records'."
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.Combo72 = Null

the source of the lookup combo is a query, so when the form opens all records are available, but if I choose any of the other three filters as in first listing I would like the lookup combo72 to show records of whichever or all of the filtered records are, as I would see in the form after clicking the filter button.
Attached is a screen shot of the main screen with obvious omissions.

Cheers for your input.

 

[Beetle]

Can you post a copy of the SQL for the combo box?

 

[tonycl69]

This is what is behind the combo that selects a record.

SELECT tblmain.Unit, tblmain.Location, tblmain.Category, tblmain.Status, tblmain.Year, tblmain.Make, tblmain.Model, tblmain.ChassisNo, tblmain.BodyType, tblmain.Tracker, qry_mainscreenFeed.datedif, qry_mainscreenFeed.NextInsp, qry_mainscreenFeed.weeknonextinsp, tblmain.Unitid, IIf([datedif]>0,"3",IIf([datedif] Between 0 And -3,"1",IIf([datedif]<=-4,"2"))) AS Expr2, qry_mainscreenFeed.MOT, qry_mainscreenFeed.ROADTAX, qry_mainscreenFeed.HIAB, qry_mainscreenFeed.[2YRTACHO], qry_mainscreenFeed.[6YRTACHO], qry_mainscreenFeed.[1YRTESTCERT], tblmain.Unitid
FROM tblmain LEFT JOIN qry_mainscreenFeed ON tblmain.Unitid = qry_mainscreenFeed.Unitid
ORDER BY tblmain.Unit;

 

[Beetle]

Just to clarify, the query that is used as the record source of the form uses this same SQL (prior to being filtered in your code)?