Filtering a record lookup combobox

[njitter]

Hello,

i've created a Record Lookup combobox by using the Wizard.

This has the following event code:

Sub Combo512_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Job] = '" & Me![Combo512] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I also have a filter that is created when the form is loaded. This has the following event code:

Private Sub Form_Load()

Dim username As String

username = fOSUserName ' Haal gebruikersnaam op

Select Case username
  
  Case "name"                   'Settings for user
  Filter = ""
  OrderBy = ""
  Filter = addcriteria(Filter, "[Status] = " + Chr(34) + "Actief" + Chr(34))
  Filter = addcriteria(Filter, "[Uitvoering] Like" + Chr(34) + "*FB*" + Chr(34))
  OrderBy = addcriteria(OrderBy, "[Job] DESC")
  FilterOn = True
  OrderByOn = True

The problem is that the Record Lookup combobox shows ALL the records, not just the filtered records.

- I would like to show this combobox only the records that are available when a filter has been set.
- When no filter has been set it should show all records

Could someone explain to me how to do this??

Kind regards,

Martijn

---
It's never too late to do the Right thing

 

[M8KWR]

You would have to create some sql and when you want it activate put this code into the combobox.rowsource value.

If you are not sure about sql, then you could cheat a little by getting the query builder to create the sql for you, and then you just paste this into the code window where you need it to be activated.

But do not forget to refresh the combo box with the new changes.

Hope this helps

 

[njitter]

There is already some SQL in the rowsource:

SELECT DISTINCTROW [Table Onderwerp].[Job], 
                   [Table Onderwerp].[Onderwerp] 
FROM               [Table Onderwerp] 
ORDER BY           Job DESC;

how to add the filter to this code?

---
It's never too late to do the Right thing

 

[M8KWR]

You want to change this to

SELECT [Table Onderwerp].Job, [Table Onderwerp].Onderwerp FROM [Table Onderwerp] ORDER BY [Table Onderwerp].[Job Desc];

jus tget rid of the carrage returns, and then set this has your rowsource in the combobox, then refresh the combo box to show changes

Hope this helps.

 

[njitter]

the carriage returns i've added for readablility.

I did a combo512.requery but this did not apply the filter to the combobox.

The form is showing 12 records. The combobox allows me to select every available record but only it will only jup to the selected record when it is one of the 12 filtered records.



---
It's never too late to do the Right thing

 

[M8KWR]

Has the combo box got its own query, not related to the form?

It would need to have one.

then you code should read

combo512.rowsource = "SELECT [Table Onderwerp].Job, [Table Onderwerp].Onderwerp FROM [Table Onderwerp] ORDER BY [Table Onderwerp].[Job Desc];"

combo512.requery

It should work, i use something lik ethis all the time.