Subform with no records

[raviyah]

I have a form/subform in my database, each has a data table. The subform
table has a foreign key tied to the form's table and the subform is linked
with a master/child using this key. I have a command button on the form used
to open a separate form to add new records. There is also a command button on
the subform which adds records to the subform table.

I added a new record to the main form. There isn't yet a record in the
subform that links. What is happening is that when I select this record from
the main form, my sub-form doesn't show at all and hence, my command button
to add the record isn't showing either. When I move to a record that has
child records, the form shows fine. any ideas?

 

[Dirk Goldgar]

I have a form/subform in my database, each has a data table. The subform
table has a foreign key tied to the form's table and the subform is linked
with a master/child using this key. I have a command button on the form
used
to open a separate form to add new records. There is also a command button
on
the subform which adds records to the subform table.

I added a new record to the main form. There isn't yet a record in the
subform that links. What is happening is that when I select this record
from
the main form, my sub-form doesn't show at all and hence, my command
button
to add the record isn't showing either. When I move to a record that has
child records, the form shows fine. any ideas?

That suggests to me that the subform doesn't allow additions -- either its
AllowAdditions property is set to No, or its recordsource is a non-updatable
query, or the main form's AllowEdits property is set to No, or (unlikely)
the database is read-only.

If the subform doesn't allow additions, then any time there are no records
to be displayed, it will be blank. If you need to disallow additions on the
subform, then you'll need to move your command button for adding a subform
record up to the main form.

 

[raviyah]

Dirk,

Thanks for the tip. This is the problem.

That suggests to me that the subform doesn't allow additions -- either its
AllowAdditions property is set to No, or its recordsource is a non-updatable
query, or the main form's AllowEdits property is set to No, or (unlikely)
the database is read-only.

If the subform doesn't allow additions, then any time there are no records
to be displayed, it will be blank. If you need to disallow additions on the
subform, then you'll need to move your command button for adding a subform
record up to the main form.

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

(please reply to the newsgroup)

 

[Mary Beth]

Hi -

I have a similar issue going on and wondered if I could get some help. My
AllowAdditions are purposely set to "no" because I have a command button to
"Lock" and "Unlock" the db. When a parent record does not have a child
record, the subform does not show until I click the "Unlock" button. Then
the subform show and allow additions. Any ideas?

Thanks,

Mary Beth

 

[Dirk Goldgar]

Hi -

I have a similar issue going on and wondered if I could get some help. My
AllowAdditions are purposely set to "no" because I have a command button
to
"Lock" and "Unlock" the db. When a parent record does not have a child
record, the subform does not show until I click the "Unlock" button. Then
the subform show and allow additions. Any ideas?

This is exactly as expected. What do you want to have happen when there are
no records to display and the subform is locked?

 

[Mary Beth]

I figured so. Ideally, I would like to have the database in a "read-only"
("Locked") mode with the subform showing, even if a parent record does not
have a child record. Is this possible?

 

[Dirk Goldgar]

I figured so. Ideally, I would like to have the database in a "read-only"
("Locked") mode with the subform showing, even if a parent record does not
have a child record. Is this possible?

If your main form's AllowEdits property is set to No, it's going to be hard
to do this. If the main form's AllowEdits property is set to Yes, then you
can leave your subform's AllowEdits set to yes, but set its Enabled property
to No when the database is supposed to be locked.

More generally, one way of approaching this sort of problem is to manipulate
the Locked property of various controls, rather than using the form's
AllowEdits property to control things. That approach often means looping
through the Controls collection to set the Locked property to True or False
as desired, but you can set up a Sub to do that. I've done this using each
control's Tag property to indicate whether or not it should participate in
the locking/unlocking process.

 

[Dirk Goldgar]

In response to the 1st paragraph, I don't see an Enabled property in the
property box on the subform itself.

The subform *control* (on the main form) has an Enabled property.

On the 2nd, seems like alot of coding when you want to have the entire db
locked, is it? This is the code I am currently using and seem to still be
having problems regardless if I take out the AllowEdits code.

The AllowEdits, Additions, Deletions property is set to "no" on both the
form/subform.

On click event of the "unlock/lock" command button:
If Me.AllowAdditions = False Then
UnlockControls
ElseIf Me.AllowEdits = True Then
LockControls
End If

On the LockControl function:
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False
Me.sfrmProgInfo.Form.AllowEdits = False
Me.sfrmProgInfo.Form.AllowAdditions = False
Me.sfrmProgInfo.Form.AllowDeletions = False
Me.cmdUnlock.Caption = "Unlock"

On the UnlockControl function:
Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True
Me.sfrmProgInfo.Form.AllowEdits = True
Me.sfrmProgInfo.Form.AllowAdditions = True
Me.sfrmProgInfo.Form.AllowDeletions = True
Me.cmdUnlock.Caption = "Lock"

Without having to do a "loop" as you have suggested, is it possible to
achieve what I am wanting with this code above?

Not with anything quite that simple, at any rate. The problem is that, as I
understand it, you want the subform to display a blank record, even when no
record can be added via the subform. Access will not let this happen. And
the mere fact of the main form's AllowEdits property being set to No makes
Access think that the subform should be completely unupdatable. Hence, no
records can be added to the subform when the main form doesn't allow edits.
This is true even if the *subform's* AllowAdditions property is set to True.

If you don't need the subform to display a blank record, you can put other
controls in the subform's Form Header and Form Footer sections, to give the
user a framework outlining where the record would be. And you could maybe
resize the form header on the fly and display a label saying "No Records On
File", or something like that.

If you really need to have the subform display a blank record even though
the user won't be allowed to enter anything in that record, I believe you'll
need to give up toggling the main form's AllowEdits property and the
subform's AllowAdditions property, and use locking/unlocking of controls
instead.

 

[Mary Beth]

I don't believe the form absolutely has to show a blank record when "locked",
so I will check to see if the statement "There are no records at this time"
will be sufficent in this case. The subform outline and my label of the form
shows even with no record.

Thank you for your patience and help. Mary Beth