How to check if subform is dirty

[Rey]

Howdy all.
Appreciate your help with several problems I'm having:

I'm trying to determine if the Visit subform (subformVisits) has a new
record or been changed, i.e. dirty. The form that contains the subform is
named Clients.

I have this code in the Add Client btn:
If Forms!Clients.s ubformVisits!Vi sitDirty = True Then
MsgBox "Visit subform is dirty!"
Else
MsgBox "problems. subform not dirty"
End If
But it displays err "applicatio n defined or object defined error."

The field VisitDirty is an unbound & not visible field with a True/False
format in the details section of subformVisits. Its code is:

Private Sub VisitDirty_Afte rUpdate()
' to see if the subform is "dirty", i.e. has changed or new record
If Me.Dirty Then
Me.VisitDirty = True
End If

End Sub

The reason for the unbound VisitDirty field is that I was having a devil of
a time trying to check if the subform was dirty. Probably didn't have the
syntax correct but tried setting a form var below but also had errs. Even
tried going to the control level but no success.

SET frmClient = Forms!Clients.s ubformVISITs

All the above is as the result of a test that caused an err...

The user enters a new record in the Visit subfrom and has tabbed thru the
fields to the DateEntered field into which the current system date is
entered if a new record.

At this point, the DateEntered field is highlighted. If the user - me - then
clicks on the Add Client button instead of the Save button which could/will
happen, I want to save the record in the subform, then move away from the
Dateentered field of the subform to the first name field before calling for
a new record in the Client form using DoCmd.GoToRecor d , , acNewRec

If the cursor is in the DateEntered field when the new record is created
then the current date is entered to the field but an err will soon display
because there is no new client number (autonumber) to insert into the Visit
table clientnum field.

Guess I need to better understand the Access event model among other things.
Using Access97 and both the client and subformVisit forms are bound to the
Clients and Visits table, respectively.

BTW, what is the difference between using the bang (!) and the dot (.) when
referencing a form as in
Forms!Clients!s ubformVisits and Forms!Clients.s ubformVisit.

Thanks in advance for your help/suggestions,

Rey

[Sigurd Bruteig]

"Rey" <re********@cox .net> skrev i melding
news:_hWjb.2941 3$gi2.21984@fed 1read01...

Howdy all.
Appreciate your help with several problems I'm having:

Try this code in subform on current event.

Sigurd
Private Sub Form_Current()
Dim rs As Recordset

Set rs = RecordsetClone
If rs.RecordCount = 0 Then
MsgBox "no records"
Else
MsgBox rs.RecordCount & " records"
End If

End Sub

[Fred Zuckerman]

Sigurd & All,
If you open a recordset to check for record count, then don't you have to
include
rs.MoveLast before checking rs.RecordCount?
Fred Zuckerman
San Diego, CA, USA
"Sigurd Bruteig" <s-********@online .no> wrote in message
news:Ul******** ***********@new s4.e.nsc.no...

"Rey" <re********@cox .net> skrev i melding
news:_hWjb.2941 3$gi2.21984@fed 1read01...

Howdy all.
Appreciate your help with several problems I'm having:

Try this code in subform on current event.

Sigurd
Private Sub Form_Current()
Dim rs As Recordset

Set rs = RecordsetClone
If rs.RecordCount = 0 Then
MsgBox "no records"
Else
MsgBox rs.RecordCount & " records"
End If

End Sub

[Sigurd Bruteig]

"Fred Zuckerman" <Zu********@sbc global.net> skrev i melding
news:PA******** ********@newssv r25.news.prodig y.com...

Sigurd & All,
If you open a recordset to check for record count, then don't you have to
include
rs.MoveLast before checking rs.RecordCount?
Fred Zuckerman
San Diego, CA, USA

No, this code works great as is.

Sigurd

[Fred Zuckerman]

>> Sigurd & All,

