CODEWITHSUNDEEP
sqlms-accessvba
santaaimonce
santaaimonce

Reputation: 144

VBA Code for an Update button on a form

I have a form that has an unbound combobox (cboJack) and 7 Unbound Text Fields (txtID, txtSwitch, txtPortNumber, txtVoiceVLAN, txtdataVLAN, txtCableNumber, txtcomments3)

Whenever I choose an option from the combobox, the 7 text fields are autopopulated through the use of the following code:

Private Sub cboJack_Change()
Me.txtID = Me.cboJack.Column(0)
Me.txtSwitch = Me.cboJack.Column(2)
Me.txtPortNumber = Me.cboJack.Column(3)
Me.txtVoiceVLAN = Me.cboJack.Column(4)
Me.txtdataVLAN = Me.cboJack.Column(5)
Me.txtCableNumber = Me.cboJack.Column(6)
Me.txtComments3 = Me.cboJack.Column(7)
End Sub

I have created an update button on the form so that after I select value from the combobox and everything autopopulates, I can edit whatever fields I want, and then press the update button to save everything. Some of the fields are number fields and some are text fields. For some reason I'm getting a syntax error. Here is the code I have for my command button.

Private Sub cmdUpdate_Click()
Dim strSQL As String

strSQL = "UPDATE Switches SET Switch = '" & Me.txtSwitch & "', [Port Number] = " & Me.txtPortNumber & ", [Voice VLAN] = " & Me.txtVoiceVLAN & ", [Data VLAN] = " & Me.txtdataVLAN & ", [Cable Number] = " & Me.txtCableNumber & ", [Comments] = '" & Me.txtComments3 & "' WHERE Jack = '" & Me.cboJack & "'"

DoCmd.RunSQL (strSQL)
End Sub

The number type columns are, Port Number, Voice VLAN, DataVLAN and Cable Number. ID is autonumber, and the rest are text columns.

I think the reason why there's a problem is that some of the fields in Port Number, Voice VLAN, data VLAN and Cable Number don't have any values (Some of the values are null)

Any suggestions on how to fix this?

Thanks in advance.

Upvotes: 0

Views: 1489

Answers (2)

Tom Robinson
Tom Robinson

Reputation: 1910

I recommend not using a SQL Update at all in your case. Other than the Null issue, your Update will also fail if someone enters a non-numeric character in one of the numeric fields, or an apostrophe in the Comments. (or worse: think "SQL injection")

When working with textboxes and text fields, Access cheerfully replaces empty strings with Null. You can either fight against it, or just "go with the flow" and use a method that handles empty strings and apostrophes safely and with no extra work.

Here is my code:

Private Sub cmdUpdate_Click()
With CurrentDb.OpenRecordset("Switches")
    .FindFirst ("Jack = '" & cboJack & "'")
    If .NoMatch Then
        '' add a new record perhaps?
    Else
        .Edit
        .Fields("Switch") = txtSwitch
        .Fields("Port Number") = txtPortNumber
        .Fields("Voice VLAN") = txtVoiceVLAN
        .Fields("Data VLAN") = txtdataVLAN
        .Fields("Cable Number") = txtCableNumber
        .Fields("Comments") = txtComments3
        .Update
    End If
End With
End Sub

Upvotes: 0

parakmiakos
parakmiakos

Reputation: 3020

Yes it is most likely that the problem is caused by Null values in your non-string elements.

Here is a quick and easy fix:

strSQL = "UPDATE Switches SET Switch = '" & Me.txtSwitch & "', " & _
" [Port Number] = " & Nz(Me.txtPortNumber,"NULL") & ", " & _
" [Voice VLAN] = " & Nz(Me.txtVoiceVLAN,"NULL") & ", " & _
" [Data VLAN] = " & Nz(Me.txtdataVLAN,"NULL") & ", " & _
" [Cable Number] = " & Nz(Me.txtCableNumber,"NULL") & ", " & _
" [Comments] = '" & Me.txtComments3 & "' WHERE Jack = '" & Me.cboJack & "'"

So you actually replace the null values (causing the sql statement to have syntax errors such as : [column] = , [column2] = , ) with the string NULL

Sample result : [column] = NULL, [column2] = NULL,

Edit : Using IIf to check for an empty string

strSQL = "UPDATE Switches SET Switch = '" & Me.txtSwitch & "', " & _
" [Port Number] = " & IIf(Len(Nz(Me.txtPortNumber)) = 0 , "NULL",Me.txtPortNumber) & ", " & _
" [Voice VLAN] = " & IIf(Len(Nz(Me.txtVoiceVLAN)) = 0 , "NULL", Me.txtVoiceVLAN) & ", " & _
" [Data VLAN] = " & IIf(Len(Nz(Me.txtdataVLAN)) = 0 , "NULL", Me.txtdataVLAN)& ", " & _
" [Cable Number] = " & IIf(Len(Nz(Me.txtCableNumber)) = 0 , "NULL", Me.txtCableNumber) & ", " & _
" [Comments] = '" & Me.txtComments3 & "' WHERE Jack = '" & Me.cboJack & "'"

Upvotes: 1

Related Questions