Error when trying to goto specific record on form - arguments wrongtype.

[Rachel]

Hi, I am trying to goto a specific record in a form afterI have
requeried the form but unfortunately the code errors.

This is really geting to me, I've been trying to sort this for days
now so any help would be greatly appreciated. The error mssage is:

"Arguments are of wrong type, are out of acceptable range, or are in
conflict with one another"

This is my code:

(txt_NSS is a textbox on the form which displays the current Patient
ID)
(NSS_Number is the name of the id field from the record source)

Private Sub cmd_Add_Case_Click()
On Error GoTo Err_cmd_Add_Case_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim tbNSS As String

tbNHS = Me.txt_NSS '* stores patient PK *
stDocName = "frm_AddCase"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmd_Add_Case_Click:

Forms!frm_SearchPatient.Requery
With Me.RecordsetClone
..MoveFirst
..Find "NSS_Number = " & tbNSS '* this is where it errors *
..Close
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Exit Sub
Err_cmd_Add_Case_Click:
MsgBox Err.Description
Resume Exit_cmd_Add_Case_Click
End Sub

 

[bcap]

..Find "NSS_Number = """ & tbNSS & """"

Presumably, also, the reference to tbNHS should read tbNSS.

 

[Rachel]

.Find "NSS_Number = """ & tbNSS & """"

Presumably, also, the reference to tbNHS should read tbNSS.














- Show quoted text -

Oops, I must have typed over it by accident - the actual code reads
TBNSS not tbNHS.

Thanks
R

 

[Sylvain Lafontaine]

If this for an ADP project file or for a MDB /ACCDB file with ODBC linked
tables?

For ADP, you must use ADO and unlike for DAO, there is no NoMatch method in
ADO. Also, if NSS_Number is an alphanumeric, you must enclose its values
between single quotes.

In my answer to your previous post, I gave you an exemple using ADO.

 

[Rachel]

If this for an ADP project file or for a MDB /ACCDB file with ODBC linked
tables?

For ADP, you must use ADO and unlike for DAO, there is no NoMatch method in
ADO.  Also, if NSS_Number is an alphanumeric, you must enclose its values
between single quotes.

In my answer to your previous post, I gave you an exemple using ADO.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)














- Show quoted text -

Many thanks for your help, it is an ADP project. I have now changed
all the code but i am now getting an error 3704 - operation is not
allowed when the object is closed. The code is in the form that I want
to set the bookmark and now it fails on the Set rs =
Me.Recordset.Clone line.

I think i'm going insane......

My code: (tbNSS is the text box value on the form before I requery)

Sub SearchRequery(tbNSS)

Dim rs As ADODB.Recordset 'NEW
Set rs = Me.Recordset.Clone

Me.Requery

rs.MoveFirst
rs.Find "[NSS_Number] = " & tbNSS
rs.Close

Me.Bookmark = rs.Bookmark

End Sub

Your thoughts would be incredibly helpful.

Many Thanks
Rachel

 

[Sylvain Lafontaine]

Move the Requery before calling the Recordset.Clone method. The code is
probably failing because there is no current recordset for the form when you
call it.

Also, if tbNSS is an alphanumeric value, you must enclose its value between
single quotes.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

If this for an ADP project file or for a MDB /ACCDB file with ODBC linked
tables?

For ADP, you must use ADO and unlike for DAO, there is no NoMatch method
in
ADO. Also, if NSS_Number is an alphanumeric, you must enclose its values
between single quotes.

In my answer to your previous post, I gave you an exemple using ADO.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)














- Show quoted text -

Many thanks for your help, it is an ADP project. I have now changed
all the code but i am now getting an error 3704 - operation is not
allowed when the object is closed. The code is in the form that I want
to set the bookmark and now it fails on the Set rs =
Me.Recordset.Clone line.

I think i'm going insane......

My code: (tbNSS is the text box value on the form before I requery)

Sub SearchRequery(tbNSS)

Dim rs As ADODB.Recordset 'NEW
Set rs = Me.Recordset.Clone

Me.Requery

rs.MoveFirst
rs.Find "[NSS_Number] = " & tbNSS
rs.Close

Me.Bookmark = rs.Bookmark

End Sub

Your thoughts would be incredibly helpful.

Many Thanks
Rachel