Need help with form with a single combobox & multiple textboxes, option buttons

[hansjhamm]

Fairly new to the Access world and this is one I just cannot figure out

Have an unbound form with a combobox (CBOName) bound to employees table
(for name lookup)
8 of the textboxes are bound to this combobox and pull address, city
state etc...for viewing purposes. The only non-viewing textbox is;
(TXTEmpID) this contains the employee number which I will need to pass
on later. I have no problems getting this data from the table.

Now comes the fun part...
I need to pass TXTEmpID data to a new table...named equipment. This
will be the number I will use in my queries...
Second problem is; I have 17 radio(option) buttons and 16 additional
textboxes that the user will fill out and it to be entered into the
equipment table. The option buttons aren't really a boolean type of
button. I set each one up individually because an employee could be
shipped multiple items.
I have tried binding the form to the equipment table, but my data from
the employees table gets corrupted. Tried unbinding it and cannot get
it to work

What is my best option here? I have looked around and have not quite
found what I am looking to do.

Thanks

Hans

 

[Guest]

What do you mean by "pass TXTEmpID data to a new table"? You then mention a
query. I don't know if you want to use the value as a criteria in a query or
append/update a table and if the table is actually "a new table" or just
another table.

17 radio buttons and 16 additional text boxes sounds like you have an
un-normalized table structure (I could be wrong).

Please tell us about your structures and what you are attempting to do.

 

[Guest]

Hans:

Like Duane I suspect that the fundamental problem might be with the logical
model. It sounds like you have a many-to-many relationship between Employees
and Equipment, i.e. each employee can be shipped one or more items of
equipment and each type of item can be shipped to one or more employee.

If this is the case then you'd need a third table to model the relationship
between Employees and Equipment, EmployeeEquipment say, with two foreign key
columns referencing the primary keys of Employees and Equipment, EmpID and
EquipmentID, along with any other columns needed to represent attributes of
the relationship type, e.g. Quantity to record how many of each item are
assigned to the employee.

The usual method of data entry for this type of situation is to have an
Employees form (which can either be bound to the Employees table or be an
unbound form with a combo box and unbound text boxes as you have at present.
Within this parent form a subform bound to the EmployeeEquipment table
enables you to assign as few or as many items of equipment as necessary to
each employee simply by adding rows in the subform. The equipment would
generally be selected from a combo box in the subform bound to the
EquipmentID column, and which draws its list from the Equipment table (the
combo box wizard can set this up so it shows the equipment name rather than
the EquipmentID or we can help you hand craft it if necessary).

If the parent form is bound to the Employees table the subform control's
LinkMasterFields property would be EmpID (the primary key of Employees); if
its an unbound form the name of the combo box on the parent whose value is
the EmpID for the selected employee would be the LinkMasterFields property.
In either case EmpID (the foreign key column in the EmployeeEquipment table),
would be the LinkChildFields property.

Multiple option buttons are rarely a good means of data entry as they are
fixed at design time. A subform with combo boxes on the other hand will
always list the current items from the Equipment table regardless of whether
new ones are added or redundant items deleted. You can even make use of a
combo box's NotInList event procedure to add new rows to the Equipment table
via the combo box.

Ken Sheridan
Stafford, England

 

[hansjhamm]

Ken,

I have been away on other work tasks and finally was able to get back
to this...while my other tables are normalized, both of you are
correct. After reading your post...this one is not normalized.
Would it be smarter to breakdown the equipment table into multiple
tables? I originally had it this way, but trying bot to have table
bloat. There are seven different catagories that would translate to
seven tables. Due to my limited knowledge of Access?

Thanks

Hans

 

[hansjhamm]

Ken,

One additional thought on this...In the equipment table Some of the
(what I called categories earlier and originally in its own table)
items are computers, which needs to have the service tag # recorded,
windows & office serial numbers recorded, mfg and model # recorded.
Another "category" is Phone where the following data is recorded; Model
Number, Phone Number, IMEI #, SIM # etc...
I think that I may have to go back to tables where each "category" is
in it's own table to make this work correctly.
The original table layout was: Computer, Phone, PDA...


Thoughts on this?

Hans