I cannot get a filter drop down to work

[nick1408]

Hi Guys,

I am tryingto get a split form to filter from a drop down and have tried various ways to get it to work but just cannot seem to work out what I'm doing wrong. The issue is on the form called 'Courses Completed'. I get a VBA error. I have done some extensive Googling but whatever I try always fails. I'm pretty sure it would be somethnig to do with the way I have structured the queries or tables but would prefer to VBA my way around the current issue rather than change table and query structure.

I'm open to all suggestions!

Thanks.

 

[The_Doc_Man]

It would be nice to tell us the error message.

 

[Minty]

Split forms can be a bit troublesome. I'm not sure they work the same as a normal form. What if you create a simple datasheet form with the same data and try and filter that?

 

[nick1408]

Split forms can be a bit troublesome. I'm not sure they work the same as a normal form. What if you create a simple datasheet form with the same data and try and filter that?

Runtime 3075. I couldn't find what was causing this error. I feel it is something in the way I set everything up.

 

[nick1408]

Split forms can be a bit troublesome. I'm not sure they work the same as a normal form. What if you create a simple datasheet form with the same data and try and filter that?

I like the idea. I already have a subform with the data (Completed Courses subform). Would I be able to do the same sort of thing with a subform?

 

[Minty]

Yes you can apply a filter to a sub form , in code from your main form.

 

[nick1408]

Yes you can apply a filter to a sub form , in code from your main form.

Cool. Is there an example somewhere I could follow?

 

[Minty]

Create a button on the main form and use some code to apply the filter, something like;

Me.YourSubformName.Form.Filter = "[YourField] = " & Me.cmbYourFilter
Me.YourSubformName.Form.FilterOn = True

Replace the control names and field names with ones appropriate to your form. I've used a combo box, which is always a handy way to ensure you only filter to available values. (By making the combo source a query to only list the available values). This assumes the value is a number, if its text put single quotes around the value;

"[YourField] = [COLOR="red"]'[/COLOR]" & Me.cmbYourFilter & "[COLOR="Red"]'[/COLOR]"

 

[nick1408]

Well you have gotten me one step closer! The VBA works without getting runtime errors but the filter returns nothing. When I try and manually filter I get a 'Type mismatch or expression' error. I don't understand why I am getting this as I am filtering for text in both the VBA (no error here) and when manually filtering (error). All my tables have the relevant fields set as text so I really should not be seeing this error I feel.

 

[Minty]

Can you post up the code you have used?

Sent from my Nexus 7 using Tapatalk

 

[nick1408]

Can you post up the code you have used?

Sent from my Nexus 7 using Tapatalk

Apologies for the late reply:

Me.FilterOn = False

On Error GoTo Proc_Error
Debug.Print Me.filterCombo
If IsNull(Me.filterCombo) Then
   Me.[Completed Courses Subform].Form.Filter = ""
   Me.[Completed Courses Subform].Form.FilterOn = False
Else
  Me.[Completed Courses_Subform].Form.Filter = "[Course Name]=" & Me.filterCombo
  Me.[Completed Courses_Subform].Form.FilterOn = True
End If
Proc_Exit:
  Exit Sub
Proc_Error:
   MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
   Resume Proc_Exit

 

[pbaldy]

Probably bed time (or pub time) in the UK. Note the suggestion in post 8 for delimiters if the value is text. Try:

Me.[Completed Courses_Subform].Form.Filter = "[Course Name]='" & Me.filterCombo & "'"

 

[nick1408]

Probably bed time (or pub time) in the UK. Note the suggestion in post 8 for delimiters if the value is text. Try:

Me.[Completed Courses_Subform].Form.Filter = "[Course Name]='" & Me.filterCombo & "'"

Thanks pbaldy. I get Error 3709 with that line. I can't see how it is a connection error though. I've uploaded my DB again. Issue is on the Courses Completed form.

 

[Minty]

(or pub time) in the UK.

Actually the sun was out for a change, and the pub had indeed beckoned

 

[Minty]

@Nick your design is confusing with the use of the Extended queries to display the names and ID's instead of dealing directly with the tables.

You are using lookup fields in your tables based on your extended queries - this is causing you a double whammy of confusion. Here is a list of things you need to do to remove the confusion;

1. Remove all those lookup fields in your tables, this is at the crux of your form design problems.
2. Change the Name of all the ID fields in the table to something meaningful - EmployeeID, CourseID, CourseTakenID.
3. Store those as foreign keys in the target tables with the same ID names.
4. Change all your combo boxes to go directly to the tables they need to not the extended queries.
5. You might want to consider building a simple function to display the full name from your employee tables rather than the slightly complex IIf() you have constructed. You can then use that anywhere you want to display the name instead of the ID.

I think then that your basic table design is sound and your forms will suddenly work as you expected.

 

[nick1408]

Thanks mate - I'll work on that a bit later today and report back

 

[nick1408]

@Nick your design is confusing with the use of the Extended queries to display the names and ID's instead of dealing directly with the tables.

You are using lookup fields in your tables based on your extended queries - this is causing you a double whammy of confusion. Here is a list of things you need to do to remove the confusion;

1. Remove all those lookup fields in your tables, this is at the crux of your form design problems.I think so too. What is the best way to consolidate the various fields into one so I don't need to use a lookup field?
2. Change the Name of all the ID fields in the table to something meaningful - EmployeeID, CourseID, CourseTakenID.Done
3. Store those as foreign keys in the target tables with the same ID names.Not sure how to do this, sorry
4. Change all your combo boxes to go directly to the tables they need to not the extended queries.Done
5. You might want to consider building a simple function to display the full name from your employee tables rather than the slightly complex IIf() you have constructed. You can then use that anywhere you want to display the name instead of the ID. Changed to Employee Name: [First Name] & " " & [Last Name]. Also changed Courses Extended query to the same syntax.
   
   I pinched most of this structure from a MS template. A lot of it is actually beyond my Access knowledge but I'm fumbling my way through.

I think then that your basic table design is sound and your forms will suddenly work as you expected.

Thanks again mate. It has taken a bit longer than expected due to other commitments. So far I have done as per bold above.