Results 1 to 5 of 5
  1. #1
    the_raul is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    1

    Master/Subform of a single table

    Hi Everyone,



    I just recently started playing with Access and have managed to build a few databases with VBA.
    However I'm stuck on my latest challenge:

    I have a table called Timesheet, in this table there are many records of peoples time booked to projects.
    The table has the following fields.

    User, WeekNo, ProjectID, Date, Hours

    I want to have a form, where at the top the user can pick their name and the week number.
    Then at the bottom there is a datasheet showing all the records for that user on that week.

    Additionally I would like them to be able to enter new records also in the datasheet.

    Which type of form should i use? I tried a Master/Sub Form but i cant seem to get it to work.

    Thanks,
    the_raul

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    unbound combo box1, cboUser
    user picks, and it filters the master form:

    Code:
    sub cboUser_afterupdate()
    if isNull(cboUser) then
       me.filterOn = false
    else
       me.filter = "[UserID]=" & cboUser   
      me.filterOn = true
    endif
    end sub



    once you have your person, cbo2 (cboDate) on the HEADER of the subform, can filter the subform:
    Code:
    sub cboDate_afterupdate()
    
    Code:
    if isNull(cboDate) then me.filterOn = false else me.filter = "[DAte]=#" & cboDate & "#" me.filterOn = true endif
    end sub

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could try a split form (not a big fan) because AFIAK, it's the only way you can get a header along with a datasheet view. In the header you'd put combos/textboxes to filter the datasheet view.

    Your details don't indicate a need for a subform, but that is the other way. Same controls in the main form to provide options and then requery the datasheet form based on those options. My guess is that your db design is part of the problem - your timesheet table has no primary key field, or you omitted it from your post. If your db is not properly designed, you are going to continue to have trouble.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that "Date" is a reserved word in Access and shouldn't be used for object names.
    "Hour" is also a reserved word, but "Hours" is OK. I would use "WorkDate" and "WorkHours" - they are more descriptive. "Date" of what?? "Hours" of what?

    Post your dB if you remain stuck....

    Good luck with your project and Welcome to the forum...........

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    An example how I would do this (unbound form and continuous subform setup)

    PS. I used ISO weeknumbers in tCalendary - simply as they were easier to access for me - and entered them manually (example time interval was short, so I didn't bother to calculate week numbers in append query, which was used to fill the table tCalendary).
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 2
    Last Post: 01-21-2018, 09:19 PM
  2. Resizing master form when subform resizes
    By Lukael in forum Programming
    Replies: 9
    Last Post: 12-16-2015, 09:05 AM
  3. Replies: 12
    Last Post: 11-17-2014, 01:33 PM
  4. Issues with subform and master form
    By asmith533 in forum Forms
    Replies: 14
    Last Post: 09-24-2012, 09:13 AM
  5. subform to open form w/ active master record
    By spitfire122 in forum Access
    Replies: 1
    Last Post: 06-28-2011, 04:28 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