Referencing a field on a form from a query

[Muzicmn]

Hi,

My situation is hard to explain but i will give it a shot. What i have is a db with employees and its being used to track training sessions. These employees are split into 6 teams and those teams are split into 7 teams each.

I have written two queries, first asks the user what team to display. This data is used in the second query. The second query asks for what training item we are searching for. The result of the 2nd query is displayed in a form. Displaying of the information on a form is important. This way the user can verify that the members on this list are the ones he is updating. Now I wrote a third query that works off of the 2nd and updates the datecompleted field with todays date.

Problem is when i post a button on the form to show all these employees complete it referes back to the first query and asks all the filtering questions again.

I would like to know if there is any way to run an update query from a form that has already been filtered. Or if there is a way to reference these records in a new query so as to avoid all the filtereing again

hope i explained it well

Thanks

Ricky

 

[WayneRyan]

Ricky,

If a query wishes to use a form field as criteria, all that's required is to
put the following on the Criteria line for the appropriate field:

Forms![YourForm]![YourField]

hth,
Wayne

 

[Muzicmn]

Thanks for the response but my problem is a little deeper than that.

The form I am trying to get data from is a continous form and i would like to use all the records that were filtered as a criteria for the query.

For example, I am using combo boxes to get me a list of records of all emplopyees who need a training item. Lets say i get 10 of them. Now i would like to be able to run a query that will show all 10 of them completing this item. With the method I am currently using I can only get the first record to show completed.

This works from the update query but i want to see the records before they are modified. If i bring the records up in a form using the combo boxes then try and run the query it asks me for all the filtering information again

Will appreciate any help or advice i can get. Thanks ahead of time

Ricky

 

[mresann]

Why not use the initial subform recordset to form the basis for a NEW query, and use that query as the underlying query to bring up a new form, so you can view the records without having to filter them, and then perform the update on them?

 

[Muzicmn]

thanks for the tip, will give it a try

Ricky

 

[QTNM]

I agree ...

S/He's absolutely right .... Use the same recordset ( query object ) to update your the dateComplete field of the recordset.

I would attempt this via a module .....

dim db
set rs=db.openrecordset (" ... etc ) then update all the records in this recordset using VBA SQL statement to update the recordset.

Does that make any sense .... or am I off the mark ?

QT

 

[Muzicmn]

I guess i am in over my head here. I understand the concept of what you guys are talking about but not sure on how to write a code that will accomplish that.

I was going to try to create fields (not visable) and place them on the form and when the update button is pushed have access place those fields in the NEW update query so as not to ask for the filtering info again.

I havnt had time to try it but it sounds like it should work

any comments???

or do you guys think I should create a module, (which i have never done)

many thanks

Ricky

 

[Sony170]

Here is a link that will help:

http://www.access-programmers.co.uk/forums/showthread.php?threadid=36145&highlight=multicriteria

scroll down to the database written by Candace Tripp and download it to your computer and give it a whirl to see if it does what you want