Re: Problem with VBA Code behind form

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 12:42:51 -0500

"Tom" <tmillington@aavf.co.uk> wrote in message
news:Hu6dnco_r4Ce5IvfRVnyuQ@eclipse.net.uk
> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
> news:eZNU$qgFFHA.228@TK2MSFTNGP15.phx.gbl...
>
>> Huh. I've never seen anything like that before. I'd be more
>> inclined to suspect a problem with your video driver than anything
>> else. What version and service-level of Access are you using, and
>> what operating system?
>
> Access 2000 with all updates installed. Tried on two PCs: (a) Pentium
> 330 with 584Mb RAM running Windows 2000 and onboard graphics, and (b)
> Sony Vaio laptop with Centrino 1.7M CPU and 512Mb Ram, running XP Pro
> with Radeon 64Mb graphics card. Same on both.

Hmm. That doesn't sound like it could be a video driver or hardware
problem, so I'm at a loss. Let's try to solve the data problem, and see
if that makes the display problem go away.

>> If your code behind the "Print" button first updates the table and
>> sets the selected records' Status to 'P', then those records won't
>> show up on the report, will they?
>
> Yes, because the report uses the field REC_ID on the form where
> checkboxes are set to get the record numbers to update. The status is
> only checked when the form is loaded or opened to confirm which
> records are to be displayed for selection.

But that's not what you said earlier. See my reply to your other recent
message in this thread.

>> Okay, so now I have to ask what "colCheckBox" is. Have you done
>> something very clever to avoid binding the check boxes on your
>> continuous form to a field in the form's recordsource?
>
> I have reproduced somebody else's 'very clever' (courtesy of Albert D
> Kallal -
> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html ). I
> had to tweak it a bit, but it works.

I thought it must be something like that. I don't see anything wrong
with that, though I probably wouldn't do it that way myself unless I had
to. Still, we must consider the possibility that your tweaking
introduced some sort of error.

>> Here is a how I think I would do something like this. If only one
>> user (at a time) will be authorized to print these records, I'd
>> probably define an additional value for the Status field, meaning
>> "to be printed". The act of selecting a record on the form --
>> whether by a button on each record or by some other means -- would
>> set the Status to that value.
>>
>> I'd change the report's recordsource to a query that returns only the
>> records with a Status of "to be printed". That way, the report would
>> need no special criteria at run time, and if it doesn't get printed
>> the first time for some reason, the records will still be marked for
>> a reprint. I'd have code in the Report's Close event that displays a
>> MsgBox dialog to ask the user if all the records printed
>> successfully. If the user answers yes, I'd execute an update query
>> to change Status to some other value for all those records where
>> Status was "to be printed", and then I'd requery the form (if it's
>> open).
>>
>> On the other hand, if multiple users are going to be marking records
>> and printing, and each user must print only her own marked set of
>> records, then I'd take a different approach. I'd probably use a
>> local table in the front-end to store the IDs of the records to be
>> printed -- selecting a record for printing would result in its ID
>> being stored in the local table. Then the report would just be
>> based on a query that inner joins the main table with the
>> "IDsToPrint" table on the ID field, so only those records would get
>> printed. If the report prints successfully, I'd first execute an
>> update query to change the status of the records in the main table,
>> and then execute a delete query to empty the IDsToPrint table.
>
> At this time, we believe there will only ever be one card
> administrator, and only about 100 or so active cards. With this low
> number of records in mind, I thought it would be simplest to work the
> following
> status sequence, thereby allowing a query just to look for a specific
> status when an action is required.
>
> 1). An operator can only have one active card at a time.
> 2). When a user is first allocated a card, it has status (N)ew.
> 3). The sequence under discussion here takes the (N)ew card, prints
> it, changes it status to (P)rinted and stamps the record's PRINT_DATE
> field. 4). The new card now exists (barcode format) but has to be
> (A)ctivated as it may not be convenient to hand over the card at the
> time it is printed. When they want to (A)ctivate the card, it
> (S)uspends any
> active card. The newly activated card goes to status (A)ctivated and
> has current date loaded into the ACTIVATED_DATE field. The old card
> has its status set to (S)uspended and has the current date
> loaded into its SUSPENDED_DATE field. In this way, we keep the
> transactions both separated, and provide our own audit trail. Each
> action also logs the administrator's ID and the PC network name.
>
> For your interest, this is the administration part of a barcoded data
> collection system for reporting scrap during the manufacturing
> process: ie, operators scan in a job number, an operation number,
> the amount of items they received to work on, the number of goods ones
> produced, the number of scrap items, and a reason code for any scrap
> produced (all known as Work in Progress). The WiP side
> is the easy bit and I have already done that.

This all sounds pretty good, so it's just a matter of getting the
sequence of event to come out right. I still believe that you really
have an additional Status for a card, that you haven't properly
accounted for -- "W" : Waiting to Print.

If I were doing this, I would drop all that extra code, have a button on
the continuous form that changes the status of a record from "N" to "W",
then have the print button save the current record (if it's dirty) and
just open the report with no WhereCondition. The report, though, would
be based on a query that selects only the records with Status = "W", and
thus would print only those that are waiting to print. I would set the
query's RecordLocks property to All Records, so that no one can update
the table while the report is printing, and I would have code in the
report's close event ask if all cards printed properly. If the user
says they did, the code would then run an update query to update the
table and set all records with Status = "W" to Status = "P". 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)