Data Type Mismatch Error - Procedure ran from Form

[Shaz2010]

Help anyone? I am fairly new at VB. Am struggling with a script that was created and used with an older version of a Database. Since then the DB is updated and and the tables names and 2 fields to-be-updated are identical datatypes.

Option Compare Database
Option Explicit

Private Sub cmd_XXX_XWIRE_Click()
Dim label_string As String
Dim label_temp_string As String
Dim wire_int As Long

Dim x As Integer
Dim y As Integer
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Dim temp_dbs As Database
Dim temp_rst As Recordset
Dim temp_strSQL As String

If Not MsgBox("Disable Triggers on WIRE Table, Are you sure", vbOKCancel, "Are you sure") = vbOK Then
Exit Sub
End If

DoCmd.Hourglass True

'*******WIRE END FIX*******START
strSQL = ""
Set dbs = CurrentDb()
strSQL = "SELECT wire.WIRE_LABEL_SIDE1, wire.WIRE_LABEL_SIDE2, wire.WIRE_TAG, wire.POLARITY,"
strSQL = strSQL & " WIRE_LABEL_CONVENTION.WIRE_LABEL_CONV_NAME, wire.WIRE_LABEL_CONV_STATUS"
strSQL = strSQL & " FROM WIRE INNER JOIN WIRE_LABEL_CONVENTION ON WIRE.WIRE_LABEL_CONV_ID = WIRE_LABEL_CONVENTION.WIRE_LABEL_CONV_ID;"

Set rst = dbs.OpenRecordset(strSQL)

While (Not (rst.EOF))

'WIRE TAG NAME
If IsNull(rst!WIRE_TAG) Then
rst.Edit
rst!WIRE_TAG = "SPARE"
rst.Update
End If

x = Len(rst!WIRE_TAG)
y = 1
label_string = ""

While (Not y > x)
If Not (Mid(rst!WIRE_TAG, y, 1)) = " " Then
label_string = label_string & Mid(rst!WIRE_TAG, y, 1)
End If
y = y + 1
Wend
'WIRE POLARITY
If rst!POLARITY = " " Then
rst.Edit
rst!POLARITY = "None"
rst.Update
End If

'WIRE END LABEL
Select Case rst!WIRE_LABEL_CONV_NAME
Case "Wire"
label_string = label_string
Case "Wire plus _C"
label_string = label_string & "_C"
Case "Wire plus _NC"
label_string = label_string & "_NC"
Case "Wire plus _NO"
label_string = label_string & "_NO"
Case "Wire plus _PWR"
label_string = label_string & "_PWR"
Case "Wire plus +24VDC"
label_string = label_string & "(+24V)"
Case "Wire plus 0V"
label_string = label_string & "(0V)"
Case "Wire plus -1"
label_string = label_string & "-1"
Case "Wire plus -1 FF"
label_string = label_string & "-1"
Case "Wire plus -2"
label_string = label_string & "-2"
Case "Wire plus -2 FF"
label_string = label_string & "-2"
Case "Wire plus -3"
label_string = label_string & "-3"
Case "Wire plus -4"
label_string = label_string & "-4"
Case "Wire plus -5"
label_string = label_string & "-5"
Case "Wire plus -6"
label_string = label_string & "-6"
Case "Wire plus -ALRM-1"
label_string = label_string & "-ALRM-1"
Case "Wire plus -ALRM-2"
label_string = label_string & "-ALRM-2"
Case "Wire plus -AUTO"
label_string = label_string & "-AUTO"
Case "Wire plus -H"
label_string = label_string & "-H"
Case "Wire plus -JOG"
label_string = label_string & "-JOG"
Case "Wire plus -L"
label_string = label_string & "-L"
Case "Wire plus -N"
label_string = label_string & "-N"
Case "Wire plus -ON"
label_string = label_string & "-ON"
Case "Wire plus Polarity"
label_string = label_string & "(" & RTrim(rst!POLARITY) & ")"
Case "Wire plus -RN"
label_string = label_string & "-RN"
Case "Wire plus -SP"
label_string = label_string & "-SP"
Case "Wire plus -ST"
label_string = label_string & "-ST"
Case "Wire plus -V-"
label_string = label_string & "-V-"
Case "Wire plus -V+"
label_string = label_string & "-V+"
Case Else
label_string = label_string
End Select

Select Case label_string
Case "SPARE(+)"
label_string = "SPARE"
Case "SPARE(-)"
label_string = "SPARE"
End Select

If rst!WIRE_TAG = "SPARE" Then
label_string = "SPARE"
End If

rst.Edit
rst!WIRE_LABEL_CONV_STATUS = "A"
rst!WIRE_LABEL_SIDE1 = label_string
rst!WIRE_LABEL_SIDE2 = label_string
rst.Update
rst.MoveNext
Wend
rst.Close
dbs.Close

'*******WIRE END FIX*******END

