Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    How do Update Record W/Check Number?
    A2k (9.0.3821) SR-1

    I have the following module that asks for the beginning check number, then a report that prints checks from a record set query that isolates records by a date entered by the operator.

    What kind of code would I put in the report event to update the check record with the check number printed?

    Any help would be appreciated.

    Thanks, John

    '************************************************* *****
    'Declarations Section of Module
    '************************************************* *****
    Option Explicit

    Global NextNumber As Long

    '================================================= ==========
    'Create the following GetNextNumber() function in the Module
    '================================================= ==========
    'This function is called in the OnOpen property of the Report.

    Function GetNextNumber()
    Dim choice As String
    Do
    choice = InputBox("Enter a Starting Check Number:", " Starting Number", "1")
    If Not (IsNumeric(choice)) Then
    If MsgBox("Value Entered is not a Number.", vbExclamation + vbOKCancel) = vbCancel Then
    Exit Function
    End If
    End If
    Loop While Not (IsNumeric(choice))
    NextNumber = CLng(choice)
    End Function

    '================================================= =============
    'Create the following ReturnNextNumber() function in the Module
    '================================================= =============
    'This function is called by the text box that will contain the
    ' number.
    '
    Function ReturnNextNumber(number As Long)
    ReturnNextNumber = NextNumber + number - 1
    End Function

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    I have gotten this far, the following report code updates the record with the 1st check no OK, it repeats the check no in the second record, then the rest of the check numbers are blank.

    Any clues?

    John


    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim strSQL As String
    Dim strTable As String
    Dim strField As String
    Dim lngID As String

    strTable = "tblSumServe"
    strField = "strCHECKNO"
    lngID = "idsKeyOfSumerve"

    Dim db As DAO.Database
    Set db = CurrentDb
    strSQL = "UPDATE " & strTable & _
    " SET " & strField & " = " & Me.strCHECKNO & _
    " WHERE " & lngID & " = " & Me.idsKeyOfSumerve
    db.Execute strSQL
    Set db = Nothing

    End Sub

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    Have you tried setting a breakpoint on the setting up of the strSQL statement, does Me.strCHECKNO contain the correct value for each Me.idsKeyOfSumerve
    value.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    Hi Pat


    MsgBox "Me.strCHECKNO: " & Me.strCHECKNO

    Reveals displaying the first two indentical check numbers as previously reported.

    So the code is only getting excuted twice.

    What next?

    Is there another way to do what I'm trying to accomplish?

    Thanks for help

    John

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    What does the underlying query return for this report? Does it return all the records that you expect?
    I'm still confused, what are you actually trying to achieve in the report? Would you spell it out for me?
    Pat

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    Hi Pat

    The report prints AP checks remittance advice, detail then check, 1 per page.

    When I print the check number I want to update the record thats driving the report with the check number.

    John

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    Hi Pat

    The report that prints the checks works fine, all the records are processed, I'm looking for the best way to update the record as the checks are printed.

    John

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    Which particular line of code do you think should be incrementing the number?
    Regards
    John



  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    Hi Pat

    All is OK

    I was printing the checks in preview mode.

    When I finally hit the last page button, I saw my debug message repeating correctly.

    Next question while I have you, which code is more efficient and upward compatible within access?

    Dim db As DAO.Database
    Set db = CurrentDb
    strSQL = "UPDATE " & strTable & _
    " SET " & strField & " = " & Me.strCHECKNO & _
    " WHERE " & lngID & " = " & Me.idsKeyOfSumerve
    db.Execute strSQL
    Set db = Nothing

    DoCmd.SetWarnings False
    strSQL = "UPDATE " & strTable & _
    " SET " & strField & " = " & Me.strCHECKNO & _
    " WHERE " & lngID & " = " & Me.idsKeyOfSumerve
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    Thanks, for your help

    John

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    Hi John,

    I'm glad you got it working, it's pretty hard to know exactly what's going on when you are not testing it yourself.
    Anyway you've done well, it's great to solve your own problems, it gives a greater sense of satisfaction.

    To answer your question re which one is the more efficient and upward compatible, I would assume that the execute command is the way to go, but that's only a guess, anyone else know this one?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: How do Update Record W/Check Number? (A2k (9.0.3821) SR-1)

    John,

    About your question on Execute vs RunSQL:

    In a situation like your report, where only one record is updated at a time, the issue if moot. For updating large numbers of records, the DAO Execute method is probably more efficient. BUT...

    (1) In a future version of Access, Microsoft may not support DAO any more. You'd have to convert your code to ADO, where Execute is a method of the Connection and Command objects.

    (2) DoCmd.RunSQL supports the Access-specific extensions to SQL: you can refer to open Forms, use Access-specific and user-defined functions etc. Execute doesn't.

Posting Permissions

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