If you open a recordset to check for record count, then don't you have to
include rs.MoveLast before checking rs.RecordCount?
Fred Zuckerman
San Diego, CA, USA

No, this code works great as is.
Sigurd

I believe that I read that in a post from Dev Ashish (one of the gurus of
this group) a long time ago. Namely, that when you open a recordset, it does
not cycle through the records immediately and thus does not have an accurate
RecordCount. But by using MoveLast you insure a complete cycling. I think
it's "possible" to get an accurate RecordCount without a MoveLast, but just
not 100% reliable.
Fred (newbie)

[Bob Quintal]

"Fred Zuckerman" <Zu********@sbc global.net> wrote in
news:PA******** ********@newssv r25.news.prodig y.com:

Sigurd & All,
If you open a recordset to check for record count, then don't
you have to include
rs.MoveLast before checking rs.RecordCount?
Fred Zuckerman
San Diego, CA, USA
Fred, if you are checking for the actual number of records, you
need to do a movelast. The recordcount will show 0 if there are no
records or a number >= 1 if there are some records. In this code,
Sigurd is only checking that there is at least ONE record. The
actual value of recordcount is not relevant to the object of the
excercise. It's leveraging known behaviour to get an increase in
speed.

Bob Q.

"Sigurd Bruteig" <s-********@online .no> wrote in message
news:Ul******** ***********@new s4.e.nsc.no...

"Rey" <re********@cox .net> skrev i melding
news:_hWjb.2941 3$gi2.21984@fed 1read01...

> Howdy all.
> Appreciate your help with several problems I'm having:
>

Try this code in subform on current event.

Sigurd
Private Sub Form_Current()
Dim rs As Recordset

Set rs = RecordsetClone
If rs.RecordCount = 0 Then
MsgBox "no records"
Else
MsgBox rs.RecordCount & " records"
End If

End Sub

[Bruce M. Thompson]

> If you open a recordset to check for record count, then don't you have to

include
rs.MoveLast before checking rs.RecordCount?

Yes. If you don't, you won't likely get an accurate count.

--
Bruce M. Thompson
bt******@mvps.o rg (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Bruce M. Thompson]

> I'm trying to determine if the Visit subform (subformVisits) has a new

record or been changed, i.e. dirty. The form that contains the subform is
named Clients.
If the focus has been moved from the subform to the main form or to another
subform, the subform's record will be saved (no longer "dirty"). If the focus is
currently on that subform, you need only check its dirty property:

If Me.Dirty Then
'Current record has been added or edited
Else
'No edits or no current record
End If
I have this code in the Add Client btn:
If Forms!Clients.s ubformVisits!Vi sitDirty = True Then
MsgBox "Visit subform is dirty!"
Else
MsgBox "problems. subform not dirty"
End If
But it displays err "applicatio n defined or object defined error."
You syntax is incorrect. You must reference the subform *control's* "Form"
property:

Forms!MainFormN ame.SubformCont rolName.Form!Co ntrolName

This assumes that "SubformControl Name" is the name of the subform *control* on
the main form, not the name of the subform (although both may share the same
name).
The field VisitDirty is an unbound & not visible field with a True/False
format in the details section of subformVisits. Its code is:

Private Sub VisitDirty_Afte rUpdate()
' to see if the subform is "dirty", i.e. has changed or new record
If Me.Dirty Then
Me.VisitDirty = True
End If

End Sub
Why would you be trying to set the value of the control after it has been
edited? That's the only time this code would run. Again, you can simply check
the form's "Dirty" property directly ("If Me.Dirty Then ...").
The reason for the unbound VisitDirty field is that I was having a devil of
a time trying to check if the subform was dirty. Probably didn't have the
syntax correct but tried setting a form var below but also had errs. Even
tried going to the control level but no success.

SET frmClient = Forms!Clients.s ubformVISITs
Again, you must reference the subform *control's* "Form" property.
All the above is as the result of a test that caused an err...

