Combo box to filter form or sub form

[CAMc]

Hi,

I have created a basic table with Id, Jobno, ItemRef, DrgStatus and I am trying to set up a text box or a combo box that will filter all records based on txtbox or combo box value, after update.

i want to filter the DrgStatus column so if “approved” is selected all records filter to only show approved rows?

i assume I need to create a form with a sub form?

i tried just a form and combo box and it filtered the table but only should one record at a time on at a time instead of every record that had approved in the column?

[ssanfu]

Add an unbound combo box to the form header. Name it cboDrgStatus. Set the combo box Row Source to the statuses. (table/query or value list)
Add the following code:

Private Sub cboDrgStatus_AfterUpdate()
    'filter the form recordset with value selected from combo
    Me.Filter = "DrgStatus =" & Me.cboDrgStatus
    Me.FilterOn = True
    Me.Requery
End Sub

Add a button named "btnReset"

Private Sub btnReset_Click()
    'remove filter
    Me.Filter = ""
    Me.FilterOn = False
    Me.Requery
End Sub

i tried just a form and combo box and it filtered the table but only should one record at a time on at a time instead of every record that had approved in the column?

Was the form in Continuous form view or Single form view??

[CAMc]

Add an unbound combo box to the form header. Name it cboDrgStatus. Set the combo box Row Source to the statuses. (table/query or value list)
Add the following code:

Private Sub cboDrgStatus_AfterUpdate()
    'filter the form recordset with value selected from combo
    Me.Filter = "DrgStatus =" & Me.cboDrgStatus
    Me.FilterOn = True
    Me.Requery
End Sub

Add a button named "btnReset"

Private Sub btnReset_Click()
    'remove filter
    Me.Filter = ""
    Me.FilterOn = False
    Me.Requery
End Sub

Was the form in Continuous form view or Single form view??

In the above code the first one came back with an error

run time error 3075

syntax error (missing operator) in query expression ‘DrgStatus = 1. To Start’.

so I changed the code a few times and added in ‘ & “ ‘ “ in second line and it worked, see below

Private Sub cboDrgStatus_AfterUpdate()
    'filter the form recordset with value selected from combo
    Me.Filter = "DrgStatus =‘ " & Me.cboDrgStatus & “ ‘ “
    Me.FilterOn = True
    Me.Requery
End Sub

and I had the form as a split form with single record in the top part and database view in bottom half of split.

so I created a new form and used the wizard and selected tabular (which I assume is continuous). That worked fine.

i did try this on another combo box, for the (JobNo) but it didn’t work. I tried using your code and my revised code and neither worked. I tried to apply the logic to a text box but failed in that too. The format of text in the table is different so assume this is why.

the format is a letter followed by 4 numbers. Which would eventually after years turn to 5 numbers so as an example the are currently H2705 or H2706 or H2707 or etc.

thinking about it a combo box would probably be better as I can link it to the JobDetails table to populate it and then show two columns in the combo box, Job No and Job Name. Most people are terrible at remembering job numbers so at least they can still see the name, without me creating a further combo box

When the table is filtered I would ideally like each combo box to filter the current filter instead of each time a the table is filtered it just resets and only filters to the last combo box selected.

so you could filter by job no to see the entire scope of the job, then you could filter which drawings are at construction status.

Or you could filter which drawings are at construction stage and then have another column tracking if glass is ordered. You could then sort the results by job no and printable. This would be a useful report. Add a few dates in and even better, start flagging up issues automatically. I know these could be done by queries but I want there would be some many different variations so this easier allowing filtering via form and the print results. Once I finish this I want to create a overall report that is printable. So it would be by job no and the list all screens not started, then all screens out for comment then all screens at construction and so on. At this minute we do all this via excel then manually discuss this in weekly meeting so access report would save a lot of time and allow time to talk about more meaningful things. I did start a excel vba project to automate this process but I was basically trying to emulate access