Recordset.Clone show error:"Data type mismatch in criteria express

L

ldiaz

I'm using this code in another form where [WO_ID] is set as Number and it
works perfectly,

but now I having problems in a new form where the field [WO_ID] is set as
Text, because I have Work Order numbers like:029456, D45122, the WO contain
more digits at the time to scan the barcode, but i'm getting just 6 left
digits.

could you help me with this, I want the same operation, the unique diferece
is that on this new form the field is text.

see code.
==========================================



Private Sub ScanWO_AfterUpdate()
On Error GoTo Err_ScanWO_Click

'To get only WOs 6 Digits
WOnumber = Left([ScanWO], 6)

'To find the record stored in the DispatchTable
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[WO_ID] = " & WOnumber
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_ScanWO_Click:
Exit Sub
Err_ScanWO_Click:
MsgBox Err.Description
Resume Exit_ScanWO_Click
End Sub

=============================================
 
L

ldiaz

Hi UpRider
I got a problem, if the WO does not exist it must show this message:

MsgBox "Error, please check if the WO scanned is on the DB!", vbCritical,
"Error, WO not found!"

but now if I scan a WO tha does not exist, it does not show the msg, and it
refresh the form at the first WO of the table, how can I fix this?



Thanks
 
L

ldiaz

Hi UpRider
I got a problem, if the WO does not exist it must show this message:

MsgBox "Error, please check if the WO scanned is on the DB!", vbCritical,
"Error, WO not found!"

but now if I scan a WO tha does not exist, it does not show the msg, and it
refresh the form at the first WO of the table, how can I fix this?



Thanks

--
Lorenzo Díaz
Cad Technician


UpRider said:
rs.FindFirst "[WO_ID] = " & chr$(39) & WOnumber & chr$(39)

UpRider

ldiaz said:
I'm using this code in another form where [WO_ID] is set as Number and it
works perfectly,

but now I having problems in a new form where the field [WO_ID] is set as
Text, because I have Work Order numbers like:029456, D45122, the WO
contain
more digits at the time to scan the barcode, but i'm getting just 6 left
digits.

could you help me with this, I want the same operation, the unique
diferece
is that on this new form the field is text.

see code.
==========================================



Private Sub ScanWO_AfterUpdate()
On Error GoTo Err_ScanWO_Click

'To get only WOs 6 Digits
WOnumber = Left([ScanWO], 6)

'To find the record stored in the DispatchTable
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[WO_ID] = " & WOnumber
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_ScanWO_Click:
Exit Sub
Err_ScanWO_Click:
MsgBox Err.Description
Resume Exit_ScanWO_Click
End Sub

=============================================
Click to expand...
Click to expand...
 
U

UpRider

Add a new event procedure for the before update event:

Private Sub ScanWO_BeforeUpdate(Cancel As Integer)
' Display message if WO is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If Len(ScanWO & vbNullString) = 0 Then
strMsg = "Error, please check if the WO scanned is on the DB!"
strTitle = "Error, WO not found!"
intStyle = vbOKOnly + vbCritical
MsgBox strMsg, intStyle, strTitle
Cancel = True
End If

This code will not allow the user to procede unless something is in ScanWO.
A more appropriate error message here would be
"WO is a required entry, it cannot be blank"

HTH, UpRider
 
U

UpRider

Sorry, I misread your last problem. The beforupdate code can be left in,
but change the afterupdate like this:

Private Sub ScanWO_AfterUpdate()
On Error GoTo Err_ScanWO_Click

'To get only WOs 6 Digits
WOnumber = Left([ScanWO], 6)

'To find the record stored in the DispatchTable
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[WO_ID] = " & chr$(39) & WOnumber & chr$(39)
'''''' added
If rs.NoMatch Then
MsgBox " your error message ", _
vbOKOnly + vbCritical, " W O N O T F O U N D
"
Screen.PreviousControl.SetFocus
ScanWO.SetFocus
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
' advance to the next control if you wish.... Me.whatever.SetFocus
End If
'''''' end added
' not needed here If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_ScanWO_Click:
Exit Sub
Err_ScanWO_Click:
MsgBox Err.Description
Resume Exit_ScanWO_Click
End Sub
 
L

ldiaz

Thanks, now it works fine.

LD
--
Lorenzo Díaz
Cad Technician


UpRider said:
Sorry, I misread your last problem. The beforupdate code can be left in,
but change the afterupdate like this:

Private Sub ScanWO_AfterUpdate()
On Error GoTo Err_ScanWO_Click

'To get only WOs 6 Digits
WOnumber = Left([ScanWO], 6)

'To find the record stored in the DispatchTable
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[WO_ID] = " & chr$(39) & WOnumber & chr$(39)
'''''' added
If rs.NoMatch Then
MsgBox " your error message ", _
vbOKOnly + vbCritical, " W O N O T F O U N D
"
Screen.PreviousControl.SetFocus
ScanWO.SetFocus
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
' advance to the next control if you wish.... Me.whatever.SetFocus
End If
'''''' end added
' not needed here If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_ScanWO_Click:
Exit Sub
Err_ScanWO_Click:
MsgBox Err.Description
Resume Exit_ScanWO_Click
End Sub
Click to expand...
 
D

David W. Fenton

Private Sub ScanWO_AfterUpdate()
On Error GoTo Err_ScanWO_Click

'To get only WOs 6 Digits
WOnumber = Left([ScanWO], 6)

'To find the record stored in the DispatchTable
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[WO_ID] = " & WOnumber
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_ScanWO_Click:
Exit Sub
Err_ScanWO_Click:
MsgBox Err.Description
Resume Exit_ScanWO_Click
End Sub
Click to expand...

This kind of code makes me nuts.


First off, you've cloned the forms recordset when you could have
just as easily have used the existing .RecordsetClone.

Second, you haven't closed your recordset clone and cleared the
recordset variable you initialized. So, somewhere you should have:

rs.Close
Set rs = Nothing

But, more serious to me is that the recordset variable is just
completely unnecessary. The code can be vastly simplified to:

With Me.RecordsetClone
.FindFirst "[WO_ID] = " & WOnumber
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With

There is no cleanup required after this.

For a clear explanation of the difference between Me.Recordset.Clone
and Me.RecordsetClone, see:

http://trigeminal.com/usenet/usenet022.asp?1033
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Barcode question? 4
How make the same Operation as ComboBox using a Bar Code Reader 2
Type Mismatch 14
Recordset.Clone 2
baffling type mismatch error 7
How extract data and then make an after event based in extracted n 8
Data Type problem 4
Bookmark feature on a data entry form 1

Top