open a form of other table for editing

[afnan_88]

hello all,

i have a form that is not related to any table
its act like a main screen so i can use it to add new records and edit for all the other forms

in my code i have a button that goes to a form and open a new recored if no one existed .. if its exist open the form for edit
text39 is were the user enter the id

On Error GoTo Err_pro900_Click

    Dim stDocName As String
    Dim stLinkCriteria As String


 x = DCount("[104_National ID]", "INITIAL ASSESSMENT", "[104_National ID] = " & Chr(34) & Me.Text39 & Chr(34))
[COLOR=SeaGreen]' first check if the id exists in a parent table[/COLOR]

If x = 0 Then
[COLOR=SeaGreen]' if not existed on the parent table[/COLOR]
    MsgBox "The National ID you have entered does not exist !"
    Me.Text39.SetFocus
    Me.Text39.SelStart = 0
   
    
Else
[COLOR=SeaGreen]' if exists[/COLOR] [COLOR=SeaGreen]on[/COLOR] [COLOR=SeaGreen]the parent table[/COLOR]
i = "pID=" + Me.Text39 + ":FU=0" [COLOR=SeaGreen]' for the openArgs[/COLOR]

y = DCount("[patientID]", "900_IMAGING", "[patientID] = " & Chr(34) & Me.Text39 & Chr(34)) [COLOR=SeaGreen]' check it the recored exists or not[/COLOR]

If y = 0 Or y = Null Then
[COLOR=SeaGreen]' if not exists enter a new one[/COLOR]
    
    stDocName = "900_IMAGING"
    stLinkCriteria = " Me.text39 =" & "'" & [patientID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , i

Else
[COLOR=SeaGreen]' if  exists open the form for edit[/COLOR]
    
    stDocName = "900_IMAGING"
    stLinkCriteria = " Me.text39 =" & "'" & [patientID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, , i
    
   
   End If
       End If

Exit_pro900_Click:
    Exit Sub

Err_pro900_Click:
    MsgBox Err.Description
    Resume Exit_pro900_Click
    
End Sub

it works excellent when i add the id for the first time
but i get a runtime error(cant find the field "|" referred in your expression.) when i want to open the form for the 2nd time 'for edit'

any solutions are appreciated
thanx in advance

 

[hk1]

I think this line of code:

stLinkCriteria = " Me.text39 =" & "'" & [patientID] & "'"

Should be changed to this:

stLinkCriteria = "[patientID] = '" & Me.text39 & "'"

 

[afnan_88]

ohhh , it was so silly !

thanq very very very much i didnt notice that