rs.AddNew or DBEngine(0)(0).Execute revisited

[pietlinden]

I'm working on an application where I don't have great control over
what people enter into my database - or whether they must fill in all
fields. If I were dealing with a form based directly or indirectly on
a "parent-most" table (well, not a child of another table), it would
be no problem. But...

I have an "enrollment" kind of problem.
Student---(1,M)---Enrollment---(M,1)----Course

is the generic problem.

My form is based on the "Enrollment" table. and then that table has
child tables.
I can check for the existence of records in Student and Course just
fine and create enrollment records using .Execute if no arguments are
left blank. I was thinking I needed that because I wanted to know if
the insert succeeded by getting the .RecordsAffected property of the
Execute (?) command.

I found Allen Browne's discussion from a while back here...
http://groups.google.com/group/micr...&lnk=gst&q=Execute+++.AddNew#921fec64f230707e

I can get the ID of the record by using

With rs
..AddNew
....
..Update
..Bookmark= .LastModified
....
End With

i'm not really interested in speed that much (because I'd enter a
single record at a time), but more in flexibility, because some fields
will not receive values, and there's not really any way I would know
which at design time.

Any ideas on which way is better in that case?

Thanks!

Pieter

 

[John W. Vinson]

My form is based on the "Enrollment" table. and then that table has
child tables.
I can check for the existence of records in Student and Course just
fine and create enrollment records using .Execute if no arguments are
left blank. I was thinking I needed that because I wanted to know if
the insert succeeded by getting the .RecordsAffected property of the
Execute (?) command.

I wouldn't be inserting records using an append query at all! Why not simply
bind the form to the enrollment table (so any data entered will go right into
the table)? If you do so you can check the StudentID and CourseID controls in
the form's BeforeUpdate event, and cancel the update with a warning message if
either is null.

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If IsNull(Me!cboStudent) Then
iAns = MsgBox("Please select a student or choose Cancel to start over", _
vbOKCancel)
Cancel = True ' cancel this addition in any case
If iAns = vbCancel Then
Me.Undo ' erase the form completely if requested
End If
End If
<do the same for cboClass>

 

[pietlinden]

I wouldn't be inserting records using an append query at all! Why not simply
bind the form to the enrollment table (so any data entered will go right into
the table)? If you do so you can check the StudentID and CourseID controlsin
the form's BeforeUpdate event, and cancel the update with a warning message if
either is null.

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If IsNull(Me!cboStudent) Then
   iAns = MsgBox("Please select a student or choose Cancel to start over", _
       vbOKCancel)
   Cancel = True ' cancel this addition in any case
   If iAns = vbCancel Then
      Me.Undo ' erase the form completely if requested
  End If
End If
<do the same for cboClass>

Good plan! Thanks! Maybe staring at this stuff for too long really
DOES make you go crazy.

 

[pietlinden]

I wouldn't be inserting records using an append query at all! Why not simply
bind the form to theenrollmenttable (so any data entered will go right into
the table)? If you do so you can check the StudentID and CourseID controlsin
the form's BeforeUpdate event, and cancel the update with a warning message if
either is null.

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If IsNull(Me!cboStudent) Then
   iAns = MsgBox("Please select a student or choose Cancel to start over", _
       vbOKCancel)
   Cancel = True ' cancel this addition in any case
   If iAns = vbCancel Then
      Me.Undo ' erase the form completely if requested
  End If
End If
<do the same for cboClass>

John,
My problem (I think) is that my hierarchy is fairly deep...

Patient--(1,M)--Enrollment---(1,M)---Serious Adverse Event--(1,M)--SAE
Report----(1,M)---Toxicity

at some point, won't I just run out of subforms? I was just thinking
that after a while, that would get unwieldy. I suppose I could force
the Patient--Enrollment---Study part to be done first (use that form
to find/add an enrollment) and then pass that primarykey to the SAE
stuff and have another hierarchy 3 deep...

Any thoughts?

Pieter

 

[John W. Vinson]

at some point, won't I just run out of subforms? I was just thinking
that after a while, that would get unwieldy. I suppose I could force
the Patient--Enrollment---Study part to be done first (use that form
to find/add an enrollment) and then pass that primarykey to the SAE
stuff and have another hierarchy 3 deep...

Any thoughts?

You can nest subforms seven deep if need be, though of course that limits you
to single-view subforms for the top six and will be visually complex for the
user. Popping up a separate form (with subforms) may be your best bet - have
the SAE form allow you to select a study (but not edit it) on its mainform and
have subforms for the other tables as appropriate.

 

[David W. Fenton]

You can nest subforms seven deep if need be, though of course that
limits you to single-view subforms for the top six and will be
visually complex for the user. Popping up a separate form (with
subforms) may be your best bet - have the SAE form allow you to
select a study (but not edit it) on its mainform and have subforms
for the other tables as appropriate.

And subforms need not be embedded in their parent form, either. You
could embed it in the parent form and link it to the ID of a
different subform that *is* that subform's parent.