david david - 1 year ago 45
SQL Question

SQL - Save record with listbox and textbox change

I have a list box - The user clicks one of the results in the list box that's populated from a table.

When they click one of the items in a list box the text boxes populate the results that are in the table

On the textbox I have on change code of:

DoCmd.RunSQL "UPDATE tbl_ComplaintsCoded SET [TicketNumber] = '" & Text3 & "' WHERE ID = " & List1.Column(0)

Text3 shows the Ticket number
Text5 shows the department

Its the department that the user is trying to change before getting an error of:

data type mismatch in criteria expression

Thanks for the help

Answer Source

Just for fun, I rewrote what you put together in something a little more elegant with some basic error handling and a little more streamlined.

Option Compare Database
'Added the option explicit to verify your variables
Option Explicit

Private Sub Button_Click()
On Error GoTo Button_Click_Err_Handler

Dim rs As DAO.Recordset

'Is your TicketNumber column a Text data type?  Me.List1.Column(0) should return a variant value, so assuming
    'your TicketNumber column is of a number type as the name implies, I think you could just use:
'Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_name WHERE TicketNumber = " & Me.list1.Column(0))

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_name WHERE TicketNumber = '" & Me.list1.Column(0) & "'")

'You should always check for BOF and EOF if you're checking if there is no record.
If rs.BOF And rs.EOF Then
    MsgBox "You have not selected a record, nothing to save!", vbInformation
    'Exiting here, instead of wrapping the entire sub in the if...  ...end if statement.  You could also just use "Exit Sub", but I added
    'the exit and error handling to make it a little more graceful.
    GoTo Button_Click_Exit
End If

'I wrapped the rs edits in a with statement and used the direct column name operator ! instead of the collection searching rs() feature.
'For illustration, I wrapped a few of the references in the Nz() function.  If none of the fields are ever null, bravo to you for excellent
'database design and database users discipline, but I almost always have a couple columns where nulls are allowed.
With rs

    'Top Categories
    !Business = Me.Text5
    !Status = Me.Text8
    !MailDate = Me.Text10

    'Complaint Detail Section
    !Type = Me.Text19
    !Sub = Me.Text21
    !c = Me.Text23

    'Complaint Coding Section
    !touch2 = Me.Combo29
    !touch1 = Me.Combo33
    !Cause2 = Me.Combo31
    !cause1 = Me.Combo35

    'CS Account Details Section
    !Account = Me.Text39
    !Feed = Me.Combo41

    'Logged Audit User
    !LoggedUser = Me.Text43
    !DateTimeLogged = Me.Text49

End With

On Error Resume Next
Exit Sub

MsgBox Err.Number & Err.Description, vbOKOnly + vbCritical, "Error"
Resume Button_Click_Exit

End Sub