Solved Set Combo box row source to match form filter

[Harry Paraskeva]

Hello everyone,

A question on combo-boxes this time.

I have a modal and pop-up form that loads and gets filtered when you click a button on another form. The pop-up/modal form contains an unbound lookup combo-box for quick navigation between the records. However, I want to set the row source for the aforementioned combo-box to match the form's filter.

So far, I've produced the following, which does not seem to work. My guess is that VBA doesn't understand the introduction of variables in the strsql string or I've introduced it the wrong way.

Dim strsql As String
Dim EqualPosition As Integer
Dim FieldName As String
Dim FieldIDValue As String

If Not IsNull(Me.Filter) Then
EqualPosition = InStr(Me.Filter, "=")
If EqualPosition > 0 Then
FieldName = Left$(Me.Filter, EqualPosition - 2)
FieldIDValue = Mid$(Me.Filter, EqualPosition + 2)
End If
End If

strsql = "SELECT Tbl_PrimaryCitations.ID, Tbl_Bibliography.Citation_Form AS Citation, Tbl_PrimaryCitations.Specific_Citation AS Specifics, Tbl_PrimaryCitations.Comments_Notes AS Notes, Tbl_PrimaryCitations.RadiocarbonID " & vbCrLf & _
"FROM Tbl_Bibliography INNER JOIN Tbl_PrimaryCitations ON Tbl_Bibliography.ID = Tbl_PrimaryCitations.CitationID " & vbCrLf & _
"WHERE ((""Tbl_PrimaryCitations."" & FieldName & ""= &"" & FieldIDValue)) " & vbCrLf & _
"ORDER BY Tbl_Bibliography.Citation_Form, Tbl_PrimaryCitations.Specific_Citation;"

Me.FilterSelector.RowSource = strsql

Thank you for any help. If I come up with a solution, I'll share.

PS: The filter contents is of this form: RadiocarbonID = 7
This is why I've used -2 +2 for the FieldName and FieldIDValue respectively.

 

[Uncle Gizmo]

I've read your question through 3 times and I'm still not getting it. I can't see any reference to a combobox? And I don't understand the plus 2 and -2....

 

[MajP]

Easiest way is to apply the filter directly to the recordset of the combobox. Something like

Dim rsTemp as dao.recordset
set rsTemp = CmboName.recordset
rsTemp.filter = me.filter
Set rsTemp = rsTemp.OpenRecordset
'set cmboName.recordset = rsTemp  'this may not be needed

 

[Harry Paraskeva]

I've read your question through 3 times and I'm still not getting it. I can't see any reference to a combobox? And I don't understand the plus 2 and -2....

Sorry about that. FilterSelector is the name of the combobox, hence it takes the rowsource property.
The plus/minus 2 is for the Left and Mid functions to extract what is necessary for the SQL string, as Access puts spaces in between the Filter function. I've checked this with Debug.Print.

 

[Harry Paraskeva]

Easiest way is to apply the filter directly to the recordset of the combobox. Something like

Dim rsTemp as dao.recordset
set rsTemp = Me.recordset
rsTemp.filter = me.filter
Set rsTemp = rsTemp.OpenRecordset
'set FilterSelector.recordset = rsTemp  'this may not be needed

This works with a minor tweak, but the combobox rowsource is not formatted correctly. Titles are shown with the names of the fields, instead of their aliases, and the first column is the id instead of the text that it represents. See attached figure.

 

[Harry Paraskeva]

Turns out it was parsing the Where clause of the SQL string erroneously or I mistyped quotations and ampersand characters. In any case, I've added another string variable and was able to fix everything, so consider this resolved! Thank you all for the ideas!

I'm attaching a screenshot of the result and the code for reference:

Dim strsql As String
Dim EqualPosition As Integer
Dim FieldName As String
Dim FieldIDValue As Integer
Dim WhereString As String

EqualPosition = InStr(Me.Filter, "=")
FieldName = Left$(Me.Filter, EqualPosition - 2)
FieldIDValue = Mid$(Me.Filter, EqualPosition + 2)
WhereString = "Tbl_PrimaryCitations." & FieldName & ")="
WhereString = WhereString & "" & FieldIDValue
WhereString = "(((" & WhereString & "))"

strsql = "SELECT Tbl_PrimaryCitations.ID, Tbl_Bibliography.Citation_Form AS Citation, Tbl_PrimaryCitations.Specific_Citation AS Specifics, Tbl_PrimaryCitations.Comments_Notes AS Notes, Tbl_PrimaryCitations.RadiocarbonID " & vbCrLf & _
"FROM Tbl_Bibliography INNER JOIN Tbl_PrimaryCitations ON Tbl_Bibliography.ID = Tbl_PrimaryCitations.CitationID " & vbCrLf & _
"WHERE"
strsql = strsql & WhereString & vbCrLf & _
"ORDER BY Tbl_Bibliography.Citation_Form, Tbl_PrimaryCitations.Specific_Citation;"

Me.FilterSelector.RowSource = strsql