Access Error: Object variable or With Block Variable not set

[rebeccabarker]

I am getting the following error message when I try to save a record in my access 2000 database: Object variable or With block variable not set. I have written VB behind a form to perform the save function.

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim sSQL As String
Dim sEIN As String
Dim sEINFstHalf As String
Dim sEINLstHalf As String

Dim sDashPos As String

sDashPos = InStr(1, Me.txtEIN, "-")
sEINFstHalf = Mid(Me.txtEIN, 1, sDashPos - 1)
sEINLstHalf = Mid(Me.txtEIN, sDashPos + 1, 10)
sEIN = sEINFstHalf + sEINLstHalf

sSQL = "Select * from TBLEMPR where EMPREIN = '" & sEIN & "'"


'THE LINE BELOW IS CAUSING THE ERROR MESSAGE

rstTBLEMPR.Open sSQL, localConnection, adOpenDynamic, adLockOptimistic, adCmdTable

If rstTBLEMPR.EOF = False Then
With rstTBLEMPR
!Street = Me.txtStreet
!City = Me.txtCity
!State = Me.txtState
!ZipCode = Me.txtZip
!TELENO = Me.txtPhoneNum
!FAXNO = Me.txtFaxNo
.Update
.Close
End With
End If


Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub



I have searched all through google and none of the answers I found are helpful. I have save functions behind other forms that are identical to this one and are working fine.

Any help is greatly appreciated!

 

[RoyVidar]

You'll need to both declare and instantiate the recordset object variable (is the connection variable also declared and instantiated?):

[tt]dim localconnection as adodb.connection
dim rstTBLEMPR as adodb.recordset
set localconnection=currentproject.connection
set rstTBLEMPR = new adodb.recordset[/tt]

- perhaps in the other forms you have a publics?

Roy-Vidar

 

[rebeccabarker]

Thanks! I had declared both variables but had only set the connection variable and not the recordset.

 

[PHV]

If by chance EMPREIN is defined as a numeric field in TBLEMPR, you may try this:
sSQL = "Select * from TBLEMPR where EMPREIN = " & sEIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244