CODEWITHSUNDEEP
ms-accessvbasql-update
whatwhatwhat
whatwhatwhat

Reputation: 2256

MS Access VB: How do I update a table with a parametrized query ONLY with textboxes that are not empty?

I have some VBA code that will update a table based on a form. It works great, except that I want the table to only update wherever the user fill in info. If that textbox is instead blank, do no update that field in the table. Below is the working update code. The line that starts with If query.Parameters("P1")... is my attempt at trying to make an If statement that will find which textbox values are Null and then ignore those, but I don't know if that's even going in the right direction.

Private Sub Command133_Click()
    'Update row for downtime

    Dim dbsCurrent As Database
    Set dbsCurrent = CurrentDb

    ', suffix, production_date, reason, downtime_minutes, comment              ,'" & CInt(Me.Text118) & "','" & CDate(Me.Text126) & "','" & Me.Text121 & "','" & CDbl(Me.Text123) & "','" & Me.Text128 & "'
    'dbsCurrent.Execute " INSERT INTO tbl_Downtime (production_date) SELECT #" & Me.Text126 & "# FROM tbl_Downtime As t WHERE t.ID = " & Me.Text135 & ";"
    'dbsCurrent.Execute "UPDATE tbl_Downtime SET job = '" & Me.Text116 & "', suffix = '" & Me.Text118 & "', production_date = #" & Me.Text126 & "#, reason = '" & Me.Text121 & "', downtime_minutes = " & Me.Text123 & ", comment = '" & Me.Text128 & "', shift = '" & Me.Text144 & "' WHERE t.ID = " & Me.Text135 & ";"

    Dim query As QueryDef
    Dim sql As String

    For Each query In CurrentDb.QueryDefs
      If query.Name = "UpdateDowntime" Then
        Exit For
      End If
    Next query

    If query Is Nothing Then
      sql = "parameters " & _
        "P1 text, P2 text, P3 Date, P4 Text, P5 Number, P6 Text, P7 Text, P8 Number;" & _
        "UPDATE [tbl_Downtime] " & _
        "SET job = [P1], suffix = [P2], production_date = [P3], reason = [P4], downtime_minutes = [P5], comment = [P6], shift = [P7] " & _
        "WHERE[tbl_Downtime].id = [P8]"

        '"(job, suffix, production_date, reason, downtime_minutes, comment, shift) " & _
       '" VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7]) WHERE[tbl_Downtime].id = [P8]"

      Set query = CurrentDb.CreateQueryDef("UpdateDowntime", sql)
    End If

    query.Parameters("P1").Value = Me.Text116
    query.Parameters("P2").Value = Me.Text118
    query.Parameters("P3").Value = Me.Text126
    query.Parameters("P4").Value = Me.Text121
    query.Parameters("P5").Value = Me.Text123
    query.Parameters("P6").Value = Me.Text128
    query.Parameters("P7").Value = Me.Text144
    query.Parameters("P8").Value = Me.Text135


    If query.Parameters("P1").Value = "" Then Set query.Parameters("P1").Value = job End If ' WHERE [tbl_Downtime].id = [P8] END


    query.Execute

Upvotes: 0

Views: 51

Answers (2)

Gustav
Gustav

Reputation: 55816

You can do it this way:

"SET job = Nz([P1], job), suffix = Nz([P2], suffix), production_date = Nz([P3], production_date), reason = Nz([P4], reason), downtime_minutes = Nz([P5], downtime_minutes), comment = Nz([P6], comment), shift = Nz([P7], shift) " & _
"WHERE [tbl_Downtime].id = Nz([P8], -[id])"

Upvotes: 0

user1641172
user1641172

Reputation:

An empty string is not the same as a null value in a database, so you need to change the parameter value to nothing if you want it to equate to null (or default value if one exists).

You could either conditionally set the parameter value like this:

If textbox1.text <> "" then
    query.Parameters("P1").Value = textbox1.text
End if

or you could write a function to set the parameter to nothing if the textbox is empty:

Function NothingIfEmpty(value As String)
    If value = "" Then
        NothingIfEmpty = Nothing
    Else
        NothingIfEmpty = value
    End If
End Function

and use it like this:

query.Parameters("P1").Value = NothingIfEmpty(textbox1.text)
query.Parameters("P2").Value = NothingIfEmpty(textbox2.text)

Upvotes: 1

Related Questions