Duplicate Values in index, primary key etc

[Ally]

Hi - it's been a while since I did much access and am trying to help someone out and have got into a pickle! A quick job has turned out to be a lot more complicated! Plus I'm having to use *spit* Acc 2k.

When I go into frmHighCostDrug (from the "add new drug episode" button on the frmPatient) I often get the message "The changes you requested were not successful because they would create duplicate values in the index, primary key, relationship etc etc."

Now, I didn't think I'd forgotten that much. I've got primary keys, values in the table set to Null, relationships set and ref integrity set and cannot for the life of me see why this is happening.

I know though, that one of you will say "oh it's that" and point it out and I'm going to feel completely stupid, but at the moment, I don't mind! I really can't figure it out.

I've attached db - it's in Acc2k and I tried to do a copy Acc'97 for those who don't have it but it caused no end of problems. Came up with messages that this form contains data that Access doesn't support!!!! ??? If I can sort it I will.

If anyone's got any ideas, I'd be v grateful!

Tnx

Ally

 

[RuralGuy]

Does ac2K support OpenArgs? You are passing an OpenArgs argument to the frmHighCostDrug form but never looking at it??? What was the intent?

 

[Ally]

Does ac2K support OpenArgs? You are passing an OpenArgs argument to the frmHighCostDrug form but never looking at it??? What was the intent?

Thanks for replying!

Not sure - can't actually find it! Did a search for OpenArgs but it doesn't find! Where is it being called?

Some of the code I tranferred from other databases but they're mainly '97 so I then converted that which errored into the 2k equivalent.

(I'm going now, but will be back tomorrow am)

 

[RuralGuy]

This is the code behind the Add New Drug Episode on the frmPatient form. The Bold Red argument is the OpenArgs argument.

Private Sub cmdAddNewDrugEpisode_Click()
On Error GoTo Err_cmdAddNewDrugEpisode_Click

'Saves record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim r As Integer
    
    stDocName = "frmHighCostDrug"
    
    r = msgbox("Do you wish to add a new High Cost Drug episode for this patient?", vbYesNo, "New Episode")
    
    If r = vbYes Then
    
        stLinkCriteria = "[PtID]=" & Me![PtID]
        DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, , [B][COLOR="Red"]stLinkCriteria[/COLOR][/B]
        
    Else
        Exit Sub
    End If

Exit_cmdAddNewDrugEpisode_Click:
    Exit Sub

Err_cmdAddNewDrugEpisode_Click:
    msgbox Err.Description
    Resume Exit_cmdAddNewDrugEpisode_Click
    
End Sub

 

[wazz]

Does ac2K support OpenArgs?

yes .

 

[RuralGuy]

Thanks Wazz!

 

[wazz]

sure thing.

that db is a mystery to me. i think you're on the right track, but there might be other things, too. the only thing i could find so far (quite bizarre) is that when i entered a new record into the new drug episode form, the PtID (FK) increased by one, along with the PK. something strange there. (i turned on the nav buttons to advance to the next record.) no more time now.

 

[Bat17]

Not had time to check out the sample but A2K is fussier about queris than A97 so it may be in the underlying query.

Peter

 

[Ally]

Thanks for the comments - will have another look at the query and the FK increment too.

It's weird cos if I do it via the query alone, then it works!

 

[Ally]

I made the Format of the PtID on the Add New Drug Episode form defaulting to that of the PtID on the previous frmPatient to prevent it incrementing, and it "appears" to be okay now!

Thanks for your help.