Combobox help (1 Viewer)

[Jonny45wakey]

Good Afternoon

I have a form in my Access DB called "frmSales" and it has the following fields:-

1. Initials
2. Company Name
3. Job No
4. Jan through to December (12 fields)
5. Date
6. Year

At the top of the form are 2 x comboboxes

cboYEAR which contains rowsource from table containing Years 2022 onwards

cboDept which contains the name of various departments

How best can i have the form open with no data presented then once the cboYEAR and cboDEPT are selected the fields filter the data accordingly

Any help appreciated

Jonny

 

[bob fitz]

Can you show us a screenshot of your table Relationships

 

[Gasman]

Use those combos as criteria?
As they will not hold anything on form load, then no data will me shown.
Then Requery when they are populated.
Presumably the combos are unbound?

 

[Uncle Gizmo]

4:- Jan through to December (12 fields)

Having the months as individual fields in your table is not a good idea!

It is spreadsheet thinking, used in a database. Although it works, unfortunately, it is the beginning of serious problems! Your database will be difficult to maintain at a later stage.

I cover the problems you will face in detail, in my blog here:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[Pat Hartman]

Several problems
1. Using the names of functions as column names such as Date and Year causes problems if you also try to use those functions. Using property names such as "Name" causes even worse problems. Use names like SaleDT or SaleYR
2. Storing year separately duplicates data and opens the possiblity of a data anomoly in that the year part of the date field might be different from the value of Year. Don't do it. You can always extract year from the date. If you insist on doing this anyway, do NOT allow the user to enter the Year. You generate the value in the AfterUpdate event of the Date field.
3. Storing an array of data in a single row is poor practice. Each month should be a separate row. So instead of one row with 12 columns, you have 12 rows with one month each.

To actually answer the question:

In your RecordSource query, use criteria that references the two combos. @Gasman alluded to this solution but was too vague.

Select .. From .. Where SaleYR = Forms!yourformname!cboSaleYR AND Dept = Forms!yourformname!cboDept

Using this technique, the form will open empty. Then in the AfterUpdate event of each combo, requery the form.

Me.Requery

PS, you didn't list Dept as one of the controls on the form.