Store primary key but show another field

[viperpurple]

I am creating a database of all the equipment that we have and also a record of when it is checked for safety and inventory.

I am currently having problems getting the primary key stored in the database but the description for each item been shown on the form to the user. I have currently got around the problem using code to store the data in two seperate places but it's a bit clunky and currently has an error where the data doesn't seem to get stored and retrieved succesfully from the table 'tbl_equipment_checks'.

Ideally i'd like to not have to use the work around of storing the data in two seperate places ('equipment_checked' & 'equipment_checked_id'). but i can provide more information about the current errors if the no other method is possible.

My database is organised as described below:

tbl_equipment - Table listing all the equipment (includes 'description' & 'last_check')
tbl_equipment_checks - Table listing everytime an equipment check is carried out (includes 'equipment_checked' & 'equipment_checked_id')
equipment_checks - Form for user input of the equipment checks (includes 'equipment_list' = listbox(unbound) & 'equipment_checked' = listbox(unbound)

Code on the form is:

When the 'CLEAR' button is clicked to clear the listbox 'equipment_checked' and also the data for that record stored in the table 'tbl_equipment_checks'

Private Sub butt_clear_Click()
    Dim check As DAO.Recordset
        Set check = CurrentDb.OpenRecordset("SELECT * FROM tbl_equipment_checks")
        
        check.Edit
        check("equipment_checked").Value = ""
        check.Update
        Me.equipment_checked.RowSource = ""
        Me.equipment_checked.Requery
    
End Sub

When the 'ADD' button is clicked to add the selected items in 'equipment_list' to the listbox 'equipment_checked' and also store the data in the table 'tbl_equipment_checks'

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.RowSourceType = "Value List"
    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

When each record is loaded to make the list box show the current stored data

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

    Me.equipment_checked.RowSourceType = "Table/Query"
    Me.equipment_checked.RowSource = temp

End Sub

I hope this all makes sense, i've tried to lay it out as logically as possible.

Regards

Adam

 

[viperpurple]

How do you use the primary key to look up data in another field on the same record and then display that on the form?