edit table based on query (1 Viewer)

[mcw21j]

Hello folks,
I’m hoping I can get some guidance from you good people. The short story is that I need a “checks” table that lets the user select check numbers that need to be printed. The "checks" table then feeds a report which lists all of the check info the user has selected. Once the check is selected, it should not appear again in the list. The problem is that the “checks” table needs to be based on a query of 2 other tables.

The way I have it setup is that an append query (involving a payments table and a company info table) populates the “checks” table. Each time the user clicks a print command button, I have a delete query followed by an append query which wipes out the “checks” table, and then populates it with the results of the append query. The checks table has a checkbox that the user selects when they want to print the check info.

Since the “checks” table is wiped each time, the check numbers that were selected appear each time as unselected. I would like that once the user selects the particular check number, it never appears again.

I hope I explained the situation well enough. Any thoughts are appreciated.

Mark

 

[Isskint]

The simple answer here is to include a 'printed' true/false field to whichever table holds the check number. This can then be set to TRUE when the check is printed.
If that table is the 'checks' table, then stop deleting the data. I would have thought it would be good to know when a check was printed?

 

[mcw21j]

Hi there,
Thank you for your reply. I have trouble posting from work so here i am on a sunday at home with a giant cup of coffee replying to my postings. haha.

well, I already do have a true/false field in the table that holds the check number.

in this scenario, the user will be able to print the selected checks successfully the FIRST time only. the next time he wants to print, it will print all the new ones he has selected PLUS all the old ones.

I would need the list that he selects from to only show the checks that he has never printed before. once he has selected and printed the checks, i need these records to disappear so that he does not even see them again.

Is there a way to do this?

I tried adding the primary key from the payments table which i think would work but since a single check can be applied for multiple payments, it would not let me group the data so that only single check data is selected.

I also thought of putting a date field that would calc to "Now()" or something similar each time the check was printed. then the criteria would be some iff statement to print only those records from today's date. unfortunately, calculated fields were not allowed in the query (or maybe i just don't know the proper syntax?).

any help is appreciated...especially you are reading and replying to this on a sunday!

 

[Isskint]

well, I already do have a true/false field in the table that holds the check number.
I also thought of putting a date field that would calc to "Now()" or something similar each time the check was printed. then the criteria would be some iff statement to print only those records from today's date. unfortunately, calculated fields were not allowed in the query (or maybe i just don't know the proper syntax?).

I take it the true/false is used to indicate which check(s) need printing? If so just add an extra true/false to indicate printed. That way if [Printed]=TRUE AND [ToPrint] = TRUE then you know its a reprint, whereas if [Printed]=FALSE AND [ToPrint] = TRUE then you know its a first print.

Equally you could use the date method. In a query you would simply use Date(). But I wonder if there is another trick here? Do checks get printed more than once? if so you might consider an extra table (PrintID,CheckID, PrintedDate, Reason). When you print a check, use an append query to add the checkID and print date. This way you have an historic audit of check prints.

 

[mcw21j]

i went the dual checkbox route and it worked! thanks so much. the user sees only the [Print] box that selects the check to print. i attached an update query to the command button that opens the report. the update query updates the [Printed] value to true if ([Print] = true). each time the user reruns this, they only see records where ([Print] and [Printed] = false).

this opened up another issue with adding new payment records to the table via append query. i was getting warning messages for key violations. i created a 2-field key, supressed the warning and now it runs great!...at least in this test version. i will put this together this week on the live version. crossing fingers.

Thanks again!