If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Issue deleting records with blank fields

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #3 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,071
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #5 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,071
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,071
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On