Re: Writing Executable VBA Statements

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ted (Ted_at_discussions.microsoft.com)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 13:37:03 -0800

well, that's very well and i'd like to try cobble down some real details if i
may at this point, rob...

so here's my take

say i create a cmdButton called 'Big Duplicate' or other. i place this on
the sub-form (called "Adverse Events (child)") which is itself on the main
form (called "Treatment and Toxicity").

say i also need a query or two. i think i need an select and/or an update
query and your append query. i think the select and/or update query would be
restricted to just those records having "Continuing" = "Yes" and "Duplicated"
= "No". its/their task would be to cull those records having the same
Patient Number and Cycle number as the record the user was viewing on his
screen and "update" the value of the "Duplicated" value to "Yes" and somehow
add 1 to "Cycle". then this subset would be appended back to the main table
"Adverse Events (child)" (same name as its sub-form). these would go into the
design of a macro's.

i know i could use some help getting the above straightened out.

moreover, it'd be good to know how to make the task of the macro's sifting
through the table seamless. so, my next question's got to do with how to
adjust the query/macro to ignore those records other than those corresponding
with the "Patient Number" and "Cycle" the user's looking at on the form on
the entry screen. would i put something into the 'Criteria' field in the
query or in the macro. given the names i'm using what would that look like.

thanks for the suggestion, this looks promising -- i might even just decide
to retain the original button i wrote about since it wouldn't do any harm
that i can foresee.

-ted

"Rob Oldfield" wrote:

