I have a problem that I thought I found a solution for. I have a table design that has a field that is not a pk set to unique. Because this is set this way I had to figure out another way to add entries. There are 4 tables with one to many relationships. So it create the effect of a heirarchy of a main part table with 3 sub datasheets. Now the part No. is unique. I create one query with a minimal amount of data, PK and the te part No. and the Cust Information and an FK. Then I created another query to enter info. With all the same data as long as the first plus other fields that would be appropriate to enter. I thought I could programatically link the fields so that I would be able to view info from one query and add info with the other. I must do this because I can not haev duplicate part No. So if I programatically link the pk's and fk's. Has anyone done this befor???

Dylan

As you may be finding out you might have been better off to use an autonumber as your primary key and then linked your tables on that key. Using that method Access would have made your links between your forms and subforms for you.

I am not sure what you mean by 'programmatically linking' the PK's and FK's. What are you trying to do? Do you have the tables linked in the Relationships window? You might be better off to go back and properly structure your database and then go from there....

Just my 3 cents worth...

Jack

I do have relationships. All tables have pk's that are autonumbered and are linked using One to many relationships. However there is a table that has a pk which is autonumbered and a field that is indexed as unique. What I considered doing was creating a form using two queries. I thought that what I might beable to do is to have one querie display certain info pertaining to a part and have another querie allow the user to enter data. If I could just have the certain autonumbered pk's have a function which increases them by one when the form is opened I believe that it will allow me to add a test number to a part no while have the part no indexed as unique.

I know this makes no sense.

Dylan

When you open a form that is based on a table with an autonumber primary key the autonumber is increased by one, automatically, when you add a new record or when you open the form in Data Entry mode. Autonumbers are NOT intended for use by the users, but is a way for Access to keep records unique. I think that you and I have been around this block before and if your tables are set up correctly I do not see any need to make a part number a unique index. If you are concerned about the user creating a duplicate part number you should use code to verifiy their input before they can continue with data entry.

I am still confused about the TestNumber and how it is related to the PartNumber. If they are in separate tables and the relationships are correct then you should be able to add TestNumber(s) to a specific PartNumber without any problems.

Jack

Well I believe the relationships are correct. What I feel will happen if i do in fact allow the Part No. to be duplicates is that The Part No. Table will have multiple listing of the same part no which will then relate to a test No. What I wanted was for there to be only one Part No. And have many Test No's so I made the Part No. non-duplicates. I am just confused. I really feel my table design is strong. I am just not sure how to attack this task.The problem is, is that instead of opening the form and being able to add a new test number to the part no. it overwrites the existing test number. So it isnt' incrementing the autoindex of the test number. Maybe this is because I have 4 tables with relationships???? I know how I want it, I just can't explain it.

Dylan

Let me try this... Here are two tables and how I suggest you set them up:

tblPartNumbers
PartNumberID (PK and autonumber)
PartNumber (this is the actual part number of the part)
...other fields as necessary...

tblTestNumbers
TestNumbersID (PK and autonumber)
PartNumberID (FK)
...other necessary fields...

Now create a form based on tblPartNumber and save it. Create a datasheet view form on tblTestNumbers and save it. Go back to the PartNumber form and using the Wizard add the TestNumber form as a subform. Access will create the links for you.

Now you can open the part number form, add a part number and then add as many test numbers, for that part number, as you like. You can move between part numbers on the main form and add, edit or review all related test numbers for that part number. At no time will you need to create or have duplicate part numbers

I hope this is what you are after. I do not know how the other tables figure in, but this simple suggestion will do, I think, what you want.

Jack

Is there a way to do it in a columnar format? Without the datasheet?

Dylan

Dylan -

You can put both tables into a query and do it that way. PartNumber will be repeated for each TestNumber and hopefully that will be OK... Giive it a try and see what you can work out...

Jack