SubForm...maybe

[alex]

Hello Experts,

Question about how to accomplish the following:

I have a Form that populates a Table.

I have the Form locked so tight that users can only enter data and
nothing else.

I what to, however, allow users to see what's in the table. I
attempted to place a Subform under the Form, but it didn't work
properly and seemed more complicated than it needed to be.

All that I want is just a replication of what's in the table (in
datasheet) under my Form. What's the best way to accomplish this?

As always, thanks for your time.

alex

 

[Wayne-I-M]

Hi Alex

One idea would be to create a new form (set format as datasheet) link this
to the main form by the primary field. Use this new form as the subform of
the main.

If it were me I would create a new query and create the datasheet form from
this - this allows more oportunites for formulas, IIf's, sorting, etc etc,
etc. Of course you could do these in the subform but I prefer to requery
OnClick of the main form to keep the information the user see's "real time"
and it's just simpler this way.

It is not really a good idea to let users have access to the table - there
is hardly ever a reason to do so if the forms are created correctly.

 

[Joan Wild]

Can you describe what problems you had placing a subform on your form?

If you create a datasheet style (or continuous style) form, bound to your
table, you can set the allow additions, allow edits, allow deletions all to
No. Open your main form in design view and drag the datasheet form from the
database window to your form. Then set the Link Master/Link Child
properties for the subform control to the primary key.

 

[alex]

Can you describe what problems you had placing a subform on your form?

If you create a datasheet style (or continuous style) form, bound to your
table, you can set the allow additions, allow edits, allow deletions all to
No. Open your main form in design view and drag the datasheet form from the
database window to your form. Then set the Link Master/Link Child
properties for the subform control to the primary key.

--
Joan Wild












- Show quoted text -

Thanks for the response; I appreciate the help.

I created a subform using the wizard and it looks ok on my Form. I
have a couple of problems, however.

First, I have a linking issue. I had to delete the Child and Master
field links to show the whole table on the subform.
Second, I've tried to lock the subform so that users can only scroll
up/down and left/right, however, I can still highlight the field(s)
with the mouse and attempt to select items from a lookup field (even
though it won't let me, it's annoying).
Third, after I enter a record and save said record, I cannot see that
record on the subform until I exit the form and then reenter.

What I basically want to do is allow users to see what's in the table
using the subform or other instrument. They'll enter data with the
Form and view what they've entered on the subform. I hope this makes
sense.

alex

 

[Joan Wild]

I created a subform using the wizard and it looks ok on my Form. I
have a couple of problems, however.

First, I have a linking issue. I had to delete the Child and Master
field links to show the whole table on the subform.

Yes, of course; I wasn't thinking when I wrote that, since your main form is
set to data entry.

Second, I've tried to lock the subform so that users can only scroll
up/down and left/right, however, I can still highlight the field(s)
with the mouse and attempt to select items from a lookup field (even
though it won't let me, it's annoying).

It may be annoying, but users will get used to the fact that they can't
actually change anything on the subform.

Third, after I enter a record and save said record, I cannot see that
record on the subform until I exit the form and then reenter.

After updating the main form, you can use
Me!NameofSubformControl.requery
to see the changes in the subform.

What I basically want to do is allow users to see what's in the table
using the subform or other instrument. They'll enter data with the
Form and view what they've entered on the subform. I hope this makes
sense.

I'm trying to think why the users really need to see the existing data,
since they can't edit it - why show it at all? Also it's usually
inefficient to show all records to a user, when they really only need to see
a couple.

You could create a form (no edits/additions) showing all the records and
provide a button to open a separate form to add new records.

 

[alex]

Yes, of course; I wasn't thinking when I wrote that, since your main form is
set to data entry.


It may be annoying, but users will get used to the fact that they can't
actually change anything on the subform.


After updating the main form, you can use
Me!NameofSubformControl.requery
to see the changes in the subform.


I'm trying to think why the users really need to see the existing data,
since they can't edit it - why show it at all? Also it's usually
inefficient to show all records to a user, when they really only need to see
a couple.

You could create a form (no edits/additions) showing all the records and
provide a button to open a separate form to add new records.

Thanks for the help Joan, I really appreciate it. Some follow-up:

My table (and subsequently my form) has a few lookup fields.
Even though I've disabled everthing that I can think of on my subform,
users can still press the combo box on the subform--even though
nothing happens. Is there a way to completely lock the subform except
the scroll bars? If the answer is no, what I could do is completely
lock the subform and keep the most recent records at the top...which
brings me to your question about why users need to see what's in the
table.

When I created the original form, I didn't want the users to
inadvertently mess up the data so I locked the form as tight as I
could...even disabling the mousewheel. What I found, however, is that
users sometimes forgot what they entered and were e-mailing me to
check the database. In a nutshell, users have physical cases stacked
at their desk that they data enter, sometimes they forget if the case
in front of them was just entered or now needs to be entered. This
may sound foolish, but without the subform (and keeping the original
Form completely locked) I can't think of a way around this issue.

alex

 

[Joan Wild]

--
Joan Wild
Microsoft Access MVP

My table (and subsequently my form) has a few lookup fields.
Even though I've disabled everthing that I can think of on my subform,
users can still press the combo box on the subform--even though
nothing happens. Is there a way to completely lock the subform except
the scroll bars?

You could base the subform on a query instead of your table. The query
would include the main table, as well as the 'lookup' tables that are used.

I don't want to side-track you, but lookups in a table can be very
confusing - create your lookups in your forms instead. Anyway, back to the
query. Include all the fields from the various tables that you want to
display. For the 'lookup' fields, pull the information from the lookup
table and don't include that field from your main table. Then your form
won't have any comboboxes. Instead all the information can be displayed in
textboxes.

When I created the original form, I didn't want the users to
inadvertently mess up the data so I locked the form as tight as I
could...even disabling the mousewheel. What I found, however, is that
users sometimes forgot what they entered and were e-mailing me to
check the database. In a nutshell, users have physical cases stacked
at their desk that they data enter, sometimes they forget if the case
in front of them was just entered or now needs to be entered. This
may sound foolish, but without the subform (and keeping the original
Form completely locked) I can't think of a way around this issue.

Another approach you could consider: Check in the BeforeUpdate event for
the control that holds the Case #, to make sure that it doesn't already
exist. Provide a message to the user that it exists and cancel.

Private Sub CaseID_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_CaseID_BeforeUpdate

If Not IsNull(CaseID.OldValue) Then
If DCount("[CaseID]", "[TableName]", "[CaseID] = " & Me.[CaseID]) >
0 Then
MsgBox "That Case is already in the database.", vbOKOnly
Cancel = True
Me.Undo
End If

Exit_CaseID_BeforeUpdate:
Exit Sub

Err_CaseID_BeforeUpdate:
Select Case Err.Number
Case Else
MsgBox "Error #" & Err.Number & " :" & vbCrLf & vbCrLf &
Err.Description
End Select
Resume Exit_CaseID_BeforeUpdate

End Sub