Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2012
    Posts
    72

    Issue deleting records with blank fields

    Rundown:

    Database with multiple forms. On the home page is a modal form that allows the opening of multiple forms based in selections with 5 txtboxes. The data that is inputted into the 5 txtboxes is copied over to corresponding txtboxes to the forms that open up.

    If the user were to close the multiple forms at this point, the data in the txtboxes would be saved as a new record. Which I don't want but have resigned to the fact it's going to happen.

    Before this all I had to do was
    Code:
    DoCmd****nSQL "DELETE FROM Cabin_Temperatures WHERE Cabin_Temperatues.[CT_Comp] Is Null"
    CT_Comp is a text field at the very end of the form that's blank if the above happens. And usually the above SQL works fine.

    For some reason, when I implemented the "open multiple forms and copy data from modal form into the fields on all the forms that opened as a result" thing...the SQL code no longer works.

    I've tried modifiying the code to include blank space, spaces etc to no avail.

    The solution I want:

    Either prevent the incomplete records from saving to the table. OR
    A code that deletes incomplete records from the table with a button. Possibly a loop that goes through and finds all records where CT_Comp is blank/null/spacey and deletes them.

    I've tried to make this a clear as possible but realize if you can't see the project there is probably going to be some comprehension issues to what I'm asking. I'll be happy to answer any questions and thanks for your help in advance!

  2. #2
    Join Date
    Nov 2012
    Posts
    72
    Dunno why the code has all those asterisks are there. Should be DoCmdR.u.n.SQL

    It keeps putting astricks there so disregard the periods.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    what defines a row as being incomplete
    ..wahtever that is, translate into VBA and place int he before update event of the form
    if the row doesn't validate then don't allow the row to be added/updated

    the asterisks appear because we were innundated with spam promoting a roumanian website that ended in ru. so dot ru got obliterated to encourage 'em to go away in short jerky movememnts
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2012
    Posts
    72
    An incomplete record is any record that has a blank field. All fields are required in order to submit the record. The way that the incomplete records are saved is when the Home button is clicked which is just a simple close event.

    If I understand you correctly, I should clear the record:

    I grasp that I should do a BeforeUpdate() event such as:

    Code:
    Private Sub CT_Comp_BeforeUpdate()
    
    If Me.CT_Comp = "" Or IsNull(Me.CT_Comp) Then
    ....stuck, how would you suggest I prevent the row from being added?
    
    Am I even on the right track?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    set the cancel flag (the return value from the function to true if you want to cancel the update)
    BeforeUpdate Event [Access 2003 VBA Language Reference]
    ...use the forms before update event NOT the control's before update

    you may also want to provide some user feedback whether thats a message using msgbox or chaneg colours of the relevant controls is up to you
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2012
    Posts
    72
    An incomplete record is any record that has a blank field. All fields are required in order to submit the record. The way that the incomplete records are saved is when the Home button is clicked which is just a simple close event.

    If I understand you correctly, I should clear the record:

    I grasp that I should do a BeforeUpdate() event such as:

    Code:
    Private Sub CT_Comp_BeforeUpdate()
    
    If Me.CT_Comp = "" Or IsNull(Me.CT_Comp) Then
    ....stuck, how would you suggest I prevent the row from being added?
    
    Am I even on the right track?
    I guess I'm not understanding the BeforeUpdate() event

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    place your validation code int he forms before update evemt

    eg
    Code:
    'inside 
    dim strMessage as string
    cancel = false 'set our default return state
    strMessage = ""
    if isnull(mycontrol1.value) = vbtrue or mycontrol1.value = "" then 
      strMessage = strMessage = "Control 1 was either null or empty" & vbcrlf
      strCancel = true 'if there is an error then set the return flag = true so the update will not happen
    endif
    '...repeat ad nauseam
    if cancel = true then 'show the message if the cancel flag = true, ie we have found errors
      msgbox strMessage
    endif
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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