To Update Query or Not To Update Query (1 Viewer)

[LGDGlen]

Hi All

as is always the way, i'm going back through the early vba code i wrote creating my database and applying the knowledge and experience i have gained since writing said bit of code.

I have been converting a lot of:

• Get recordset
• loop through recordset 1 record at a time
  • if record matches a certain criteria
    • update set of fields of record
  • end if
• end loop

into update queries

but i have hit a bit of code and am not sure how i'd go about changing it:

Private Sub btnUpdateSelected_Click()
    If IsNull(Me.txtSelected) Or Me.txtSelected = "" Or Me.txtSelected = "," Then
        Me.txtInfoMessages = "No records selected"
        Exit Sub
    End If
   
    Dim rs As DAO.Recordset
    Set rs = Form.Recordset
     With rs
        .MoveFirst
        Do While Not .EOF
            If Me.txtSelected Like "*," & !id & ",*" Then
                .Edit
                If Not (IsNull(Me.txtReferenceNumber)) And Me.txtReferenceNumber <> "" Then !reference = Me.txtReferenceNumber
                If Not (IsNull(Me.cboType)) And Me.cboType <> "" Then !type_id = Me.cboType
                If Not (IsNull(Me.cboSubType)) And Me.cboSubType <> "" Then !subtype_id = Me.cboSubType
                If Not (IsNull(Me.cboFromCompany)) And Me.cboFromCompany <> "" Then !from_id = Me.cboFromCompany
                If Not (IsNull(Me.cboToCompany)) And Me.cboToCompany <> "" Then !to_id = Me.cboToCompany
                If Not (IsNull(Me.cboCurrency)) And Me.cboCurrency <> "" Then !currency_id = Me.cboCurrency
                If Not (IsNull(Me.cboStatus)) And Me.cboStatus <> "" Then !status_id = Me.cboStatus
                If Not (IsNull(Me.txtInvoiceDate)) And Me.txtInvoiceDate <> "" Then !date_of_INVorCN = Me.txtInvoiceDate
                .Update
            End If
            .MoveNext
        Loop
        .MoveFirst
    End With
    Set rs = Nothing
End Sub

so basically i want to design an update query that only updates fields that have been selected to be updated by the user and i'm not sure how to build that.

if say cboType is not set the records selected would not have the data altered if there was data already there, if it was selected then the data is written to the field of the record

hope that makes sense

as always any help/direction will be gratefully received even if it is that what i want to do is actually better done like the above code does, hence the subject

kind regards

Glen

 

[MajP]

What is the question? If the question is how to handle either a value or null I use this function to delimit my values.

If I have a text field and has value abc it delimits it to 'abc' if that field is null it returns NULL for the insert
Dates 1/1/2022 becomes #/1/1/2022# or NULL
https://www.access-programmers.co.uk/forums/threads/work-smarter-not-harder-sql-and-filters.311743/
see the CSQL function.

The easier way then making insert queries is a function for parameterized QDF. That code is also in the link.

Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  If Left(TheFields, 1) <> "(" Then TheFields = "(" & TheFields & ")"
 
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function
Public Sub TestParamInsert()
  Dim TheFields As String
  Dim FirstName As Variant
  Dim LastName As Variant
  FirstName = Null
  LastName = "Smith"
  TheFields = "(FirstName, LastName, OrderID, OrderDate)"
  ParamInsert "MyTable", TheFields, FirstName, LastName, 1, Date
End Sub

Pass in the field names and all the values even if null.

 

[LGDGlen]

sorry if it wasn't clear

the question is, if the user hasn't entered a value in a specific field i don't want the update query to affect the data in that field, so if the form field is blank that means "DO NOT UPDATE THIS FIELD"

so the update query needs to dynamically build the list of fields it is going to update based on the users entry. and i'm just curious if the amount of work that is is less than what i already have

 

[LGDGlen]

so looking a bit more at the code you provided i can see what you have done and i might try and adapt it for my needs but i was trying to reduce the code down from what i had so the update query method of doing all the fields requiring update in 1 line of sql was what i was hoping for but in this instance i think i might have to do more work than i was hoping for

thank you @MajP

 

[moke123]

I'm confused. Are you using a bound form? If so, what is the purpose of your code?

so if the form field is blank that means "DO NOT UPDATE THIS FIELD"

If the form field is blank wouldnt the update just update it to blank again?

 

[LGDGlen]

@moke123 its an unbound form that updates a set of selected invoice records to have a reference number, status, who it was from, who it is to and the status

so the user selects the records from the continuous form and then details what values they want to update on the selected records to say "Finalised" as a status and then hits a button to update them all

the user may have 10s of records to update in one go so this process is to make that job easier for them

 

[theDBguy]

sorry if it wasn't clear

the question is, if the user hasn't entered a value in a specific field i don't want the update query to affect the data in that field, so if the form field is blank that means "DO NOT UPDATE THIS FIELD"

so the update query needs to dynamically build the list of fields it is going to update based on the users entry. and i'm just curious if the amount of work that is is less than what i already have

Hi. Like you said, the way I do this is loop through the values and build each column to update, one at a time. For example:

Dim strColumns As String
If Me.Field1 > "" Then
    strColumns = strColumns & ",Field1=" & Me.Field1
End If
If Me.Field2 > "" Then
    strColumns = strColumns & ",Field2=" & Me.Field2
End If
If strColumns<>"" Then
   strColumns = Mid(strColumns,2)
End If
strSQL = strSELECT & strColumns & strWHERE

Hope that makes sense...

 

[MajP]

A little different than I was thinking. My code probably not related here, but likely good to have. I think I would make some helper functions to do this, but I think coding a generic solution will be no shorter or easier than you have. You would have to dynamically build the query string. To do that you would need to know which control goes to which field. You would have to put that information in the tag field. So you loop the controls and those with a value in them you include in the sql string. But writing the tags and doing your loop is no less work than what you have IMO.

 

[Pat Hartman]

Not sure what the purpose of this code is. It looks a little scary. You seem to be using a bound form but you are updating it directly.

The code would be more straightforward if you just used a simple loop (that has been posted here multiple times) in the form's BeforeUpdate event that looks at each control and if it is null, raises an error and prevents the record from being saved. If not all fields are required, then you have to use the Tag property to create a group you can loop through. So, instead of requiring all controls be not null, just look at the ones with "R" in the Tag property. Here is just one example. Put the Function in a standard module and call it from ANY form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If EnsureNotEmpty(Me) = False Then
        Cancel = True
        Exit Sub
    End If
End Sub

Public Function EnsureNotEmpty(frm As Form) As Boolean
Dim ctl As Control

    For Each ctl In frm.Controls
       Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl.Tag = "R" Then
                    If ctl & "" = "" Then
                        ctl.SetFocus
                        EnsureNotEmpty = False
                        MsgBox ctl.Name & " is required.", vbOKOnly
                        Exit Function
                    End If
                End If
        End Select
    Next ctl
  
    EnsureNotEmpty = True
End Function

Looks like you are doing the opposite but I'm not sure that Access is not saving the record anyway. Are you absolutely certain this code works correctly? If a record is null/ZLS in one of those controls, is the record still getting saved.