Controls don't show on form

[Guest]

I have a form that is based on a query that combines 3 tables. When trying
to add a record I got an error message that an ID field was not in the
recordset. I thought I had included all linking fields. I made some changes
to the query(adding linking fields) and now when I open the form the body is
blank. I can't see anything that would cause this. all controls are set to
visible and the control sources are included in the query. What am I missing
that would cause this?
Thanks!

 

[Rick Brandt]

I have a form that is based on a query that combines 3 tables. When
trying to add a record I got an error message that an ID field was
not in the recordset. I thought I had included all linking fields.
I made some changes to the query(adding linking fields) and now when
I open the form the body is blank. I can't see anything that would
cause this. all controls are set to visible and the control sources
are included in the query. What am I missing that would cause this?
Thanks!

Multi-table queries are often not editable (Access/Jet is one of the few
databases where they are ever editable). Even when they are editable they are
not always good to use for that.

Sounds like you had a query that was uneditable and your change also caused it
to return zero records. When a form is bound to a query that is not editable
and which returns zero records then the entire detail section appears blank.

 

[Guest]

Thanks Rick for your reply. I've been trying to decide the best table design
to accomplish what I need to without duplication. Apparently I need to
redesign my table setup.
Thanks again!

 

[Rick Brandt]

Thanks Rick for your reply. I've been trying to decide the best
table design to accomplish what I need to without duplication.
Apparently I need to redesign my table setup.
Thanks again!

Going from more tables to fewer tables is seldom a good idea. In general a
properly normalized database will have more tables than a poorly designed one.
You just can't expect to combine all of your proper tables into one big query to
do editing with.

Most often a form/subform setup is the way to handle this.

 

[Guest]

Thanks again. I didn't remove any tables just changed some fields around so
all the forms would need only one table for it's control source. These forms
work as subforms being linked(supposedly) on opening from a command button.
However I am having a problem accomplishing this. Here is my present code
behind the button:
Private Sub cmdRoutineService_Click()
On Error GoTo Err_cmdRoutineService_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRoutineService"
stLinkCriteria = [ServiceDetailsID] = Me![ServiceDetailsID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_cmdRoutineService_Click:
Exit Sub

Err_cmdRoutineService_Click:
MsgBox Err.Description
Resume Exit_cmdRoutineService_Click

End Sub
I have also tried "stLinkCriteria = forms!frmRoutineService!ServiceDetailsID
= forms!frmServiceDetails!ServiceDetailsID" without any luck.
Thanks so much!

 

[Rick Brandt]

Thanks again. I didn't remove any tables just changed some fields
around so all the forms would need only one table for it's control
source. These forms work as subforms being linked(supposedly) on
opening from a command button.

Hold on there for a terminology check. A "subform" only exists as a form
embedded within another one using a subform control. If you are "opening" the
other forms then they are NOT subforms. You are simply opening a form
pre-filtered to show records related to the record in the first form. Other
than having a filter applied they have no relationship or link to the original
form.

In your code below...

stLinkCriteria = [ServiceDetailsID] = Me![ServiceDetailsID]

....should be...

stLinkCriteria = "[ServiceDetailsID] = " & Me![ServiceDetailsID]

....because stLinkCiteria is a String variable it needs to have quotes around the
assignment. This shold open the second form with the appropriate filter, but
because this is not an actual subform, that filter will not automatically link
any NEW records that you might add to the second form.

However I am having a problem
accomplishing this. Here is my present code behind the button:
Private Sub cmdRoutineService_Click()
On Error GoTo Err_cmdRoutineService_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRoutineService"
stLinkCriteria = [ServiceDetailsID] = Me![ServiceDetailsID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_cmdRoutineService_Click:
Exit Sub

Err_cmdRoutineService_Click:
MsgBox Err.Description
Resume Exit_cmdRoutineService_Click

End Sub

 

[Guest]

Okay let's see if I understand this. Opening a form this way is only for
displaying data not for inputing. I can make the second form a subform by
dragging it onto the first form. Is it possible to have multiple subforms of
a main form that would only be visible upon clicking a command button? If so
how do you get these to show in full view without scroll bars, basically show

Thanks again. I didn't remove any tables just changed some fields
around so all the forms would need only one table for it's control
source. These forms work as subforms being linked(supposedly) on
opening from a command button.

Hold on there for a terminology check. A "subform" only exists as a form
embedded within another one using a subform control. If you are "opening" the
other forms then they are NOT subforms. You are simply opening a form
pre-filtered to show records related to the record in the first form. Other
than having a filter applied they have no relationship or link to the original
form.

In your code below...

stLinkCriteria = [ServiceDetailsID] = Me![ServiceDetailsID]

....should be...

stLinkCriteria = "[ServiceDetailsID] = " & Me![ServiceDetailsID]

....because stLinkCiteria is a String variable it needs to have quotes around the
assignment. This shold open the second form with the appropriate filter, but
because this is not an actual subform, that filter will not automatically link
any NEW records that you might add to the second form.

However I am having a problem
accomplishing this. Here is my present code behind the button:
Private Sub cmdRoutineService_Click()
On Error GoTo Err_cmdRoutineService_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRoutineService"
stLinkCriteria = [ServiceDetailsID] = Me![ServiceDetailsID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_cmdRoutineService_Click:
Exit Sub

Err_cmdRoutineService_Click:
MsgBox Err.Description
Resume Exit_cmdRoutineService_Click

End Sub

 

[Rick Brandt]

Okay let's see if I understand this. Opening a form this way is only
for displaying data not for inputing.

No, you can use them for editing. It's just that if editing includes adding new
records then YOU have to design into the second form a facility for inheriting
the linking field values from the first form as there is nothing automatic built
into Access to do that for you. One way would be to set the DefaultValue
property for those field controls to...

=Forms!NameOfFirstForm!NameOfField

I can make the second form a
subform by dragging it onto the first form. Is it possible to have
multiple subforms of a main form that would only be visible upon
clicking a command button? If so how do you get these to show in
full view without scroll bars, basically show on top of the main
form, since they may be as big as the main form?
I appreciate your help very much.

You could add a subform control that takes up as much space of the parent form
that you want and then set that subform control's visible property to No. You
could then have buttons that set the SourceObject of the subform to control to
any form you want to use as a subform and then make it visible. Placing
subforms on TabControl pages is another way to do this.

 

[Guest]

Many thanks again for all your help!

No, you can use them for editing. It's just that if editing includes adding new
records then YOU have to design into the second form a facility for inheriting
the linking field values from the first form as there is nothing automatic built
into Access to do that for you. One way would be to set the DefaultValue
property for those field controls to...

=Forms!NameOfFirstForm!NameOfField


You could add a subform control that takes up as much space of the parent form
that you want and then set that subform control's visible property to No. You
could then have buttons that set the SourceObject of the subform to control to
any form you want to use as a subform and then make it visible. Placing
subforms on TabControl pages is another way to do this.