Creating a function to add records to tables (1 Viewer)

[ca9mbu]

What I have at the moment is a command button that has code in the On_Click procedure that adds a new record to a given table and populates it with values from text boxes on the form.

Because I am adding records to different tables through different forms then the same code structure is repeated many times...a classic case to use a function!

I thought the easiest way to do this was create an array into which you pass the form name, table name, textbox names (on the form) and field names (in the table)

The problem I have is, different tables have different numbers of fields, and therefore the forms have different numbers of text boxes. This means I can't declare an array with a fixed number of arguments.

How do I go about declaring an array with varying numbers of arguments (presumably defined at run-time?), or is there a different approach I should be trying?

Thanx greatly, in advance

Matt

 

[Pat Hartman]

Although the general logic for these procedures is the same, the specific instructions are different. Each form has a unique set of controls that need to update a unique set of table fields. Putting a bunch of controls in an array won't do you any good unless you also put the cooresponding tables fields in an array in the same order. Then you could write a common code routine that takes controlarray(index) and updates tablearray(index). In theory you could use the fields collections of the form and table objects except that you can't easily control the sequence of the stored fields so again, matching by indexing is out of the question. You would need to match on name. That means you would loop through the controls in the detail section, bypassing certain control types (labels for example) and for each control you would have to loop through all the table fields looking for a match.

I would not want to incur this type of overhead. It serves no purpose and just makes the update process harder to follow because there will be no code reference that says Forms!YourForm!YourControl updates recordset.YourField.

 

[GMC]

I've done something similar in the past but have used a DATA_SOURCE table. In this table you store unique names for each of the database tables, the number of fields in each, the name/position of their keyfield, etc, etc.

The form that I have to capture the data has controls which are named [c_one], [c_two], etc. When I click to add the record I call a function, passing the unique tablename as a parameter. This function then creates a temporary recordset for the table, using the myrec(index) method to post values into fields on a record.

Make sense??

I know this is a rambling description...you can email me on grantmcarr@hotmail.com if you would like a better description.

Hope this helps
Grant