The user enters a new record in the Visit subfrom and has tabbed thru the
fields to the DateEntered field into which the current system date is
entered if a new record.

At this point, the DateEntered field is highlighted. If the user - me - then
clicks on the Add Client button instead of the Save button which could/will
happen, I want to save the record in the subform, then move away from the
Dateentered field of the subform to the first name field before calling for
a new record in the Client form using DoCmd.GoToRecor d , , acNewRec

If the cursor is in the DateEntered field when the new record is created
then the current date is entered to the field but an err will soon display
because there is no new client number (autonumber) to insert into the Visit
table clientnum field.
Isn't the "Add Client" command button on the main (Client) form? If so, the
focus will no longer be in the subform, so I guess I don't see where you are
going here unless you are trying to add a child record (in the subform) before
you have created a client record (in the main form).
Guess I need to better understand the Access event model among other things.
Using Access97 and both the client and subformVisit forms are bound to the
Clients and Visits table, respectively.

BTW, what is the difference between using the bang (!) and the dot (.) when
referencing a form as in
Forms!Clients!s ubformVisits and Forms!Clients.s ubformVisit.

From a practical viewpoint, not much, but neither will work unless you are
referencing the subform *control's* "Form" property (I know - nag, nag, nag
<g>).

:-)
--
Bruce M. Thompson
bt******@mvps.o rg (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Wayne Gillespie]

On Fri, 17 Oct 2003 22:22:49 GMT, Bob Quintal <bq******@gener ation.net> wrote:

"Fred Zuckerman" <Zu********@sbc global.net> wrote in
news:PA******* *********@newss vr25.news.prodi gy.com:

Sigurd & All,
If you open a recordset to check for record count, then don't
you have to include
rs.MoveLast before checking rs.RecordCount?
Fred Zuckerman
San Diego, CA, USA

Fred, if you are checking for the actual number of records, you
need to do a movelast. The recordcount will show 0 if there are no
records or a number >= 1 if there are some records. In this code,
Sigurd is only checking that there is at least ONE record. The
actual value of recordcount is not relevant to the object of the
excercise. It's leveraging known behaviour to get an increase in
speed.

Bob Q.

Actually it is relevant becuase of the line -
MsgBox rs.RecordCount & " records"
Without a .MoveLast it can't be guaranteed the MsgBox will return an accurate record count.

You are correct that a RecordCount >=1 indicates that the rs has returned records, but if the RecountCount value is going to be displayed to the user
in any way, the .MoveLast is a must.


"Sigurd Bruteig" <s-********@online .no> wrote in message
news:Ul******** ***********@new s4.e.nsc.no...

"Rey" <re********@cox .net> skrev i melding
news:_hWjb.2941 3$gi2.21984@fed 1read01...
> Howdy all.
> Appreciate your help with several problems I'm having:
>

Try this code in subform on current event.

Sigurd
Private Sub Form_Current()
Dim rs As Recordset

Set rs = RecordsetClone
If rs.RecordCount = 0 Then
MsgBox "no records"
Else
MsgBox rs.RecordCount & " records"
End If

End Sub

Wayne Gillespie
Gosford NSW Australia

[Bob Quintal]

Wayne Gillespie <be*****@NObest fitsoftwareSPAM .com.au> wrote in
news:ca******** *************** *********@4ax.c om:

Actually it is relevant becuase of the line -
MsgBox rs.RecordCount & " records"
Without a .MoveLast it can't be guaranteed the MsgBox will
return an accurate record count.

You are correct that a RecordCount >=1 indicates that the rs
has returned records, but if the RecountCount value is going
to be displayed to the user in any way, the .MoveLast is a
must.

You are correct. I read the Original Poster's code, but failed to
note the detail in Sigurd's code, posted as an example.

My error for glossing over little details. However, in real-world
applications, where one needs to know only whether the recordset is
empty, Sigurd's code is adequate. Actually it is better than code
that would have an unnecessary .movelast, being faster.

Bob Q.