runtime error 91 object variable or With block variable not set

[Member 785440]

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select *from Payroll", Cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Date = Text1.Text
rs!Emp_ID = Combo1.Text
rs!EmpName = Text2.Text
rs!Father_Husband = Text3.Text
rs!Designation = Text4.Text
rs!PANNo = Text5.Text
rs!PF_PENNo = Text6.Text
rs!HRA = Text7.Text
rs!Basic = Text8.Text
rs!TA = Text9.Text
rs!DA = Text10.Text
rs!PF = Text11.Text
rs!Medical = Text12.Text
rs!GrossPay = Text13.Text
rs!PTax = Text14.Text
rs!NetPay = Text15.Text
rs!TotalDeduction = Text16.Text
rs!AccountNo = Text17.Text
rs.Update
MsgBox "Your Record Has been Saved."
End Sub

in thi coding iam getting errr91 please solve this problem

[PVBert]

1. You attempted to use an object variable that hasn't been Set
Solution:
Specify a reference for the object variable.
For example, use a Set statement to set a reference to the object:

Dim MyObject As Object ' Create object variable.
Set MyObject = Sheets(1) ' Create valid object reference.

2. You attempted to use an object variable that has been Set to Nothing.

Set MyObject = Nothing ' Release the object.

Solution:
So you have to specify a new Set statement to a reference to the object:

Set MyObject = Sheets(1)

3. The object is a valid object, but it wasn't set because the object library
in which it is described hasn't been selected in the References dialog box.
Solution:
Select the object library in the Add References dialog box.

[Pilgrim-Last]

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select *from Payroll", Cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Date = Text1.Text
rs!Emp_ID = Combo1.Text
rs!EmpName = Text2.Text
rs!Father_Husband = Text3.Text
rs!Designation = Text4.Text
rs!PANNo = Text5.Text
rs!PF_PENNo = Text6.Text
rs!HRA = Text7.Text
rs!Basic = Text8.Text
rs!TA = Text9.Text
rs!DA = Text10.Text
rs!PF = Text11.Text
rs!Medical = Text12.Text
rs!GrossPay = Text13.Text
rs!PTax = Text14.Text
rs!NetPay = Text15.Text
rs!TotalDeduction = Text16.Text
rs!AccountNo = Text17.Text
rs.Update
MsgBox "Your Record Has been Saved."
End Sub

in thi coding iam getting errr91 please solve this problem

As a suggestion look at the following URL

http://www.w3schools.com/ado/ado_recordset.asp

this and one or two other topics should enable you to sort it out.

Also I don't know if you are writting the above code just for yourself or work purposes
but I would recommend that you look at your control naming as the code supplied would
not be easy for another programmer to pick up and maintain.

ie.

Rename button Command1 to cmdAddNewRecord

Also renaming the text controls can help to stop the wrong values being loaded
into the wrong record.

Lets say for example that for some reason in the following two lines of code that
Text12 is actuall Gross Pay and Text13 Medical.

rs!Medical = Text12.Text
rs!GrossPay = Text13.Text

if Text12 had been named txtGrossPay then that sort of error can be avoided.

I only point this out as its 1 good practice and having had to update a package written by someone else with the standard Text1, text2 etc I know it took me 2 weeks longer to complete the task.

Sorry end rant but hopes it helps yo out!

[johnwillyum]

I'm not sure at what point your program is crashing but I suspect it is because of
line 4. There should be a space between the asterisk & from. When you open a recordset at least check that it exists. You should also check that Cn is in fact open before you use it.

[kinwang2009]

I think you haven't dim cn that you have used. and there are other errors too. correct code should be like

Dim cn As ADODB.cnnection
Dim cmd As ADODB.Command
Dim cnString As String
Set rs As New ADODB.Recordset   
        cnString = "DBQ=" & App.Path & "\Database.mdb" & "; Driver={Microsoft Access Driver (*.mdb)};"
    Set cn = New ADODB.cnnection
    With cn
        .cnnectionString = cnString
        .Open , "Admin", "password" 'Username and password of database
    End With
rs.Open "select * from Payroll", cn, adOpenForwardOnly, adLockOptimistic

With rs
rs.AddNew
rs!Date = Text1.Text
rs!Emp_ID = Combo1.Text
rs!EmpName = Text2.Text
rs!Father_Husband = Text3.Text
rs!Designation = Text4.Text
rs!PANNo = Text5.Text
rs!PF_PENNo = Text6.Text
rs!HRA = Text7.Text
rs!Basic = Text8.Text
rs!TA = Text9.Text
rs!DA = Text10.Text
rs!PF = Text11.Text
rs!Medical = Text12.Text
rs!GrossPay = Text13.Text
rs!PTax = Text14.Text
rs!NetPay = Text15.Text
rs!TotalDeduction = Text16.Text
rs!AccountNo = Text17.Text
rs.Update
MsgBox "Your Record Has been Saved."
End With

Set cmd = Nothing
    
cn.Close
Set cn = Nothing

Hope this helps