Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't enter data in Access 2007 form

    I have a data base with 5 tables. Lets call the A, B, C, D, & E. i was able to create a query and a form from the query for tables, A&B, A&C, A&D, A&E. These forms work fine and I can enter data into these forms. I created a query and a form from the query for ALL 5 tables. The form comes up fine but I am NOT able to enter date into this particular form. Thank you in advance for your help and suggestions.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    gts19,

    Welcome to the Lounge as a new poster!

    One important piece of information that is missing in your query is the relationships between the tables. I'd assume that based on the information provided that table A is the master and all the others are in a one to many relationship, e.g. one record in A relates to many records in all the other tables?

    If this is the case ASFAIK you can't create a single form to accomplish the data entry function. I've faced situations like this before and what I do is create a multi-form screen where when a record is selected in the master form it automatically synchronizes the other forms with the master record key.

    Redacted Access Multi Screen.jpg

    When the Owner is changed on the Owner Input/Update form this code is automatically called:
    Code:
    '                            +--------------------+               +----------+
    '----------------------------|   Form_Current()   |---------------| 01/07/13 |
    '                            +--------------------+               +----------+
    'Called by: Change on frmOwnerInput
    'Calls    : cmdSync()
    
    Private Sub Form_Current()
    
       Dim iOwnerID  As Integer
       Dim rst       As DAO.Recordset
       Dim lRecCnt   As Long
       Dim lCntr     As Long
       
       If IsNull(Me.OwnerID) Then Exit Sub  'New Record!
       
       iOwnerID = Me.OwnerID
       
    '   MsgBox "OwnerID = " & Format(iOwnerID) & vbCrLf & vbCrLf & _
    '          "Forms: " & Format(Forms.Count)
       
       With Forms.frmAssignLots
           .SetFocus
           .OwnerID.SetFocus
           Set rst = .Recordset
           rst.FindFirst "OwnerID = " & iOwnerID
           If rst.NoMatch Then
               MsgBox "No Lot Records found for Owner ID: " & Format(iOwnerID)
           Else
    '        MsgBox "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & _
    '               rst.Name
            DoCmd.GoToRecord acDataForm, "frmAssignLots", acGoTo, rst.AbsolutePosition + 1
           End If
       End With
       
       With Forms.frmAssignDocks
           .SetFocus
           .OwnerID.SetFocus
           Set rst = .Recordset
           rst.FindFirst "OwnerID = " & iOwnerID
           If rst.NoMatch Then
    '           MsgBox "No Dock Records found for Owner ID: " & Format(iOwnerID)
               .Visible = False
               Me.cmdShowDocks.Caption = "Show Docks"
           Else
    '        MsgBox "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & _
    '               rst.Name
            DoCmd.GoToRecord acDataForm, "frmAssignDocks", acGoTo, rst.AbsolutePosition + 1
            .Visible = True
             Me.cmdShowDocks.Caption = "Hide Docks"
           End If
       End With
       
       With Forms.frmassignstorage
           .SetFocus
           .OwnerID.SetFocus
           Set rst = .Recordset
           rst.FindFirst "OwnerID = " & iOwnerID
           If rst.NoMatch Then
    '         MsgBox "No Storage Records found for Owner ID: " & Format(iOwnerID)
             .Visible = False
             Me.cmdShowStorageLots.Caption = "Show Storage Lots"
           Else
    '        MsgBox "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & _
    '               rst.Name
             DoCmd.GoToRecord acDataForm, "frmAssignStorage", acGoTo, rst.AbsolutePosition + 1
             .Visible = True
           Me.cmdShowStorageLots.Caption = "Hide Storage Lots"
           End If
       End With
       
       Forms.frmOwnerInput.SetFocus
       DoCmd.MoveSize 0, 0
       
    End Sub                      'Form_Current()
    This syncs up the records on the other forms and hides the form if there is no data for that owner.

    Note: Owner ID is the Key for the Owners table and the Foreign key in all the other tables.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Something you might want to try is pulling up the query as a query, not as a form, and try to edit the data directly from there. If it doesn't allow you, then as RG said, it probably the relationships that's the problem. Good luck.

  4. #4
    New Lounger
    Join Date
    Dec 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thank you for the great suggestions.
    I will work on it per your recommendations and post results.

  5. #5
    New Lounger
    Join Date
    Dec 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    schajw, do you mean edit/enter date from the Query ?

  6. #6
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    After you open the database, on the ribbon/menu, hit Create, then Query Design. Hit close/cancel when it prompts you for tables, then hit the SQL button at the top/left. Copy/paste your query SQL into that window, then run it. At least this is how I do it - the graphical query editor gets in my way, so I go straight to the SQL window. If you wanted, you could use the graphical editor and duplicate your query there.

    After you run the query as a query, navigate to a field and try to type data into it. It should tell you if you can't do that.

  7. #7
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Quote Originally Posted by gts19 View Post
    I have a data base with 5 tables. Lets call the A, B, C, D, & E. i was able to create a query and a form from the query for tables, A&B, A&C, A&D, A&E. These forms work fine and I can enter data into these forms. I created a query and a form from the query for ALL 5 tables. The form comes up fine but I am NOT able to enter date into this particular form. Thank you in advance for your help and suggestions.
    date entry form shoudl user a single table as the record sources. related tables will be in sub forms. Lookup table use a list box or combo box control to show the related data from the lookup table.

    Parent form: Record source query on table A
    --Parent form: combo box bound to field in table A. Combo box row source table B
    --Sub form 1: Table C - linking fields master key table A and foreign key in Table C
    --Sub form 2: Table D - linking fields master key table A and foreign key in Table D
    ----Sub form 2: combo box bound to field in table D. Combo box row source table E

    I am only guessing to what the tables really mean.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    As Boyd has said you should use a master form and sub forms.

    The master source should be a table or query of the table applicable (eg. Table A).
    Sub form B's record source should be a table or query for table B, table A and B are related by table A's primary key to a foreign key in table B's record.
    The master/child's links come into play here, the masters primary key to the sub forms foreign key.

    When you get to the access number of sub form's limit of 1, the Master link (of sub form C where it's master is sub form B) becomes a hidden text box on the Main form (say txtPrimSFB) which is the Primary key of sub form B.
    To update the hidden text box you need to put code in Sub form B's OnCurrent event that does just that:
    Me.Parent!txtPrimSFB = Primary key of sub form B.

    The same applies for sub forms D and E.

    All this assumes that table A is the master of Table B, Table B is the master of table C, Table C is the master of table D, Table D is the master of table E, all this should be defined in the relationship diagram.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •