populating combo box in access form

[ll]

I'm trying to do what should be a fairly straightforward: populate a
combo box in an access form with data from a table.
I have two tables - the 'main' table and then the table that only has
data for the combo box in the form. I'm running into problems with
all of the records in a column populating with the same data, etc.
Does anyone know of a good tutorial or run-through for this?

Thanks,
Louis

 

[Jeff Boyce]

?"... all of the records in a column ..."?

Is the combo box bound to the underlying data field in your main table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hartman \(MVP\)]

If you are populating the RowSource via code, your code is not moving
through the source data properly. You may need to add a .movenext.
However, since Combos/ListBoxes in Access can be bound to tables/queries,
use that method rather than code. Just create a query that returns the
records you want. Save the query and place the query name in the RowSource
property of the combo.

 

[ll]

As it's been quite a while since I've designed a form in Access, this
process is seeming quite convoluted. I've been using the Query
Builder, as well, which has helped. I removed the foreign key link to
this table that is to populate the ComboBox, and that was a success.

I now have an employee table (tblEmployee) that also has drop down
values, a bid table (tblBid) which has bid details, and an assignment
table (tblAssign) which has assignment details. Initially, I had a
foreign key (emplID from tblEmployee) in tblAssign, as well as a
foreign key (BidID from tblBid) in tblAssign; however, as I tried to
use the employee values (last name, first name) from tblEmployee, each
time I would make a selection with the comboBox (which was bound to
EmplID in tblEmployee), additional rows would be added to the
tblEmployee table, rather than only appearing in the tblAssign fields
(emplID, emplLast, emplFirst). Is there a way to retrieve values from
tblEmployee to populate the comboBox and then populate the record
fields in tblAssign?

Thanks very much,
Louis

 

[Pat Hartman \(MVP\)]

When you create a query that joins the 1-side and many-side tables, only
include the foreign key from the many-side table. Make sure that your
foreign keys are actually defined in the correct table. Do not use lookups
at the table level. They only confuse you and make creating queries and
writing VBA against the table with the lookup experience problems.

 

[ll]

Many thanks for all your help in this; however, I think I need to
start over from scratch. I am wanting to keep track of employee
assignments, with columns for employee name (text), solicitation name
(memo), status (text), type of solicitation (combo box - text), close
date (date), regents date (date), regents approval (check box - y/n),
campus (text). I can see that I could have separate tables for
employees, solicitations, type of solicitation (to populate the combo
box), and assignments.

In 'talking through' the relationships, each employee can have more
than one solicitation and more than one assignment (which consists of
solicitation data, employee data, and date, etc). Each assignment can
only have one employee. Each solicitation can have more than one
employee, as well.

I get stuck at this point - assigning the one-to-many in
relationships. If the tblEmployees had, as its primary key, emplID,
would tblSolicitations and tblAssignments contain emplID foreign keys,
or would there be only one?

Thanks for any help you can provide,
Louis

 

[Pat Hartman \(MVP\)]

The "foreign" key goes into the many-side table. So, if you have "many"
solicitations, the solicitation table would contain the EmpID which is the
primary key of the employee table to which it points. Usually you would
define foreign keys as required and remove the 0 default value that Access
automatically assigns for numeric fields. You want the foreign key field to
default to null so that the record won't save if the field is empty.