how do I require entry of a field in a subform

[Guest]

I need assistance in forcing the creation and entry of a new child record (2
fields: ID and Text field) upon the entry of a parent record. I'm working
with an MS Access form and subform. I have a statement that creates the child
record with record ID, but I can't get it to force entry of a value in the
text field:

Private Sub Form_AfterInsert()
Dim strSQL As String

strSQL = "INSERT INTO SubdivisionParcels(NewSubID) " & "Values(" &
Me!NewSubID & ")"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

If I make the text field in the related child table a required field, the
system fails on "CurrentDB.Execute strSQL, dbFailOnError"

Any help is greatly appreciated! Thank you.

 

[Marshall Barton]

I need assistance in forcing the creation and entry of a new child record (2
fields: ID and Text field) upon the entry of a parent record. I'm working
with an MS Access form and subform. I have a statement that creates the child
record with record ID, but I can't get it to force entry of a value in the
text field:

Private Sub Form_AfterInsert()
Dim strSQL As String

strSQL = "INSERT INTO SubdivisionParcels(NewSubID) " & "Values(" &
Me!NewSubID & ")"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

If I make the text field in the related child table a required field, the
system fails on "CurrentDB.Execute strSQL, dbFailOnError"

strSQL = "INSERT INTO SubdivisionParcels(NewSubID,textfld)
Values(" & Me!NewSubID & ", """ & Me.textbox & """)"

Note the quoting difference between numeric and text values.

 

[John Vinson]

I need assistance in forcing the creation and entry of a new child record (2
fields: ID and Text field) upon the entry of a parent record.

Not easy. The problem is that the parent record must be saved to disk
FIRST, in order for referential integrity to be possible. So there is
a period during which a mainform record is saved but there is not yet
a subform.

I'm working
with an MS Access form and subform. I have a statement that creates the child
record with record ID, but I can't get it to force entry of a value in the
text field:

Private Sub Form_AfterInsert()
Dim strSQL As String

strSQL = "INSERT INTO SubdivisionParcels(NewSubID) " & "Values(" &
Me!NewSubID & ")"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

If I make the text field in the related child table a required field, the
system fails on "CurrentDB.Execute strSQL, dbFailOnError"

I'm really confused here. If you're using a subform, the Master/Child
Link automatically fills in the subform record, and no code is needed.

You might try this code in the Subform's GotFocus event; and you
cannot both create a "placeholder" record with a blank text field
while at the same time making it required. You can't have it both
ways!

John W. Vinson[MVP]

 

[Guest]

Hello Marshall,
Thank you for the prompt reply. I amended my SQL statement to include the
additional text field; however, I can still navigate away from the records
(both master and child) without entering a value in the text field located on
the subform; that is, by closing the form or creating a new master record. I
need the cursor to stay in the subform text box until the user has entered a
value. Any other thoughts?

Andrea

 

[Marshall Barton]

Sorry I wasn't clear about the issue. I was trying to fix
your query so the two values would be saved. The issue of
requiring the data to exist is a whole different story.
Since John has already explained the chicken and egg aspects
of what you are asking about, you should think about what he
said and respond to his post with further followup
questions.

 

[Guest]

Good morning, John.
I apologize for the confusion. It is my understanding that a Master/Child
link is automatically created if/when a user enters a value in the subform.
The code I provided earlier forces the creation of the child record (the
related table shows the new record with the link ID). I need an additional
line or two that would then require the user to enter a value in the text box
(the second field in the related table). The result would be for every main
record there is at least one related child record: the automatically
populated link (ID) and the user entered text. Again, I apologize if my
explanation is not clear. I greatly appreciate any assistance. Thank you.
Andrea

 

[John Vinson]

Good morning, John.
I apologize for the confusion. It is my understanding that a Master/Child
link is automatically created if/when a user enters a value in the subform.
The code I provided earlier forces the creation of the child record (the
related table shows the new record with the link ID). I need an additional
line or two that would then require the user to enter a value in the text box
(the second field in the related table). The result would be for every main
record there is at least one related child record: the automatically
populated link (ID) and the user entered text. Again, I apologize if my
explanation is not clear. I greatly appreciate any assistance. Thank you.
Andrea

The explanation was clear - it seems my answer was not.

The answer is:


*This is very, very hard to do*.


The reason that it is hard to do is because of the way Subforms work.
When you set focus to any field in a subform, the mainform record is
saved to disk AT THAT MOMENT. The mainform's BeforeUpdate and
AfterInsert and AfterUpdate events all fire, so any mainform
validation code gets executed, before you have had any time to enter
any data into any field on the subform.

Access does this because (typically) the main form's table and the
subform's table are in a one to many relationship, referential
integrity enforced. You cannot create a record in the subform, using
the Master Link Field/Child Link Field properties, until there is a
matching record *stored on disk* in the parent table.

So - at least for a moment - there MUST be the possibility of having a
mainform record stored without any corresponding record in the child
table. It's a "chicken or egg" problem - you can't save both records
at the same instant.

If it is ESSENTIAL to do this, the only way I know of is to base the
main and subform on two *additional* tables; in the Subform's
AfterUpdate event check to see if the appropriate data exists in both
parent and child records, and if so run two Append queries to append
the records from the parent and child "scratch" tables into the "real"
tables.

John W. Vinson[MVP]

 

[Guest]

Hello John,
I'm glad to hear that this is a difficult task -- I thought it was me! I was
thinking of a very similar solution as the one you suggested. Again, thank
you for the reply. Andrea