multiple records from one form into one table

[mmasuda]

I have a table "caller," "videoItems", "libraryItems," and "topicPackets," using MS Access 2003
"Caller" collects the demographics from people requesting information. Each caller may request up to 3 video items, 3 library items, and 5 topic packets.
I created a main form for "caller" and inserted subforms for the other tables. The fields in those are drop down boxes which are fed from a table which has all the items listed.
But if I list the drop down boxes, for example I will need 5 of them for the topic packets, it will not allow me to make different choices.

Example:
topic 1: wheelchair dancing
topic 2: wheelchair dancing

What can I do, so I can make different choices which are stored in the same column of the topic table? That way at the end of each quater, I can retrieve how many different items were requested by each caller.

 

[GingerR]

Hi.
What is the recordsource of the topicPacket subform?
What are the child/master links set to?
What is the row source of the topic combo box?
What is the control source of the topic combo box?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

I am not sure what you mean by child/master links (I am still inexperienced in Access) but there are three tables involved:
Table: Caller
Table: tblTopicPacket
Table: DisabilityPacket

Caller is related to tblTopicPacket 1:many and tblTopicPacket is related to DisabilityTopic many:many

Main Form: Caller
retrieves all the demographics from the caller and stores it in table "Caller."

SubForm "topicPacket" uses as recordsource tblTopicPacket.
the combo box rowsource is "Disability Topic" from "Disability Packet"
The controlsource for the combo box is "topicName" from tblTopicPacket.

I hope I am making a bit more sense now. Thanks

 

[GingerR]

Child/Master links means how the subform is related to the main form.

In the design of the Main form, if you are viewing the Properties window and click once on the subform, there will be properties Child and Master links. For instance, if the main form is based on Caller, and say in that table is a CallerID. then in the table "tblTopicPacket", I assume that there is also a field CallerID, which relates this table to the table "Caller". So for your subform properties, the Child and Master links will be "CallerID". That's how Access maintains the relationship.

So it sounds like your form is set up properly. Are you saying that in the topicPacket subform, you can only choose one item? Are you getting an error message? If so, what is it? When you pick a topicPacket, then look in the associated tables, is the correct data going in the places you expect it to?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

No, I don't get error messages. Even though I have 5 fields for topic packet, it cannot get it to forward to the next row.

subform topicPacket
Topic 1 topic 2 topic 3 ...
Alzheimers Behavior Disability Education

ideally this should be stored in table tblTopicPacket.
If the callerId for that particular caller is 5, then

Table: tblTopicPacket
callerId topicName
5 Alzheimers
5 Behavior
5 Disability Education

However, if I choose Alzheimers for topic 1 all the other combo boxes turn to Alzheimers as well. The only thing it will put into the table is one topic into one row. It wont forward to the next row

Yes, the tables are related by callerID which is unique for caller table.

 

[GingerR]

Why is your subform horizontal?
It should be vertical, just like the tblTopicPacket is.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

so are you still having a problem?
please post the structure of your table tblTopicPacket. i.e.
Field Names, and the Keys
also some sample data would help.

thanks--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

tblTopicPacket has 3 Columns.
packetId: autonumber, Primary Key
callerId: Integer, foreign key to tblCallerId
topicName: text (255 char)

packetId           callerId       topicName
1                  5              Wheelchair Dancing
2                  5              Behavior Disorders
3                  5              Alzheimer's
4                  6              Individual Education Rep
5                  7              Behavior Disorders
6                  7              Individual Education Rep

Anyone who calls in may request up to five items.
Every three month a report has to be generated that among other things shows how many packets were sent out totally and how many people asked for how many packets.
Hope this makes sense.

 

[GingerR]

Yes this table structure makes sense. It's your subform I can't figure out.
What is the record source of the subform? Why are there five controls on it, horizontally? I'm still confused by that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

That is the only way I could think on how to enter the five choices for the topicName.
I thought it would be easiest for the person who will be doing the data entry, if she had a form that would give her a form with 5 combo boxes, where she could then use the 1. combo box for the first choice of the topicName, second combo box for the second choice, etc. All the choices need to go into the tblTopicPacket

If I need to be more detailed let me know.

 

[GingerR]

What is the recordsource of the subform?
what is the control source of the combo box(es)?
what is the rowsource of the combo box(es)?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mmasuda]

I figured it out!!!!!!!!!!!!!!
Instead of using the form view with combo boxes, I am using the datasheet view with lookup fields.
Thank you so much for all your help