Variables for all form/sub form fields

[Tupacmoche]

Hi Form Masters,


I want to convert an existing tabbed form with about six tabs with about 120 data fields into variables that I can save using vba code in a button or simple clear and start over again. The back end is sql server 2008 R2. I want the vba code to be the only way data entry into any control gets into the database.What is the best practice for doing this?

 

[Frothingslosh]

Using a bound form rather than 120 variables.

 

[pbaldy]

Jason has info here:

http://www.baldyweb.com/BoundUnbound.htm

The bound form Frothy suggests is certainly easier. If you want to go unbound, simplest might be an append query that gets its values from the form. Failing that, you can use an ADO command object to execute a stored procedure, or a pass through query to execute SQL.

Edit: or the AddNew method of a recordset.

 

[Deleted member 147267]

If you are bound and determined to use vba and an unbound form with 120 controls, I'd say you have quite a challenge ahead of you when it comes to validation and constructing a sql statement based on that many controls - especially if they're not all going to be required fields. For a limited count, I'd try naming controls with a trailing number so that I could match them to variables ending in those numbers and loop through the controls collection - but not for that many. Have to wonder why the unbound form seems so desirable, assuming that's what using vba means.