Results 1 to 15 of 15
  1. #1
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65

    Button not working in Navigation Form tab

    Hello.


    I am creating a database that allows for single-click sign ins and sign outs that timestamp with an employeeID to keep track of hours.
    I'm also doing this for clients of my day care.

    The issue I'm having is that when I use my buttons in the forms, they work great. But when I try to use them through a Navigation form, I get the error:
    "You cannot add or change a record because a related record is required in table 'table_Clients'"

    I've been trying for a week to figure out how to reference the subtable within the original form, within the navigation form, but I can't seem to get it.

    Here's the macro I'm using now for the sign in button:
    Control Name: subform_SignIn //this is the name of the subform that is shown on the sign-in form
    GoToRecord: Record: New
    SetValue: Item: [subform_SignIn].[Form]![SignInType]: Expression ="In"
    SetValue: Item: [subform_SignIn].[Form]![SignInTime]: Expression =now()
    SetValue: Item: [subform_SignIn].[Form]![SignInDate]: Expression =now()

    I feel like I have an idea what it's *supposed* to do, but I'm new to Access and I'm so stuck on this. Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cannot save a sub record until the Master record is saved.
    If you added a master record, it could still be in edit mode when you move to the sub table.
    you MUST save the master record first.

  3. #3
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    Quote Originally Posted by ranman256 View Post
    you cannot save a sub record until the Master record is saved.
    If you added a master record, it could still be in edit mode when you move to the sub table.
    you MUST save the master record first.
    but I'm not creating a new master record, only selecting one that already exists. How do I lock it on the selected master record (client) so that I can update the ins/outs of the sub record?

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Sounds like the Main table key field for the employee record is not getting passed to your subform table. Are you using LinkMaster and LinkChild properties to link both forms? If so that will pass the Key field in the subform. If not then you need to make sure the key field in Main table is written to the subform table when they click the button.

  5. #5
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    That seems to be what's going on, although I've been racking myself to figure out how to fix that. I don't see a Link Master or child in the navigation form. In the subform that is on a tab on the navigation controls on the Home page, it is linking master and child both to ClientID which i would expect to pass just that. How would I set the ClientID to wherever it's storing the current value? I'm not sure how to reference that.

  6. #6
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    I'm still pretty new at access, and the database is a work in progress- Here is a copy I put up on my google drive if anyone wants to take a look and give me some feedback on how to fix this issue.
    https://drive.google.com/drive/folde...Xc?usp=sharing

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    It would be on the subform properties. Or you could set it manually on the Subform in the BeforeInsert Event, put something like Me.ClientID = Forms!Main!ClientID

  8. #8
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    Afraid that didn't work. 4 years of college for game design, tons of programming courses, and I remember now why I chose to become a firefighter instead lol.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Which part didn't work? In Design mode of the Main form, when you click on the subform and look at properties, is anything in the LinkMaster or LinkChild boxes in the Data tab? I assume key for Client table is CliendID, then in SignIn table you have field CliendID also right? If so add ClientID to the LinkMaster and LinkChild options.

  10. #10
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    Quote Originally Posted by Bulzie View Post
    Which part didn't work? In Design mode of the Main form, when you click on the subform and look at properties, is anything in the LinkMaster or LinkChild boxes in the Data tab? I assume key for Client table is CliendID, then in SignIn table you have field CliendID also right? If so add ClientID to the LinkMaster and LinkChild options.
    Yes, clientID is in both fields already. Master and child, from their respective locations. That doesn't fix the issue is what I meant. I posted a link to a copy i put in my google drive if you want to take a look.

  11. #11
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    Anyone else have any suggestions? I'm really stuck here.

  12. #12
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can you describe your form structure. So you have a navigation form that has a button to a Client Form and an Employee form? And then once on those forms you have a subform on each to do the Signin and Signout? Or is the SignIn/SignOut form another button on the navigation form?

  13. #13
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    Objects:
    table_Clients (the key, ClientID is an autonumber that represents each client record)
    form_SignIn (the form header contains a search to select clients by first, last, or clientID, as well as next and previous record buttons. The body contains two buttons, one that says SIGN IN and one SIGN OUT. There is a subform below them that contains subform_SignIn shown in table form, so they can see their previous sign times/dates, as well as the one they'll be entering upon button press)
    subform_SignIn (lists ClientID, date, time, sign in type [in or out] and SignInID)

    The forms:
    Home (contains the main navigation for the other forms)
    NavigationControlBar (the bar that contains the tabs for selecting the forms)
    NavigationSubform (the form that houses the selected forms, in this case form_SignIn, which itself contains the in/out buttons and subform_SignIn)

    Other:
    subform_SignIn contains LinkMasterFields which targets ClientID from the table_Clients, and LinkChildFields which contains ClientID from subform_SignIn (which I thought would pass the clientID when I use the search combo boxes to select a record. it does select the correct records and updates the subform_SignIn with the INs and OUTs associated with the ClientID)
    form_SignIn recordsource is table_Clients

  14. #14
    mcmcd99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    65
    So maybe I can streamline this..
    the form Main contains NavigationSubform contains form_SignIn contains subform_SignIn and the two buttons

  15. #15
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe instead of fooling with the forms, add the data directly to the SignIn/Out table when they click each button and then refresh that SignIn/Out subform.

    Syntax is not all correct and you will need to change names to your forms, fields, tables but something like this:

    SignIn Button OnClick event:
    Dim dbs As Database

    Set dbs = CurrentDb

    dbs.Execute " INSERT INTO tblSignInOut " _
    & "(ClientID, SignDate, SignTime, SignType) VALUES " _
    & "(Forms!frmMain!ClientID, Date(), Time(), 'In');"
    dbs.Close

    Forms!frmMain!subfrm_SignIn.Requery

    Do the same for the SignOut Button but change 'In' to 'Out'. Not sure if that reference above to ClientID will work, you might need to play with that one some. Also best not to use date and time as field names as those are reserved words in Access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-08-2016, 11:37 AM
  2. Replies: 13
    Last Post: 01-22-2015, 05:27 PM
  3. Replies: 4
    Last Post: 10-08-2012, 05:33 PM
  4. Print Button on a Navigation Form
    By sotssax in forum Forms
    Replies: 2
    Last Post: 09-15-2011, 07:47 AM
  5. Replies: 2
    Last Post: 08-18-2011, 10:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums