Cascading Combo Boxes Employed to Look Up Records in Subform

[pickaxemellie]

Hi! I'm somewhat new to Access, but have a pretty good understanding of how to build a database without using VBA. Sigh... I should probably learn how to use it. However, the current project I'm working on is nearly finished, so I just want to wrap it up.

I have a form called "Performance Expectations," which is a continuous form bound to a table of the same name, filtered by two cascading combo boxes. The first combo box filters the REP Name and REP ID to the next combo box, which filters the Infraction Date and Expectation Category. With the records filtered in this manner, I should see the record I want in the subform, however, it stays blank. With this form, I want to be able to filter to the correct infraction and update the record.

Any help or feedback offered is genuinely appreciated.Melanie's Copy REPit Tracking Database.zip

[ranman256]

i thought you cannot have subform on continuous forms.
upon update of combo39, filter the form:

me.filter="[clientid]=" & cboClientID & " and [field]='" & cboCode & "'"
me.filterOn = true

[pbaldy]

Your combo isn't returning a date, it's returning date plus text. If you get it returning just a date, this should work (VBA):

Me.Performance_Expectations_Subform.Form.Filter = "[Infraction Date] = #" & Format(Me.Combo39, "mm/dd/yyyy") & "#"
Me.Performance_Expectations_Subform.Form.FilterOn = True

[ssanfu]

I see many things that should be corrected...

In response to your question:

I have a form called "Performance Expectations," which is a continuous form bound to a table of the same name, filtered by two cascading combo boxes. The first combo box filters the REP Name and REP ID to the next combo box, which filters the Infraction Date and Expectation Category. With the records filtered in this manner, I should see the record I want in the subform, however, it stays blank. With this form, I want to be able to filter to the correct infraction and update the record.

In "Performance Expectations", the combo boxes are not set up correctly.
The first combo box, "Rep", the row source has 2 fields, with the 2nd field as the bound field.
When you click on the DATA tab and look at the Column count, it is 1. The Column Widths are: 1";0.5521".
The way you have set up this combo box the Column count should be 2 and the Column Widths should be 1",0". (Not the way I would set up the combo box )

In the second combo box, "Combo39", the Row source has two fields.
the bound column is 1
the Column Count is 1 (should be 2)
the column widths are 0.8855";1.8021";0.4375" (should be 0";1.8021")

I would not use a subform in this case. I would put the data in the detail section of the (main) form.
The main problem is the table design. There is no way to identify which record to display. I had to add an autonumber field, then modify "Combo39" row source, then set the form filter to display the data.
See attached example dB.

---------------------------------------------------------------
Other things that I think should be corrected:

"Name" is a reserved word in Access and shouldn't be used for object names.
Delete ALL SPACES in object names!! This includes Field, Table, Query, Form, Report and Module names.
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
(that means "REP ID#" is a POOR field name -> has a space and special character in name)

IMHO, EVERY table should have an autonumber PK field.
I add a suffix of "_PK" for the PK fields and a suffix of "_FK" for the foreign key fields. Just easier to read.
I never use macros, so I would convert all macros to VBA. I understand some people have a problem with VBA, but Macros are too limiting (for me).
Also not a good idea to use make table table queries in production dBs...

Good luck with your project........


I cut out a lot of stuff in this example of

[pickaxemellie]

Thank you, Steve, for helping! I have completed my project with your guidance

Also, thank you to all the other responders. You all are helping me learn how to navigate Access, and I truly appreciate it

[ssanfu]

You are very welcome....