Need to record multiple duplicates of a combobox/listbox. Another table? Need conceptual advice.

[Dirty_Head]

We are a construction staffing company to put it simply. When giving safety equipment, we give it for free the first time, but take the cost out of their paycheck if they lose it/purposefully damage it. This is simple stuff like Hard-Hats, Gloves, Protective Eye glasses, etc. I've got a table: IssuedLog - has fields for: SSN, EmployeeName, PhoneNumber, Address, DateOfFirstIssue, EquipmentIssued. They're all flat fields except for "EquipmentIssued" which is a dropdown combobox pulling from another table "ExpendableEquipment" - this table contains the type of equipment, the cost, and an arbitrary ID number.

My form does this: At the top is a combobox with SSN set to "Find a record on my form based on the value selected . . ." This works. If we search for a SSN, it pulls up the user data and allows edits. If the SSN does not exist, it gives an error and I've provided a button to move to "Add New Record" which blanks my form and allows data entry. I've added "existing field" for "EquipmentIssued" in two places. One of them is "Locked" to display current issued equipment but not allow updating. The other to allow them to dropdown and check boxes for IF the employee has never been issued a specific piece of equipment. (I'd also like to lock these individual checkboxes too, but that's another post)

So i'm recording WHO (name, phone number, ssn, etc.) is receiving [Edit:NOT checking out] WHICH equipment and HOW MANY TIMES - if >1, provide a separate listbox with checkboxes to create a report that will list the COST of the equipment issued that can be given to payroll department.

I hope this isn't terribly confusing.

[Dirty_Head]

Here is a screenshot of the form in question.

[Missinglinq]

You've got a major design flaw, here. Your employee Form should actually be a Main Form, and your 'equipment issued' data should reside in a separate, related Table, and displayed in a Subform, with the relationship to the Main Table being a One-to-Many one.

The Main Table should contain everything you've listed except the equipment. There should be a Primary Key Field that is unique to each employee Record; I suppose that the SSN would work for this...although many prefer to use an Access generated Autonumber, serving no purpose except to uniquely identify a given Record.

The related Table that the Subform is based on should contain a Foreign Key that matched the Primary Key from the Main Table, and acts to tie these two Tables together.

The Subtable needs to have the Foreign Key Field mentioned, above, as well as Fields for all of the equipment related data...DateOfIssue (note no 'First' in that field...we'll address that in a minute) EquipmentIssued, the Cost, and an arbitrary ID number.

Rather than having a separate DateOfFirstIssue field, you should have a Boolean Field Bound to a Checkbox, on the Subform Record, that when checked indicates that this was the original issuance of the piece of equipment to a given employee.

The equipment Field could still be based on a Combobox, pulling the equipment data from your equipment Table. but each piece of equipment would be recorded in a separate Record, rather than all of it, for a given employee, being crammed in a single Field.

Each time you add a piece of equipment, you could run a DCount() against the underlying Table, seeing if that employee has already been issued that particular piece of equipment before.

Note that if you elect to have the PK in the Main Table as an Autonumber, then the matching FK Field, in the SubTable, needs to be defined as a Number (Long Integer) Datatype.

Linq ;0)>

[Dirty_Head]

Uh...I'll be honest...everything you just said was over my head.

Your employee Form should actually be a Main Form

I'm not sure what you mean by this statement. Main Table? So...EmployeeTable kinda? I do have a Primary Key in each of my tables, I do not change that, just add to it. It's an autonumber on each of my tables starting at 1. I chose SSN because it was definitely unique and not hard to remember. I looked into doing a kind of auto-increment field but the VBA again, was over my head.

I'm not familiar with foreign keys at all. I don't fully understand what you mean by a Boolean field bound to a checkbox....You mean a Yes/No field? Right now, my equipment is in a separate table in separate records?

Where would I put the DCount() at? "Default Value"?


Is there any way you could explain a little more simple way? I've been working with Access for about 2 months now beyond the basic "does it open?" support I've provided in the past.

(I Suppose I could use the last 5 or 6 digits of their ssn instead of the whole thing)

[Dirty_Head]

OK, after doing some research/self-education on the topics you mentioned, I've made a little progress.

I now have my ExternalEmployees table as the Record Source for my "Issue Equipment" form. I have placed the combo box for SSN to filter records on form based on the entry. This works - I can type/dropdown a social security number and it shows me the employee data from ExternalEmployees table. I've added a datasheet subform to the "IssueEquipment" form. I've changed the ID in "ExternalEmployees", "ExpendableEquipmentWithCost", and "IssuedLog" to a unique Primary Key in each table. I now have "OrderID", "EmployeeID", "ItemID" as the primary keys in IssuedLog, ExternalEmployees, and ExpendableEquipmentWithCost. I've built one-to-many relationships from IssuedLog to ExternalEmployees and IssuedLog to ExpendableEquipmentWithCost.

Now, when I select someone's SSN from the combobox/dropdown, it shows me their employee personal data in my main form and all of the equipment they've ever been issued in the subform. (With dates!) So this gets me about halfway there.

Now I need to "Add Record" to the "IssuedLog" table from my form. I like that I can see the history/current information, but how do I append to that table from here?

[Dirty_Head]

I solved my last problem by adding a subform with record source tied to the log and an add record button.

[ArviLaanemets]

To add a record to form/subform are several options - shortcut key, New Record option in menu, and unless you have disabled it on form/subform, an Add New Record button at bottom of form. The last is visible in your added picture too. At bottom of your subform you have controls to navigate the form (after "Record:" label) from left to right - Go to 1st Record, Previous Record, a field where the number of current record is displayed, and where you can insert the number of record you want to be displayed/selected, Next Record, Last Record and New record.

Btw, probably your main form has those controls too, and when they are one below other, it is easy to click the wrong one. Your main form data fields aren't very wide, so my advice is place the subform to right of main form controls. As result, your app will be more compact, and will be easier to handle.