Hide a Subform

[NEWER USER]

I want to Hide/Unhide a subform on my Main form based upon if any records are
showing on the subform.

If no records are showing on the subform, then hide the subform
Else
Show the subform.

Can anyone get me started with some coding? Thanks

 

[Dirk Goldgar]

I want to Hide/Unhide a subform on my Main form based upon if any records
are
showing on the subform.

If no records are showing on the subform, then hide the subform
Else
Show the subform.

Can anyone get me started with some coding? Thanks

Possibly something like this *untested* code, running in the main form's
Current event:

With Me.sfMySubform
.Visible = (.Form.Recordset.RecordCount > 0)
End With

 

[NEWER USER]

Worked with no problems; Thanks again

Possibly something like this *untested* code, running in the main form's
Current event:

With Me.sfMySubform
.Visible = (.Form.Recordset.RecordCount > 0)
End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[croy]

Possibly something like this *untested* code, running in the main form's
Current event:

With Me.sfMySubform
.Visible = (.Form.Recordset.RecordCount > 0)
End With

I've got that working in one of my form/subform
combinations, but if the main form current record is one of
those without subform data, and I close the db, an error
pops up: "No current record". I can't seem to sort this
one out--very unfamiliar with the "With" statement.

 

[Dirk Goldgar]

I've got that working in one of my form/subform
combinations, but if the main form current record is one of
those without subform data, and I close the db, an error
pops up: "No current record". I can't seem to sort this
one out--very unfamiliar with the "With" statement.

The With statement just "grabs" an object and says, "for the duration of
this block of code (up to the End With), any unqualified property or method
references should be understood as referring to this object."

I'm not sure why you're getting a "no current record" error, but if it's due
to this code, you might try suppressing errors during its execution by
leading it with the "On Error Resume Next" statement:

On Error Resume Next

With Me.sfMySubform
.Visible = (.Form.Recordset.RecordCount > 0)
End With

Afterward, you can restore any normal error-handling you have in place, or
else restore default error-handling with

On Error GoTo 0

Please let me know if this works to suppress the error, or if it doesn't.

 

[croy]

The With statement just "grabs" an object and says, "for the duration of
this block of code (up to the End With), any unqualified property or method
references should be understood as referring to this object."

I'm not sure why you're getting a "no current record" error, but if it's due
to this code, you might try suppressing errors during its execution by
leading it with the "On Error Resume Next" statement:

On Error Resume Next

With Me.sfMySubform
.Visible = (.Form.Recordset.RecordCount > 0)
End With

Afterward, you can restore any normal error-handling you have in place, or
else restore default error-handling with

On Error GoTo 0

Please let me know if this works to suppress the error, or if it doesn't.

That sounded like a good thing to try, but no, it didn't
stop the error (I tried both error-handlers, too).

Scratching head...

 

[Dirk Goldgar]

That sounded like a good thing to try, but no, it didn't
stop the error (I tried both error-handlers, too).

Scratching head...

Have you isolated the problem to that section of code? What happens if you
just remove that block of code entirely? Do you still get the error?

 

[croy]

Have you isolated the problem to that section of code?
Yup.

What happens if you
just remove that block of code entirely? Do you still get the error?

Nope. It seems to just be the "With" statement that causes
the error when the db is closed (and *if* the last current
record was one without any subform data).

I'm outta here for today. I'll hit it again in the morning.

 

[Dirk Goldgar]

Nope. It seems to just be the "With" statement that causes
the error when the db is closed (and *if* the last current
record was one without any subform data).

Odd; I'd have though the "On Error Resume Next" statement would have
suppressed the error. Can you trap the error in the form's Error event?

 

[croy]

Odd; I'd have though the "On Error Resume Next" statement would have
suppressed the error. Can you trap the error in the form's Error event?

I haven't been able to get any different results that way,
but I don't know if I'm doing it right.

I put this in the On Error event:

***
Private Sub Form_Error(DataErr As Integer, Response As
Integer)
On Error GoTo Err_Form_Error

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Number
Resume Exit_Form_Error

End Sub
***

 

[Dirk Goldgar]

I haven't been able to get any different results that way,
but I don't know if I'm doing it right.

I put this in the On Error event:

***
Private Sub Form_Error(DataErr As Integer, Response As
Integer)
On Error GoTo Err_Form_Error

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Number
Resume Exit_Form_Error

End Sub
***

That won't do anything, in itself. If it's going to work at all, we need
the body of the event procedure to examine the value of DataErr, decide that
it's the one we want to ignore, and then set the Response argument to
acDataErrContinue. For now, since I don't know the number of the error,
modify your procedure like this:

'----- start of code -----
Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

MsgBox "Error " & DataErr & " was raised."

Response = acDataErrContinue


Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Form_Error

End Sub

'----- end of code -----

Then trigger the error as you have been doing, and see if you get the
message from the event procedure. If you do, you can change the event
procedure to this:

'----- start of code -----
Private Sub Form_Error(DataErr As Integer, Response As Integer)


On Error GoTo Err_Form_Error

If DataErr = <the error number> Then
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Form_Error

End Sub

'----- end of code -----

 

[croy]

That won't do anything, in itself. If it's going to work at all, we need
the body of the event procedure to examine the value of DataErr, decide that
it's the one we want to ignore, and then set the Response argument to
acDataErrContinue. For now, since I don't know the number of the error,
modify your procedure like this:

'----- start of code -----
Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

MsgBox "Error " & DataErr & " was raised."

Response = acDataErrContinue


Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Form_Error

End Sub

'----- end of code -----

Then trigger the error as you have been doing, and see if you get the
message from the event procedure. If you do, you can change the event
procedure to this:

[snip]

Not a peep! Just the original error dialog, "No current
record".

 

