incrementing records in linked parent/child (main/subform) situati

[Guest]

have a pair of linked (parent and child) forms; the child is I guess what
you’d call the subform. They are linked by LN, FN, MI, MR_Number, IRB Number,
where the first three fields record the last name, first name and middle
initial of each patient.

The main form is called ‘Screening Log (Edit Only)’, the subform is called
‘DaysView’. Their respective record sources are ‘Screening Log Query For Form
(Revised)’ and ‘DaysView’.

There is a control on the subform/child I call ‘Visit #’ which I would like
to automatically increment, as in 1,2,3,4,5,……,etc. when I click an ‘Add
Record’ button I’m going to create on the main/parent form. Since the records
from the pair of forms are linked, we would want to reset the counter to 1
when ‘Visit #’ has never been created for a patient. I’m very sure that this
is doable but would like to know if there’s some template to get me started.

With thanks in advance,

-ted

 

[Wayne Morgan]

Ok, if it stopped at that line, I would expect hovering over Me.Visit to be
Null. Until the line is executed, a value won't be assigned to Me.Visit and
it won't execute the line because of the error. However, the error indicates
a name problem, there is something it doesn't recognize the name of. It
could be that the wrong name has been entered or it could be that the quotes
aren't quite right. The quotes appear right, but I've looked at them so much
now that I may be going bug-eyed and missing something. Assuming they are
correct, then that leaves a name being wrong. Without seeing the database, I
can't find that.

--
Wayne Morgan
MS Access MVP

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 (e-mail address removed). Attach a note telling me where in the
file
to
look.

--
Wayne Morgan
MS Access MVP


hi wayne,

i pasted the expression below which i'm copying and pasting
'as
is'
from
the
'Default Value' property's setting below for good measure:

=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

and still the #Name? appears in the 'Visit' control when
viewing
this
sub-form in datasheet view on the mainform. arrrgh :-(