stLinkCriteria and field not found

[hockeylvr]

I'm sure I posted this last week but it is no where on the forum to be found so I'm trying again.

I am trying to do a simple thing with stLinkCriteria and it simply will not work. I am getting the error "..Access cannot find the field 'clientid' referred to".

Private Sub Command2_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmnotes"
stLinkCriteria = "[clientid]= " & Me![clientid]
DoCmd.OpenForm stDocName, , , stLinkCriteria

All I'm trying to do is select a client from my combo box on frmclients, hit my command button and have frmnotes open with the details based on the clientid chosen in frmclients.

frmclients combo box - Unbound - SELECT tblclients.clientid, tblclients.lname & ", " & tblclients.fname FROM tblclients;

clientid in tblclients is an autonumber and type "number" in tblnotes.

frmnotes is based on qrynotes - sql below
SELECT tblnotes.clientid, tblclients.lname, tblclients.fname, tblnotes.notedate, tblnotes.note
FROM tblclients INNER JOIN tblnotes ON tblclients.clientid = tblnotes.clientid;


Would appreciate any ideas on what simple item I'm missing in my coding.

Thanks!

 

[addy]

I think you have to explicitly point to your frmclients Form as in the code below:

Otherwise, it will treat the Me! as the Form you are opening and will therefore error.

Private Sub Command2_Click()
 Dim stDocName As String    
    Dim stLinkCriteria As String  
    stDocName = "frmnotes"    
    stLinkCriteria = "[clientid]= " & Forms![frmclients]![clientid]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[hockeylvr]

Thanks for the reply, what you stated makes sense and I've changed my code but am still getting the same error. I've checked the spelling in all of my fields against the tables and query and it all matches perfectly. I'm thinking I need to try it with a SQL string and see if that approach will work.

Thanks again
Toni

 

[PHV]

What about this ?
stLinkCriteria = "tblnotes.clientid=" & Me![clientid]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

What about this ?
stLinkCriteria = "tblnotes.clientid=" & Me![frmclients combo box].Column(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hockeylvr]

Got it - thanks for all the help. Just getting back into Access and new it was a simple thing that I just couldn't remember!

stLinkCriteria = "[clientid]=" & Me![cboname]