Results 1 to 5 of 5
  1. #1
    William McKinley is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    59

    Splitting Records in Continuous Form

    All,



    I've been given a great solution to a previous problem in this thread but a new requirement has been added to the form. My supervisors want the ability to record multiple types of production hours for each employee by day on one form. Thanks to this forum, I achieve the multiple records on one form. Unfortunately splitting the hours has left me quite stumped.

    At first I thought I would create unbound text boxes to key in each type of hours worked for each employee's daily record. That did now work as the data updated for all rows of the unbound text box in the form. My next thought was to create Dummy fields to associate with the records, which worked, but also causes a normalization issue. My thought to fix the normalization issue is to have some kind of DoCmd.RunSQL function create two new records based on the textbox control it's associated with: textbox1(ProdHours) would create a record for Production Hours, textbox2 (CleanHours) would create a record for Cleaning Hours. \

    The SQL function RespCode (responsibility code) associated based on what they were doing. That would fix the normalization issue, but I can't figure out how to do it.

    Here's what everything looks like:



    The first three records are what the form outputs currently. The last six records are what I would want the SQL function to spit out. I would then run a delete record query if the RespCode field is null.

    Any thoughts on how to achieve the SQL part of this?

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The image is not showing for me (nor do the ones in the other thread).

    So you want two records for each employee listed in the combobox? Simplest approach at this point is to have two For loops:

    intCount = Combo17.ListCount

    For i = 0 to intCount - 1
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, i)
    Me.RespCode = {code for Production}
    Next

    For i = 0 to intCount - 1
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, i)
    Me.RespCode = {code for Cleaning}
    Next

    Run DELETE query to remove records where Hours field is null.
    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
    William McKinley is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2010
    Posts
    59
    Does the image show up now?

    They want to maintain a "matrix" style form if all possible. It "makes it easier for them to key in data"

    I tried modifying your code by doing:

    Code:
    Dim sql As String
    sql = "INSERT INTO tblHours(Hours, RespCode, EmployeeID, ProdDate) Values ([Forms]![TEST]!ProdHours,1,[Forms]![TEST]!Combo17,[Forms]![TEST]!Combo17) "
    
    intCount = Combo17.ListCount
     For i = 0 To intCount - 1
     DoCmd.RunSQL sql
     Me.Combo17 = Combo17.Column(0, i)
     Next
    but it only updates everything with the current selected record's data

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Yes, it shows now but I am viewing at home so don't know if that makes a difference if you did something.

    Well, that data entry form does not reflect the normalized structure of the six records. It might be easier for the users but programming for it is a pain.

    Either do normalized or non-normalized, don't try to mix them. If you really have to use 'matrix' style, probably will have to be with an unbound form and lots of code.
    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.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Okay, here is the only way I can envision your 'matrix' form to work with normalized table (aside from your original arrangement with finite set of controls).

    Put in form header section combo or list box of employees, unbound textboxes for hours, and a command button. Form can be bound to the table with bound controls in Detail section.

    Select employee from listbox, enter hours in textboxes, click button to save records. Repeat for each employee in the list. Code behind the button:

    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17
    Me.RespCode = {code for Production}
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17
    Me.RespCode = {code for Cleaning}
    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: 12
    Last Post: 05-23-2013, 10:51 AM
  2. selecting all records on continuous form using vb
    By Mclaren in forum Programming
    Replies: 13
    Last Post: 01-03-2012, 12:20 PM
  3. Rearranging records on continuous form
    By aytee111 in forum Forms
    Replies: 6
    Last Post: 11-16-2011, 03:04 PM
  4. Replies: 1
    Last Post: 04-02-2011, 11:55 AM
  5. Clear records off of continuous form
    By Ashe in forum Forms
    Replies: 2
    Last Post: 01-04-2011, 12:27 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