So you have 52 fields, named like "Week1", "Week2", etc.?
First, I highly suggest you normalize your database. See the links in my signature for information. You will have problems forever with this kind of setup. You should not have to have 52 queries and 52 reports for 52 fields. Highly inefficient.
That being said, here's a solution, tho again not my preference.
Instead of a query "prompting" you for an [Area], how about this instead: From a form, the user will pick an area, enter in a week number, hit a button and produce a report. All without using any of your queries. How's that?
Without details from you on table and field names, I've made some assumptions. I have a table called "Weeks", with fields "Area", "Week1", "Week2", "Week3", etc. If you have more information you want on your report, you'll have to tweak as necessary.
AREA combo box: First let's make a form that pulls that information for you. So make a new form; on it put a combo box which has a rowsource of a unique list of your Areas. So it will list for example:
cboArea
Loading Dock
Shop
Engineering
The ideal situation is that you have an existing table which lists each 'area' one time. If you don't, it would be a good idea to have one (normalization/relational database rules again). If there are only a few areas and they never or seldom change, you can make the combo box row source a VALUE list and just type them in yourself. In any case, use the wizard to make a combo box. Call it cboArea.
WEEK NUMBER text box: Then make a text box, and call it txtWeekNumber. In order to make sure the user enters something valid in it, you could
a) set the DEFAULT property: 1
b) set the VALIDATION RULE property: Between 1 And 52
c) set the VALIDATION TEXT property: Must be a value between 1 and 52!
BUTTON: Now put a button on your form. Cancel when the wizard starts up because we're going to write our own code. View PROPERTIES and in the button's NAME property, name it btnViewReport. In it's OnClick event, pick [Event Procedure] and hit the build button (the little button with three dots on it to the right). In that event, paste this code. What it does is take the Week Number the user puts on the form (i.e. 1 or 2 or 18), adds it to the word "Week" to end up with the field name you want (i.e. Week1 or Week2 or Week18), then calls that field a generic name "ReportWeek" which is the field you'll end up putting on the report.
Code:
'First check to make sure the user selected an area
If Me.cboArea = "" Or IsNull(Me.cboArea) Then
MsgBox "Please choose an Area!", vbOKOnly, "Missing Info"
Me.cboArea.SetFocus
Exit Sub
End If
'Make sure txtWeekNumber is an integer
If CInt(Me.txtWeekNumber) <> Me.txtWeekNumber Then
MsgBox "Please enter an integer!", vbOKOnly, "Invalid Info"
Me.txtWeekNumber.SetFocus
Exit Sub
End If
'Create the recordsource for the Report
DoCmd.SetWarnings False
Dim strSQL As String
strSQL = "Select [Area],[Week" & Me.txtWeekNumber & "] as ReportWeek from [Weeks] where [Area]='" & [cboArea] & "'"
DoCmd.OpenReport "Report1", acViewDesign
Reports!Report1.RecordSource = strSQL
DoCmd.Close acReport, "Report1", acSaveYes
DoCmd.OpenReport "Report1", acViewPreview
DoCmd.SetWarnings True
REPORT: Now make a new report. In Design mode, in it's recordsource, put
Code:
SELECT Area, ReportWeek FROM Weeks;
This will give you the two fields you want. Again, if you want more, put them here. Bring all of the fields you want into the report. Save it as Report1. You can change the name later, and at that time you'll have to tweak the code above to replace "Report1" with whatever you name the report.
Close the report.
View the form in Form View. Pick an area and enter in a Week number. Click the button. The report will open in design view, set the recordsource to pull the correct field's data, close, then reopen in PREVIEW mode with the info you want.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
