Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Posts
    6

    Unanswered: Programatically Advance Records in Continuous Form

    I have an Access Form I'm using for an employee performance survey. Each employee is rated in 11 categories on a rating scale from 1-9. Rather than have each employee come up per category on a single form (equaling 11 forms per each employee rated!) I have changed the form to be continuous so the form displays all 11 categories being scored for each employee. I have disabled scrolling and other window modifications to keep the form pretty stationary. The form advance button is something I am manually coding.

    I've been able to get around the unbound option group used for scoring by posting the results to a temp table. That's not a problem. The problem is when I try to advance exactly 11 records so that the previous employee goes off the continuous form and the next one comes up with their 11 records.

    Declaring a PageCounter variable at the start of the form's code here's how I tried it:

    PageCounter = PageCounter + 1

    ' Post the scoring from the temp table for the real responses

    CurrentDb.Execute "INSERT INTO dbo_TblResponses SELECT * FROM TblDummy"
    DoCmd.SetWarnings (WarningsOff)

    ' Flush the temp table for the next employee being scored

    CurrentDb.Execute "delete from TblDummy"

    ' Try to advance to the next 11 set of categories to be scored

    DoCmd.GoToRecord , , acGoTo, (PageCounter * 12)
    Me.Refresh

    The first form advances just down a single record so that record 12 is at the bottom of the form (rather than at the top). Subsequent advances are 12 records forward but now the count is off one so the pagination is all messed up.

    Any ideas or suggestions?

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    This should do the trick:


    Sub YourButton_Click()
    Dim i As Integer
    Dim rst As DAO.Recordset
    With RecordsetClone
    For i = 1 to 11
    .MoveNext
    If .EOF Then
    .MoveLast
    GoTo TheEnd
    Next i
    TheEnd:
    Bookmark=.Bookmark
    End Sub

    Good luck

    Dan

  3. #3
    Join Date
    Sep 2002
    Posts
    6
    I get a 'Next without For' error when compiling this code.

  4. #4
    Join Date
    Sep 2002
    Posts
    6
    Originally posted by gregarican
    I get a 'Next without For' error when compiling this code.
    Actually I placed the End With and End If statements to get your code to compile. Here's what I put in:

    Dim i As Integer
    Dim rst As Recordset


    CurrentDb.Execute "INSERT INTO dbo_TblResponses SELECT * FROM TblDummy"
    DoCmd.SetWarnings (WarningsOff)
    CurrentDb.Execute "delete from TblDummy"

    With RecordsetClone
    For i = 1 To 11
    .MoveNext
    If .EOF Then
    .MoveLast
    GoTo TheEnd
    End If
    Next i

    TheEnd:
    Bookmark = .Bookmark
    End With

    The records jump, but I was trying to get the 1st, 12th, 24th, etc. record to populate the first row in the continuous form. That way the 11 scored categories for each employee display on a single page. This code still advances the rows toward the bottom of the form.

  5. #5
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    I think you've taken a complicated route...but I may be mistaken.
    Why don't you create a single form with employee data with a tabular subform containing the categories?

    No code needed, and data will be consistant. Whenever you go to another record you'll get the corresponding employee with his ratings.

    And you're right...I had forgotten the End If and End With statements. That's because I typed everything in the web form instead of VBE...

    Dan

  6. #6
    Join Date
    Sep 2002
    Posts
    6
    The tabular subform idea was right on target. Sometimes I get stuck in one gear and can't see the forest for the tress! I'm coding the subform now and it works like a champ. Thanks for all of the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •