How to check for non entered data on a data entry form

[graeme34 via AccessMonster.com]

Im quite new to this so pardon my ignorance
Am I right in saying that for a data entry form there is no recordset?
If so how can you check for non entered data?
What I am trying to do is verify that data has been entered into the subform
order detail, when leaving the master form, thus preventing having Orders
without order details. I know I could check each individual text box for
values but this seems a bit longwinded, If there was a recordset created I
would you the recordcount funtion but I'm under the impression there is no
recordset automatically created for a data entry form.
Any suggestions??

 

[Rick B]

This may not answer your question, but why don't you take a look at the
Northwind database that ships as a sample with Access? They have a form for
orders and a subform with the line items. Unlike your description, I'm
pretty sure the forms are all bound to the tables. I rarely build an
unbound form. Using a table or query as your data source makes it so much
faster and easier to create the form; most of the control defaults are there
for you already. Building an unbound form and trying to figure out all the
edits and criteria for the various controls, then writing all that data to a
table, just seems like double work to me.

 

[Bob Hairgrove]

Im quite new to this so pardon my ignorance
Am I right in saying that for a data entry form there is no recordset?
If so how can you check for non entered data?

It never occurred to me that a bound form might not have a recordset.
But in Access-Land, anything is (im)possible!

What I am trying to do is verify that data has been entered into the subform
order detail, when leaving the master form, thus preventing having Orders
without order details. I know I could check each individual text box for
values but this seems a bit longwinded,

Indeed. The relational model, which can be enforced at the engine
level, specifies that IF there is a 1-to-N or 1-to-1 relationship
between the parent and child tables, that you must have exactly one
and only one record in the parent table to satisfy the child relation
(or foreign key constraint on the child table).

However, you need to assure that there is at least one child record
for each parent record. You cannot do this with bound forms, AFAIK,
because it requires additional checks. Only with an unbound form can
you be sure that the new parent record is inserted BEFORE the child
record, and if an error occurs, you can roll back the insertion. This
is important because you need to treat insertions to the parent and
child tables (at least the initial insert) as an atomic entity for
which you need a transaction mechanism, and this just isn't available
with the Access native form/subform mechanism.

 

[Klatuu]

Your assumption regarding a data entry form is incorrect, mostly. You can
have an unbound form and manipulate the data programmatically, but I doubt
that is the case. You can tell by looking at the form's properties for the
RecordSource property. If there is none, it is an unbound form; otherwise,
its recordset is what is defined in the RecordSource property.

The same will be true of your subform. The one thing to be aware of is the
master record is inserted when you move to the subform, so you will have to
delete it when you determine there are no child records.

The problem with trying to get a record count is that the recordcount
property is not available for subforms. Another way to do this would be to
do a DLookup on the table or query that is the recordsource for the sub form.
If it returns a null, then there are no line items for the order:

If IsNull(DLookup("[OrderNumber]", "OrderDetailTable", "[OrderNumber] = '" _
& Me.Parent!txtOrderNumber & "'") Then
msgbox "No Detail For this order
End If

 

[graeme34 via AccessMonster.com]

Thanks for your replies guys..
In response to Klatuu reply.
The recordsource property for the master form is tblSalesOrder, the record
source for the child form is based on a query qrySalesLine. I will try out
your Dlookup suggestion and let you know how it goes.
Thanks

Your assumption regarding a data entry form is incorrect, mostly. You can
have an unbound form and manipulate the data programmatically, but I doubt
that is the case. You can tell by looking at the form's properties for the
RecordSource property. If there is none, it is an unbound form; otherwise,
its recordset is what is defined in the RecordSource property.

The same will be true of your subform. The one thing to be aware of is the
master record is inserted when you move to the subform, so you will have to
delete it when you determine there are no child records.

The problem with trying to get a record count is that the recordcount
property is not available for subforms. Another way to do this would be to
do a DLookup on the table or query that is the recordsource for the sub form.
If it returns a null, then there are no line items for the order:

If IsNull(DLookup("[OrderNumber]", "OrderDetailTable", "[OrderNumber] = '" _
& Me.Parent!txtOrderNumber & "'") Then
msgbox "No Detail For this order
End If

Im quite new to this so pardon my ignorance
Am I right in saying that for a data entry form there is no recordset?

[quoted text clipped - 6 lines]

recordset automatically created for a data entry form.
Any suggestions??

 

[graeme34 via AccessMonster.com]

Having problems with the code, here is the code from both command buttons on
form:

Private Sub cmdCancelOrder_Click()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblSalesOrder")

DoCmd.Close acForm, "frmSalesOrder"
rs.MoveLast
rs.Delete
rs.Close
OpenMainMenu

End Sub

Private Sub cmdReturnMainMenu_Click()

If IsNull(DLookup("[SalesOrderNumber]", "tblSalesOrderLine", _
"[SalesOrderNumber] = '" & Me.Parent!txtSalesOrderNumber & "'")) Then
MsgBox "No Detail For this order"
Exit Sub
End If

OpenMainMenu
DoCmd.Close acForm, "frmSalesOrder", acSaveYes

End Sub

The cancel order command works fine, but the return to main menu where I want
to check prevent the user from saving the master record if there is no child
record is failing at the If IsNull statement.....giving a 2452 runtime error,
invalid reference to parent property...any suggestions???
Thanx

Thanks for your replies guys..
In response to Klatuu reply.
The recordsource property for the master form is tblSalesOrder, the record
source for the child form is based on a query qrySalesLine. I will try out
your Dlookup suggestion and let you know how it goes.
Thanks

Your assumption regarding a data entry form is incorrect, mostly. You can
have an unbound form and manipulate the data programmatically, but I doubt

[quoted text clipped - 21 lines]