writing data to a table using VBA

[Don Starnes]

This must be a basic question: how do I write data to a new record in a table
using VBA?

I have 3 tables (Table 1, Table 2, Table 3) and a form which shows fields
from tables 1 and 2.
I have a list box on the form which lists records from Table 1.
Fields from a record on Table 2 are displayed on my form.
I have a Table 3 with two fields.

I want to use a button to write the following to Table 3:

- the index number for the Table 1 record selected in the list box
- the index number for the current Table 2 record being viewed on the form

these would be written to a new record in the two fields of Table 3.

I can't figure out the basic code for writing data to a table. Can you give
an example?

Thanks!

Don

 

[dimwit]

try using an append query and in you form on_click you type docmd.openquery
your append query

 

[Marshall Barton]

This must be a basic question: how do I write data to a new record in a table
using VBA?

I have 3 tables (Table 1, Table 2, Table 3) and a form which shows fields
from tables 1 and 2.
I have a list box on the form which lists records from Table 1.
Fields from a record on Table 2 are displayed on my form.
I have a Table 3 with two fields.

I want to use a button to write the following to Table 3:

- the index number for the Table 1 record selected in the list box
- the index number for the current Table 2 record being viewed on the form

these would be written to a new record in the two fields of Table 3.

I can't figure out the basic code for writing data to a table. Can you give
an example?

You can execute an Insert Into query to add a new record.
Here's some air code to get you started:

Dim db As Database
Dim strSQL As String

strSQL = "INSERT INTO Table3 (tb1ndx, tb2ndx) " _
& "VALUES(" & tb1ndxlistbox & "," & tb2ndxtextbox & ")"
Set db = CurrentDb()
db.Execute strSQL, dbFailOnError
Set db = Nothing

 

[Dirk Goldgar]

Or use the following syntax: (yeah i know it is DAO)

Dim db as databas
dim rst as recordset

set db= currentdb()
set rst = db.openrecordset("Select * from YourTable",dbopendynaset)

rst.addnew
rst!FirstField = yourvalue
rst!SecondField = Yourothervalue
rst.update

rst = nothing
db = nothing

Or try looking in Access Help for how to write it in ADO that is far
better.

What makes you think ADO would be better? DAO is designed and optimized
for working with Jet databases (.mdb files). I can't think of any way
in which ADO is better than DAO for this purpose. Connecting with SQL
server or other data sources is another matter.

 

[Don Starnes]

Thanks!

This idea worked, with some alteration:

' variables
Dim mydb As Database
Dim rst As DAO.Recordset
v_group = "a value"
v_person = "another value"

' open the table
Set mydb = CurrentDb()
Set rst = mydb.OpenRecordset("table name")

' add new record to the table,
' set the fields' values and
' update the table
rst.AddNew
rst![field name] = v_group
rst![another field name] = v_person
rst.Update

' reset variables
Set rst = Nothing
Set mydb = Nothing

I couldn't figure out how to write it in ADO, but this works.

Thanks very much for your help!

Don