Auto ID.

[Addanny]

Existent data:


Table one: ORGID ---- auto number ----- primary key
ORGName ---- text ----- user input (roughly 650 records)


Table two : ORGName --- text ---- same as the names in table one but many repeats (roughly 310000 records) all user input.
ORGID ---- not populated as of yet. ------ number


Problem How do I auto-populate ORGID in the second table to insure ref integrity between the tables and use it as a relational variable?

Access 2010 on a 64 bit windows 7 machine

[RuralGuy]

If you use a Form/SubForm arrangement and bind the ORGID table (or rather a query of same) to the MainForm and the ORGName table (query) to the SubForm then the LinkChildFields and the LinkMasterFields properties of the SubFormControl will place those values in the SubForm table. You do *not* need the ORGName text from the ORGID table in the ORGName table, only the ORGID. I will have to admit I do not really understand your table structures but wanted to describe the Form/SubForm ability with those LinkField properties of the SubFormControl.

[Addanny]

OK so I made a form on the two tables. The form is based on table one. For each OrgName which every name has a unique identifier should be many records. The subform is based on the second table where I am looking to fill i the orgID field. I linked the fields and saved the form the field will not self update.

[RuralGuy]

ORGID should be in the LinkChildFields *and* the LinkMasterFields properties of the SubFormControl. Is It?

[Addanny]

Yes it is. I made sure that it is in both fields. I also checked in the querry format. I made a querry with the two tables and tried pushing the fields to output still no luck

[Addanny]

Anyway this is what i have. Check the attachements. It is a mockup but close to what i have

[RuralGuy]

I don't see any Form or SubForm. Just two tables.

[Addanny]

I took the form off. this is just an example of the database I have. I was just showing how the tables are built.
I added a form with a subform. Still the ID will not update. I did not link the fields on this form just so I can see where I am going wrong when you change it.

[RuralGuy]

It requires a Form and a SubForm!

[Addanny]

Ok so I make a form based on the Source table. This is a blank form at first then I add the fields ORGID and field name. Then i add the subform and save it with all the fields from table to be updated. I link the master and child fields on the control of the subform and save. Then I go to the normal view and same thin. I can see the records from the source table but the records from the subform did not change ID.

[RuralGuy]

If you don't want to create the Form/SubForm for us to see, that's fine. This is not a test to see if I can do it. We're trying to assist *you* in creating it.

[ssanfu]

PMFJI,

Problem How do I auto-populate ORGID in the second table to insure ref integrity between the tables

For future records, do what RuralGuy suggests: form/sub-form for entry.

For the current records in Table Two, there is NO WAY to auto-populate ORGID.

Well, unless the text in ORGName in both tables is EXACTLY the same. Then that leaves you with 309,350 records to manually enter the ORGID.

Do you have another field to identify which records in Table Two should be linked with a particular ORGID from Table one?? If so, you could write code to set the ORGID in Table two.

Otherwise, it looks like a lot of manual updating.

[Addanny]

LOL Rural. I did make a form/sub form. This is what i am trying to say. It is attached to the database posted the second time. I am not savy enough to work with forms and subforms so I just use the templates provided. I just added the subform manually and made sure the link fields were there. I still cannot get it to update. Now after the changes the database will not upload anylonger because I am exceeding the file size for the server. I am just trying to figure it out not trying to be lazy lol.

[RuralGuy]

The 2nd attachment you posted does *not* have a SubForm in the Form. I would expect to see two forms under the Forms category. If you Compact and Repair your db and then zip it before attaching to a post, the limit is 2MB.

[Addanny]

Ok Here is what I have