Query criteria - from different forms 1

[jfrizelle]

Hi. I have a Query that displays employees for a specified dealer - when I change the Dealer field on my form, I 'Requery' the Employee field. Now I want to do the same thing in another form, but the criteria in my query is hard-coded to read the Dealer code from the first form. I can't figure out how to make this dynamic - put the dealer code into a global variable and run the query based on that, or some such thing. It seems a bit pointless to have 2 queries to do the exact same thing.

Thanks for any help.
Jennie.

 

[Rolliee]

Why do you wish to requery? A requery really means to do a repeat of a query. A query is a question to a one of more tables the answer of which is ussually a set of records. Unless you change one of those records you need not requery.

 

[rdodge]

There's a few options you can use.

You can setup a global variable and then run the query based on the value of that global variable, provided this isn't going to cause a conflict among the different objects that uses this query.

You can setup the RowSource of a Combo/List box as a SQL Statement with it's RowSourceType property being set to "Table/Query", which then use the AfterUpdate event of a different control to change the SQL Statement, such as using the AfterUpdate Event of the Dealer's ID/Name field.

If you prefer to work with UNBOUND forms/controls like I do given issues that I have with Bound forms/controls, you can use DAO/ADO recordsets via VBA.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jfrizelle]

Hi Rolliee.

I'm creating training records for employees associated with specific dealers. If I select dealer 1000, I want to requery the employee field to only show employees associated with dealer 1000 - if I change the dealer field to 1001, I want to show the employees associated with that dealer to do this, in the AfterUpdate event for the dealer field, I have the code Me!Emp_Id.Requery. The employee field is built on a query that selects Emp_Id, Emp_Name and Dealer_Code, and the selection criteria for Dealer_Code is [Forms]![frm_Training]![D_Code].

However, I also have a form that allows employees to move to a different dealer - on that form, I have the From_Dealer, the Emp_Id, and the To_Dealer. Again, if the From_Dealer is changed, I want to requery the Emp_Id field to only show the relevant employees.

I have created a second query, identical to the first, except the selection criteria for the Dealer_Code is hardcoded to read a different field on a different form.

It's not really a big deal - I just like a neat solution that reuses what's already there, and this is kinda bugging me... I'd like to know if it's possible to reuse the existing query.

Thanks,
Jennie.

 

[Rolliee]

Jennie,

Use a mainform and subform. The mainform shows a dealer and associatged info. The subform shows all the employees for that dealer. Does that help. I also use a main form with a Details Button. Clicking that button brings up a new form which has all the employees of that dealer. Move up and down the employee list to see the data. Close that employees form and the dealer form is visible to make another choice. There are endless combo's. Just put yourself in the seat of the user and ask what would be intuitively next.