'*******CROSS WIRE END FIX*******
strSQL = ""
Set dbs = CurrentDb()
strSQL = "SELECT WIRE_TERMINAL.WIRE_ID, PANEL.PANEL_NAME, CABINET_RACK.CABINET_RACK_NAME, RACK_POSITION.RACK_POS_NAME, APPARATUS.APPAR_NAME, PANEL_STRIP.strip_name, CHANNEL.CHANNEL_NAME, PANEL_STRIP_TERM.TERM_NUM, WIRE.WIRE_NOTE, WIRE.WIRE_LABEL_SIDE1, WIRE.WIRE_LABEL_SIDE2"
strSQL = strSQL & " FROM CABINET_RACK, RACK_POSITION, APPARATUS, PANEL, PANEL_STRIP, CHANNEL, PANEL_STRIP_TERM, WIRE, WIRE_TERMINAL"
strSQL = strSQL & " where WIRE.WIRE_ID = WIRE_TERMINAL.WIRE_ID and WIRE_TERMINAL.PANEL_ID = PANEL.PANEL_ID AND PANEL_STRIP_TERM.TERM_ID = WIRE_TERMINAL.TERM_ID AND CHANNEL.CHANNEL_ID = PANEL_STRIP_TERM.CHANNEL_ID AND PANEL_STRIP.strip_id = WIRE_TERMINAL.STRIP_ID AND PANEL_STRIP.panel_id = WIRE_TERMINAL.PANEL_ID AND APPARATUS.APPAR_ID = PANEL_STRIP.APPAR_ID AND RACK_POSITION.RACK_POS_ID = APPARATUS.RACK_POS_ID AND CABINET_RACK.RACK_ID = RACK_POSITION.RACK_ID AND PANEL.PANEL_TYPE_ID= 0 AND RACK_POSITION.RACK_POS_NAME Like 'Card*'AND WIRE_TERMINAL.CABLE_ID=1999 AND WIRE_TERMINAL.TERM_SIDE='L';"

Set rst = dbs.OpenRecordset(strSQL)****Quits here***

 

[MajP]

you need to show us where the code breaks. But likely you have a field in your table that is a Number and you are trying to set it equal to a string or the other way around.

Or it is one of your variables
Dim label_string As String
Dim label_temp_string As String
Dim wire_int As Long

Dim x As Integer
Dim y As Integer

and you are assigning a number to a string or vice versa.

 

[SkipVought]

You have no SPACE here...

Like 'Card*'AND 
            ^

Furthermore, it makes maintenance and understandability easier if your do something like this...

    strSQL = "SELECT"
    strSQL = strSQL & "  WIRE_TERMINAL.WIRE_ID"
    strSQL = strSQL & ", PANEL.PANEL_NAME"
    strSQL = strSQL & ", CABINET_RACK.CABINET_RACK_NAME"
    strSQL = strSQL & ", RACK_POSITION.RACK_POS_NAME"
    strSQL = strSQL & ", APPARATUS.APPAR_NAME"
    strSQL = strSQL & ", PANEL_STRIP.strip_name"
    strSQL = strSQL & ", CHANNEL.CHANNEL_NAME"
    strSQL = strSQL & ", PANEL_STRIP_TERM.TERM_NUM"
    strSQL = strSQL & ", WIRE.WIRE_NOTE"
    strSQL = strSQL & ", WIRE.WIRE_LABEL_SIDE1"
    strSQL = strSQL & ", WIRE.WIRE_LABEL_SIDE2"
    strSQL = strSQL & vbLf
    strSQL = strSQL & " FROM"
    strSQL = strSQL & "  CABINET_RACK"
    strSQL = strSQL & ", RACK_POSITION"
    strSQL = strSQL & ", APPARATUS"
    strSQL = strSQL & ", PANEL"
    strSQL = strSQL & ", PANEL_STRIP"
    strSQL = strSQL & ", CHANNEL"
    strSQL = strSQL & ", PANEL_STRIP_TERM"
    strSQL = strSQL & ", WIRE"
    strSQL = strSQL & ", WIRE_TERMINAL"
    strSQL = strSQL & vbLf
    strSQL = strSQL & "where WIRE.WIRE_ID              = WIRE_TERMINAL.WIRE_ID"
    strSQL = strSQL & "  and WIRE_TERMINAL.PANEL_ID    = PANEL.PANEL_ID"
    strSQL = strSQL & "  AND PANEL_STRIP_TERM.TERM_ID  = WIRE_TERMINAL.TERM_ID"
    strSQL = strSQL & "  AND CHANNEL.CHANNEL_ID        = PANEL_STRIP_TERM.CHANNEL_ID"
    strSQL = strSQL & "  AND PANEL_STRIP.strip_id      = WIRE_TERMINAL.STRIP_ID"
    strSQL = strSQL & "  AND PANEL_STRIP.panel_id      = WIRE_TERMINAL.PANEL_ID"
    strSQL = strSQL & "  AND APPARATUS.APPAR_ID        = PANEL_STRIP.APPAR_ID"
    strSQL = strSQL & "  AND RACK_POSITION.RACK_POS_ID = APPARATUS.RACK_POS_ID"
    strSQL = strSQL & "  AND CABINET_RACK.RACK_ID      = RACK_POSITION.RACK_ID"
    strSQL = strSQL & "  AND PANEL.PANEL_TYPE_ID       = 0"   '[b][red]check this one out[/red][/b]
    strSQL = strSQL & "  AND RACK_POSITION.RACK_POS_NAME Like 'Card*'"
    strSQL = strSQL & "  AND WIRE_TERMINAL.CABLE_ID    =1999" '[b][red]check this one out[/red][/b]
    strSQL = strSQL & "  AND WIRE_TERMINAL.TERM_SIDE   ='L';"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[boblarson]

Also, be sure to change this:

    Dim dbs As Database
    Dim rst As Recordset

to this:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com