Filter all DB records by one combo selection on DB entry (1 Viewer)

[fraser_lindsay]

Hi,

I am trying to add a new interim 'startup screen' on my DB. This new form is unbound and has a combo box on it, the record source of which is 'BusinessName'.

In my database I have two main user forms that are used frmEmployeeDetails' and 'frmJSA'.

I would like the DB to filter all of the records in the database by whatever the user selects on the new startup form.

So for example, the user selects 'Company A' form the drop down and when they browse the employee details or JSA records they only see records linked to Company A.

So they click the company in the combo that filters the DB and then automatically loads the main Switchboard which allows them to browse the employee details and JSA forms etc.

I have made a start which doesn't work. This code is applied in the 'after update' of the startup form combo box.

Private Sub BusinessSelector_AfterUpdate()

DoCmd.OpenForm ("Switchboard")

frmJSA.Filter = "" & Me.BusinessSelector
frmEmployeeDetails.Filter = "" & Me.BusinessSelector
   Me.FilterOn = True


End Sub

I have a feeling this may be more complex than I am assuming.

Can anyone help please?

 

[Dennisk]

I would pass the filter information to each form as an argument in the open statement.

Each form would then check (in the on open event) if an argument was present, if so then use that argument in an apply filter statement.

 

[fraser_lindsay]

Ah ok, that sounds sensible. Thanks.

One more question though. How do I get the database to 'remember' what combo was selected when first loaded on the startup form to be able to resolve the 'on open' argument each time it is loaded on each form in that session?

I can do it for a fixed argument but this will vary by user.

 

[JANR]

You can open a form in Dialogmode and hide it insted of closing it, that way you can refrence it in other forms. Here is an explenation of how to do this: http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html

JR

 

[fraser_lindsay]

Great, thanks for the link. It looks very useful. I'll have a go at that next week when I'm back in the office.

 

[fraser_lindsay]

Hi,

I have been following up on this now I am back in the office. I have made some progress but it's not quite working yet. I get the data type mismatch error when the filter tries to apply and I don't understand why.

'frmStartFilter' is the startup form with the unbound combo to select company.
'cboBusinessSelector' is the unbound combo box to select business. The record source is a select statement from the business table, BusinessName column.
'BusinessName' is the name of the field in the query on the form I am trying to filter 'frmJSA'.

This is my code set in the 'On Open' event:

Private Sub Form_Open(Cancel As Integer)

'This part checks if the start filter form is loaded prior to loading the JSA form. If not it opens it, but hidden.
If CurrentProject.AllForms("frmStartFilter").IsLoaded = True Then
Forms!frmStartFilter.FormVisible = False

Else
DoCmd.OpenForm "frmStartFilter", , , , , acHidden

End If


