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

    Updating Multiple Record Form Based on Record Count

    All,

    A long time ago I made this thread to create a form with multiple editable records to use as a daily shift production tracker. I'm dusting it off to potentially use in another application, but I need to make it work a little better. I'm still terrible at coding however I am able to piece snippets together to achieve stuff from time to time. I've hit a brick wall in this regard.

    This OnLoad procedure allows me to create multiple records for different employees.
    Code:
    Private Sub Form_Load()
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 0)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 1)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 2)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 3)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 4)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 5)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 6)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 7)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 8)
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, 9)
    End Sub
    Which yields this form:


    This allows supervisors to enter daily production hours fairly easily on one form directly into my production database. Unfortunately, it requires me to hard code in a finite amount of columns to add to the form. I want the flexibility of re-querying the form by shift and employee type which can change the amount of records to display. In the instance above, I want the form to rerun the code based on 1st Shift "Auditors", which has an employee record count of 2. It would be reduced to two records as a result and look like this:





    Is there a way to do this? Maybe like some kind of if statement with a loop until the last record is reached?

    Any help would be greatly appreciated!

    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
    52,815
    Use combobox ListCount property. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

    intCount = Combo17.ListCount
    For i = 0 to intCount - 1
    DoCmd.GoToRecord acForm, "TEST", acNewRec
    Me.Combo17 = Combo17.Column(0, i)
    Next
    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 XP Access 2007
    Join Date
    Jul 2010
    Posts
    59
    Perfect! Thank you so much!

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

Similar Threads

  1. Record Count based on Form control value
    By Monterey_Manzer in forum Forms
    Replies: 7
    Last Post: 11-29-2012, 10:47 AM
  2. Replies: 2
    Last Post: 03-01-2012, 03:11 PM
  3. Navigation Bar Record Count not updating
    By ylatodd in forum Forms
    Replies: 27
    Last Post: 02-13-2012, 10:45 AM
  4. Updating record based on textbox entry
    By timmy in forum Programming
    Replies: 16
    Last Post: 04-06-2011, 12:05 AM
  5. Replies: 1
    Last Post: 02-03-2010, 08:17 AM

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