how get a form to update 2 tables (differently: append and replace

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Newbie here.
I am creating an ID badge app. I want each
new staff person's record entered in my form
to be appended to the MASTER TABLE of all
employees. No problem.

I am using a report to display/format all the
id Badges for printing. Since I need to print
only the latest staff badges at one time not
a whole page (for example, just print the
badges for 2 staff just hired today), I am
thinking the best thing to do is have the ID
Badge report draw from another TEMPORARY
TABLE that just contains the records I want
to print today.

This requires having the form append the new
staff records to the MASTER TABLE and at the
same time replacing all the records on the
TEMPORARY TABLE with these new staff
records...so my report will print only these
new ones.

I looked over the help file and discussion
threads and am still not sure the best way to
do these things:

1.) cause a form to put the same records on

two tables (identical fields)
2.) have the records be appended to the
'master' table and replace all records on the
'temporary' table

Appreciate any leads.
Thanks much.

Paul
(e-mail address removed)
 
In your MASTER TABLE create a Yes/No field titled Badges and make the default
No.
Create a one time query to change the field to "YES"(-1) to mark all current
employess as having badges. Whenever a new employee is hired the field will
be marked "NO" (0). To create a report for those needing badges first create
a query with the criteria of the Badges set to 0.
 
You may want to consider adding a field, such as, DateAdded and append Now()
when adding the record. Then you can query on the current date or, maybe,
DateAdd("d", -1, Now()) to get the previous day's.
 
Thanks, Solun. That's what I needed.
It works great.

Is there a way to set the Badge yes/no field to yes automatically when I
print from the report? So when I print it, the staff's badge won't come up
again.
Thanks again,
Paul
 
You may want to place a SQL statement in the report open or close event of
the report. e.g.
DoCmd.RunSQL "UPDATE BadgeTable SET Badge = True WHERE
DateAdded=FunctionThatReturnsYourDate();"
 
Back
Top