AddNew and variables

[GregC]

I am trying to make what is essentially a template for a series of forms. Each form will basically do the same thing but work with a different table. My intention is to have a series of variables to hold the name of the table and fields so that I can easily make each form fit it's specific needs.

Ex:
strTblName - holds the name of the table
strTblField - holds the name of the field

I've gotten everything to work except for an AddNew statement. I cannot come up with the proper code for it.

strAdd = "rstNewData!" & strTblField & " = " & strData

'MsgBox (strAdd)

Set rstNewData = New ADODB.Recordset

rstNewData.ActiveConnection = CurrentProject.Connection
rstNewData.CursorType = adOpenKeyset
rstNewData.LockType = adLockOptimistic
rstNewData.Open "Select * from " & strTblName

rstNewData.AddNew
?????????????????
rstNewData.Update

rstNewData.Close

I've tried a variety of methods but can't seem to find one that works. Any help would be appreciated.

 

[Pat Hartman]

I guess I'm not understanding the purpose of this. You can't make objects in a shared database so you wouldn't be able to use this techinque in a multi-user environment. As to making is for yourself, having a sufficent number of identical tables to even consider such an aide is sending warning bells ringing in my head. Can you tell us more about why you have so many tables.

BTW when using Jet tables you should be using DAO.

 

[GregC]

Thanks for the reply Pat. The term, many tables, may be misleading. I have to this point 3 relatively small tables that are similarly composed. 2 fields - one autonumber for a primary field and the 2nd for the specific data.

The 3 tables will be used with others for a medical QA system. The tables are:
Chief Complaint
EKG Rhythm
Electrical Therapy

The tables will be relatively small and static once finally built, but may have some additions over time. I am trying to develop a way so that whoever takes over the system from me (sometime in the future) will be able to place new data in the tables as needed.

With regards to the sharing issue, my intention is to build this so that the Admin person can update the table on the back end of the split database. When each user launches the application, it updates a similar table on the fe. From that point on the application only uses the fe table for data. My thoughts were that this would limit the traffic on the network to 1 transfer of data per table per application use.

I could build each form to address each table individually. But being a self taught user, I try to learn and teach myself new things each time I work with Access. I felt this, extensive variable use, would be something that would prove beneficial to me in the future.

Appears that I need to learn a bit more about Jet vs DAO vs ADO. I thought that I had this one figured out right.

And finally, is there a way to take place somthing like the "strAdd" variable after the rstNewData.AddNew line so that Access accepts it?