OpenArgs data type mismatch in criteria expression

[Guest]

Hi

I'm trying to open an existing record in an amendment form.

The user enters the ref_number in form with a text box and button and this
opens new form passing the ref_number as OpenArgs.

I get an error 'Runtime 3464 data type mismatch in criteria expression.' and
the debugger highlights the line begining Set rst = db.openrecordset ....etc

Here's the code

--------------------------------------------
Private Sub Form_Load()


Dim db As Database, rst As Recordset, link As Integer

link = Me.OpenArgs

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tbl_main_data WHERE
(ref_number = '" & link & "')")

rst.MoveFirst

------------------------------------------

When I hover the pointer over & link & it shows the numerical value in
tooltip.
Also ref_numberis the primary key (autonumber).

Currently I have the form working with it being bound to the query but this
means that users are opening the form and changing data on the form without
realising that the changes are immediately reflected in the data. I'm aiming
to to populate the form, allow them to make changes and then only commit to
the change by pressing a save button.

Any help appreciated

 

[Allen Browne]

Try:
If IsNumeric(Me.OpenArgs) Then
link = Val(Me.OpenArgs)
End If

 

[Keith Wilby]

Hi

I'm trying to open an existing record in an amendment form.

The user enters the ref_number in form with a text box and button and this
opens new form passing the ref_number as OpenArgs.

I get an error 'Runtime 3464 data type mismatch in criteria expression.'
and
the debugger highlights the line begining Set rst = db.openrecordset
....etc

Here's the code

--------------------------------------------
Private Sub Form_Load()


Dim db As Database, rst As Recordset, link As Integer

link = Me.OpenArgs

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tbl_main_data WHERE
(ref_number = '" & link & "')")

rst.MoveFirst

It's a number so remove the single quotation marks (I think one pair of
brackets is redundant too):

Set rst = db.OpenRecordset("SELECT * FROM tbl_main_data WHERE [ref_number] =
" & link & ")"

HTH - Keith.
www.keithwilby.com

 

[Guest]

I've amended the offending line to:
Set rst = db.OpenRecordset("SELECT * FROM tbl_main_data WHERE [ref_number] =
" & link & "")
and it works now.

Many thanks to you both

Hi

I'm trying to open an existing record in an amendment form.

The user enters the ref_number in form with a text box and button and this
opens new form passing the ref_number as OpenArgs.

I get an error 'Runtime 3464 data type mismatch in criteria expression.'
and
the debugger highlights the line begining Set rst = db.openrecordset
....etc

Here's the code

--------------------------------------------
Private Sub Form_Load()


Dim db As Database, rst As Recordset, link As Integer

link = Me.OpenArgs

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tbl_main_data WHERE
(ref_number = '" & link & "')")

rst.MoveFirst

It's a number so remove the single quotation marks (I think one pair of
brackets is redundant too):

Set rst = db.OpenRecordset("SELECT * FROM tbl_main_data WHERE [ref_number] =
" & link & ")"

HTH - Keith.
www.keithwilby.com