3 small vba routines

[Ted]

there are two fields on a certain form in my a2k app'n that are of interest
to this thread: "Patient Number" and "Lesion Number". when the form's
opening, the first one is completed 'automatically' via this code immediately
followin:

Private Sub Form_Load()
LAS_EnableSecurity Me
Me.Patient_Number.Value = Forms![RECIST Disease Evaluation: Nontarget
Lesions]![Patient Number]
End Sub

there are repeated instances of the same value of "Patient Number" when the
user is doing their entry (it's also a 'filtered' form so only one patient
number's value is seen at any one time) and what is would like to do is
'automate' the task of entering the "Patient Number" AND also incrementing
the value of "Lesion Number" (1,2,3,....) as records are added.

Private Sub Lesion_Number_Exit(Cancel As Integer)
Me.[Lesion Number] = IIf(IsNull("[Lesion Number]"), 1, 1 + DMax("[Lesion
Number]", "Lesions: Non-Target", "[Patient Number] = " & Me![Patient Number]))
If Me![Lesion Number] > 10 Then
RetValue = MsgBox("Delete any records exceeding the upper limit",
vbInformation)
End If
End Sub


Private Sub Patient_Number_AfterUpdate()
Me.[Patient Number].DefaultValue = "'" & [Patient Number] & "'" ' that's
a single quote within doubles...
End Sub

the first one works just fine. but the 2 and 3rd don't seem to be quite in
synch with each other yet. when the cursor is in the "Patient Number" field
and it's blank i want the duplicated value entered AND i want the "Lesion
Number" to be one more than the previous one (which assumes the records are
sequentially numbered 1,2,3,,,,)

any thoughts?

 

[Tim Ferguson]

Me.Patient_Number.Value = Forms! _
[RECIST Disease Evaluation: Nontarget Lesions]! _

[Patient Number]

Me.[Lesion Number] = IIf(IsNull("[Lesion Number]"), _
1, _
1 + DMax("[Lesion Number]", _
"Lesions: Non-Target", _
"[Patient Number] = " & Me![Patient Number]))

Me.[Patient Number].DefaultValue = _

"'" & [Patient Number] & "'" '

the first one works just fine. but the 2 and 3rd don't seem to be
quite in synch with each other yet.

First of all, you don't say what errors you are getting, and I am not
completely sure what you are trying to achieve here either. Still,
there's a couple of things to notice.

If the first example works, it's because there is a control on the
current form called Patient_Value. The other functions refer to a control
called [Patient Value] with a space character in the middle of it.

I don't know if "Lesions: Non-Target" is a legal table name in the DMax
function, but to be safe I'd rewrite it as "[Lesions: Non-Target]".

Better than either of those, though, would be to revisit your entire
naming strategy. Characters like ":", "-", and above all " " are legal in
Access but that's a weakness in Microsoft not an asset, and you will get
burned if you try to move this sort of stuff to a real database some time
in the future. Get used to using proper names now and you will save
yourself immeasureable heartache later.

HTH


Tim F

 

[Ted]

it's not that i'm getting any error/information messages, per se, tim; it's
that the desired beharior's not getting thru.

i started out w/ the Dmax statement at one point and it worked fine; it
would increment the value of the Lesion Number with every additional record's
creation via this form.

then i got sorta ambitious and thought i'd try my luck and get the app'n to
generate duplicated values of the "Patient Number" control (both of which btw
were comboboxes).

i changed Patient Number and Lesion Number controls to be textboxes.

i wrote this one line code to duplicate the former.

now i'm trying my hand at putting together a little mechanism that would
duplicate the value of the Patient Number and increment the value of Lesion
Number when the user proceeds to add another record.

making more sense?

-ted

Me.Patient_Number.Value = Forms! _
[RECIST Disease Evaluation: Nontarget Lesions]! _

[Patient Number]

Me.[Lesion Number] = IIf(IsNull("[Lesion Number]"), _
1, _
1 + DMax("[Lesion Number]", _
"Lesions: Non-Target", _
"[Patient Number] = " & Me![Patient Number]))

Me.[Patient Number].DefaultValue = _

"'" & [Patient Number] & "'" '

the first one works just fine. but the 2 and 3rd don't seem to be
quite in synch with each other yet.

First of all, you don't say what errors you are getting, and I am not
completely sure what you are trying to achieve here either. Still,
there's a couple of things to notice.

If the first example works, it's because there is a control on the
current form called Patient_Value. The other functions refer to a control
called [Patient Value] with a space character in the middle of it.

I don't know if "Lesions: Non-Target" is a legal table name in the DMax
function, but to be safe I'd rewrite it as "[Lesions: Non-Target]".

Better than either of those, though, would be to revisit your entire
naming strategy. Characters like ":", "-", and above all " " are legal in
Access but that's a weakness in Microsoft not an asset, and you will get
burned if you try to move this sort of stuff to a real database some time
in the future. Get used to using proper names now and you will save
yourself immeasureable heartache later.

HTH


Tim F

 

[Tim Ferguson]

now i'm trying my hand at putting together a little mechanism that
would duplicate the value of the Patient Number and increment the
value of Lesion Number when the user proceeds to add another record.

For my taste, I prefer to handle this explicitly by, for example, forcing
the user to go through a search dialog or pressing a button that says
"Give me a new record, now!".

Whatever you choose to do, you do have to map out the process so that the
allocation happens at a correct and predictable moment. Just dropping
some code into an event does not always give the desired outcome! For
example, in your code, you use the Form_BeforeUpdate event to change the
current record. Off-hand, I have no idea what effect that has on the
current data or even whether it's legal -- regardless of whether it would
sometimes work or not, the very fact of me being uncertain would rule it
out as a plan for me, if you see what I mean.

All the best


Tim F

 

[Ted]

so what i 'want' to do is add a cmdbutton to the form to 'give me a new
record' and remove the 'property' which allows the user to user the "*"
feature at the bottom of the screen. prior to getting the urge to kick this
up a notch so to speak the following code was in the afterupdate property of
the 'Patient Number',

Private Sub Patient_Number_AfterUpdate()
[Lesion Number] = IIf(IsNull("[Lesion Number]"), 1, 1 + DMax("[Lesion
Number]", "Lesions: Non-Target", "[Patient Number] = " & Me![Patient Number]))
If Me![Lesion Number] > 10 Then
RetValue = MsgBox("Delete any records exceeding the upper limit",
vbInformation)
End If
End Sub

it WORKED just fine.

then i started tinkering with the process and tried to work an 'automated'
ability to begin with the "Patient Number" which the user had selected to
filter the records appearing in these continuous forms in another (and open)
form. "Patient Number" and "Lesion Number" are both at this time
comboboxes/lookup controls restricted to their lists.

i then added

Private Sub Form_Load()
LAS_EnableSecurity Me
Me.Patient_Number.Value = Forms![RECIST Disease Evaluation: Nontarget
Lesions]![Patient Number]
End Sub

to the form's onload property with a view to populating the form under
discussion with the "Patient Number" designated by the user in the 'other'
(open form) w/o intervention.

and then i found news on these pages that

Me.[Patient Number].DefaultValue = "'" & [Patient Number] & "'" ' that's
a single quote within doubles...

would suffice to duplicate the value of the "Patient Number".

since that point, i've tried hacking the loose ends into a coherent whole
what would 'work' but still no cigar.

it's also occured to me that in lieu of the 'onload' event code above i
might be able to fiddle with the 'Default' value of the "Patient Number"
and/or the "Lesion Number" if i change them to textboxes, i.e. have them open
with a duplicate of the initial value OR the value in the RECIST Disease
Evaluation: Nontarget
Lesions] form and be 1 more than the preceding value of the "Lesion Number"

what do you think?

 

[Tim Ferguson]

so what i 'want' to do is add a cmdbutton to the form to 'give me a
new record' and remove the 'property' which allows the user to user
the "*" feature at the bottom of the screen. prior to getting the urge
to kick this up a notch so to speak the following code was in the
afterupdate property of the 'Patient Number',

Although I guess I don't really need to, I do not have a clear idea of
the process you are trying to support. What I imagine is something like
this:

There is a Patient form where the user maintains details relating to the
person;

There is a Lesions form that pops up in order to add details of
individual lesions. For some reason you want to limit the number of
lesions to ten per person and they have to be numbered between 0 and 9. I
don't understand the reason for either of these constraints but I do see
that they are giving you big headaches as one would expect.

Faced with this challenge, I think I'd pass the work back to the user.
Perhaps I'd create a listbox with ten items on it, like this:

0: Subphrenic abscess
1: Intrapleural mycetoma
3: (unused)
4: (unused)

and so on

In that way, then can pick which one they want to overwrite if there are
no blanks. You can respond to the click with a DELETE command if it's a
valid row (or even if it isn't) followed by an INSERT to create it, then
point the Lesions form at the new record.

Alternatives: you could have a form with ten labels or text boxes and
fill them in from a recordset.

Another alternative would be to question the constraints. It seem strange
that someone cannot have eleven things wrong with them...

All the best


Tim F

 

[Ted]

what i've tried doing in the ensuing interval is use to expressions in the
Default property settings of the "Patient Number" and "Lesion Number" form
controls respectively.

in the instance of the Patient Number, the Default came to:

=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient Number]

in the latter's instance the Default was:

=Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] = " &
[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0)+1

with these two settings in effect, and the user's selected a patient number
from the RECIST form and opened the "Lesions: NonTarget - Baseline" form,
clicking on the "*" button results in the proper Patient Number but not in
the Lesion Number, instead we see an #Error word appear in the Lesion Number
control.

thanks for all the bandwidth tim.

-ted