Solved Set Combo box row source to match form filter (1 Viewer)

Harry Paraskeva

Registered User.
Local time
Today, 12:11
Joined
Sep 8, 2013
Messages
67
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.

Code:
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.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:11
Joined
Jul 9, 2003
Messages
16,243
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

You've got your good things, and you've got mine.
Local time
Today, 05:11
Joined
May 21, 2018
Messages
8,463
Easiest way is to apply the filter directly to the recordset of the combobox. Something like

Code:
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

Registered User.
Local time
Today, 12:11
Joined
Sep 8, 2013
Messages
67
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.
 
Last edited:

Harry Paraskeva

Registered User.
Local time
Today, 12:11
Joined
Sep 8, 2013
Messages
67
Easiest way is to apply the filter directly to the recordset of the combobox. Something like

Code:
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.
 

Attachments

  • Screenshot1.png
    Screenshot1.png
    28 KB · Views: 272

Harry Paraskeva

Registered User.
Local time
Today, 12:11
Joined
Sep 8, 2013
Messages
67
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:
Code:
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
 

Attachments

  • Screenshot2.png
    Screenshot2.png
    26.2 KB · Views: 235

Users who are viewing this thread

Top Bottom