single value in 2 fields 1

[samusa]

I have 2 tables with studentID as primary key in both tables. I am using DAP for data entry. Is it possible to enter primary key that gets store in table1 should reflect in table2 as well. Please help.

Sam

 

[formerTexan]

samusa,

I think you are getting crosswise with normalization issues.

If the two tables are going to hold related data (particularly if the data is in a one-many relationship), then StudentID should be a foreign key in the 2nd table.

It is possible, but unlikely to have a one-one relationship that requires more than the allowed number(254?) of fields in a table. Hence a split between tables. But even here I see no reason why a PK/FK arrangement wouldn't be viable.

In either case you could generate your own PK at the time of record creation rather than leaving it to Autonumbers.
This can then be applied to more than one table.

Give us a bit more info as to where you are trying to get to and perhaps some other suggestions can be made.

Cheers,
Bill

 

[samusa]

I tried to use studentID as Foreign key in second table and created one to many relationship, but when i enter primary key value, table 2 with studentID does not get updated.Do I need to write update query for it. Your help will be greatly Appreciated

Sam

 

[fneily]

On the first DAP, you could have the following code on the onchange event of the StudentID control: (I added more than one field for demo purposes)

<SCRIPT language=vbscript event=onchange for=StudentID>
<!--
dim sSQL
sSQL = "INSERT INTO SecondTable(StudentID,Otherdata,etc) VALUES('" & CINT(StudentID.value) & "','" & CSTR(Acq_acreage.value) & "','" & CSTR(holdID) & "')"
MSODSC.Connection.Execute sSQL
-->
</SCRIPT>

Note: This will insert StudentID only one time. You said you had a one to one relationship.

Now you can add a hyperlink to the second DAP with the filter StudentID = [StudentID] which was in another post of yours.

 

[samusa]

I tried above script but not working.StudentID field in Second table doesn't get updated at all.Also when I add hyperlink with filter StudentID = [StudentID], I am getting following message "Data Provider Failed while executing Provider Command"

Please help

Sam

 

[fneily]

You'll have to supply your code.

 

[samusa]

fneily, you are life saver. It worked however i had to remove referential integrity option.Using referential integrity i was able to delete all information of a student existing in both tables from DAP1. Also when I used server filter StudentID=[StudentID] , iam getting follwing message
"the specified field "StudentID" refer to more than one table listed in the FROM clause of your SQL statement."



Sam

 

[fneily]

Is your DAP1 using a table or a query has it's source? You are putting the criteria in the bottom box on the Insert Hyperlink dialog box, right?
Also, didn't you set one up before? Check to see if the syntax is the same.

 

[samusa]

DAP1 is using a table as a source.I am putting StudentID=[StudentID] as criteria in the server filter window.I am using virtual directory for test purposes and sometimes i get the following message ""the specified field "StudentID" refer to more than one table listed in the FROM clause of your SQL statement." sometimes hyperlink does not work at all. When i remove server filter, it works fine.


Sam