Type Mismatch Error

[viperpurple]

Hey,

I get a 'type mismatch' error when i run this code, the field 'equipment_checked' is of type 'memo' and i understand that the rowsource can only hold 2048 characters but i have no idea how to get around either issue.

The idea is that i am using a list box to display data on a form and storing the rowsource line for each record in the table which then is used to populate the list box on the form.

I have a table that lists all the equipment and then a table that records when a equipment check is carried out and what is checked. The check form uses a list box populated from the equipment table for the user to choose from and then records which item is checked and then the idea is that if i lookup any item i can easily pull up when it was checked. The only unique field is the primary key but the user needs to see the description field. If there is a way to store the primary key and then display the description field on the form then all my problems would be solved.

Private Sub Form_Current()
Dim temp As String
 
    temp = CurrentDb.OpenRecordset("SELECT equipment_checked FROM tbl_equipment_checks")
    Me.equipment_checked.RowSource = temp
End Sub

I can send the database but it's about 40MB

Regards

Adam

 

[boblarson]

two things.

1. you are trying to open a recordset with a string variable instead of a recordset variable.

2. You would be trying to assign a recordset object to something which is expecting a string.

The correct way would be:

Private Sub Form_Current()
Dim temp As String
    
    temp = "SELECT equipment_checked FROM tbl_equipment_checks"

    Me.equipment_checked.RowSource = temp

End Sub

 

[viperpurple]

Bob i just tried your suggestion and it just makes my list box show 'SELECT equipment_checked FROM tbl_equipment_checks' rather than looking in the table.
I guess this is because it just sees it as a string, i tried removing the quotes but then it hasn't been told where the recordset is so it has a compile error.

I have the rowsource.type set to value list and as i populate the listbox with values selected from another listbox linked to the table of equipment.

This is the code that populates the listbox

Private Sub butt_add_Click()
Dim oItem As Variant
    Dim sTemp As String
    Dim lTemp As String
    Dim iCount As Integer
    Dim check As DAO.Recordset
    Dim varItm As Variant
     
    Set check = CurrentDb.OpenRecordset("SELECT * FROM tbl_equipment_checks")
    iCount = 0
    
    If Not Me.equipment_checked.RowSource = "" Then
        sTemp = Me.equipment_checked.RowSource & ";"
        lTemp = check("equipment_checked_id").Value & vbCrLf
    Else
        sTemp = ""
        lTemp = ""
    End If
            
    If Me.equipment_list.ItemsSelected.Count <> 0 Then
        For Each oItem In Me.equipment_list.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me.equipment_list.Column(3, oItem)
                lTemp = lTemp & Me.equipment_list.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & ";" & Me.equipment_list.Column(3, oItem)
                lTemp = lTemp & vbCrLf & Me.equipment_list.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
        MsgBox sTemp & vbCrLf & lTemp, vbOKOnly, "Title"
    check.Edit
    check("equipment_checked_id").Value = lTemp
    check.Update
    Me.equipment_checked.RowSource = sTemp
    Me.equipment_checked.Requery
    
'    With Me.equipment_list
'
'        For Each varItm In .ItemsSelected
'            .Selected(varItm) = False
'        Next varItm
'
'    End With
End Sub

 

[boblarson]

Bob i just tried your suggestion and it just makes my list box show 'SELECT equipment_checked FROM tbl_equipment_checks' rather than looking in the table. I guess this is because it just sees it as a string, i tried removing the quotes but then it hasn't been told where the recordset is so it has a compile error.

The RowSource Type has to also be set as TABLE/QUERY instead of Value List.

 

[viperpurple]

just figured the reason for the error, see the edited reply above