Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Orlando Florida
    Posts
    20

    Unanswered: pass form parameters to store procedure

    Hi All,

    I created a form and I want to pass the form values into a store procedure using parameters. On my form_load() module I have the following code. I am receiving a 'Run-time error 2185, You can't reference a property or method for a control unless the control has the focus'. What am I doing wrong.

    Option Explicit
    Option Compare Database

    Private cnnIrp As New ADODB.Connection
    Private cmmIrp As New ADODB.Command
    Private poriginals As New ADODB.Parameter
    Private pcopies As New ADODB.Parameter
    Private ptitle As New ADODB.Parameter
    Private pdate_time As New ADODB.Parameter
    Private pdevice As New ADODB.Parameter
    Private ppaper_type As New ADODB.Parameter
    Private ppaper_color As New ADODB.Parameter
    Private pcover_type As New ADODB.Parameter
    Private pfront_cover_color As New ADODB.Parameter
    Private pback_cover_color As New ADODB.Parameter
    Private paccount_code As New ADODB.Parameter
    Private puserid As New ADODB.Parameter
    Private pfull_name As New ADODB.Parameter
    Private pdepartment As New ADODB.Parameter
    Private rstIrp As New ADODB.Recordset

    Private Sub Form_Load()

    Dim sFile As String
    sFile = "c:\irp-acct.rst"

    'Specify the connection
    With cnnIrp
    .Open "Data Source=IRP;"
    End With

    With cmmIrp
    'Create and append parameter
    Set poriginals = .CreateParameter("originals", adChar, adParamInput, , Val(originals))
    .Parameters.Append poriginals
    Set pcopies = .CreateParameter("copies", adChar, adParamInput, Len(copies), copies)
    .Parameters.Append pcopies
    Set ptitle = .CreateParameter("title", adChar, adParamInput, Len(title), title)
    .Parameters.Append ptitle
    Set pdate_time = .CreateParameter("date_time", adChar, adParamInput, Len(date_time), date_time)
    .Parameters.Append pdate_time
    Set pdevice = .CreateParameter("device", adChar, adParamInput, Len(device), device)
    .Parameters.Append pdevice
    Set ppaper_type = .CreateParameter("paper_type", adChar, adParamInput, Len(paper_type), paper_type)
    .Parameters.Append ppaper_type
    Set ppaper_color = .CreateParameter("paper_color", adChar, adParamInput, Len(paper_color), paper_color)
    .Parameters.Append ppaper_color
    Set pcover_type = .CreateParameter("cover_type", adChar, adParamInput, Len(cover_type), cover_type)
    .Parameters.Append pcover_type
    Set pfront_cover_color = .CreateParameter("front_cover_color", adChar, adParamInput, Len(front_cover_color), front_cover_color)
    .Parameters.Append pfront_cover_color
    Set pback_cover_color = .CreateParameter("back_cover_color", adChar, adParamInput, Len(back_cover_color), back_cover_color)
    .Parameters.Append pback_cover_color
    Set paccount_code = .CreateParameter("account_code", adChar, adParamInput, Len(account_code), account_code)
    .Parameters.Append paccount_code
    Set puserid = .CreateParameter("userid", adChar, adParamInput, Len(userid), userid)
    .Parameters.Append puserid
    Set pfull_name = .CreateParameter("full_name", adChar, adParamInput, Len(full_name), full_name)
    .Parameters.Append pfull_name
    Set pdepartment = .CreateParameter("department", adChar, adParamInput, Len(department), department)
    .Parameters.Append pdepartment

    Set .ActiveConnection = cnnIrp
    'Specify a stored procedure
    .CommandType = adCmdStoredProc
    .CommandText = "irp_log_acct"
    'Receive the Recordset
    Set rstIrp = .Execute
    End With

    With rstIrp
    'Save (persist) the forward-only Recordset to a file
    On Error Resume Next
    'Delete the file, if it exists
    Kill sFile
    On Error GoTo 0
    'Save the file
    .Save sFile
    .Close
    'Open the file with default locking and cursor
    .Open sFile, "Provider=MSPersist", , , adCmdFile
    End With

    Set Me.Recordset = rstIrp

    'Me.originals.ControlSource = "originals"
    'Me.copies.ControlSource = "copies"
    'Me.title.ControlSource = "title"
    'Me.date_time.ControlSource = "date_time"
    'Me.device.ControlSource = "device"
    'Me.paper_type.ControlSource = "paper_type"
    'Me.paper_color.ControlSource = "paper_color"
    'Me.cover_type.ControlSource = "cover_type"
    'Me.front_cover_color.ControlSource = "front_cover_color"
    'Me.back_cover_color.ControlSource = "back_cover_color"
    'Me.account_code.ControlSource = "account_code"
    'Me.userid.ControlSource = "userid"
    'Me.full_name.ControlSource = "full_name"
    'Me.department.ControlSource = "department"
    'Me.date_time.ControlSource = "date_time"

    End Sub


    Thanks,

    ~bdavis

  2. #2
    Join Date
    Nov 2003
    Location
    Orlando Florida
    Posts
    20

    Re: pass form parameters to store procedure

    Originally posted by bdavis
    Hi All,

    I created a form and I want to pass the form values into a store procedure using parameters. On my form_load() module I have the following code. I am receiving a 'Run-time error 2185, You can't reference a property or method for a control unless the control has the focus'. What am I doing wrong.

    Option Explicit
    Option Compare Database

    Private cnnIrp As New ADODB.Connection
    Private cmmIrp As New ADODB.Command
    Private poriginals As New ADODB.Parameter
    Private pcopies As New ADODB.Parameter
    Private ptitle As New ADODB.Parameter
    Private pdate_time As New ADODB.Parameter
    Private pdevice As New ADODB.Parameter
    Private ppaper_type As New ADODB.Parameter
    Private ppaper_color As New ADODB.Parameter
    Private pcover_type As New ADODB.Parameter
    Private pfront_cover_color As New ADODB.Parameter
    Private pback_cover_color As New ADODB.Parameter
    Private paccount_code As New ADODB.Parameter
    Private puserid As New ADODB.Parameter
    Private pfull_name As New ADODB.Parameter
    Private pdepartment As New ADODB.Parameter
    Private rstIrp As New ADODB.Recordset

    Private Sub Form_Load()

    Dim sFile As String
    sFile = "c:\irp-acct.rst"

    'Specify the connection
    With cnnIrp
    .Open "Data Source=IRP;"
    End With

    With cmmIrp
    'Create and append parameter
    Set poriginals = .CreateParameter("originals", adChar, adParamInput, , Val(originals))
    .Parameters.Append poriginals
    Set pcopies = .CreateParameter("copies", adChar, adParamInput, Len(copies), copies)
    .Parameters.Append pcopies
    Set ptitle = .CreateParameter("title", adChar, adParamInput, Len(title), title)
    .Parameters.Append ptitle
    Set pdate_time = .CreateParameter("date_time", adChar, adParamInput, Len(date_time), date_time)
    .Parameters.Append pdate_time
    Set pdevice = .CreateParameter("device", adChar, adParamInput, Len(device), device)
    .Parameters.Append pdevice
    Set ppaper_type = .CreateParameter("paper_type", adChar, adParamInput, Len(paper_type), paper_type)
    .Parameters.Append ppaper_type
    Set ppaper_color = .CreateParameter("paper_color", adChar, adParamInput, Len(paper_color), paper_color)
    .Parameters.Append ppaper_color
    Set pcover_type = .CreateParameter("cover_type", adChar, adParamInput, Len(cover_type), cover_type)
    .Parameters.Append pcover_type
    Set pfront_cover_color = .CreateParameter("front_cover_color", adChar, adParamInput, Len(front_cover_color), front_cover_color)
    .Parameters.Append pfront_cover_color
    Set pback_cover_color = .CreateParameter("back_cover_color", adChar, adParamInput, Len(back_cover_color), back_cover_color)
    .Parameters.Append pback_cover_color
    Set paccount_code = .CreateParameter("account_code", adChar, adParamInput, Len(account_code), account_code)
    .Parameters.Append paccount_code
    Set puserid = .CreateParameter("userid", adChar, adParamInput, Len(userid), userid)
    .Parameters.Append puserid
    Set pfull_name = .CreateParameter("full_name", adChar, adParamInput, Len(full_name), full_name)
    .Parameters.Append pfull_name
    Set pdepartment = .CreateParameter("department", adChar, adParamInput, Len(department), department)
    .Parameters.Append pdepartment

    Set .ActiveConnection = cnnIrp
    'Specify a stored procedure
    .CommandType = adCmdStoredProc
    .CommandText = "irp_log_acct"
    'Receive the Recordset
    Set rstIrp = .Execute
    End With

    With rstIrp
    'Save (persist) the forward-only Recordset to a file
    On Error Resume Next
    'Delete the file, if it exists
    Kill sFile
    On Error GoTo 0
    'Save the file
    .Save sFile
    .Close
    'Open the file with default locking and cursor
    .Open sFile, "Provider=MSPersist", , , adCmdFile
    End With

    Set Me.Recordset = rstIrp

    'Me.originals.ControlSource = "originals"
    'Me.copies.ControlSource = "copies"
    'Me.title.ControlSource = "title"
    'Me.date_time.ControlSource = "date_time"
    'Me.device.ControlSource = "device"
    'Me.paper_type.ControlSource = "paper_type"
    'Me.paper_color.ControlSource = "paper_color"
    'Me.cover_type.ControlSource = "cover_type"
    'Me.front_cover_color.ControlSource = "front_cover_color"
    'Me.back_cover_color.ControlSource = "back_cover_color"
    'Me.account_code.ControlSource = "account_code"
    'Me.userid.ControlSource = "userid"
    'Me.full_name.ControlSource = "full_name"
    'Me.department.ControlSource = "department"
    'Me.date_time.ControlSource = "date_time"

    End Sub


    Thanks,

    ~bdavis
    I forgot to mention that I am using a postgres database on the back end. Also, the code is failing on the CreateParameter syntax. I know they are not consistent, but I was trying to get the first one working.

    Thanks,

    ~bdavis

Posting Permissions

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