Adding a new record

[Marchello2004]

Hi everyone! Anyone able to add records to the database using VBA?

I started using 'DoCmd.GoToRecord , , acNewRec
to generate the new ID for a new record, but this doesnt seem to add the ID number directly into the database, instead I had to create a textfield in which to write this ID, and then the data appears in the table, WIERD! anyway, instead I'm trying to use this VBA code, but the keeps coming up with a messange saying ODBC - call failed!

Private Sub Submit_form_Click()

On Error GoTo Err_Submit_form_Click

Dim ID As ADODB.Recordset
Set ID = New ADODB.Recordset

ID.Open "Bookings", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If ID.Supports(adAddNew) Then
With ID
.AddNew
.Fields("staff_name").Value = Name
.Update
End With
End If

ID.close
Set ID = Nothing

DoCmb.close

Exit_Submit_form_Click:
Exit Sub

Err_Submit_form_Click:
MsgBox Err.description
Resume Exit_Submit_form_Click
End Sub

It seems to me that there is something missing in the connection to the database, but I can't figure out what it is...

Anyone used this before? Any ideas? I'm not using Access Project, is just plain access...

Thank you!!

 

[Remou]

Why not DAO or an append query?

 

[Marchello2004]

Well I'm not using an appended query since all the tables already exist and I'm not using DAO since I'm not really that good with access. That's why I'm in a forum!

All I need is to get the data into a table. Can't be that hard but the code I've written doesn't seem to work, and I can't see why it doesn't.

Let me explain a bit further... There are three tables involved here.

One is the Staff table from which two combo boxes pick up names and staff IDs.
Second table is the resources table. I have another two combo boxes which pick up the resource title and resource ID.
The third table is the bookings table in which I would like to add the data picked up by all these combo boxes.

As I can't figure out of the auto generate and ID for the bookings table, the closest I got to get it working is when I create an ID text field linked to the bookings table and I type in an ID manually, lets say 1568, then the data appears on the table, if I don’t type this number is it wont do it. I guess my question is if there is a way to generate this number automatically as I open the form? It seems like acNewRec goes to the last record but it won’t add the new ID to the table, therefore I have to do it manually!! So frustrating...

Any help is much appreciated

Kind regards

MarcelQD

 

[Remou]

How about:

Private Sub Submit_form_Click()

On Error GoTo Err_Submit_form_Click

Dim strSQL As String

strSQL= "Insert Into Bookings (staff_name) Values ('" _
& Me.Name & "'"
CurrentDb.Execute strSQL
    
Exit_Submit_form_Click:
    Exit Sub
    
Err_Submit_form_Click:
    MsgBox Err.description
    Resume Exit_Submit_form_Click
End Sub

'Name' is a very poor name for a field or control and will lead to many problems. It is probably the most reserved of reserved words.

An Append query (Insert Into) adds records, it does not create tables.

DAO is generally preferred for Access, unless you intend to use it as a front end for, say, MS SQL. You will find that the majority of examples in Access help use DAO, as do a great many of the examples in these fora.

Good luck.

 

[Marchello2004]

Hey, I just wanted to say thanks for your help. In the end I managed to get your code running. The main problem is that the ID Column in the table wasn't set to AutoNumber, and without this ID nothing would be allowed to be stored in any of the other columns. That's why it did work when I typed in the ID manually. I attached your code adding some other fields to a submit command and it all works beautifully.

Again, thank you very much.