How To Force User To Enter At Least One Detail Record

[doyle60]

What is the best way to force a user to enter at least one detail
record? I've never had to do such a thing before but I do have a
situation now where I would like to prevent a "dry" form. Thanks,

Matt

 

[dymondjack]

You could always build a function into the BeforeUpdate of the main form to
check and see if there's any related records in the related table.

Ex:
strSQL = "SELECT * FROM tblname WHERE [ID] = " & Me.ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.Recordcount = 0 Then
MsgBox "Enter a Record
End If

The two things you are going to run into like this is
1) You will have to work around New Records (the main record will always
save when the focus is shifted to a subform), and
2)This will only work if the user changes something on the mainform (after
Me.NewRecord = False).

Anyway, that's what comes to mind. I don't know of any 'built-in' way to
handle this. I guess, on the BeforeUpdate event, you might be able to check
and see if it's a new record, and open a dailog form to enter some detail
info. But then, you would have to save the detail info to a temp table until
the mainform saves, and copy it into the related table.


On a different train of thought, if you wanted to do this outside of the
Form environment (maybe as a maintence function), you could run some
comparisons between tables and delete any top-level records with no related
ones

Ex:
While Not rsParent.EOF
Set rsChild = CurrentDb.OpenRecordset( _
"SELECT * FROM tblname WHERE [ID] = " & rsParent(0)
If rsChild.RecordCount = 0 Then rsParent.Delete
rsChild.Close
Wend

Something along those lines anyway... the code examples are rather cryptic,
and this example would take a while, but AFAIK there's no really easy way to
do it.

Maybe someone else has some better ideas

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[doyle60]

How about some method that forces the form to filter when a new record
is being added. If there are no detail records the form is filtered
and if there is at least one detail record, the form does not filter,
allowing the user to navigate off the record.

Just thinking out loud.

I could always do an error report.

Another related problem is that the user can delete the detail lines
and then try and delete the header. But will not be allowed to delete
the header owing to it being connected elsewhere. But they already
deleted the detail lines. Woops!

It isn't a too big a deal all of this but I thought if there was an
easy solution I would put it in. So please don't work up any
extravagant solutions on my account.

Matt

 

[dymondjack]

How about some method that forces the form to filter when a new record

is being added.

That's easily enough done, though I'm not sure I gather your idea. Anyway,
you could use the OnCurrent event and check for a new record, executing some
code or another based on what it finds.

Private Sub Form_Current()
If Me.NewRecord = True Then
'Set some sort of filter?
End If
End Sub

I think I see where you're coming from now... although I'm not sure if you
could set a filter without losing the 'NewRecord' status.

Unfortunatly access doesn't provide a 'BeforeCurrent' event (AFIAK).

But, if you were to keep a variable private to the form's module, and have
it hold the record number of the previous record... (more thinking out loud)
on the OnCurrent event (when the user navigates away), check for details
against the previous record (the one in the variable). If no records details
are found, force the user back to that record.

Something like this....

Option Compare Database
Option Explicit

Private pLastID As Long

Private Sub Form_Current()
'Check for Null in case we're just opening the form
If (Not IsNull(pLastID)) And (Me.NewRecord = False) Then

'Check for details from the last record
If FunctionThatChecksRecords = False
DoCmd.GotoRecord....
GoTo ExitSub
'I think you'll need the exit statement because
'if it changes records, this function will run once more
'for the new record, and then this one will finish
End If
End If

pLastID = Me.CurrentRecord

ExitSub:
Exit Sub
End Sub

Private Function FunctionThatChecksRecords() As Boolean
Dim strSQL As String
Dim rs As DAO.Recordset
...
...
End Function


That might actually do it. With a few touchups of course, but I think it
might be pretty close. Not too awfully bad of task.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[dymondjack]

I'm not gonna let this one stump me...

If (Not IsNull(pLastID)) And (Me.NewRecord = False) Then

Get rid of the Me.NewRecord check in this line (you don't care if the user's
trying to enter a new record, you want to make sure the last one they were on
has a detail)

And, to get rid of the need for the IsNull check, try setting that value on
the form's Open event, though I'm not sure if Current or Open runs first, you
should be ok. Nevermind, scratch that... that would make a neverending
loop... leave the isnull check

I think the only other thing to keep in mind is that the record ID that you
need to check for your FunctionThatChecksForRecords function won't always be
the same as Me.CurrentRecord, so you'll probably end up using a
RecordsetClone/Bookmark feature rather than DoCmd.GoToRecord

I think that should do it though... let me know how you make out, if you
decide to go with it... I'm curious



--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[John W. Vinson]

What is the best way to force a user to enter at least one detail
record? I've never had to do such a thing before but I do have a
situation now where I would like to prevent a "dry" form. Thanks,

Matt

This is more difficult than one would think! If you're using a mainform for
the record, and a subform for the details, the mainform record must be saved
*before* you can start entering any data into the subform (so that the
master/child relationship can take effect; you can't insert the child table's
foreign key value until you know the master table's primary key value).

As a result, the mainform's BeforeUpdate event won't work: it fires the
instant you set focus to the subform, and there will not be a subform record.

If it's impossible to train your users, you will need to either run a check
for missing detail records in a query called from the main form's Close event,
or from some other appropriate event (e.g. before running a report).

 

[Dirk Goldgar]

This is more difficult than one would think! If you're using a mainform
for
the record, and a subform for the details, the mainform record must be
saved
*before* you can start entering any data into the subform (so that the
master/child relationship can take effect; you can't insert the child
table's
foreign key value until you know the master table's primary key value).

As a result, the mainform's BeforeUpdate event won't work: it fires the
instant you set focus to the subform, and there will not be a subform
record.

If it's impossible to train your users, you will need to either run a
check
for missing detail records in a query called from the main form's Close
event,
or from some other appropriate event (e.g. before running a report).

It's possible to keep the user from navigating to another record is there
are no detail records. That can help, combined with John's suggested check
in the Close or Unload event. You'd keep a module-level variable that would
hold the ID field of the record last accessed, and use the the form's
Current to to see if that record has any child records. If it doesn't, and
it's not he current record's ID, go back to that record.

 

[Dirk Goldgar]

That's easily enough done, though I'm not sure I gather your idea.
Anyway,
you could use the OnCurrent event and check for a new record, executing
some
code or another based on what it finds.

Private Sub Form_Current()
If Me.NewRecord = True Then
'Set some sort of filter?
End If
End Sub

I think I see where you're coming from now... although I'm not sure if
you
could set a filter without losing the 'NewRecord' status.

Unfortunatly access doesn't provide a 'BeforeCurrent' event (AFIAK).

But, if you were to keep a variable private to the form's module, and have
it hold the record number of the previous record... (more thinking out
loud)
on the OnCurrent event (when the user navigates away), check for details
against the previous record (the one in the variable). If no records
details
are found, force the user back to that record.

Something like this....

Option Compare Database
Option Explicit

Private pLastID As Long

Private Sub Form_Current()
'Check for Null in case we're just opening the form
If (Not IsNull(pLastID)) And (Me.NewRecord = False) Then

'Check for details from the last record
If FunctionThatChecksRecords = False
DoCmd.GotoRecord....
GoTo ExitSub
'I think you'll need the exit statement because
'if it changes records, this function will run once more
'for the new record, and then this one will finish
End If
End If

pLastID = Me.CurrentRecord

ExitSub:
Exit Sub
End Sub

Private Function FunctionThatChecksRecords() As Boolean
Dim strSQL As String
Dim rs As DAO.Recordset
...
...
End Function


That might actually do it. With a few touchups of course, but I think it
might be pretty close. Not too awfully bad of task.

Sorry, Jack, I didn't see you'd posted this approach, or I wouldn't have
posted my other reply. I can attest that the approach works, though I
haven't looked at your code closely to see if that particular implementation
works.

 

[David H]

What is the specific NEED to have the user enter a detail record?

 

[doyle60]

What is the specific NEED to have the user enter a detail record?

The main form has a big Memo box that will have a lot of text in it.
The subform keeps track of the updates to that box with a date field
and explanation. They are constantly being updated. So the date for
the memo is in the sub (the date being the Max of the Date). Each
memo must have a date associated with it because users when copying
the memo to specific orders on another table, must carry forth the
date of the memo they used; it's a required field. They actually copy
the memo, it's ID, and its last updated date, with a command button,
and then edit the large memo down to what they need (so the memo isn't
exactly linked but the order does contain the information about which
memo was copied and what date it had at the time). Anyway the date is
a must because I want to find all orders whose edited memo was based
on an outdated original memo. The order may not have shipped yet and
errors could be caught in time.

All works, mind you, except when they try to copy over an original
memo without a date, that is, without a detail record, they get an
error because the date is required.

I fully understood when I mapped out the process on paper that I had
an issue here. I thought about having at least one detail record in
the header, there aren't that many fields, and doing a query that
would determine if later dates are in the detail. There are other
solutions too.

But as mentioned, it isn't too big a problem. If no detail records
exist, users will not be able to use it and will call the person in
charge of the original form and fix it. I can also just put in a form
that the users see that show these issues.

But I thought to ask here because I know my main frame computer does
not allow headers without detail lines on several forms and thought
perhaps it was something easy that I just didn't know.

I know people here like puzzles and challenges like I do, but please
don't go too far on my account. Not worth it.

Thanks to all,

Matt