> I've never actually run any speed tests to attempt to compare the recordset
> approach against the query approach but I'd guess that the query should
> generally be faster - the Jet engine outperforming VBA. A query will,
> almost certainly, be easier to understand when your user asks for a 'small
> change' in a year's time as well.
>
> And yes, I think you could certainly extend the idea to fit the additional
> idea you've come up with. That would be dependent upon just ensuring that
> your flag system is fully normalised.
>
>
> "Ted" <Ted@discussions.microsoft.com> wrote in message
> news:0EA5F1DB-9275-4A7A-849E-C7D080FA487D@microsoft.com...
> > hi rob, and first of all, thanks for the bandwidth. it sounds promising.
> i'm
> > usually reluctant to use macros and queries 'cause they seem somehow less
> > 'high-tech' and glossy than this stuff, but the apparent ease of
> implementing
> > this is attractive.
> >
> > after posting my query, it occurred to me that the user might have reason
> to
> > do a bulk duplication on more than one occasion for the same individual,
> e.g.
> > say two weeks after it, she entered some more adverse events for the same
> > patient which continued into the next cycle. at that point we have a
> > situation where i think we would want to cull just those aes which had not
> > been duplicated the first time for this bulk duplication. what would that
> > require, then, some sort of flag ('Duplicated' = 'Yes' or 'No') field
> which
> > is by default 'No' and which gets toggled to 'Yes' in the process.
> >
> > -ted
> >
> > "Rob Oldfield" wrote:
> >
> > > If I have what you're after correct, then instead of doing it using
> > > recordset type code, it's going to be a great deal easier just to use an
> > > append query. Search for all the records you want duplicated, add 1 to
> the
> > > cycle number, and append it back on to the same table.
> > >
> > > (...let me know if you need any more detail.)
> > >
> > >
> > > "Ted" <Ted@discussions.microsoft.com> wrote in message
> > > news:10B56C3B-8FB3-4D32-9433-F7119F13268A@microsoft.com...
> > > > i use a2k on a windows 2000 professional desktop.
> > > >
> > > > i built an application a while back which my users have finally got
> 'round
> > > > to testing and i want to go over one of the features i put together as
> > > part
> > > > of a response to a 'wish list' from one. the same one wishes it would
> go
> > > one
> > > > better.....
> > > >
> > > > the ingredients of the scenario go something like this:
> > > >
> > > > there is a main form with a nested sub-form. the main one's called
> > > > 'Treatment and Toxicity' and the sub-form's called 'Adverse Events
> > > (child).
> > > > the linking child fields are "Patient Number" and "Cycle". the linking
> > > master
> > > > fields are "Patient Number" and "Current Cycle Number".
> > > >
> > > > there is a listbox on the subform which can be "Yes" or "No" which
> > > indicates
> > > > whether the information coded for the patient on the current cycle
> > > continues
> > > > into the next cycle. since these are adverse events in a clinical
> trial
> > > and
> > > > the data are being captured for each of multiple numbers of cycles in
> the
> > > > trial the patient goest through until he de-enrolls or finishes it, an
> > > > adverse event (e.g. 'headache', etc. etc) have continue beyond the end
> of
> > > the
> > > > current cycle. so, the data entry person asked if it'd be possible to
> > > > automatically duplicate a lot of the information in the subsequent
> cycle
> > > > which she entered for the patient's current cycle.
> > > >
> > > > the code i designed to do this is below:
> > > >
> > > > Private Sub Duplicate_Click()
> > > > Dim Response As Integer
> > > > ' pending pending
> > > > Response = MsgBox("Before proceding to duplicate this record, verify
> that
> > > > the top part of this form " & Chr(13) & _
> > > > "for this patient's next cycle (i.e., MR and Cycle) were already
> entered.
> > > If
> > > > not, press 'Cancel', otherwise press 'OK'.", vbOKCancel + vbCritical +
> > > > vbDefaultButton2, "Critical !")
> > > > If Response = 1 Then
> > > > With Me.RecordsetClone
> > > > .AddNew
> > > > ![Patient Number] = Me.Patient_Number
> > > > ' ignore ![Cycle] = Me.Cycle + 1
> > > > ![Cycle] = Forms![Treatment and Toxicity].[Current Cycle Number] + 1
> > > > ![AE Description] = Me.AE_Description
> > > > ![Subtype] = Me.Subtype
> > > > ![Onset] = Me.Onset
> > > > ![Grade] = Me.Grade
> > > > ![Serious] = Me.Serious
> > > > ![Attribution] = Me.Attribution
> > > > ![Action] = Me.Action
> > > > ![Outcome] = Me.Outcome
> > > > ![DLT] = Me.DLT
> > > > ![AER Filed] = Me.AER_Filed
> > > > ![ContinuingEndCycle] = "No"
> > > > ' the following is intended to add a comment in the Updates field
> > > > ![Updates] = "On " & Now() & " , " & LAS_GetUserName() & " duplicated
> this
> > > > patient's Cycle #" & Forms![Treatment and Toxicity].[Current Cycle
> Number]
> > > &
> > > > " record."
> > > > .Update
> > > > Me.Bookmark = .LastModified
> > > > End With
> > > > Else
> > > > End If
> > > > End Sub
> > > >
> > > > according to this data entry person, it works like the proverbial
> charm.
> > > > however, she's apparently gotten even lazier since i developed this
> and
> > > now
> > > > wishes that there were some way that pressing a button would set off a
> > > series
> > > > of events which would automatically populate the next cycle for the
> > > current
> > > > patient whenever the listbox were 'Yes' without having to
> individualize
> > > the
> > > > requests which she currently has to do.
> > > >
> > > > the 'Duplicate' command button on the subform is only 'active' when
> the
> > > > value of the listbox (whose name is 'Continuing') is 'Yes' as a safety
> > > > feature to minimize erroneously launching it. so the machinegun
> approach
> > > to
> > > > this would involve somehow sending my code through the database for
> the
> > > > current patient and current cycle, sifting through all records having
> a
> > > 'Yes'
> > > > for Me.Continuing and performing the desired action.
> > > >
> > > > i'm waiting for the mis people to send their technician to try to
> repair
> > > the
> > > > vba help documentation -- which could take quite a while -- so i
> figured
> > > i'd
> > > > bring this up in this venue.
> > > >
> > > > anyone?
> > > >
> > >
> > >
> > >
>
>
>

Quantcast