By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

How to check if subform is dirty

P: n/a
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.subformVisits!VisitDirty = True Then
MsgBox "Visit subform is dirty!"
Else
MsgBox "problems. subform not dirty"
End If
But it displays err "application 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_AfterUpdate()
' 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.subformVISITs

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.GoToRecord , , 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!subformVisits and Forms!Clients.subformVisit.

Thanks in advance for your help/suggestions,

Rey
Nov 12 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a

"Rey" <re********@cox.net> skrev i melding
news:_hWjb.29413$gi2.21984@fed1read01...
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
Nov 12 '05 #2

P: n/a
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*******************@news4.e.nsc.no...

"Rey" <re********@cox.net> skrev i melding
news:_hWjb.29413$gi2.21984@fed1read01...
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

Nov 12 '05 #3

P: n/a

"Fred Zuckerman" <Zu********@sbcglobal.net> skrev i melding
news:PA****************@newssvr25.news.prodigy.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
Nov 12 '05 #4

P: n/a
>> 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)

Nov 12 '05 #5

P: n/a
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in
news:PA****************@newssvr25.news.prodigy.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*******************@news4.e.nsc.no...

"Rey" <re********@cox.net> skrev i melding
news:_hWjb.29413$gi2.21984@fed1read01...
> 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



Nov 12 '05 #6

P: n/a
> 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.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #7

P: n/a
> 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.subformVisits!VisitDirty = True Then
MsgBox "Visit subform is dirty!"
Else
MsgBox "problems. subform not dirty"
End If
But it displays err "application defined or object defined error."
You syntax is incorrect. You must reference the subform *control's* "Form"
property:

Forms!MainFormName.SubformControlName.Form!Control Name

This assumes that "SubformControlName" 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_AfterUpdate()
' 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.subformVISITs
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.GoToRecord , , 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!subformVisits and Forms!Clients.subformVisit.


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.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #8

P: n/a
On Fri, 17 Oct 2003 22:22:49 GMT, Bob Quintal <bq******@generation.net> wrote:
"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in
news:PA****************@newssvr25.news.prodigy.co m:
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*******************@news4.e.nsc.no...

"Rey" <re********@cox.net> skrev i melding
news:_hWjb.29413$gi2.21984@fed1read01...
> 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
Nov 12 '05 #9

P: n/a
Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in
news:ca********************************@4ax.com:

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.

Nov 12 '05 #10

P: n/a
"Rey" <re********@cox.net> wrote in
news:_hWjb.29413$gi2.21984@fed1read01:
Howdy all.
Appreciate your help with several problems I'm having:
BTW, what is the difference between using the bang (!) and the
dot (.) when referencing a form as in
Forms!Clients!subformVisits and Forms!Clients.subformVisit.


This was discussed yesterday. Check Google for the full story. In
short bang refers to an object in a collection, dot refers to a
property or method of an object. If you named it, use a bang. If
Microsoft named it, use a dot.

Bob Q.

Nov 12 '05 #11

P: n/a
"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in message news:<vp************@corp.supernews.com>...
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.


Bruce, you might want to try a different method of checking a subform
for changes - Place this code in your subform's before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If MsgBox("Do You Want to Save the Current Changes?" _
, vbYesNo + vbQuestion, "Save Comment") = vbYes Then
DoCmd.Save
Else
Me.Undo
End If

End Sub

The update event will only fire if any data has changed in the subform
and then the message box will be fired and the user will have the
option to save or undo their changes. Otherwise, with no data changes
nothing will happen.
Nov 12 '05 #12

P: n/a
On Sat, 18 Oct 2003 00:55:46 GMT, Bob Quintal <bq******@generation.net> wrote:
Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in
news:ca********************************@4ax.com :

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.


Agreed.

Wayne Gillespie
Gosford NSW Australia
Nov 12 '05 #13

P: n/a
Response below.
Bruce, you might want to try a different method of checking a subform
for changes - Place this code in your subform's before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If MsgBox("Do You Want to Save the Current Changes?" _
, vbYesNo + vbQuestion, "Save Comment") = vbYes Then
DoCmd.Save
Else
Me.Undo
End If

End Sub

The update event will only fire if any data has changed in the subform
and then the message box will be fired and the user will have the
option to save or undo their changes. Otherwise, with no data changes
nothing will happen.


Actually, I wouldn't want to use anything other than what I offered. The OP
asked how to determine whether the form was dirty (unsaved edits). The "Dirty"
property gives me the answer that I need. I am fully aware of how to use the
"BeforeUpdate" event (if you google on this subject, you will likely find scores
of posts made by me providing just the code that you have suggested), but that
does not give you the status of the current record. If you are running code that
depends on the current record not containing unsaved data, the "BeforeUpdate"
event would be the wrong place to do that as this only occurs when a save is
taking place.

--
Bruce M. Thompson
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #14

P: n/a
Any alternative to the recordset count, would be to use the dcount()
function it goes a follows:

variable = dcount("*","tableName")

if variable = 0 then
msgbox "No Records"
else
msbox "There are " & variable & " records in the table."
end if

Hope of help
Philippa

"Rey" <re********@cox.net> wrote in message news:<_hWjb.29413$gi2.21984@fed1read01>...
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.subformVisits!VisitDirty = True Then
MsgBox "Visit subform is dirty!"
Else
MsgBox "problems. subform not dirty"
End If
But it displays err "application 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_AfterUpdate()
' 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.subformVISITs

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.GoToRecord , , 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!subformVisits and Forms!Clients.subformVisit.

Thanks in advance for your help/suggestions,

Rey

Nov 12 '05 #15

P: n/a
Rey
Apologize for lateness but thank you to all respondants. Learn the correct
syntax along the way also 8-)

Rey
Nov 12 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.