Access VBA database connection

[Poweruser99]

I am very new to VBA for Access.

This is my problem

I have made my own form from scratch to write to a table. I want to know what kind of code I can use to make the various textboxes and combo boxes to write to the table.

I want this done with the click of a button. Plus the wizards are very limited, thats the reason I had to make my own form.

 

[Poweruser99]

What I need help is with the statements as follows.

dim rs as recordset

then I need some way of referenceing a table to that I can make my object forms write to it with the add button I have.

 

[PHV]

You can try a keyword search in this forum for access dao recordset

Hope This Help
PH.

 

[TonyJollans]

Hi Poweruser99,

I think you are making things more complicated than you need. If you create an autoform using the wizards, it will be bound to your table and when you enter new records they will be automatically written to your table. No need for lots of code.

If the wizard doesn't give you EXACTLY what you want you can change it in Design View, but at least all the mechanics tying the Form to the Table will be in place.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[zerocul]

Poweruser99,

I agree with Tony that you might be making things more complicated than required. However, if you want to accomplish a connection to your table and recordset through code, here is what you need to do:

Dim dbs as dao.database
Dim rst as dao.recordset

set dbs=currentdb()
set rst=dbs.openrecordset("your table name here",dbopendynaset)

From here you will need to assign the values from your textboxes and comboboxes to your table fields as follows:

with rst
.addnew
!field1=me.textbox1.value
!field2=me.textbox2.value
.
.
!field=me.textbox.value
.update
.close (closes recordset)
set dbs=nothing (releases dbs object from memory)
end with

If you chose to close the recordset and set the dbs object to nothing as shown above, you must reconnect to the database and recordset the next time you want to add a record using the set dbs = and set rst = as shown above.

However, keep in mind that the proper way to perform this task is to search the table first to insure a duplicate value does not exist (utilizing the .NoMatch property). Only then should you add a new record unless your table allows for duplicate records.