Trying To Append Data With Code

[matethreat]

Hello,

I have a form with 3 unbound text boxes. I want to be able to append the data to a table when I exit the form. I get the following error each time.

User-Defined Type Not Defined
(This error is on the first line of my code)

Dim cnn As DAO.Database
Dim rs As DAO.Recordset

Set cnn = CurrentDb()
Set rs = cnn.OpenRecordSet("Material")

With rs
.AddNew
![Description] = Me.Description2
![ItemNumber] = Me.ItemNumber
![Value] = Me.Value
.Append
End With

rs.Close
cnn.Close

This isn't working????
I am not sure what I am doing wrong

 

[KenReay]

Hi
You need a reference to the DAO library.

Open and code module in design view, choose tools \ references, look down the list and seelct DAO library.

Presumably you are using a version of Access after Access97, upto and including Access97 DAO was the default data access library, from Access2000 on wards ADO has been the default library, and you must explicitly add DAO if you wish to use DAO objects.


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scriverb]

Also change the .Append to .Update

The .AddNew indicates an append new record to the recordset. The appropriate Save action is the .Update command.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[matethreat]

Ok, I have re-written the code for access2000 and this is what it looks like:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = CurrentProject.Connection
strsql = "SELECT * FROM Material"

With rs
Set .ActiveConnection = cnn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strsql

![JobNumber] = JobNumber
.Update
End With

rs.Close
cnn.Close

My error now is "Object Variable or With Block Variable Not Set

 

[KenReay]

Hi

Well spotted Bob, missed that still clearing the sleep from my head

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scriverb]

You have not set your recordset. Use the following code to do that:

Set cnn = CurrentProject.Connection
[red]Set rs = New ADODB.Recordset[/red]

Now this is setup no to update the first record in your recordset only. But, that will not be the record on your form. You must modify your SQL string to add a criteria selection of the current record on your form.

Something like the following:

strsql = "SELECT * FROM Material WHERE [ID] = FORMS![[red][i]yourformname[/i][/red]]![[i][red]IDcontrolname[/red][/i];"

Let me know if this helps

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[matethreat]

Hey Bob,

Thanks for that, I don't know how I missed setting my recordset, the end of the day and I must have been tired. I didn't actually want to modify and records, so I added .AddNew to the code.

Aaron