Part 2 Sync Combo Boxes

[pjscott]

I'm using Access 2003 and Sql 2000. I'm trying to sync 2 combo boxes that are
in a sub form. The 2 combo boxes are Branch and Department. I'm using the
code below to sync the 2 combo boxes.

I'm having 2 problems. One, when I open the form the Branch combo box
doesn't display any data even though the data is there. If I select a Branch
name from the combo box the Branch combo box remains populated but that
creates problem two.

Problem two, when you select a name from the Branch combo box it populates
the field with the Branch name but when you check the table the Branch field
displays the BranchId number and not the Branch name.

Me.cboDepartment.RowSource = "SELECT Departments FROM" & _
" tblDepartments WHERE BranchId = " & cboBranch & _
" ORDER BY Departments"

When do I need to do to solve these problems.

Thanks for the help,


Paul

 

[Dale Fye]

Paul,

What is the RowSource for your cboBranches? I would assume that that combo
box would contain two columns (BranchID and BranchName) with BoundColumn = 1,
would have column widths that would look like: 0, 2" or something like that.
My guess is that you either only have a ColumnCount = 1 or have the column
widths set wrong.

Your RowSource should look something like:

SELECT BranchID, BranchName
FROM tbl_Branches
ORDER BY BranchName

Then, in the AfterUpdate event of cboBranch, you should have some code that
requeries cboDepartment.

Private Sub cboBranch_AfterUpdate

me.cboDepartments.Requery

End sub

The reason the table shows the BranchID rather than the BranchName is that
the bound column is probably set to 1. If you set it to 2, you will probably
get an error because that fields data type is set to "Number". This is done
because it is far more efficient to store numbers (BranchID) than it is to
store text (BranchName). And since you should never allow your users to see
your tables anyway, this is not a big deal.

If you would like to see that BranchName rather than the BranchID when you
are browsing your table, then I recommend creating a query that joins the
table to your branches table on the BranchID field, and use that query rather
than the table.

HTH
Dale