By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Saving from one form to multiple tables

P: n/a
Hello

I am trying to save information from one form to two tables. I have
a table for Student info and Transcript line. I have a form that list
all the classes (using text boxes) a student is supposed to take to
graduate. This form also has a combo box for assigning grades for each
class. I am trying to save information from the form to the student
info table and to save each class on the form as separate records in
the transcript line Table. Could anyone please point me in the right
direction or give me any ideas on how to go about this. Thanks.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You can create code to dimention 2 recordsets (one for each table). First I
would create 2 querys, 1 based on each table. I have found that
manipulating query's via recordsets seems to work better than manipulating
tables directly.

Look up Recordset and RecordsetClone in the help. This will definitly solve
your problem.

Just to start you off...look for something like this:

dim dbs as database
dim rst1 as recordset ' query 1
dim rst2 as recordset ' query 2

set dbs = currentdb()
set rst1 = dbs.openrecordset("query1", dbopendynaset)
set rst2 = dbs.openrecordset("query2", dbopendynaset)

at this point you can use a With statement and Wend if you want, but if its
only 1 record being written you wont need to.
use .addnew to add a new record to the query/table or use .edit to edit an
existing record.

rst1!TargetFieldname = me!Sourcefieldname
rst1.addnew
etc.
etc.
do this for as many fields as necessary.
rst1.update

rst2!TargetFieldname = me!Sourcefieldname
rst2.addnew
etc.
etc.
do this for as many fields as necessary.
rst2.update

dbs.close
set dbs = nothing
set rst1 = nothing
set rst2 = nothing
HTH
Paul

"Tolu" <ro**************@yahoo.com> wrote in message
news:91**************************@posting.google.c om...
Hello

I am trying to save information from one form to two tables. I have
a table for Student info and Transcript line. I have a form that list
all the classes (using text boxes) a student is supposed to take to
graduate. This form also has a combo box for assigning grades for each
class. I am trying to save information from the form to the student
info table and to save each class on the form as separate records in
the transcript line Table. Could anyone please point me in the right
direction or give me any ideas on how to go about this. Thanks.

Nov 13 '05 #2

P: n/a
ro**************@yahoo.com (Tolu) wrote in message news:<91**************************@posting.google. com>...
Hello

I am trying to save information from one form to two tables. I have
a table for Student info and Transcript line. I have a form that list
all the classes (using text boxes) a student is supposed to take to
graduate. This form also has a combo box for assigning grades for each
class. I am trying to save information from the form to the student
info table and to save each class on the form as separate records in
the transcript line Table. Could anyone please point me in the right
direction or give me any ideas on how to go about this. Thanks.


Looks like a query to return a set of classes the student is supposed
to take (Cartesian product with filters). Put this stuff into a
multi-select listbox, put a button on your form to run an append query
to some ClassRoster table, then show the results for the current
student at the bottom of the form in a grid and have a combobox for
entering grades or something like that. And you're done.

There's code at Accessweb to do the hard stuff here. (playing with
multi-select listboxes, etc).
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.