Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access:Code not updating field (2007)

    We have the following code that is supposed to update the Points field in the Newbord table of the board database. It supposed to take point values from different tables and add the point values to the Points field in the Newbord table. It is not updating the Points field in the Newbord table.

    Sub FixPoints()
    Dim P As Long, CurrPts As Long

    Set BoardDB = OpenDatabase(gsDataBasePath + "boarddata.mdb")
    Set NewBordTB = BoardDB.OpenRecordset("newbord")
    Set MemberConvInfoTB = BoardDB.OpenRecordset("memberconvinfo")
    MemberConvInfoTB.Index = "number"
    Set ConvAttendTB = BoardDB.OpenRecordset("convattend")
    ConvAttendTB.Index = "number"
    Set ConvActivitiesTB = BoardDB.OpenRecordset("Convactivities")
    ConvActivitiesTB.Index = "primarykey"
    Set RosterTB = BoardDB.OpenRecordset("roster")
    RosterTB.Index = "number"
    Set MeetingTB = BoardDB.OpenRecordset("meeting")
    MeetingTB.Index = "primarykey"

    NewBordTB.MoveFirst
    Do While NewBordTB.EOF = False
    P = 0

    MemberConvInfoTB.Seek "=", NewBordTB!Number
    If MemberConvInfoTB.NoMatch = False Then
    If MemberConvInfoTB!Canceled = False Then
    P = P + 30
    Else
    GoTo SkipConvPoints
    End If
    End If

    ConvAttendTB.Seek "=", NewBordTB!Number
    If ConvAttendTB.NoMatch = False Then
    Do While ConvAttendTB!Number = NewBordTB!Number
    ConvActivitiesTB.Seek "=", ConvAttendTB!ActivityID
    If IsNull(ConvActivitiesTB!POINTS) = False Then
    P = P + ConvActivitiesTB!POINTS
    End If
    ConvAttendTB.MoveNext
    If ConvAttendTB.EOF = True Then Exit Do
    Loop
    End If

    SkipConvPoints:

    RosterTB.Seek "=", NewBordTB!Number
    If RosterTB.NoMatch = False Then
    Do While RosterTB!Number = NewBordTB!Number
    MeetingTB.Seek "=", RosterTB!MNUM
    If IsNull(MeetingTB!POINTS) = False Then
    P = P + MeetingTB!POINTS
    End If
    RosterTB.MoveNext
    If RosterTB.EOF = True Then Exit Do
    Loop
    End If

    If IsNull(NewBordTB!POINTS) = True Then
    CurrPts = 0
    Else
    CurrPts = NewBordTB!POINTS
    End If

    If P > CurrPts Then
    NewBordTB.Edit
    NewBordTB!POINTS = P
    NewBordTB.Update
    End If

    NewBordTB.MoveNext
    Loop
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access:Code not updating field (2007)

    Click in the line

    Sub FixPoints()

    and press F8 to start executing the code step by step. Each time you press F8 one instruction will be executed. You can inspect the value of variables by hovering the mouse pointer over them in the code.
    Hopefully you'll find out why the records aren't updated. Perhaps the condition P > CurrPts is never met.

  3. #3
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access:Code not updating field (2007)

    The first error I am getting is on line three:
    Set BoardDB = OpenDatabase(gsDataBasePath + "boarddata.mdb")
    Error 3024; Could not find Boarddata.mdb

    The BoardData.mdb resides in the same folder as the database running the code. This code was created for Access 2002 and has since been updated to Access 2007. We are using the BoardData.mdb to hold all of the data, while the BoardProgram has linked tables to the Boarddata.mdb and runs all of the objects and all of the code.

    Your insight is greatly appreciated.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access:Code not updating field (2007)

    If your frontend database contains links to the tables in BoardData.mdb, you should be able to use

    Set BoardDB = CurrentDb

    (I don't have Access 2007, so I cannot tell whether opening an external database works differently there)

  5. #5
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access:Code not updating field (2007)

    Making that change did help.

    The next error comes on this line MemberConvInfoTB.Index = "number"

    Error 3251: Operation is not supported for this type of object. Number is the first primary Key in the MemberConvInfo table, (year is the second Primary key) Both Primary keys are set to text field type however, entry for both are numbers.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access:Code not updating field (2007)

    Try changing the line

    Set MemberConvInfoTB = BoardDB.OpenRecordset("memberconvinfo")

    to

    Set MemberConvInfoTB = BoardDB.OpenRecordset("memberconvinfo", dbOpenDynaset)

    (Similar for the others)

    You must use the name of an index, not the name of a field.

  7. #7
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access:Code not updating field (2007)

    Hans, I do appreciate your help with this. I know only a little bit about programming and while I understand what is being done by each line of code, I do not know what is wrong or how to make it work correctly. I do try to see if I can figure things out by using the keywords and the help syntax.

    My next error comes in this line:
    MemberConvInfoTB.Seek "=", NewBordTB!NUMBER Error 3251: Operation is not supported for this type of object

    In your response to my previous error 3251: Which is the Index?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access:Code not updating field (2007)

    I've never had much luck with the Seek method, so I always use FindFirst instead. For example, remove (delete) the line

    MemberConvInfoTB.Index = "number"

    and change the line

    MemberConvInfoTB.Seek "=", NewBordTB!NUMBER

    to

    MemberConvInfoTB.FindFirst "[Number] = " & Chr(34) & NewBordTB!NUMBER & Chr(34)

    The Chr(34)'s are double quotes ", they are needed to enclose a text value.

  9. #9
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access:Code not updating field (2007)

    ' MemberConvInfoTB.Seek "=", NewBordTB!NUMBER (Remmed out till new code is working)

    MemberConvInfoTB.FindFirst "[Number] = " & Chr(34) & NewBordTB!NUMBER & Chr(34) gives the error 91: Object Variable or With Block Variable not set.

    This is what the code looks like after previous changes.

    Sub FixPoints()
    Dim P As Long, CurrPts As Long
    'Code starting here has been changed with Hans' help.
    Set BoardDB = CurrentDb
    Set NewBordTB = BoardDB.OpenRecordset("newbord")
    'Set MemberConvInfoTB = BoardDB.OpenRecordset("memberconvinfo", dbOpenDynaset) remmed out till new code is working
    Set ConvAttendTB = BoardDB.OpenRecordset("ConvAttend", dbOpenDynaset)
    Set ConvActivitiesTB = BoardDB.OpenRecordset("Convactivities", dbOpenDynaset)
    Set RosterTB = BoardDB.OpenRecordset("roster", dbOpenDynaset)
    Set MeetingTB = BoardDB.OpenRecordset("meeting", dbOpenDynaset)

    NewBordTB.MoveFirst
    Do While NewBordTB.EOF = False
    P = 0

    ' MemberConvInfoTB.Seek "=", NewBordTB!NUMBER
    'New error message 91 as indicated above
    MemberConvInfoTB.FindFirst "[Number] = " & Chr(34) & NewBordTB!NUMBER & Chr(34)
    If MemberConvInfoTB.NoMatch = False Then
    If MemberConvInfoTB!Canceled = False Then
    P = P + 30
    Else
    GoTo SkipConvPoints
    End If
    End If

    ConvAttendTB.Seek "=", NewBordTB!NUMBER
    If ConvAttendTB.NoMatch = False Then
    Do While ConvAttendTB!NUMBER = NewBordTB!NUMBER
    ConvActivitiesTB.Seek "=", ConvAttendTB!ActivityID
    If IsNull(ConvActivitiesTB!POINTS) = False Then
    P = P + ConvActivitiesTB!POINTS
    End If
    ConvAttendTB.MoveNext
    If ConvAttendTB.EOF = True Then Exit Do
    Loop
    End If

    SkipConvPoints:

    RosterTB.Seek "=", NewBordTB!NUMBER
    If RosterTB.NoMatch = False Then
    Do While RosterTB!NUMBER = NewBordTB!NUMBER
    MeetingTB.Seek "=", RosterTB!MNUM
    If IsNull(MeetingTB!POINTS) = False Then
    P = P + MeetingTB!POINTS
    End If
    RosterTB.MoveNext
    If RosterTB.EOF = True Then Exit Do
    Loop
    End If

    If IsNull(NewBordTB!POINTS) = True Then
    CurrPts = 0
    Else
    CurrPts = NewBordTB!POINTS
    End If

    If P > CurrPts Then
    NewBordTB.Edit
    NewBordTB!POINTS = P
    NewBordTB.Update
    End If

    NewBordTB.MoveNext
    Loop
    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access:Code not updating field (2007)

    You have commented out the line that opens the recordset MemberConvInfoTB, so later on when you try to use this recordset, it is invalid.
    You should NOT comment out the line that opens the recordset, only the line that sets the Index.

  11. #11
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access:Code not updating field (2007)

    Thanks for all of your help. I am not getting anymore error messages on my code. However, the newboard table has over 18,000 records in it. Is there a way to temporally change the code so that I can look for a specific record number? This person is showing current points of 65 and they should show current points of 85. I tried to change the following line
    MemberConvInfoTB.FindFirst "[Number] = " & Chr(34) & NewBordTB!NUMBER & Chr(34)
    to
    MemberConvInfoTB.FindFirst "[Number] = "017947" & NewBordTB!NUMBER = "017947"

    Which of course gives me a Compile error: Syntax error

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access:Code not updating field (2007)

    Instead of doing that, keep the old line, and open the recordset like this:
    <code>
    Set NewBordTB = BoardDB.OpenRecordset("SELECT * FROM newbord WHERE [Number] = '017947'", dbOpenDynaset)
    </code>
    Note the use of single quotes around the text value wothin the string.

  13. #13
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access:Code not updating field (2007)

    I have updated the code as follows:
    Dim P As Long, CurrPts As Long
    Set BoardDB = CurrentDb
    'Set NewBordTB = BoardDB.OpenRecordset("newbord")
    Set NewBordTB = BoardDB.OpenRecordset("SELECT * FROM newbord WHERE [Number] = '017947'", dbOpenDynaset)
    'Set MemberConvInfoTB = BoardDB.OpenRecordset("memberconvinfo", dbOpenDynaset)
    Set MemberConvInfoTB = BoardDB.OpenRecordset("SELECT * FROM memberconvinfo WHERE [Number] = '017947'", dbOpenDynaset)
    'Set ConvAttendTB = BoardDB.OpenRecordset("ConvAttend", dbOpenDynaset)
    Set ConvAttendTB = BoardDB.OpenRecordset("SELECT * FROM ConvAttend WHERE [Number] = '017947'", dbOpenDynaset)
    'Set ConvActivitiesTB = BoardDB.OpenRecordset("Convactivities", dbOpenDynaset)
    Set ConvActivitiesTB = BoardDB.OpenRecordset("SELECT * FROM Convactivities WHERE [Number] = '017947'", dbOpenDynaset)
    'Set RosterTB = BoardDB.OpenRecordset("roster", dbOpenDynaset)
    Set RosterTB = BoardDB.OpenRecordset("SELECT * FROM roster WHERE [Number] = '017947'", dbOpenDynaset)
    'Set MeetingTB = BoardDB.OpenRecordset("meeting", dbOpenDynaset)
    Set MeetingTB = BoardDB.OpenRecordset("SELECT * FROM meeting WHERE [Number] = '017947'", dbOpenDynaset)

    The following line is giving an error:
    Set ConvActivitiesTB = BoardDB.OpenRecordset("SELECT * FROM Convactivities WHERE [Number] = '017947'", dbOpenDynaset)
    Gives error 3061 Too few parameters. Expected 1.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access:Code not updating field (2007)

    What is Convactivities? A table or a query?
    And is Number a field in Convactivities?

  15. #15
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access:Code not updating field (2007)

    Convactivities is a table The member number does not appear in this table so I removed the comment on this and the next line of code. It is going through until
    ConvActivitiesTB.FindFirst "[ActivityID] = " & Chr(34) & ConvAttendTB!ActivityID & Chr(34)
    I am getting an error 3464: Data type mismatch in criteria expression.

    The ActivityId exists in both tables as a Long Integer

Page 1 of 2 12 LastLast

Posting Permissions

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