Adding data from Form to Table using VBA

[Barbados]

Hi,

I have a form (called Form2) with say 3 text boxes, called txt_Field1, txt_Field2 and txt_Field3.

In txt_Field1 the user specifies the number of records to be added to an existing table. The fields txt_Field2 and txt_Field3 are used to pre-populate the records with default values.

Furthermore, I have set up a table called Table1. It's headers are ID, Strategy, divRate. ID is Autonumber, Strategy is Text, and divRate is Number.

Suppose the user inputs the following on Form2:
txt_Field1 = 3
txt_Field2 = Covered
txt_Field3 = 0.04

How do I programmatically add 3 records (since txt_Field1 = 3) to Table1 such that the table will look like:

ID, Strategy, divRate
1, Covered, 0.04
2, Covered, 0.04
3, Covered, 0.04

Thanks.

 

[pbaldy]

The tools you would use are the AddNew method of a recordset and a For/Next loop using Field1 for the the upper limit.

 

[mdlueck]

I would do the OP's proposed scenario in a couple of steps:

1) Use this share code to safely read the form controls into VBA variables:

Safely read form field text controls
http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

Once read, then it is possible to perform data validation, then...

2) Use ADO objects to perform SQL:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730 #post1214730

The overall way I use Access:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[Barbados]

I would do the OP's proposed scenario in a couple of steps:

1) Use this share code to safely read the form controls into VBA variables:

Safely read form field text controls
http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

Once read, then it is possible to perform data validation, then...

2) Use ADO objects to perform SQL:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730 #post1214730

The overall way I use Access:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

I tried your suggestion but I have never done any ADODB stuff. So not sure why thing are going wrong. Do you mind spending some minutes to see where I am doing things wrong. The code is found under formStrategy (function cmd_formStrategy_OK_Click). The table where things should go into is tblStrategy.

Thanks.

 

[mdlueck]

Looks like you did not heed the blue note I added to the bottom of the post you were referencing that linked to an additional VBA Module needed for Late-Binding of the ADO objects.

Quickly added the reference, the .CommandType LOC no longer blows up. Looks like you have an excellent start!

Oh, and you specify a p13, but never create/provide p13.

 

[Barbados]

Looks like you did not heed the blue note I added to the bottom of the post you were referencing that linked to an additional VBA Module needed for Late-Binding of the ADO objects.

Quickly added the reference, the .CommandType LOC no longer blows up. Looks like you have an excellent start!

Oh, and you specify a p13, but never create/provide p13.

Yes, I wasn't sure if the field ID (primary key) had to be included or not. Great help by the way. Since it is the first time I am using ADO, I would have never thought of adding the reference.

Thanks.

 

[mdlueck]

I have found it helpful to convert ADO VBA code over to Late-Binding style coding. Such is more portable between various ADO versions which may be installed on target computers. The module I coded up was based on ADO 2.8 required constants.

For INSERT, you do not supply the key if it is an autonumber field. You do supply it for SELECT / UPDATE statements, however.