report/query update combination (1 Viewer)

[tcaprice]

Here's my scenario:

I have a query that randomly selects a record from an Employee Table for a prize drawing. This query is the source for a report that prints out a pretty "your the winner"document. All of this is working well. However, what I'd like to do and can't figure out how is:

For the record in the Employee Table that was selected, not only do I want the report to report on the data, but I would like to update a field in the Employee Table to designate that this empolyee had been selected. Somehow I need the employee id number that was selected passed to an update query to update the "Selected" field.

Is this possible in Access? If so, please describe how it can be done.

Much thanks!!

Tom

 

[glynch]

I'm guessing that every time you run the query it will pick another employee. In that case I would include the employee ID field in the query that produces the report, stick the value in a textbox, and set it's visible property to false. Then from the same code module that produces the report, run an update query to update a new field in your table inicating that this employee was selected.

You might also want to make that field a date field so you'll know when the person was a winner.

HTH

 

[tcaprice]

glycnh,

Thanks for the suggestion. I worked on it prior to seeing your method and I have solved my problem though thru a less sophsticated method than you propose. I ended up creating a macro where I had the first query do a random select and create a "Winner" table with the record selected. The next query updated my "Employee Master" with a "Selected Winner" tag and then my report has this new 1 record "Winner" table as it's source. Each time I run the macro I overlay the "Winner" table with the newest winner. I turned off warning messages in the macro so it seems seamless to the end user. More difficult than it probably has to be but it ended up meeting the requirement.

I am going to go back though and add the date selected to the Employee Master, that's a good idea.

Thanks again for your help.

Tom