Access 2010 --- table1: (t_Students) with ID, Fname, Lname --- table
2: (t_Classes) with ClassNumber, ClassName --- table 3:
(t_StudentClasses) with ID, ClassNumber

I would like to select "t_Student ID" from table 1 and "t_Classes
ClassNumber" from table 2 and enter these pieces of information as a
new record in the third table "t_StudentClasses" without erasing any
data already in the third table.

Being unfamiliar with Access 2010 I would appreciate any help I can
get on this problem

Do you want to accomplish this manually, with a query, or with macros / VBA
code?

I'm oriented to being a database application developer, rather than an end
user, so If I were doing this on a regular basis, I'd create a Form with
RecordSource of t_StudentClasses, and set in the Data Properties to
"DataEntry". On this form I'd create two Combo Boxes:

cboStudent, bound to Field ID, with row source of ID, Fname, and Lname from
t_Students

cboClass, bound to Field ClassNumber, with row source of ClassNumber,
ClassName

I'd add VBA code in the BeforeUpdate event of the Form to ensure that a
value had been selected in each of the ComboBoxes, and Cancel the Update if
either is Null. Also I'd add code to check if the identical data has already
been stored in t_StudentClasses, and if it has, Cancel the Update. (You can
"back up" your validity checking by setting the t_StudentClasses Field
Validation on both Fields to Not Null, and back up the validation of
non-duplicated Records by combining the two Fields into one Primary Key
(which does not allow duplicates). But the error messages may not be all
that intuitive if you do that without "intercepting" the error in your own
code.

If all goes well, then either moving to another record, or moving off the
form will cause it to add a new record with the selected values to your
table.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Deac" <mwallen (AT) centurylink (DOT) net> wrote

"Deac" wrote in message
news:9474de81-1efa-42cb-9b77-73d7e2a1646c (AT) l37g2000yqd (DOT) googlegroups.com...

You have some great advice here that suggests using code, and that's usually
my first choice also.

However it is summertime, and I often use my brains to do less work, or as
little work as possible!

Also, since you new to access, I'm going to vote and and suggest a
completely code free approach.

With the wizards or yourself, build a main form based on students. Now also
build a continues form based on student classes (a multiple items form). For
this continuous form, make the class number a combo box (again allow the
wizard to build this combo box for you and the source of this combo box will
be classes, and it will store set your class number .

Now bring up your original students form in design mode, and drag and drop
in this new continuous form you built onto this form. As this point you'll
have a child form or what we call a sub form in access.

If you set up your relationships correctly, when you do this drag and drop,
access will set up the link of master to child relationship.

Now it's a simple matter to browse to or navigate to a particular student in
question, and then using the sub form, you can use the combo box to select a
particular class you want, and the whole thing will function without you
having written anything. Access will insert and set the student ID into this
table, and your choice in the combo box will set the class number.

So, this can be built via drag and drop and just some mouse clicks - no code
needed.

On this note? - time for a coffee!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote

Great suggestion, Albert! (Now why didn't I think of that?)

Actually, I think I'll take an _iced_ coffee, if nobody objects. Predicted
high temp in my area (Dallas - Ft Worth) was 110F and somewhere between
115 - 120F "heat index".

Larry