Trouble adding new record to Access DB

Printable View

I keep getting "Compile Error - Method or data member not found" for .AddNew in my code. What am I doing wrong here?

VB Code:

Dim sql As String
adcPayroll.CommandType = adCmdText
sql = "SELECT * FROM Employees_pay"
adcPayroll.RecordSource = sql
adcPayroll.Refresh
adcPayroll.AddNew
    adcPayroll!FirstName = txtFirstName.Text
    adcPayroll!LastName = txtLastName.Text
    adcPayroll!DepartmentName = cmbDepartment.Text
    adcPayroll!Shift = lstShift.Text
    adcPayroll!SocialSecurityNumber = mskID.Text
    adcPayroll!Salary = txtSalary.Text
    adcPayroll!PayrollEndingDate = mskDate.Text
    adcPayroll!Dependents = lstDependents.Text
    adcPayroll!HoursWorked = txtHours.Text
    adcPayroll!Shift = lstShift.Text
    adcPayroll.Update
End Sub

May 12th, 2003, 04:31 PM
AmmerBow

Code:

Dim Db As ADODB.Connection Set Db = New ADODB.Connection With Db .Provider = "Microsoft.Jet.OLEDB.4.0" .Open "c:\accessdatabase.mdb" End With Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open "Select * From [Employees_pay]", Db, adOpenKeyset, adLockOptimistic adcPayroll.AddNew adcPayroll!FirstName = txtFirstName.Text adcPayroll!LastName = txtLastName.Text adcPayroll!DepartmentName = cmbDepartment.Text adcPayroll!Shift = lstShift.Text adcPayroll!SocialSecurityNumber = mskID.Text adcPayroll!Salary = txtSalary.Text adcPayroll!PayrollEndingDate = mskDate.Text adcPayroll!Dependents = lstDependents.Text adcPayroll!HoursWorked = txtHours.Text adcPayroll!Shift = lstShift.Text adcPayroll.Update End Sub
May 12th, 2003, 04:32 PM
AmmerBow

If this code is inside Access and not VB then use

Code:

Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open "Select * From [Employees_pay]", CurrentProject.Connection, adOpenKeyset, adLockOptimistic adcPayroll.AddNew adcPayroll!FirstName = txtFirstName.Text adcPayroll!LastName = txtLastName.Text adcPayroll!DepartmentName = cmbDepartment.Text adcPayroll!Shift = lstShift.Text adcPayroll!SocialSecurityNumber = mskID.Text adcPayroll!Salary = txtSalary.Text adcPayroll!PayrollEndingDate = mskDate.Text adcPayroll!Dependents = lstDependents.Text adcPayroll!HoursWorked = txtHours.Text adcPayroll!Shift = lstShift.Text adcPayroll.Update End Sub
May 12th, 2003, 04:33 PM
McGenius

You'd need to declare or set your object variable as new adodb.recordset first and then try to open.

assuming your connection (adoCNN) is already open then:
Set adcPayroll = New ADODB.recordset
adcPayroll.Open strSQL, adoCNN
May 12th, 2003, 05:25 PM
Rick LV

I am still getting the same error for some reason. :confused:
May 12th, 2003, 05:31 PM
Rick LV

Basically when I type adcPayroll. and all the options come up in the drop down box, "AddNew" isn't there. Is there some sort of property I need to change?

http://www.vbforums.com/images/ieimages/2003/05/1.jpg
May 12th, 2003, 09:08 PM
Armbruster
is adcPayroll an ado control? if yes, it should be . . .
VB Code:
```
adcPayroll.Recordset.AddNew
```
That should get you going, but to save headaches in the future, learn ADO. Because if I've said it once, I've said it a thousand times . . . DATA-BOUND CONTROLS SUCK!
May 13th, 2003, 03:24 AM
Rick LV
Quote:
Originally posted by Armbruster
is adcPayroll an ado control? if yes, it should be . . .

VB Code:

adcPayroll.Recordset.AddNew

That should get you going, but to save headaches in the future, learn ADO. Because if I've said it once, I've said it a thousand times . . . DATA-BOUND CONTROLS SUCK!
Thanks.