Ok, if the tables a like i described then we wont mess with them
I set the tables up as I stated in my previous answer and tried to get
everthing working in a test database. In my DB I have the 3 tables tblDept,
tblCategory and tblEmployee. I then set the relationship between tblDept and
tblCategory. Next, I created an input form for the department and category
tables. This is a form with a sub form on it (the subform is linked to the
main form, just like the tables). What you should have in the 2 tables is
information something like:
----------------
tblDeptID
lngDeptID strDeptName
1 Accounts
2 Sales
3 IT
----------------
tblCategoryID
lngCategoryID lngDeptID strCategoryName
1 1 Admin
2 1 Manager
3 2 Buyer
4 2 Admin Assistant
5 2 Cheif Buyer
6 3 Junior Assistant
7 3 Manager
----------------
I then created the employee form from the employee table and changed the
textbox's lngDeptID & lngCategoryID to be combobox's. I also changed the
names of the controls on the form to aviod any confusion of what
field/control is to be used (this might be the problem of the propting you
get). The controls on this form are named as follows
Name=txtEmployeeID, ControlSource=lngEmployeeID (in the employee table)
Name=cboDeptID, ControlSource=lngDeptID (in the employee table)
Name=cboCategoryID, ControlSource=lngCategoryID (in the employee table)
The RowSources for the combos are as follows
cboDeptID
SELECT tblDept.lngDeptID, tblDept.strDeptName FROM tblDept;
cboCategoryID
SELECT tblCategory.lngCategoryID, tblCategory.lngDeptID,
tblCategory.strCategoryName FROM tblCategory WHERE
(((tblCategory.lngDeptID)=[cboDeptID]));
columns and column widths for these are the same as in my previous email.
The prompt box problem is because it can't find the control on the form.
Hopefully modifying the control names and updating the WHERE clause of the
category combo will solve this problem (I can never remember the correct
syntax needed to get this to work properly off the top of my head
)
The other problem is because something is not quiet right in either the
column count or in the underlying table. For e.g. If the select statement
for the category combo was as follows
SELECT tblCategory.lngDeptID, tblCategory.lngCategoryID, e.t.c. (Note
that DeptID is now the first column)
The combo uses the first column as the bound column by default (make sure
the bound column is set to 1 on both comboboxs) and in this case it will get
confused as to which item to select. There are 2 records which have 1 in the
dept ID, how does it know which to select? It doesn't, which is why it will
always select the first.
If after all this, it still isn't working, I could send you the replica one
that I created (and sods law it works fine for me).
The last tip (and slightly off topic) that I am going to give you is when
you are just about to post a new question to this group, "How do I add
another item to my combobox's drop down menu when a user enter's something
that isn't already in the list", look in the help files for the NotInList
event and the LimitToList property. There will probably be lots of posts
already in this newsgroup on how to acheive this as well. It is always the
next question ;-)
HTH,
Neil.
Cathi said:
Status: table structure was always set up correctly. I even tried
exactly what you you suggested (Greatly appreciate your time and help --- at
least I know I wasn't totally off my rocker and was doing exactly what
should" work said:
First, I get a prompt box on the forms.mainform.subform.cbodept Now, if I
enter in the dept ID that should sync the category combo box I get the right
results list in the categories ---- but why am I getting the prompt box? I
have double, triple checked all the spelling, names, etc. don't have a clue
why.
The other MAJOR problem is that once I select something from the
categories table (and it doesn't matter what I select) it will only display
the first item in the list; so if I choose the 4th item in the categories
list - the first line is all that ever appears.
