Results 1 to 15 of 15
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: How do I pass a recordset object to sub procedures

    Hello,

    I am trying to pass a recordset object to (Next, Previous, First, Last) buttons sub procedures, in order to allow the user to navigate thru the recordset. Is this possible?

    Thanking you in advance for your help!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes. Just pass it like any variable (although it will behave differently to a value type - we'll deal with that if it comes up). What are you having problems with?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2010
    Posts
    67
    The following is partial code for the NEXT navigation button:

    Private Sub NextRecbtn_Click(rs1 As ADODB.Recordset)

    rs1.MoveNext
    Call Move_Record_To_Form(rs1)

    End Sub





    I am getting the following compile error:

    procedure declaration does not match description of event or procedure having the same name

    What am I missing? Your help is appreciated.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If NextRecbtn_Click is the sub called by the OnClick event of a button, it cannot work: you cannot change the arguments (if any) of such a procedure, it's defined by Access.

    Otherwise, what's calling the sub NextRecbtn_Click and what's the definition of the function or sub Move_Record_To_Form? Please post both lines of code.
    Have a nice day!

  5. #5
    Join Date
    May 2010
    Posts
    67
    Thank you so much for you help!

    The NextRecbtn_Click sub is called by the OnClick event. The Move_Record_To_Form sub is called by another sub inside the program.

    So, then how would I obtain the recordset object, when the sub that needs the recordset object is called by an On Click event? Can I save the recordset object.

    What is the definition of Move_Record_to_Form? I am somewhat new to access, I am learning as I code practically, so I do not completely understand what you mean by "definition".

    The Move_Record_To_Form sub is called within the program by other subs.

    The Move_Record_To_ Form code follows:

    Public Sub Move_Record_To_Form(ByRef rs1 As ADODB.Recordset)

    clientid.Value = rs1![clientid]
    JobNumber.Value = rs1![JobNumber]
    CampaignType.Value = rs1![CampaignType]
    StartDate.Value = rs1![StartDate]
    EndDate.Value = rs1![EndDate]
    MailerStatus.Value = rs1![MailerStatus]
    MailingName.Value = rs1![MailingName]
    TriggerSegment.Value = rs1![TriggerSegment]
    TSName.Value = rs1![TSName]
    MailingFrequency.Value = rs1![MailingFrequency]
    NbrMailingDay.Value = rs1![NbrMailingDay]
    DataSource.Value = rs1![DataSource]
    RemoteAccess.Value = rs1![RemoteAccess]
    reportid.Value = rs1![reportid]
    NbrDLDay.Value = rs1![NbrDLDay]
    Comments.Value = rs1![Comments]
    'ScheduleID.Value = rs1![ScheduleID]


    Dim position
    Dim SearchString As String

    If Not IsNull(rs1!MailingDays) Then
    SearchString = rs1!MailingDays

    position = InStr(1, SearchString, "1", 1)
    If position > 0 Then
    ChkMailSun.Value = 1
    End If

    position = InStr(1, SearchString, "2", 1)
    If position > 0 Then
    ChkMailMon.Value = 1
    End If

    position = InStr(1, SearchString, "3", 1)
    If position > 0 Then
    ChkMailTue.Value = 1
    End If

    position = InStr(1, SearchString, "4", 1)
    If position > 0 Then
    ChkMailWed.Value = 1
    End If

    position = InStr(1, SearchString, "5", 1)
    If position > 0 Then
    ChkMailThu.Value = 1
    End If

    position = InStr(1, SearchString, "6", 1)
    If position > 0 Then
    ChkMailFri.Value = 1
    End If

    position = InStr(1, SearchString, "7", 1)
    If position > 0 Then
    ChkMailSat.Value = 1
    End If
    End If

    If Not IsNull(rs1!DownloadDays) Then
    SearchString = rs1!DownloadDays

    position = InStr(1, SearchString, "1", 1)
    If position > 0 Then
    ChkDLSun.Value = 1
    End If

    position = InStr(1, SearchString, "2", 1)
    If position > 0 Then
    ChkDLMon.Value = 1
    End If

    position = InStr(1, SearchString, "3", 1)
    If position > 0 Then
    ChkDLTue.Value = 1
    End If

    position = InStr(1, SearchString, "4", 1)
    If position > 0 Then
    ChkDLWed.Value = 1
    End If

    position = InStr(1, SearchString, "5", 1)
    If position > 0 Then
    ChkDLThu.Value = 1
    End If

    position = InStr(1, SearchString, "6", 1)
    If position > 0 Then
    ChkDLFri.Value = 1
    End If

    position = InStr(1, SearchString, "7", 1)
    If position > 0 Then
    ChkDLSat.Value = 1
    End If
    End If

    'Dim varBookmark As Variant
    rs1.MoveLast
    lngTotalRec = rs1.RecordCount
    rs1.MoveFirst
    varBookmark = rs1.Bookmark
    Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngTotalRec

    FirstRecBtn.Enabled = True
    PrevRecBtn.Enabled = True
    NextRecBtn.Enabled = True
    LastRecBtn.Enabled = True

    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by eva.thaeh View Post
    The NextRecbtn_Click sub is called by the OnClick event.
    As I wrote before: you cannot change the arguments (if any) of such a procedure, it's defined by Access.

    Quote Originally Posted by eva.thaeh View Post
    how would I obtain the recordset object, when the sub that needs the recordset object is called by an On Click event?
    Where and how is the recordset defined (i.e. what's the line of code that creates or opens it, and in which module is it located)?
    Have a nice day!

  7. #7
    Join Date
    May 2010
    Posts
    67
    The recordset is created by the ViewCmd_Click(). Which is called by an OnClick event.

    The line of code that creates the recordset follows:

    Private Sub ViewCmd_Click()

    Debug.Print Form.Name

    DoCmd.SetWarnings False

    If IsNull(Me.clientid) Then
    MsgBox "Please enter or select a CLIENT NAME"
    Exit Sub
    End If

    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.Connection
    Dim rs1 As New ADODB.Recordset
    rs1.ActiveConnection = Conn

    ' do some more processing in sub

    End Sub

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the ViewCmd_Click() procedure is in the same module as the Move_Record_To_Form you can make rs1 available in the whole module by declaring it as a private member of this module:
    Code:
    ' In the Declarations section of the module:
    '
    Private rs1 As ADODB.Recordset
    
    ' In the Sub ViewCmd_Click:
    '
        Set rs1 = New ADODB.Recordset
        rs1.ActiveConnection = Conn
    
    ' Declaration of the Move_Record_To_Form procedure:
    '
    Public Sub Move_Record_To_Form()
    
    ' The rs1 recordset is available here 
    ' as it is a private member of the class
    ' and you don't need to pass it as parameter.
    '
    Have a nice day!

  9. #9
    Join Date
    May 2010
    Posts
    67
    Thank you Sinndho,

    It is working. Your help is truly appreciated!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please, for the love of God, remove this line:

    DoCmd.SetWarnings False
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2010
    Posts
    67
    Hi Pootle flump

    Please explain why...

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Create a new database. Setwarnings to false. Do some stuff like create some forms new forms, add controls, close without saving. Then you'll see why. SetWarnings is evil and most of the times that it is used it is like using a nuclear bomb to swot a fly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    May 2010
    Posts
    67
    Thank you for explaining....I am still new at Access, I am a mainframer. So all coding help and advice is truly is appreciated.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good - glad it helps
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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