Add multiple records to table given existing data

[Mizzoug]

Hi all,

First, thanks for all the help you give each other - I've gotten answers here for years.

For my problem, I can't find anything helpful on the web and have trouble describing the problem concisely.

I work at a school and am creating a db for our volunteer coordinator. The idea is that there is a table for the parents and a table for the volunteer opportunities (VolOpptys). The VolOpptys consist of Categories, Events, and WaysToHelp fields, with some repeats. I've included a pic of an example of this table as it is where my trouble lies (the final table will be bigger).

I want the user to be able to add to it and am trying to create a form that is simple and user-friendly, but can't figure how. I want her to be able to 1 - see the current Categories and then add another if she chooses OR 2 - Select a current category and add an event OR 3 - Select a current category and event and add a WaysToHelp. I want her to see each category or event only once and can't seem to Show Unique in a form.

She hands out questionaires for each family and has them check their areas of interest. She wants to put these answers into the db and then search later, for example, Who wants to help with soccer? Who wants to help with auction processing? etc. I'm hoping to link the parents table and the VolOpptys table for this data input, but as she changes the questionaire over time, I'd like her to be able to make the changes to the VolOpptys table.

Ideally, I'd like to open a form and have it display unique values for Categories, then she selects on and the Events for that category display and she can select one of them and see the current WaysToHelp and add another - or add a category or event at any point. I've created combo boxes for Categories that, when updated, displays a combo box for Events, that when updated displays the current WaysToHelp, but that doesn't help me add information.

I'm open to suggestions.

Thanks,
M

 

[Poppa Smurf]

I would have a table for Category, a table for Events, a table for Ways to Help, a table for Volunteers and finally a table for VolOpptys. Each of these entries in the tables will have a reference id.

On your form you would have a combo box for Volunteers, a combo box for Category, a combo box for Event and a combo box for Ways to Help.

If the item is not in the combo box (except Volunteer) then you can use the NotInList event to add the item to the drop down.

Whne you add the record the reference id of the selected item in each of combo box will be added to the VolOptys table.

If you require further assistance please contact me as below.

 

[Mizzoug]

Thanks, Poppa Smurf.

I have two follow up questions:

1. If I have a table each for categories, events and waystohelp, how do I link them (and write to table VolunteerOpptys) when the categories don't necessarily share event names and event names don't necessarily share waystohelp.

2. In playing with the NotInList event, I am using this code on the event:

Private Sub CategoryList_NotInList(NewData As String, Response As Integer)
Response = AddNewToList(NewData, "VolunteerOpptys", "Category", "categories")
End Sub

And the attached module code, but am getting the error "The text you entered isn't an item in the list" after saying YES to the msg about adding it - and it DOES add the item to the list. I've tried DoCmd.SetWarnings False at multiple places but no joy.

Can you offer any insight on this?

Thanks again,
M

 

[Poppa Smurf]

Post a zip copy of your database to my email address below and I will do the linking the for you. I will also explain how the linking is done etc and look at your error message.

 

[cjrhoads]

Would it be possible for you to just create a lookup on the table itself that gives you all the existing items for that field?

I do this with my tables all the time. For example, I want people to enter Mr. or Ms. or Mrs. or Rev. or Dr. or whatever. They need to be able to add a new one, but if one is already there, I want them to just select it. The field is called Title_Prefix.

In the Table Design, I choose the field and then the Lookup tab.
Change the Display Control to Combo Box
The row source type is Table/Query
Then I create a query with that one field in it, with the Unique option on. That enters the following code in the Row Source:
SELECT DISTINCT People.Title_prefix FROM People;
(You can probably type that in, but I have a poor memory for syntax, so I use the query tool to generate the code).

There might be some reason (like resources) not to do this on huge tables, but for little single-user databases, I've never had trouble with it.

Would that work, or am I missing something in what you are trying to do?

 

[Mizzoug]

Thanks cjrhoads,

I am actually doing that to pull up the existing info but am having difficulty allowing the user to add new info to the list when there are three fields that need to be added (a new category, new event and new waystohelp).

Thanks,
M