'This code filters the records on the JSA form by filtering the BusinessName field using teh business selected on the form 'frmStartFilter' combo 'cboBusinessSelector'
If Forms!frmStartFilter!cboBusinessSelector = "ENOC Group EHSQ" Then
        Me.FilterOn = False
        DoCmd.ShowAllRecords
    Else
        Me.Filter = "BusinessName = """ & Forms!frmStartFilter!cboBusinessSelector & """"
        Me.FilterOn = True
    End If


MsgBox "Records shown are specific to your operating company."

End Sub

What am I doing wrong?

 

[fraser_lindsay]

I have tried many different combinations but I'm not really getting anywhere. I think part of the problem is that the field that need filtered on the main form (frmJSA) seems to be resolving numerically in the code, but displaying as text on the form.

I'm trying to match text but that clearly isn't working.

I have tried various uses of '.Column(1)' in my code but had no joy.

Back to Google...

 

[fraser_lindsay]

I have tried numerous variations in code, none of which work. I did manage to finally get the code to resolve the numerical reference for the combo box to the text value. I can see when the code debugs that it shows the business name for each. However it doesn't actually filter when the form loads and it breaks the recordset - shows 0 of 1 records and nothing works.

It's really frustrating. I can load the form, clcik the 'BusinessName' field, click 'Filter' chose a business and it filters records perfectly.

Why can't I do this in the code?

 

[JANR]

Could you post a sample DB to look at?

JR

 

[wiklendt]

fraser, have you tried filtering with your combobox USING the numerical portion? i assume this would be the ID of the field you are trying to filter by (let's say it's "BusinessID")- in which case, make your Me.Filter NOT BusinessName but BusinessID since that's what the combo is using...

 

[fraser_lindsay]

JANR - I'll try a few more troubleshooting suggestions if any has any before I try to upload a sample. I'm not sure I will be able to easily cut it down and keep it operational. It's 10MB at present, (for the FE) and BE.

Wikilendt

I have taken your suggestion. I edited the start form so that the unbound combo box displays has two columns, 0 and 1. The BusinessID is column 0.
Then I edited my code to see if I could get it to work.

My current code is this:

Private Sub Form_Open(Cancel As Integer)

'This part checks if the start filter form is loaded prior to loading the JSA form. If not it opens it, but hidden.
If CurrentProject.AllForms("frmStartFilter").IsLoaded = True Then


Else
DoCmd.OpenForm "frmStartFilter", , , , , acHidden

End If

'This code filters the records on the JSA form by filtering the BusinessName field using the business selected on the form 'frmStartFilter' combo 'cboBusinessSelector'
If Forms![frmStartFilter]![cboBusinessSelector].Column(0) = 21 Then
        DoCmd.ShowAllRecords
    Else
        'DoCmd.Beep
        Me.Filter = [BusinessName] = "" & Forms![frmStartFilter].[cboBusinessSelector].Column(0)
        Me.FilterOn = True
        
    
    End If
    

End Sub

That does the same. Loads, looks like iot is about to work but then displays 1 of 0 records and no tabs work, everything falls down. Then I reach for the hammer and take aim at the screen...

I know the code is fine up the commented out beep, that's why I inserted it there. It's just applying the filter that seems to be the problem.

Is there any way to code this in steps as if I was actually selecting options using the mouse, as that works. This doesn't.

 

[wiklendt]

I have taken your suggestion. I edited the start form so that the unbound combo box displays has two columns, 0 and 1. The BusinessID is column 0.
Then I edited my code to see if I could get it to work.

almost, you missed the bit where i said to use Me.Filter (and i meant in your code) on the BusinessID. also, your syntax was slightly off (sorry, i didn't get time to look at it properly in your first post)

make your Me.Filter NOT BusinessName but BusinessID since that's what the combo is using...

so, to implement my suggestion, you'd need to do try this (not tested! i don't have your DB to test it on, so this MAY work, or MAY need further amendment):

Private Sub Form_Open(Cancel As Integer)

'This part checks if the start filter form is loaded prior to loading the JSA form. If not it opens it, but hidden.
If CurrentProject.AllForms("frmStartFilter").IsLoaded = True Then


Else
DoCmd.OpenForm "frmStartFilter", , , , , acHidden

End If

'This code filters the records on the JSA form by filtering the [COLOR=Red][B]BusinessID[/B][/COLOR] field using the business selected on the form 'frmStartFilter' combo 'cboBusinessSelector'
If Forms![frmStartFilter]![cboBusinessSelector] = 21 Then
        DoCmd.ShowAllRecords
    Else
        'DoCmd.Beep
        Me.Filter = [COLOR=Red]"[/COLOR][Business[B][COLOR=Red]ID[/COLOR][/B]] = [B][COLOR=Red]" & [/COLOR][COLOR=Red]Forms![frmStartFilter].[cboBusinessSelector][/COLOR][/B]
        Me.FilterOn = True    
    End If
End Sub

now, with all the playing around that one does with this sort of thing, you need to make sure of a few things:

1) that your BOUND column in the combo properties is the one with the BussinessID in it (presumably column 1, but doesn't have to be). once you have your BOUND column correct, you don't need to refer to any ".Column(1)" in your code because access assumes you mean your bound column when you refer to the combo.

the tricky part, for future reference, is that your properties refer to columns 1, 2, 3... but CODE refers to the columns in base 0, meaning that column 1 is Column(0), column 2 is Column(1) and so forth.

this becomes tricker still if your refered column is text, because then yo'd be trying to make a string of a string, and you need some funky placement of quotation marks in that case. having said that, this is why it is generally recommended (and a lot easier) to use the auto/number type ID fields for filtration etc...
​

2) that you report HAS the BusinessID field on it (it may be hidden if you don't want to see human-meaningless autonumbers)

3) aesthetically, you don't need to SEE all the columns of your combo - what you can do is to have two columns: BusinessID and BusinessName.

then, in your column count (of combo properties) make sure you have "2" (or more if you put more into your combo)

now in your column widths property of the combo you can set it to display whichever columns you want for the humans - this is done by making the 'hidden' ones a width of zero cm (or whatever units is your default). so if you have a two column combo with BusinessID and BusinessName, but for aesthetics you only want BusinessName (but need the ID there for access) then make the widths "0cm; 3cm". that will allow access to refer to the BusinessID but not have it interfere with the prettyness of your form.
​

HTH

edit: my descriptions above assume that your ID field in that combo is called "BusinessID". if it is different, you will need to change it to whatever it actually is in your application.

 

[fraser_lindsay]

Wiklendt,

You are my hero! Seriously, that's sorted it. I already had the combo box set as two columns with the ID hidden.

What I also did was add the table 'Business' to my query which was the record source for the form, added 'BusinessID' to the query and then to the form, made it invisible and then reapplied the filter, which worked.

Thanks very much for your help and the edited code.

Now for my next few problems...

Thanks,

Fraser