(Noob) Foriegn keys? (1 Viewer)

[Rick Stanich]

The snippet of code below should add a new line to the table "tblPartRev" but it doesnt. I think it has to do with a foriegn key in that table.

The table: tblPartRev
pkPartRevID
fkPartID
txtRev

                    myrecset1.Open "tblPartRev", , adOpenDynamic, adLockOptimistic
                    With myrecset1
                        .AddNew
                        'add foriegn key here?
                        !txtRev = myrev
                        .Update
                        .Close
                    End With
                    Set myrecset1 = Nothing

I have tried to find examples using foriegn keys but have been unsuccesfull.

I am able to add new to another table:

                    myrecset1.Open "tblParts", , adOpenDynamic, adLockOptimistic
                    With myrecset1
                        .AddNew
                        !txtPartNo = mypart
                        holdPartIDpk = !pkPartID
                        .Update
                        .Close
                    End With
                    Set myrecset1 = Nothing

Allthough it doesnt have a foriegn key.

Any hints, tips or examples are appreciated.

 

[namliam]

What is your PK? Is that an Autonumber?
Do you have any fields in the table that are "required" or are set to " not allow null values" ???

 

[Rick Stanich]

PK is autonumber
fk is Number (Long integer)
txt is text

For null values, if you mean "not allow zero values", tblPartRev is set to "Yes"
Required is set to "No"

 

[namliam]

How about the properties of your fkPartID? Looks like it might be set to "Required" yes or "Allow Zero Length" no

 

[Rick Stanich]

tblPartRev:
fkPartID;
Required = No
doesnt have an option for "Zero length"?

txtRev;
Zero length = Yes

 

[Rick Stanich]

Solved:
Learned to establish foriegn key links in code
Example:

                    With myrecset3
                        .AddNew
                        !txtRev = myrev
                        [COLOR=red]!fkPartID = holdpartpk [/COLOR][COLOR=lime]'Foriegn Key link[/COLOR]
                        holdpartrevpk = !pkPartRevID
                        .Update
                        .Close
                    End With