i've been ooo on friday and well, y'know the weekend, but i managed to
put
this idea of yours into the DaysView subform:
Private Sub Form_Current()
Me.txtCurKey1 = Me.LastName
Me.txtCurKey2 = Me.First_Name
Me.txtCurKey3 = Me.M_I
Me.txtCurKey4 = Me.MRNumber
Me.txtCurKey5 = Me.IRBNumber
Me.txtCurKey6 = Me.Visit
If Me.NewRecord Then
Me.Visit = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ &
[LastName] & """ And [First Name] = """ & [FirstName] & """ And [MI] =
"""
&
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """
&
[IRBNumber] & """"), 0) + 1
End If
End Sub
(there was some extant code already in the OnCurrent event) and it's
talking
about an error 2465 and "Oncology Screening Log can't find the field
'|'
referred to in your expression". when i click Debug button it took me
to
the
Me.Visit line which is yellow hightlingted and hoevering over Me.Visit
it
tells me its value is 'Null'.
getting an abbreviated structure to you that would be workable is not
as
simple as it sounds because of the need to incorporate numerous l/u
tables. i
wish it were.
where it stands right now.
-ted
:
Me.Visit won't show a value while you're stopped at that line, it's
not
assigned until that line is executed.
Either a name of a control or field is wrong or there are quotes in
the
wrong place. Without actually seeing it, I would have a hard time
fixing
it.
If you could create a new mdb file, import the form, table structure
(if
you
import only the structure, no data will be imported) for these tables
and
the queries I will type in some "dummy" data and see what I come up
with.
To import only the table structure, click on the Advanced button in
the
Import dialog (File|Get External Data|Import).
--
Wayne Morgan
MS Access MVP
so, i'm working on the Dirty Event property and adjusted the
quotation
number
to just 4 (i don't know where the extra quotation came from, but
anyway)..
Private Sub Form_Dirty(Cancel As Integer)
Me.Visit = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] =
""" &
[LastName] & """ And [First Name] = """ & [FirstName] & """ And [MI]
=
"""
&
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] =
"""
&
[IRBNumber] & """"), 0) + 1
End Sub
when i open the mainform and view the subform datasheet, i see an
asterisk
on the lefthand side sitting on what will be the (for the current
patient)
9th subform record. the famous, or rather infamous, #Name? appears
in
the
Visit control. when i attempt to enter something in the Visit Type
control
a
message arrives on the screen which reads "Rim=time error '2465':
Microsoft
can't find the field '|' in your expression." with several buttons
at
the
botoom like End, Debug and the like. when i clicked on Debug, it
took
me
to
the Me.Visit line which was highlighted in yellow. hovering over
Me.Visit
though, failed to show me any value?
best,
-ted
:
Thanks for copying and pasting what you have in the code.
Me.Visit = Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] =
"""
&
[LastName] & """ And [First Name] = """ & [FirstName] & """ And
[MI]
=
"""
&
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number]
=
"""
&
[IRBNumber] & """""),0)+1
The last set of quotes should be 4 double quotes ", you have 5.
Yes, I saw the second message, let's see if this helps first.
--
Wayne Morgan
MS Access MVP
we seem to be getting nearer something more useful.....
i pasted your string and modified the lefthand member into th
current
event
property vba thus
Me.Visit = Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] =
"""
&
[LastName] & """ And [First Name] = """ & [FirstName] & """ And
[MI]
=
"""
&
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number]
=
"""
&
[IRBNumber] & """""),0)+1
although when looking at it, it is on one (1) line; nevertheless
when i
went
to compile it, it gave me an error first about a missing ")" and
now
it's
just talking about 'Syntax Error'?
your Dirty Event idea sounds goog (did you read my posting about
which
are
going to make the PK elements)?
-ted
:
Yes, you may be able to do this in a VBA event. If you use the
form's
Current event then check to see if you're at a new record, you
could
assign
a value to the control at that point. To do this in the Current
event:
If Me.NewRecord Then
Me.NameOfControl =
Nz(DMax("[RecordNumber]","[DaysView]","[Last
Name]
=
""" & [LastName] & """
And [First Name] = """ & [FirstName] & """ And [MI] = """ &
[M_I]
&
"""
And
[MR_Number] = " & [MRNumber] & " And [IRB Number] = """ &
[IRBNumber]
&
""""),0)+1
End If
There is one potential problem with this. If the value is set by
the
Default
Value property, it won't "dirty" the record. This prevents going
to
a
new
record but not making any manual entries from causing a new
record
to
be
created. However, doing this in the Current event will "dirty"
the
record.
You'll also need to see if the user makes any other entry and
Undo
the
record if they don't.
Another option is to place the above code in the form's Dirty
event.
That
way the value will only be inserted if the user first makes
another
entry
to
create the record. If the user made that first entry in this
field,
then
the
code would overwrite their entry. So you would need to keep the
user
from
making their first entry in the new record in this field.
--
Wayne Morgan
MS Access MVP
i opened a blank word document which i set to be in landscape
mode
and
whose
margins i set to as wide as possible along with reducing the
fontsize
maximally. i pasted the script you sent as is onto the said
document
but
yet
even so it didn't fit onto one line but spilled over slightly
onto
the
2nd
one. i selected the first line and pasted it in the Default
Value
window
and
then reprised that with the 10 or so characters remaining from
the
2nd
one.
still no cigar when i view the form but i suppose that was a
long
shot
anyway.
the infor'n in the file is 'sensitive' and i suppose i could
remove
all
but
the two tables and forms involved in this thread, delete all
but
a
few
records and alter the names of the persons described to
anonymize
it.
still i
sort of wish it didn't have to come to that. which brings me
to
my
next
'idea' -- would it be possible to use an event property
instead
of
the
entering the expression into the 'Default Value' property? i
just
read
something on the web which seems to me to suggest that
conditional
defaults
could be vba-ed into an anfter update event? i'm thinking
about
workarounds....
-ted
:
Copying "as is" will cause a problem. The news reader has
wrapped
it
to 4
lines. It needs to all be on one line. If that still doesn't
get
it,
please
zip and forward me a copy of the file (or a link where it can
be
downloaded)
to
[email protected]. Attach a note telling me where in the
