We have a program built around MS SQL Server, front end is Access project with a lot of VBA code (I think). My programmer moved on and it's unlikely that my superiors can afford a replacement, so I'm trying to learn enough to complete our project. (I work for the USMC, but I'm a manager/Family Therapist.)
Here's my problem/question:
I have a report which can provide details based upon a Command/Battalion selection. I want the user to be able to choose the the Command and a filtered list of associated Battalions for that Command. The choices then populate the report with appropriate data.
I've used a stored procedure to acquire the data for the report and it works beautifully . . . if you have memorized the Commands and Battalions so that you get the right data. I do not understand how to present the Command and Battalion information to user and then pass the user selection to my procedure to get the right data.
There is more than one way to skin this cat, but none of them are very difficult. Here is a general solution:
I assume (hope) you have a table of Commands with a unique key, and then a table of Battalions with a field linked to the primary key of the Commands table. The battalions table should also include a unique index, since I suppose it is possible for two battalions to have the same name if they are in different commands?
Create a form, (it does not have to have a datasource) and add a combo box do it that uses the Commands table as its source and includes the primary key field.
Create a second dropdown on the form for battalions, but leave it's rowsource blank and leave it disabled by default.
Next, create an ON CHANGE event for the Command dropdown that performs the following actions
1) Set the value of the battalion dropdown to null, clearing any existing value.
2) Set the datasource of the battalion query to an SQL query string similar to this: "select battalion_id, battalion from battalion_table where command_id = " + [command_dropdown].
3) Requiries the battalion dropdown
Add a button to your form that opens the report.
Modify the report to filter on the battalion_ID field from your form.
...yes, the details of this are going to depend on your table structure, but this should give you an idea of the direction you need to go.
OK, took a look at your directions and the structural elements are already in place. Where I get stuck is little things like: "1) Set the value of the battalion dropdown to null, clearing any existing value." I'm a novice, and while I have examples of what the orignal programmer did, when I copied and suitably modified (I thought) his code, it didn't work.
Would you be willing to communicate directly via email so I could show the code I have and perhaps you could point out what I'm missing? [I will not be offended with a no. 8-)]