Tabbed Form Question

[ginap]

Hi,

I have a form in my producer database for Current Customers. One tab has all the general info, one has the radio show info and I have a third tab for incidents/complaints. I like the set up of this, because it keeps all of the information for each current customer in one place. My issue is with the Incidents/Complaints tab. This tab has information that is pulled from my Incident/Complaints table. What I want to do is be able to add more than one incident for each customer if I need to.

Here's the problem: when I add a new record in my Incidents table for a current customer, it associates a new ID with the new record, even though the name is the same. There is a relationship between the parent (all customers) and child (incidents) tables by ID, so I don't know why this would happen.

I hope this is clear enough for someone to help. Thanks so much!

 

[missinglinq]

How is the ID field defined in the Incidents table? It sounds as if it, too, is defined as an Autonumber, and it should be defined as a Long. Only the ID number in the Customers table should be defined as an Autonumber.

 

[ginap]

OK. So I should change the ID field data type from Autonumber to Long? Do I have to remove the relationship first in order to do this? While we're at it, should I change the ID type in all my child tables and only leave the parent table's ID as an autonumber?

 

[SOS]

OK. So I should change the ID field data type from Autonumber to Long? Do I have to remove the relationship first in order to do this? While we're at it, should I change the ID type in all my child tables and only leave the parent table's ID as an autonumber?

No, each table would have an autonumber primary key. The issue Linq was getting at was that you need to link the proper fields.

In other words as an example:

Table1
Table1ID - Autonumber (PK)
Table1Field1 - XXX
Table1Field2 - XXX

Table2
Table2ID - Autonumber (PK)
Table1ID - Long Integer (FK)
Table2Field1 - XXX
Table2Field2 - XXX

Table3
Table3ID - Autonumber (PK)
Table2ID - Long Integer (FK)
Table3Field1 - XXX

or with a junction table

Table3X
Table3ID - Autonumber (PK)
Table1ID - Long Integer (FK)
Table2ID - Long Integer (FK)

And the links are like:

Table1                       Table2               Table3
Table1ID -------------------->Table1ID
                               Table2ID---------Table2ID

Table3

 

[ginap]

As you can see, I have only been using Access for about 2 weeks. You are saying to create a new ID field for each child table and link all of those fields together? By PK, I know you mean Primary Key, but what is FK?

Sorrt about this if I am totally wrong!! Thanks so much for your help.

 

[SOS]

As you can see, I have only been using Access for about 2 weeks. You are saying to create a new ID field for each child table and link all of those fields together? By PK, I know you mean Primary Key, but what is FK?

Sorrt about this if I am totally wrong!! Thanks so much for your help.

A foreign key is a primary key from another table.

So if you have a primary key from table1 and you are storing it in table2, in table 2 it is the foreign key. Table2 still has its own primary key but you store the key from table one as a long integer and it is linked back to the table1 autonumber.

Does that help?

 

[ginap]

Yes. That makes sense. When you say "you store the key from table one as a long integer and it is linked back to the table1 autonumber", how do I do that? Thanks.

 

[SOS]

Yes. That makes sense. When you say "you store the key from table one as a long integer and it is linked back to the table1 autonumber", how do I do that? Thanks.

That is set up in your Relationships window (TOOLS > RELATIONSHIPS)

 

[ginap]

I figured. I am actually about to that as we speak. So I should create a unique ID in each child table, like in my CurrentCustomers table for example, create a field called CurrentID, and link that to the other chidren's unique ID fields, as well as the primary key in the parent table? If that's right, when I create the field, what type should I choose, autonumber? Thank you so much...

 

[SOS]

I figured. I am actually about to that as we speak. So I should create a unique ID in each child table, like in my CurrentCustomers table for example, create a field called CurrentID, and link that to the other chidren's unique ID fields, as well as the primary key in the parent table?

No, I don't think we've still figured it out. I'm posting a sample for you - go to the relationships window to see how it is done.

I don't know how your whole process is neede to work so I may not have the sample exactly as you need it but take a look at the tables and the relationships to see how it kind of works.

 

[ginap]

Thanks. I am walking out the door, but I am going to look at this tonight when I get home and see if I can make sense of it. Thank you SO MUCh for your help!

 

[ginap]

I took a look at it and it makes pefect sense to me. Thank you so much for going through the trouble. Tomorrow in work I will create an ID field in each table and link them as you illustrated. So for the field type, it is number, not autonumber, except for the customerID in the parent table.

Do I have to manually assign this number to each new record when I enter them in? What I mean is, when I add a new incident for a customer, do I assign it a number in the incidentID field, or will that happen automatically?

 

[SOS]

Do I have to manually assign this number to each new record when I enter them in? What I mean is, when I add a new incident for a customer, do I assign it a number in the incidentID field, or will that happen automatically?

You would use a main form for the customer records and then you use subforms for the other tables. And as long as the Master/Child links are set to the CustomerID then when you add a record to the subform, it will automatically add the CustomerID to the child table.

 

[ginap]

You are so great to be helping me, thank you again! I did exactly what you suggested and linked everything together. Now my relationships all make sense.

Of course, I am still having problems making the form do what I want it to do. Grrr... When I try to create a new incident/complaint record for an existing customer, it assigns it a new ID number anyway. So now in the parent table, I have 2 records for the same person with 2 different ID numbers. Obsiously, all I WANT it to do is create a new incident record for the same customer (sometimes customers will have 2 or 3 or more incidents associated with them). I am doing something incredibly stupid, I am sure. I just wish I knew what it was..........

 

[SOS]

How about posting your database, what you currently have?

 

[ginap]

I will do that first thing Monday (I am out of the office until then). I cannot tell you how much I appreciate the help. This is a new job for me, and this was my first big project: buy a book, learn Access and create a producer database...yeah, no problem, right?

I will post it next week. Have a great weekend and thanks again.

 

[SOS]

See you then

 

[ginap]

I tried to upload the file and it failed. Could I email it to you? The only issue might be that I created it using Access 2007. So I tried to save it as an earlier version, but apparently I am using features that aren't available in earlier versions, so it won't let me. Any ideas? Thanks.

 

[SOS]

Did you first do Compact and Repair (Round Office Button > Manage > COMPACT and REPAIR) and then Zip it before trying to upload?

 

[ginap]

(I did the Compact and Repair, but I didn't zip it. I just did (I think), so let me know if you can't open it. Thanks.)

OK. Here is my db (remember, I am a beginner!). The form I want my users to use to add incidents/complaints for our current producers is called Current Producers. It is a tabbed form, and the tab called Incidents/Complaints is where I want the users to see the incidents associated with that customer only. If they could add new incidents right there in the tab, even better, but I am assuming that can't be done, which is why I have a button on there to connect to a separate form. Either way, I just want to be able to add a new incident and not have it create a new customer. For example, Marino Velasquez is ID number 1 in the All Customers (aka parent) table. When I go to add a new incident (either in the tabbed Current Producers form or in the Incidents/Complaints form), it adds Marino as a new customer and assigns him a new ID number. This is obviously not what I want.

Thanks so much for taking a look at this!! This is pretty much the last piece of the puzzle for me. After I fix this, I can start actually entering data and we can actually (hopefully) use this thing.