[Dirk Goldgar]

Not a peep! Just the original error dialog, "No current
record".

Hmm. This means war.

What version (and SP) of Access are you using? I've tried and failed to
reproduce the problem in a form in my own test database, under Access 2003
SP3.

Can you send me a copy of the database to look at? Ideally, it would be a
cut-down copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in size
(preferably much smaller) -- I'll have a look at it, time permitting. You
can send it to the address derived by removing NO SPAM and ".invalid" from
the reply address of this message. If that address isn't visible to you,
you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

 

[croy]

Hmm. This means war.

What version (and SP) of Access are you using? I've tried and failed to
reproduce the problem in a form in my own test database, under Access 2003
SP3.

Here I'm running Access 2002 (xp) all patched, under Windows
XP. There is a setting for running the mdb as an Access
2002 or Access 2000 file--I've got it set to 2000. I'm not
sure if this is a global setting, or just for this mdb.

I did find an interesting anomaly on this form/subform
combination: in checking for the presence of code, there
was something on one of the fields, so I remarked that out,
but *then* I saw code under Form_Current. This was
interesting because in the properties window for the subform
events, there was nothing there. But the code was there (an
old attempt to hide this form when there was no data), so I
deleted it. But alas, still no joy. The same error still
appears when I close the form on a record with no subform
data.

Can you send me a copy of the database to look at? Ideally, it would be a
cut-down copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in size
(preferably much smaller) -- I'll have a look at it, time permitting. You
can send it to the address derived by removing NO SPAM and ".invalid" from
the reply address of this message. If that address isn't visible to you,
you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

In view of that orphaned code that I found, when I come in
on Monday, I'm going to try using the external compact and
repair utility, and see if that has any good effect--if not,
then I'll look up your address and shoot you a stripped-down
copy of the mdb.

 

[croy]

Hmm. This means war.

What version (and SP) of Access are you using? I've tried and failed to
reproduce the problem in a form in my own test database, under Access 2003
SP3.

I'm using Access 2002, Windows Xp, all patches in place.

Can you send me a copy of the database to look at? Ideally, it would be a
cut-down copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in size
(preferably much smaller) -- I'll have a look at it, time permitting. You
can send it to the address derived by removing NO SPAM and ".invalid" from
the reply address of this message. If that address isn't visible to you,
you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

I looked thru all the code, most of which was done years ago
(possibly under version 1 of Access, when I knew absolutely
nothing about anything), and tweaked a few things, but no
difference in the problem status.

I'll strip out most of the records from a copy of the mdb
and send it off to you. But fixing this is not a high
priority for me, mostly just curiosity about what is causing
the behavior. So don't put it ahead of anything that pays!

 

[Dirk Goldgar]

I'll strip out most of the records from a copy of the mdb
and send it off to you. But fixing this is not a high
priority for me, mostly just curiosity about what is causing
the behavior. So don't put it ahead of anything that pays!

I've had a look, and found a workaround for the problem, which seems to me
to be a big in Access. Where the original code referred directly to the
recordset of the subform via its Recordset property:

With Me.sfMySubform
.Visible = (.Form.Recordset.RecordCount > 0)
End With

.... I find that referring to a *clone* of the recordset using the subform's
RecordsetClone property:

With Me.sfMySubform
.Visible = (.Form.RecordsetClone.RecordCount > 0)
End With

.... does not cause the error -- at least, not for me, using Access 2003.

 

[croy]

Hmm. This means war.

What version (and SP) of Access are you using? I've tried and failed to
reproduce the problem in a form in my own test database, under Access 2003
SP3.

Can you send me a copy of the database to look at? Ideally, it would be a
cut-down copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in size
(preferably much smaller) -- I'll have a look at it, time permitting. You
can send it to the address derived by removing NO SPAM and ".invalid" from
the reply address of this message. If that address isn't visible to you,
you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

Got your eMail with revised mdb. I envy your ability to
look at a strange db, understand it and troubleshoot it in
such a short amount of time.

Such small changes--such great effect!

Thank you Dirk, very informative.

 

[croy]

I've had a look, and found a workaround for the problem, which seems to me
to be a big in Access. Where the original code referred directly to the
recordset of the subform via its Recordset property:


... I find that referring to a *clone* of the recordset using the subform's
RecordsetClone property:

With Me.sfMySubform
.Visible = (.Form.RecordsetClone.RecordCount > 0)
End With

... does not cause the error -- at least, not for me, using Access 2003.

And the same here.

I've never used the "With" statement before, and find little
in the help file on it. It seems to use a style of
argument(?) that's different from what I'm used to as well
(".Visible", for example). But it make sense now that I
think about it--state the object once, and not have to
repeat it for all the property statements.

Thanks for the lesson. I'll have to play with "With" for a
while to try to flesh out its territory. As you can tell,
I'm not a programmer.

 

[Dirk Goldgar]

I've never used the "With" statement before, and find little
in the help file on it. It seems to use a style of
argument(?) that's different from what I'm used to as well
(".Visible", for example). But it make sense now that I
think about it--state the object once, and not have to
repeat it for all the property statements.

Exactly. You should be able to find a help-file entry for it in the Visual
Basic Language Reference section. You may have to look that up while in the
VB Editor, rather than in the Access application window. Here's what mine
says in its Remarks section:

<QUOTE>
The With statement allows you to perform a series of statements on a
specified object without requalifying the name of the object. For example,
to change a number of different properties on a single object, place the
property assignment statements within the With control structure, referring
to the object once instead of referring to it with each property assignment.

Thanks for the lesson. I'll have to play with "With" for a
while to try to flesh out its territory. As you can tell,
I'm not a programmer.

You are now. ;-)