Results 1 to 13 of 13
  1. #1
    mjlerman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    7

    Access 2007 First record entered in Sub Form stays synchronized with control in Main Form, while no

    Presenting problem: First record entered in Subform stays synchronized with control in Main Form, while no other records do.


    main form = frmEntry
    controls in main form = Date, Audit Type, Shift
    sub form (continuous form) = frmSubform
    Link Master Fields = Date, Audit Type, Shift
    Link Child Fields = Date, Audit Type, Shift
    I have these links set up because I want them to be reflected on every record in the Subform w/o the user having to type it in every time. I also want the Subform to filter based on the Date, Audit Type, and Shift so that when a new Date, Audit Type, or Shift is chosen there will be no records displayed, only the option to make a new entry. This is when the problem starts to appear:
    I make these control selections in the main form and everything goes according to plan.
    Date = 9/10/2014
    Audit Type = Daily
    Shift = 1
    Click image for larger version. 

Name:	date1.PNG 
Views:	8 
Size:	18.6 KB 
ID:	18085

    Now when I select a new date I would like to enter new records that reflect these new choices (essentially is should start a clean slate):
    Date = 9/11/2014
    Audit Type = Daily
    Shift = 1
    Click image for larger version. 

Name:	date2.PNG 
Views:	8 
Size:	13.7 KB 
ID:	18086
    Here's where things start to go downhill, the first record that was entered for 9/10/2014 gets carried over to this new Subform that should display zero records because nothing has been submitted with this date.
    Click image for larger version. 

Name:	table.PNG 
Views:	8 
Size:	11.6 KB 
ID:	18087



    Here is my VBA code for the Main Form:
    Private Sub Form_Current()

    [frmSubform].Requery

    End SubPrivate Sub Form_AfterUpdate()

    [frmSubform].Requery

    End Sub

    Question: What should I change in order to have the first record not carry over?
    Thanks! Any help is greatly appreciated!!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What is the primary key field defined in the main form table? What is the related foreign key field in the subform table? These should be the fields used in the Master/Child Links properties.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mjlerman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    7
    Quote Originally Posted by June7 View Post
    What is the primary key field defined in the main form table? What is the related foreign key field in the subform table? These should be the fields used in the Master/Child Links properties.
    The Main Form and the Subform store data in the same table because I want a collection of all of the records in the same place. The primary key is CustId.
    Click image for larger version. 

Name:	table1.PNG 
Views:	6 
Size:	12.6 KB 
ID:	18088

  4. #4
    mjlerman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    7
    I don't know if I replied directly to your comment, so I'm posting this to let you know. Would it help if I uploaded my database in a zip file?
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A main form and subform bound to same table doesn't make sense to me. Maybe you should consider a Split Form. Or a single form set in Continuous view which can be arranged to look like Datasheet. Code can be used to carry forward the Date, Type, Shift data to next record. http://access.mvps.org/access/forms/frm0012.htm

    Or maybe should have two tables.

    Can upload db if you want but don't think will change my assessment.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    mjlerman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    7
    I really like the functionality of my current continuous form. The operator first scans all of their model and serial numbers into the DB and then goes back to update the defect information. I don't think this would be convenient with the split form. How would I go about using two tables?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure about your data relationships. If CustID, Date, Type, Shift are repetitive then perhaps those should be in one table and other info in a related child table. Record ID (autonumber?) would be PK and saved as FK in child table. Or use the suggested code to carry forward the repetitive data to new records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mjlerman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    7
    Is it possible to make a continuous form that has Date, Type, Shift in the header and then use the suggested code to carry it forward? I'm not picturing how this carrying over would be useful w/o a continuous form. Because I really need the user to be able to scan these in rapidly and a single form would require button clicking?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The carry forward by setting DefaultValue works regardless of form mode. It might work with those 3 fields in the header - I never tried. No button clicking is required.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    mjlerman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    7
    Is there no way to keep my current setup? I really want to be able to have all my data in one table. One of the main functions I want my DB to have is the ability to display information on the continuous form that is based off of the Date, Audit Type, and Shift. For example, when the Date is 9/11/2014, Audit Type is Daily, and Shift is 1 I want those records to display, but when the Date switches to 9/12/2014, etc, I want those records to display.
    Would I be able to have this ability if I use a single continuous form with these controls in the header? What do you think my options are?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Options are 1 table or 2 related tables.

    If one table/continuous form, can use UNBOUND controls to input filter criteria by any combination of Date/Type/Shift. http://www.allenbrowne.com/ser-62code.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    mjlerman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    7
    How do I mark this as solved?

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Thread Tools dropdown above first post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 10
    Last Post: 02-20-2013, 07:04 AM
  2. Replies: 6
    Last Post: 07-23-2012, 10:35 AM
  3. Replies: 3
    Last Post: 03-29-2012, 12:40 PM
  4. Replies: 3
    Last Post: 12-01-2011, 06:51 AM
  5. Access 2007 - Form control with VBA
    By washington in forum Forms
    Replies: 1
    Last Post: 07-18-2011, 08:39 AM

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
  •  
Other Forums: Microsoft Office Forums