Can somebody find the error in this code please?

I am prompting a user for a value then running this query.
Error is "No value given for 1 or more required parameters. This is similar but different than my last post.

Any help would be appreciated.

Thanks
Mark

code:

With rsCertInfo
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
'open the recordset based on tblContacts table using the existing connection
strSQL = "SELECT * FROM tblCertificationInfo WHERE tblCertificationInfo.ProviderName = '" & _
Forms![frmGetProviderName]![cboProviderName] & "'"
.Open strSQL, cnCertInfo ' defined above
End With

Where is your cnCertInfo being defined?

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Is the form "frmGetProviderName" open when you execute the code? If not, try it again while the form is open.

Just my 2 cents...

Here is the full code, I am using Access 2003.

Thanks, Mark

Private Sub Form_Load()

Dim cnCertInfo As ADODB.Connection
Dim rsCertInfo As ADODB.Recordset
Dim strConnection As String

'specify the connection string for connecting to the database
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\CertGUI_2012-04-04.mdb;"

'create a new connection instance and open it using the connection string
Set cnCertInfo = New ADODB.Connection
cnCertInfo.Open strConnection

'create a new instance of a recordset
Set rsCertInfo = New ADODB.Recordset
'set various properties of the recordset

'create a new instance of a recordset
'Set rsCertInfo = New ADODB.Recordset
'set various properties of the recordset

With rsCertInfo
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
'open the recordset based on tblContacts table using the existing connection
'strSQL = "SELECT * FROM tblCertificationInfo WHERE tblCertificationInfo.ProviderName = '" & _
' Forms![frmGetProviderName]![cboProviderName] & "'"
.Open "SELECT * FROM tblCertificationInfo WHERE tblCertificationInfo.ProviderName = A Bridge to Recovery, Inc' "

'.Open strSQL, cnCertInfo
End With

'if the recordset is empty
If rsCertInfo.BOF And rsCertInfo.EOF Then
MsgBox "There are no records in the database."
Exit Sub
'if the recordset is not empty, then bind the
'recordset property of the form to the rsContacts recordset
Else
Set Me.Recordset = rsCertInfo
End If

'bind the controls of the form to the proper field in the recordset (which has
'the same field names as the tblContacts table from which it was generated)
Me.txtProgramName.ControlSource = "ProgramName"
Me.txtProvider.ControlSource = "ProgramSponsor"
Me.txtCertBeginDate.ControlSource = "CertBeginDate"
Me.txtCertEndDate.ControlSource = "CertEndDate"
Me.txtCertNumber.ControlSource = "CertNumber"
Me.txtCertInvalidDate.ControlSource = "CertInvalidDate"
Me.txtCertType.ControlSource = "CertType"
Me.txtContact.ControlSource = "Contact"
Me.txtState.ControlSource = "State"
Me.txtCity.ControlSource = "City"
Me.txtZip.ControlSource = "Zip"
Me.txtPhone.ControlSource = "Phone"
Me.txtAddress1.ControlSource = "Address1"
Me.txtAddress2.ControlSource = "Address2"
Me.txtCounty.ControlSource = "County"
Me.txtRegion.ControlSource = "Region"
Me.txtRegionalCenter.ControlSource = "RegionalCenter"
Me.txtNotes.ControlSource = "Notes"

End Sub