Help With recordset

[sxi12345]

Hi

I am trying to add values from a form to a table using recordsets.

I keep getting this error object variable or with block vairable not set.

Heres my code...

Private Sub cmdsave_Click()

Dim rststudent As DAO.Recordset
Set rststudent = dbase.OpenRecordset("Student")


rststudent.AddNew
'copy data from text boxes into the member table

rstAddMember("MemberNo") = newNumber("M")
rststudent("FirstName") = txtName.Value
rsttblstudent("Title") = cboTitle.Value
rsttblstudent("DateOfBirth") = cboDateOfBirth.Value
rsttblstudent("Address") = txtAddress.Value
rsttblstudent("City") = txtCity.Value
rsttblstudent("PostCode") = txtPostCode.Value

'update the table(s) the record set refers to in the database
rststudent.Update
rsttblstudent.Bookmark = rsttblstudent.LastModified
End Sub

 

[Rabbie]

You need to declare dbase so you can open the recordset.

Try

Private Sub cmdsave_Click()
Dim dbase as database
Dim rststudent As DAO.Recordset
Set dbase = Currentdb
Set rststudent = dbase.OpenRecordset("Student")


rststudent.AddNew
'copy data from text boxes into the member table

rstAddMember("MemberNo") = newNumber("M")
rststudent("FirstName") = txtName.Value
rsttblstudent("Title") = cboTitle.Value
rsttblstudent("DateOfBirth") = cboDateOfBirth.Value
rsttblstudent("Address") = txtAddress.Value
rsttblstudent("City") = txtCity.Value
rsttblstudent("PostCode") = txtPostCode.Value

'update the table(s) the record set refers to in the database
rststudent.Update
rsttblstudent.Bookmark = rsttblstudent.LastModified
End Sub

Good Luck

 

[sxi12345]

I have tried that but it keeps coming up with the error

 

[rapsr59]

DAO – Data Access Object(s)

What are Data Access Object(s)?

Data Access Objects are objects that allow access to data stored in a database (In this case Microsoft Access)

Two objects used for accessing data in MS Access are
1. Database
2. Recordset.

Before data can be read from or written to a table, two variables must be set. One that references the subject database and the second that references the table that contains the data.
Before the recordset can be found by Access, Access must know which database the recordset (table) is located in. So the first thing to do is set the Database object. I like to use the prefix “DAO” so I know I am dealing the Data Access Object(s).
Dim dbs as DAO.Database

‘Currentdb is the database that I am working in and contains the tables and data I need.
Set dbs = Currentdb

So far I have referenced the database object, I need to set a variable and reference the table that contains the data I need. Like to use rst as my prefix for a recordset and I like to Capitalize the name of my recordset.

Dim rstStudent as DAO.Recordset
Set rstStudent = dbs.openrecordset("Student")

I have now set the required DAO variables (database and recordset) for accessing and manipulating the data in the table “Student”.

I now instruct Access that I am going to add a new record to my table “Student”

rstStudent.AddNew

In your code, you are referencing three(3) recordsets, two that are not set, and one(1) that HAS been properly set.

Your code
rstAddMember("MemberNo") = newNumber("M")
Note that rstAddMember does not have a variable set to this recordset object.

Your code

rsttblstudent("Title") = cboTitle.Value
rsttblstudent("DateOfBirth") = cboDateOfBirth.Value
rsttblstudent("Address") = txtAddress.Value
rsttblstudent("City") = txtCity.Value
rsttblstudent("PostCode") = txtPostCode.Value


Note that rsttblstudent does not have a variable set to this recordset object.

If all the data you are trying to add belongs to the table “Student” then you must reference ONLY the rstStudent recordset.

Your code should read:

rstStudent("MemberNo") = newMember("M")
rstStudent("FirstName") = txtName.Value ‘This line is ok
rstStudent("Title") = cboTitle.Value
rstStudent("DateOfBirth") = cboTitle.Value
rstStudent("Address") = txtAddress.Value
rstStudent("City") = txtCity.Value
rstStudent("PostCode") = txtPostCode.Value

(I like to use this syntax. I seems to work a little more quickly.
rstStudent![PostCode] = txtPostCode.Value)

I then instruct Access to update the table (recordset) adding the new record to the Student table

rstStudent.Update

I like to close both the database and recordset objects to make sure that the memory that was allocated by Access is freed up.

Set rstStudent = Nothing
Set DBS = Nothing


Alternate

rstStudent.Close
dbs.Close

I hope this helps you with your dilemma.

Richard