Please help: forms on multi tables

[ArthurN]

Hi,
Sorry, if you find my request kinda stupid, but I'm really stuck, I'm
reading books and help, but I can't find an answer. What I'm trying to do is
to bring 3 tables into one form and to be able to edit them. On of the tables
is a lookup table. Something like this:
tblUsers
userID ! username
-------!----------
1 ! Arthur

tblGroups
groupID ! groupname
--------!----------
1 ! marketing

tblUsersAndGroups -> lookup table
userID ! groupID
--------!---------
1 ! 1

frmUsersAndGroups -> datasheet view
userID ! username ! groupname
-------!----------!----------
1 ! Arthur ! marketing ^ -> this field would have to be a combo box
or drop-down list

How do I achieve this? Do I have to write some ADO RecordSource code behind
the form?
Please help, I'm desperate
Thanks in advance
ArthurN

 

[j_p_s]

Sounds to me like you need to start with a query, and then set the
query as the record source for the form.

Here is a quick SQL statement that you can drop into a query to see
what i mean:

===========================

Select tblUsers.UserID, tblUsers.UserName, tblGroups.GroupName From
tblUsers Inner Join tblUsersAndGroups on tblUsers.userID =
tblUsersAndGroups.UserID Inner Join tblGroups on
tblUsersAndGroups.GroupID = tblGroups.GroupID

===========================

Open a query, then set the query to SQL view, then paste the code above
into the SQL view screen. Then change the query to design view and you
should see your tables and how they are linked.

You may have to edit the above code a litte, because I just wrote it
based on my interpretation of your example in your question.

Good luck

 

[ArthurN]

Thank you for you reply,
Yes, I can set a multi table query, but I can't edit it, can I? At least,
that's what I read in Alison Balter's Mastering Microsoft Office Access 2003
and I couldn't get it to work any way. Or am I wrong here? But, what I would
like is to be able to edit it with a combo box, I was able to do that with a
2 table design, but never with a lookup table to join them
Thank you again,
ArthurN

 

[J. Goddard]

Hi -
Actually, you have it the wrong way around. The table tblUsersandGroups
is not a lookup table - the other two are. The fields UserID and
GroupID in tblUsersandGroups are foreign keys to tblUsers and tblGroups.

You could accomplish what you want to do with three sub-forms, one each
for tblUsers, tblGroups and tblUsersandGroups. The sub-form for
tblUsersandGroups (with tblUsersandGroups as its record source) could
have the combo boxes based on the other two tables.

I strongly suggest you set up relationships which enforce referential
integrity between the three tables.

Hope this helps

John

 

[ArthurN]

Hi,
Thankx again. I see that I can bring all three tables through three
sub-forms, but this isn't not exactly what I want to achive, I'm sorry for
not making myself clear- I still have a long way to go in Access development.
What I want is this:
userID ! username ! usergroup
------------------------------
1 ! Arthur ! marketing
2 ! John ! ^ this field should be a combo box that lets me
set/change the group for the user.

This form should list all the users and should give me a chance to
set/change the usergoup though a combo box or a drop-down list. It's based on
three tables: users, usergroup (which just defines the groups),
usersAndgroups (which has only userID and usergroupID)
Thank you in advance,
ArthurN

 

[J. Goddard]

Hi -

You're almost there. In the form frmUsersandGroups, you only need a
combo box for the UserGroup table. The combo box:
1) is based on the group table,
2) has the limit to list propty="Yes"
3) displays the group name (e.g. Marketing...),
4) contains a hidden column (Width=0.0) for groupID
5) is bound to the GroupID field in the UsersandGroups table

To see the user name instead of the userID:
1) create a query linking tblUsersandGroups to tblUsers (on UserID)
2) Include the User Name in the query
3) set the record source for frmUsersandGroups to this query
4) display the User Name from this query in frmUsersandGroups
5) if you don't want to be able to change the *user* ID in this form,
set the "Locked" property of the control to "Yes"

For this to work, the UserId must be the primary key of the User table.

Hope this helps

John

 

[ArthurN]

John,
Can't tell you how much I appreciate your help. Have been trying to work
this out for a week now finding no answers in books like Alison Balter's
Mastering Microsoft Office Access 2003.
Hope this approach will work for those forms that'll be based on more than 3
forms.
Thanks again,
ArthurN