Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Controls don't show on form

Discussion in 'Information Technology' started by Loni - RWT, Jul 28, 2006.

  1. Loni - RWT

    Loni - RWT
    Expand Collapse
    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!
     
  2. Loading...


  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Loni - RWT wrote:
    > 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.


    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  4. Loni - RWT

    Loni - RWT
    Expand Collapse
    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" wrote:

    > Loni - RWT wrote:
    > > 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.
    >
    >
    > --
    > Rick Brandt, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
    >
    >
    >
     
  5. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Loni - RWT wrote:
    > 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.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  6. Loni - RWT

    Loni - RWT
    Expand Collapse
    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" wrote:

    > Loni - RWT wrote:
    > > 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.
    >
    > --
    > Rick Brandt, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
    >
    >
    >
     
  7. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Loni - RWT wrote:
    > 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, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  8. Loni - RWT

    Loni - RWT
    Expand Collapse
    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
    on top of the main form, since they may be as big as the main form?
    I appreciate your help very much.

    "Rick Brandt" wrote:

    > Loni - RWT wrote:
    > > 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, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
    >
    >
    >
     
  9. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Loni - RWT wrote:
    > 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.


    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  10. Loni - RWT

    Loni - RWT
    Expand Collapse
    Guest

    Many thanks again for all your help!

    "Rick Brandt" wrote:

    > Loni - RWT wrote:
    > > 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.
    >
    >
    > --
    > Rick Brandt, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
    >
    >
    >
     

Share This Page