Running a Query from Combo Box and displaying result in Sub Form

shelds

New member
Local time
Today, 07:28
Joined
Mar 6, 2007
Messages
9
Hello

I haven't used Access for a while, and think I may have a difficult first project. The aim of the system is to allow support analysts to record results of daily checks, backups complete etc. There are 3 different support teams with analysts that are assigned to different check types. I will attach a ERM for you to view for ease of understanding.

What I would like to do is have a combo box on a form that allows you to select a analyst name, then do a query so it matches the analyst name selected to the checktypes assigned to them, via a joining table in a many to many relationship, i.e analyst id and checktype id. Then display these checktypes and in a subform so they can then enter the date, result etc next to their check.

Not sure how I do this though, I know the SQL query required would be something like:
Code:
 select checkname from tbl_checktype where
tbl_analyst_checks.checktype_id = tbl.checktype.checktype_id and
tbl.analyst.analyst_id  (or combo box value) = tbl_analystchecks.analyst_id

I used to be able to do something similar in ASP, but no idea in Access, even if this can be done? I want the analyst to be able to insert the data they enter via the subform, in the table afterwards also.

Anyone got any advice, I can't seem to find what I'm looking for on the web? Would really appreciate any tips or guidance.
 

Attachments

  • ERM for Database.jpg
    ERM for Database.jpg
    50.1 KB · Views: 170
For speed, Access usually uses inner joins instead of just a Where clause. whether or not this will work depends on where you will be using this. If you are using it VBA, then it would be fairly easy. Perhaps that is like giving directions to someone and saying, "you can't miss it," but bear with me for a second.

I like to start with the basics and I wonder about your table structure. Why is Team in the Check Type table?

Beyond that, if all you want to do is to, as your subject says, "[Run] a Query from Combo Box and displaying result in Sub Form," then use the VBA OnClick to run a query then requery the subform once the Analyst has been chosen.

Here is the query:

Code:
SELECT tbl_checktype.CheckName
FROM (tbl_checktype INNER JOIN tbl_AnalystChecks ON tbl_checktype.CheckTypeId = tbl_AnalystChecks.CheckTypeID) INNER JOIN tbl_Analyst ON tbl_AnalystChecks.AnalystID = tbl_Analyst.AnalystID;

If you want to use a parameter from the combo box:

Code:
PARAMETERS [cmbAnalyst] Long;
SELECT tbl_checktype.CheckName
FROM (tbl_checktype INNER JOIN tbl_AnalystChecks ON tbl_checktype.CheckTypeId = tbl_AnalystChecks.CheckTypeID) INNER JOIN tbl_Analyst ON tbl_AnalystChecks.AnalystID = tbl_Analyst.AnalystID
WHERE (((tbl_Analyst.AnalystID)=[cmbAnalyst]));

In vba, open this second query and set the parameter value in code before executing the query. I put some code for running queries in vba in another post today.

John M Reynolds
 
Forms and Queries

Hi

In reply, I put Team in the Checktype table, so for reporting I could query checks made by for example, the Helpdesk which were not completed. I suppose the team field could have gone in the Analyst table, but I think it would work this way also. It is also easy to add a check type and view a particular team's checks.

By the way, I'm new to VBA I have never yet had to use it in Access. I've managed to get so far. In the table structure I did a lookup on the rowsource for the foreign keys in the tables, so it retrieves the actual values, i.e analyst name and check name instead of the id. Like how setup in the northwind database. But am now stuck, when selecting the analyst name from a combo, and then selecting a checktype from the subform, the analyst name in the subform changes to the actual id number rather than the name as I specified in the lookup in the table structure. It works fine when going into table, but for some reason not in the subform. Is it something to do with how the subform updates when selecting a record? It then proceeds to add the id into the analyst name field in the check instance table and overwrites the analyst name in the analyst table!! Help! Not having much luck with this.
 
But am now stuck, when selecting the analyst name from a combo, and then selecting a checktype from the subform, the analyst name in the subform changes to the actual id number rather than the name as I specified in the lookup in the table structure. It works fine when going into table, but for some reason not in the subform. Is it something to do with how the subform updates when selecting a record? It then proceeds to add the id into the analyst name field in the check instance table and overwrites the analyst name in the analyst table!! Help! Not having much luck with this.

The combo box is likely bound to the employee id number. That is a good thing. If you want the name to show up in the subform, then you will have to display it. You can hide the id field if you like. It sounds like you are equating the name to the pull down list which is bound to the id number even though the name shows. You can also use the Forms!Contacts!Customers.Column(1) feature to access the employee's name though this may only work in vba. Note that the number in the brackets is 0 for the first column, 1 for the second, and so on.

If this does not help, can you post a picture of your form with an explanation of what you are doing?

John M Reynolds
 

Users who are viewing this thread

Back
Top Bottom