Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Question Unanswered: Update Query which ignores null parameters from form?

    Hi,

    Using MS Access 2000 and apologies, I am pretty new to VBA and Access.

    I have a table (tbl_Round_Collection_Database) which I hope to update using an update query based on entries on a form (frm_bulk_update1). This table is linked via a unique number to an address database to enable a user to search by the street name and/or postcode (like a zipcode) to determine which records in the table will be updated.

    It works as follows - the user enters the street name and/or the postcode and clicks a button to view all the records which are selected (via a select query). If they are happy that their search only includes those records they wish to alter then they close that query result, enter the relevant data onto the "update to" textboxes/checkboxes on the form and click the update button to run the query (called qry_bulk_update_address) as an update query (so I have 1 select and 1 update query with the same WHERE parameters passed from the form). It can update the fields for a number of records (like an entire street) at once.


    Although it works well I have a problem in that if nothing is entered in one of the form's (unbound) fields (eg [Forms]![frm_bulk_update1]![up_refuse_week]), it will update the resulting record ([tbl_Round_Collection_Database.Refuse_Week]) with a null value, deleting existing information.

    Is there any way of having an update query which will only update a particular record if something has been entered in the corresponding form text box (ie some sort of conditional check)?

    My other thought was to populate the form with the existing data in the first record returned after the initial search has been done. I can add an "existing data" field on the form if required (and populate from the select query), but I do not know how to force the update query to use that "existing data" field if the updating field is empty.

    the "qry_bulk_update_address" query is as below:

    UPDATE LLPG_Live_layer INNER JOIN tbl_Round_Collection_Database ON LLPG_Live_layer.BLPU_UPRN = tbl_Round_Collection_Database.BLPU_UPRN

    SET tbl_Round_Collection_Database.Refuse_Week = [Forms]![frm_bulk_update1]![up_refuse_week], tbl_Round_Collection_Database.Refuse_Day = [Forms]![frm_bulk_update1]![up_refuse_day], tbl_Round_Collection_Database.Refuse_Crew = [Forms]![frm_bulk_update1]![up_refuse_crew], tbl_Round_Collection_Database.Refuse_Bin_Type = [Forms]![frm_bulk_update1]![up_refuse_bin], tbl_Round_Collection_Database.Refuse_Assist_Coll = [Forms]![frm_bulk_update1]![up_refuse_assist], tbl_Round_Collection_Database.Organic_Week = [Forms]![frm_bulk_update1]![up_organic_week], tbl_Round_Collection_Database.Organic_Day = [Forms]![frm_bulk_update1]![up_organic_day], tbl_Round_Collection_Database.Organic_Crew = [Forms]![frm_bulk_update1]![up_organic_crew], tbl_Round_Collection_Database.Organic_Bin_Type = [Forms]![frm_bulk_update1]![up_organic_bin], tbl_Round_Collection_Database.Organic_Assist_Coll = [Forms]![frm_bulk_update1]![up_organic_assist], tbl_Round_Collection_Database.Paper_Week = [Forms]![frm_bulk_update1]![up_paper_week], tbl_Round_Collection_Database.Paper_Day = [Forms]![frm_bulk_update1]![up_paper_day],
    tbl_Round_Collection_Database.Paper_Crew = [Forms]![frm_bulk_update1]![up_paper_crew], tbl_Round_Collection_Database.Paper_Bin_Type = [Forms]![frm_bulk_update1]![up_paper_bin], tbl_Round_Collection_Database.Paper_Assist_Coll = [Forms]![frm_bulk_update1]![up_paper_assist], tbl_Round_Collection_Database.Plas_Can_Week = [Forms]![frm_bulk_update1]![up_pp_week], tbl_Round_Collection_Database.Plas_Can_Day = [Forms]![frm_bulk_update1]![up_pp_day], tbl_Round_Collection_Database.Plas_Can_Crew = [Forms]![frm_bulk_update1]![up_pp_crew], tbl_Round_Collection_Database.Plas_Can_Bin_Type = [Forms]![frm_bulk_update1]![up_pp_bin], tbl_Round_Collection_Database.Plas_Can_Assist_Coll = [Forms]![frm_bulk_update1]![up_pp_assist]
    WHERE (((LLPG_Live_layer.streetdescr)=[Forms]![frm_bulk_update1]![bulk_street_name]) AND ((([LLPG_Live_layer].[postcode]) Like [Forms]![frm_bulk_update1]![bulk_postcode]) Is Null)) OR (((LLPG_Live_layer.postcode)=[Forms]![frm_bulk_update1]![bulk_postcode]));
    I have attached a screenshot of part of the form to give you an idea of how it looks (excuse the random colour variation that saving it as a .gif generated).

    the update button runs with the following code:

    Option Compare Database

    Private Sub run_update_qry_Click()
    On Error GoTo Err_run_update_qry_Click

    Dim stDocName As String

    stDocName = "qry_bulk_update_address"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_run_update_qry_Click:
    Exit Sub

    Err_run_update_qry_Click:
    MsgBox err.Description
    Resume Exit_run_update_qry_Click

    End Sub
    Attached Thumbnails Attached Thumbnails update_form.gif  

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    CASE statements can be your friend
    Code:
    SET tbl_Round_Collection_Database.Refuse_Week =
    (
    CASE WHEN [Forms]![frm_bulk_update1]![up_refuse_week] IS NULL
    THEN tbl_Round_Collection_Database.Refuse_Week
    ELSE [Forms]![frm_bulk_update1]![up_refuse_week]
    END
    )
    Give that code a bash and see what happens - if you don't understand it then say and I will try explain better
    However, what if a user wants to erase the value of one field?
    EDIT: You might want to play about with IS NULL or = '' (blank string)
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    CASE won't be his friend in Access
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh my days, you are quite correct!
    Access = Iif statements?
    Alternatively (my preferred solution) would be to build up the querystring dynamically and run it using the DoCmd.RunSQL command in VBA.
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You need a function to validate the data in each field. You'll call that function before running your update query. The function looks at each field on the form, making sure that date fields actually contain dates, numberic fields contain numbers, etc. If any field has bad data or no data, have the function return "True". If it returns true, you remind the user to update the offending field, and don't run the update query.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Apr 2007
    Posts
    5
    Thanks for the quick replies!

    Quote Originally Posted by RedNeckGeek
    You need a function to validate the data in each field. You'll call that function before running your update query. The function looks at each field on the form, making sure that date fields actually contain dates, numberic fields contain numbers, etc. If any field has bad data or no data, have the function return "True". If it returns true, you remind the user to update the offending field, and don't run the update query.

    Cheers, but in some cases they will want to change one part (eg the refuse data) for an entire street without affecting another part (eg the organic data) which may be different for different parts of the street (done by postcode for example). A validation function would mean they would have to overwrite all the data, I would like to leave that other data as it is, unless a value exists in the update form.


    Quote Originally Posted by georgev
    Access = Iif statements?
    Alternatively (my preferred solution) would be to build up the querystring dynamically and run it using the DoCmd.RunSQL command in VBA.
    Cheers, I will have a look at IiF statements (or whatever the equivalent to case statements are) and see what they do.
    Building the string dynamically? I am new to this but would it be something like:

    variable1 = [Forms]![frm_bulk_update1]![up_refuse_week]
    >>code to check if variable1 contains a value or not
    >>if it contains a value create variable1SQL
    variable1SQL = "SET tbl_Round_Collection_Database.Refuse_Week = " & "variable1"
    >>if not skip it somehow?

    next variable and so on

    SQLstart = "UPDATE LLPG_Live_layer INNER JOIN tbl_Round_Collection_Database ON LLPG_Live_layer.BLPU_UPRN = tbl_Round_Collection_Database.BLPU_UPRN"
    SQLend = "WHERE" clause...
    finalSQL = SQLstart & variable1SQL & variable2SQL etc & SQLend

    then run that SQL
    In that sort of thing, would having a lot of blank sections (if no variable1 was made) be a problem for the SQL?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The best way I can see of validating your data is to have input masks on, for example, the date fields.
    -----------------------
    Code:
    UPDATE MyTable
    SET Field1 = 'a'
    WHERE PrimaryKey = 1
    this code updates a single field
    UPDATE MyTable
    SET Field1 = 'a', Field2 = 'b', Field3 = 'c'
    WHERE PrimaryKey = 1
    this code updates 3 fields in one go
    What I'm trying to say is: with a dynamically built SQL statement (similar to your attemp above) you can completely drop fields that do not have a value in them from the statement.

    HTH
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Just a couple of thoughts:
    Input mask for date fields. I greatly dislike them. If the field has a date format on it, Access will validate that date as a date, then put whatever the user entered in the format of the format property. For example, if the user enters 27 apr 07 or 27/4, or apr 27, or apr 27, 07 they will always get the 27th of April, 2007. And they can enter the date in the fashion they are comfortable with, not having to conform to the form the developer is comfortable with.

    Thought number 2. When checking for blank, empty field, null entry in a field, I use this construct which covers about everything in one test:
    If Len(Nz(fldName)) = 0 Then .... 'For text fields -OR-
    If Nz(fldName) = 0 Then ... 'For numeric fields.

    Just suggestions,

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Good call Vic
    In this instance I opted for something along the lines of
    Code:
    Dim Var1 As Variant
    
    Var1 = Me.txt1.value
    
    if Not IsNull(Var1) Then
    ...
    Which works when referencing the value property of a textbox. (note that a blank string ' ' will still fall into the IsNull in the above example)
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    But it is a negative test. The postive test does not work. Mine works either way, therefore for the simple minded (me) it is beautiful.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think you're the first person I've met who believes in their programming being positive Personally, Nz is a new function to me (you introduced me to it in fact!) and I don't think that testing length is the simplest way. I guess I treat each problem as new, I used the above solution when I got an "invalid use of null" for Var1
    Break it to fix it!
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Short history lesson.
    Got out of the AirForce and had no idea what I wanted to do when I "grew up". First semester in college took an Intro To Data Processing class (all business majors had to). Found my true love (work wise) and been here ever since. More years than you (George) have been alive. I have really enjoyed having a career that I have found fun to go to work each and every day. Of course some of the places I have worked were not enjoyable, but I have always enjoyed what I do. And in all of that, I have found POSITIVE the best way to approach everything.
    End of history lesson.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Haha, well I guess the method is tried and tested then
    I do admire your approach and perhaps I will slowly make my way over to the "positive coding side" - sounds like fun ove rthere
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2007
    Posts
    5
    Thanks for the help guys, much appreciated.

    I have it sorted, created a sub procedure which calls all the functions in turn to build the body of the SQL string, adds the first and last parts (the UPDATE and WHERE clauses) then runs it. Going to take me a while to code in all 17 fields (there must be a neater way) but here is the function that I have created.

    Code:
    Private Function eval_RW() As String
       Dim varUpdate
    
    Set varUpdate = Me.up_refuse_week
      'show in immediate window
          'Debug.Print varUpdate
     'if null value then do not update record
       If IsNull(varUpdate) Then
            eval_RW = "tbl_Round_Collection_Database.Refuse_Week = tbl_Round_Collection_Database.Refuse_Week"
       Else: eval_RW = "tbl_Round_Collection_Database.Refuse_Week = " & varUpdate
     End If
    
    'MsgBox eval_RW, vbOKOnly
    End Function
    with my debugging and comments included

    The main sub inserts the SET command, adds the commas in the right place and runs the SQL.

    tonight!

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds good, except for one little niggling problem that I just thought of...

    What if you want to update a record to a blank/null?
    Or would this never be needed?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •