Links? Relations? HELP!!!! (1 Viewer)

[Gretchen]

I am completely stumped and was hoping you could help me. I know I've done this a couple of years ago, but for the life of me I can't remember how I did it now!

I have a master dbase that has a table with Name, Organization, Organization addy, phone, email as well as home addy, phone, email, AutoID and contact type. Contact type is CCP, NAC, Board, or CCP Nac. From this table I need to connect to other dbases which have information particular to just those contact types. The autoID that is assigned in the master dbase becomes the ID# for that person no matter how many other dbsaes they appear in. How do I relate/link these other dbases to the master dbase so that if they are just a CCP contact, their Master dbase info appears in the CCP dbase where other pertinent info just for CCP's is kept? Or make it appear in the NAC dbase only?

I've tried relating them, but there isn't anything the same in the databases. I thought of subforms; but the CCP and NAC dbases are separate from the Master dbase so couldn't find a way to do that.

You don't know how much I'd appreciate your help here!!

 

[Rob.Mills]

Can you just setup a query in the other database that pulls just a certain contact type?

 

[Gretchen]

Yes, I can do that and it works well. However, when I try to get the CCP data in the CCP table to match to the query in a form, I can't get it to work. If I use a subform, the subform goes page to page to page; but doesn't move the main form information, therefore it isn't "connecting" to it properly.
I based the form on the query on the master table and the CCP table in the CCP dbase

 

[Rob.Mills]

Forgive me if my explanations are not clear but I'm just trying to get a clear image in my head of what you're trying to accomplish.

First, have you set up the link to the different databases?

Second, are you wanting to take the info from the linked table and filter for CCP and then take that info and put it in another table?

Let me know if I'm correct on this before I give you a lengthy explanation.

 

[Gretchen]

Rob, I am gratefui for your assistance! In the ccp dbase, I linked to the master dbase, then created the query on it to filter just the CCP contacts. I tried creating a relationship from the AutoID in the master dbase to the CCP table but since there was no "common" denominator didn't know what to chose - so created the relationship AutoID<--->LastName. I have no idea if this is good or bad. (self taught, so please bear with me)

I want to have the CCP info about Susie Smith relate to the correct Susie Smith in the master dbase and only to her. Does this explanation make sense?

This way, if someone calls in and no longer works at Craft Daycare, we can make the change only once and it will appear in the appropriate dbases.

 

[Rob.Mills]

If I understand you correctly you don't need to setup a relationship. In your CCP dbase you have the main table linked and then a query that filters out the CCP contacs and this query contains the AutoID of each record, correct? I would base everything in the CCP dbase off of that query. That way as things in the main dbase are updated it will automatically update in the CCP dbase. And that's totally fine to do. I base a lot of forms and reports off of queries.

Let me know if I'm making sense here.

 

[Gretchen]

If I understand you correctly you don't need to setup a relationship. In your CCP dbase you have the main table linked and then a query that filters out the CCP contacs and this query contains the AutoID of each record, correct? TRUE/YES

I would base everything in the CCP dbase off of that query. But how??? I guess this is where I get lost. Sorry if I'm being dense, but I just don't understand where to go from here. On a form, how do I tie the CCP table info to the info gained from the query?

 

[Rob.Mills]

Ok, now we're moving forward.

To base a form on a query: Open up the form and then open the properties of the form. On the Record Source property click on the drop-down box. Then select the name of the query. Everything else will be the same. The form really doesn't care if it's recordsource is either a table, query, or an SQL statement. I actually prefer to use SQL statements but I don't want to confuse you too much. Just use the query and then set the form up the same way.

Hope that helps. Good luck.

 

[Gretchen]

ok, that takes care of the part of the form that's based on the master dbase query. Now what about the part of the form that's based on the CCP dbase? Is this a subform or what? And do I base it on the CCP table?

 

[Rob.Mills]

You're really not going to need a table in the CCP database for the contacts. The query will suffice for all recordsources. Not for your subform, what info is contained in that?

 

[Gretchen]

Sorry, was out of the office yesterday.

The subform contains information held in the CCP dbase table such as DateSTarted, DAteAwarded, LetterSent, etc

when I try to make a subform connected to the main form, the information doesn't carry through. I can click from subform record to record, but the main form record just stays still. Therefore, I don't think they are connected correctly.

 

[Rob.Mills]

Alright! Now things are starting to crystalize. You've got the main table linked. In the CCP database you have a table the you want to setup with a one-to-many relationship with the linked table. Ok.

Make sure you have the relationship setup correctly in the relationship window of the CCP db. What I want you to do is open the main form in design view. Then set focus on the subform. Don't click into it if you know what I mean. In other words, once you've selected it you should be able to resize it. Then open up the properties window. Click on the 'Data' tab. There's two fields in here that are important. 'Link Child Fields' and 'Link master Fields'. The Child field is where you are going to select the foreign key in the CCP table. The Master field is where you put the primary key for the linked table. That should be it. Good luck.

 

[Gretchen]

When I click on "Link Child Fields" I get an error message in the Subform Field Linker that says "Can't build a link between unbound forms". I get the same message in the Link Master Fields. I don't see any unbound forms!!

Also, I know what the primary key is in the master form, but what is a foreign key??? (Something sent in from Europe? heheh just a little humor)

Just want to once again say thanks for your continued support and help on this!!

 

[Rob.Mills]

Unbound subform?? The form you setup to be the subform... Is it selected in the source object in the subform properties? If there's nothing in that property then that's your problem.

And yes, a foreign key is what you use to unlock a house in Europe. ;-)

A foreign key is the field in the subtable that you use to link to the primary key in the main table. For instance:

Customer table primary key: CustomerID
Order table primary key: OrderID
Order table foreign key: CustomerID

 

[Gretchen]

ok, got that to work! now the two forms (main and sub) appear; but the data in the subform still doesn't come up with the corresponding main form name. What am I doing wrong?? (grrr smetimes hate being self taught .. am sure I missed something important along the way here)

 

[Rob.Mills]

Man!! How frustrating! I thought for sure we were there.

Back to the drawing board.

Alright, you're going to have to bear with me on this method cause it's a bit more involved. Clear out the link fields in the subform property window. Then:

Click into the subform so that the upper right corner has a black dot in the middle and then go to the recordsource property.

Delete what is in there (it's ok, it's easy to fix if something goes wrong) and then click on the build button to the right.

That will open a query in design view. Select the CCP subtable and then drag the fields you need into it. Then on the field you need to set criteria to type: [Forms]![MainFormName]![PrimaryKey]

Then DON'T press the save key. Just close out the query. It will open a msgbox and click 'Yes'.

This will set the recordsource to an SQL statement.

Now what this is going to do is when you open the form the subform is going to run a query and only display records that contain the primary key. But we're not done :-o Stay with me.

Hopefully you have a little bit of VBA experience and a little is ok. Open the properties window for the main form. Click on the events tab. Then find the 'On Current' event. Should be the first one. Click on it's build button. If it opens a small window with three selections choose 'Code Builder'. This will open the form's module ready to go.

Right where the cursor is type this: Me!SubFormName.Requery

Then close out the module. This makes the subform requery everytime you move to a different record in the main form.

I know this is a lot but I know it works. So let me know if you have problems and I'll walk you through each step. You can email me if you need to.

 

[Gretchen]

arggghhhhhhhh! All went well until I went to the main form and there was no 'On Current' event!

Switching to email Houston!! (or Raleigh in this case)