add to array?

[AccessIdiot]

Hello all,

I need help setting up and adding to an array. I have never done this in access before though I have worked with arrays a *little* bit and understand their basic nature.

Okay, so on my form I have a combo box for "Staff". But I need to be able to have multiple staff entered into the database (the combo box itself is fed from a separate table), and preferably into one field.

So I was thinking that there could be a button "Add Staff member". So the user could choose the first Staff member from the dropdown, then click "Add Staff member" which would clear the combo box so they could choose another etc. Every time they chose another staff member it would get appended to the array.

Could something like this work well? Is there a better or easier way to do this WITHOUT having multiple fields for multiple staff? I'm trying to avoid empty cells here because one day the user may have 3 staff members and the next day 1 or 5. So I don't want to design the master table to have multiple staff fields. Rather I thought it would be better to have one field that can hold multiple values.

Thanks for any help!

[MMcCarthy]

I wouldn't do it using an array. This is a many to many join between the user and staff table. i.e. one user can have one or many staff and one staff can have one or many users. You would do this using a join table which would have a composite key of the primary keys of the other two tables.

For example,

tblStaffPerUser
StaffID (Composite PK and Foreign key reference to the PK of Staff table)
UserID (Composite PK and Foreign key reference to the PK of User table)

The staff could then be added using a subform based on the above table.

Mary

[AccessIdiot]

I'm sorry, I wasn't clear. By user I meant the person filling out the form. It is actually a one to many relationship between user and event. So on any one event there can be one or many staff (although there will almost always be at least 2 people involved in an event).

So in the event table (tbl_Event) there is a field for Staff_ID (foreign key). In the Staff table (tbl_Staff) there is a Staff_ID field (primary key) and Staff_Name. tbl_Staff.Staff _Name feeds the combo box on the frm_Event but what gets stored in tbl_Event is the Staff_ID field that is the foreign key.

So I just need to be able to store multiple Staff in tbl_Event. Would it be better to store the Staff_Names or the Staff_ID? And what is the best way to do it if not an Array?

Thanks for the help!
melissa :-)

[MMcCarthy]

I'm sorry, I wasn't clear. By user I meant the person filling out the form. It is actually a one to many relationship between user and event. So on any one event there can be one or many staff (although there will almost always be at least 2 people involved in an event).

So in the event table (tbl_Event) there is a field for Staff_ID (foreign key). In the Staff table (tbl_Staff) there is a Staff_ID field (primary key) and Staff_Name. tbl_Staff.Staff _Name feeds the combo box on the frm_Event but what gets stored in tbl_Event is the Staff_ID field that is the foreign key.

So I just need to be able to store multiple Staff in tbl_Event. Would it be better to store the Staff_Names or the Staff_ID? And what is the best way to do it if not an Array?

Thanks for the help!
melissa :-)

Melissa

This is the same problem but with the event table rather than the user table. The whole point of having a foreign key is to tie it to the other table. If you put multiple values in there you can't do that. This is still a many to many relationship as an event can have one or many staff and staff can be assigned to one or many events.

The only way this can be handled is with a join table.

Mary

[AccessIdiot]

oh! okay, I get it now - thanks! I'll give it a shot.

[AccessIdiot]

So I'm doing something wrong (big surprise).

I did as you suggested and made a separate table. The primary key is a combo of two values, the event ID and the staff ID.

On the form I have a combo box for the first staff member. There is a button to add additional staff. When clicked, it makes visible a subform with five combo boxes, all with the control source of StaffID from the junction table. But when I choose one value it sets the value for all the combo boxes. Also, when I enter the data only one value gets into the table in the database.

What am I doing wrong? I'm a total newbie here kind of stumbling my way along.

Cheers,
melissa :-)

[MMcCarthy]

So I'm doing something wrong (big surprise).

I did as you suggested and made a separate table. The primary key is a combo of two values, the event ID and the staff ID.

On the form I have a combo box for the first staff member. There is a button to add additional staff. When clicked, it makes visible a subform with five combo boxes, all with the control source of StaffID from the junction table. But when I choose one value it sets the value for all the combo boxes. Also, when I enter the data only one value gets into the table in the database.

What am I doing wrong? I'm a total newbie here kind of stumbling my way along.

Cheers,
melissa :-)

It's OK Melissa, we all have to learn.

Make your subform based on the join table and make the StaffID a lookup field to the staff table. Make the layout of the subform either a datasheet or continuous form. Now you will be able to add the staff as new records. The subform will tie to the main form with the EventID which can be hidden.

Mary

[AccessIdiot]

Thanks Mary! This works well except I am using custom buttons for navigation control. When I click on the button for "add additional staff" I get an error message that I can't go to the specified record. The button is a simple one with the standard Access built in code:

Private Sub btnAddStaff_Click()
On Error GoTo Err_btnAddStaff_Click


    DoCmd.GoToRecord , , acNewRec

Exit_btnAddStaff_Click:
    Exit Sub

Err_btnAddStaff_Click:
    MsgBox Err.Description
    Resume Exit_btnAddStaff_Click
    
End Sub

I have changed the subform to be a single form for space sake. Is this causing the problem?

thanks again

[AccessIdiot]

I also tried enabling and using the navigation buttons Access provides. When I do that I get an error message that I can't add or change a record because a related record is required in table tbl_Event. I did double check that the fields were linked. On the subform the Link Child Fields is set to StaffID and the Link Master Fields is set to Event_ID.

What am I doing wrong?

[MMcCarthy]

Thanks Mary! This works well except I am using custom buttons for navigation control. When I click on the button for "add additional staff" I get an error message that I can't go to the specified record. The button is a simple one with the standard Access built in code:

Private Sub btnAddStaff_Click()
On Error GoTo Err_btnAddStaff_Click


    DoCmd.GoToRecord , , acNewRec

Exit_btnAddStaff_Click:
    Exit Sub

Err_btnAddStaff_Click:
    MsgBox Err.Description
    Resume Exit_btnAddStaff_Click
    
End Sub

I have changed the subform to be a single form for space sake. Is this causing the problem?

thanks again

For this to work the button should be on the subform. Is it?

[AccessIdiot]

yes it is indeed. :-)

[MMcCarthy]

yes it is indeed. :-)

Make the default value of the EventID

=[Forms]![MainFormName]![EventID]

[AccessIdiot]

That did it! Thank you so much!

[MMcCarthy]

That did it! Thank you so much!

You're welcome.