trying to add new record to table with code

[BRC]

Hi all
I would like to add a new record to existing table (or query) based on info
on open form. Briefly, I have 2 tables, "Clients" "Jobs". new clients are
either associated with existing job or a new job has to be created. If a new
job must be created I would like to create the record in the "jobs" table
when I save the new client info. (some data is common to both tables). when
i click the save command button on the "new client" form the "jobs" table is
not open. (I have tried several example I have found on inet but I'm getting
alot of "data mismatch" and similar vb errors.) Hope this makes sense. any
help or suggestions would be appreciated
BRC

 

[tina]

my first suggestion is that your review your table structure. if there is a
one-to-many relationship between clients and jobs (one client may have many
jobs, but each job may belong to only one client), then the only common data
between tables Clients and Jobs should be the primary key field of Clients,
which should be used as a foreign key field in Jobs. if, instead, there is a
many-to-many relationship between clients and jobs (one client may have many
jobs, and one job may have many clients), then the two tables should not be
linked at all, and should have no common data. a join table would be used
store the records of what clients are linked to what jobs; again, the only
common data in the join table would be the primary key from each of the
other two tables.

once you're confident that your tables are properly structured and related,
you may find that you don't need extensive VBA code to get the data entry
results you want. in most cases, a standard mainform/subform(s) setup will
get the job done.

hth

 

[Brad Pears]

Generally what I do when I want to add a record to a table that is not open
for the paticular function that you are currently performing is to build an
"insert" SQL statement, and simply run that SQL statement as part of the
"click" event you are using.

You can run an SQL statement a few ways, but if you build your SQL string
like...

dim strSQL as string
strSQL = "insert into tablename (field1, field2, field3) values ("Test", 1,
2)"

and then issue the following command..
"docmd.runsql(strSQL)"

A record will be inserted into "tablename" for you... You will be prompted
that a record is being inserted. If you do not want to be prompted for the
inserts, then use "docmd.setwarnings false" before you actually run the
insert SQL statement. However, be sure to turn setwarnings back on again
after that... "docmd.setwarnings true"

Hope this helps...

Brad