Delete Blank (1 Viewer)

Gismo

Registered User.
Local time
Today, 08:47
Joined
Jun 12, 2017
Messages
1,298
Hi All,

i have a stupid situation I got myself into and not sure how to handle this
I have a form that creates an entry / place holder in another table.
just the reference will be held in this table

i no additional interaction is added on the form, the table remains with this reference
if additional data is entered on the form, i want to delete the initial reference

so basically, if the table has no data in the other fields except for the reference, the data must remain in the table
if additional data is entered, the line with blanks in the first record must be deleted

as below, second record must be deleted as it has related registration and sequence number with additional data
line 1 must not be deleted, it is a place holder for a different sequence number, but does not have additional data

1590149715122.png


I have created 2 queries, one that only shows records with no additional data and one that only shows records with additional data
unmached query does not seem to work for me here
 

Attachments

  • 1590149671794.png
    1590149671794.png
    10.3 KB · Views: 85

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:47
Joined
Oct 29, 2018
Messages
21,358
Hi. I am thinking you need three queries for this. One to show all the empties, another one to show the non-empties, and a final delete query joining the the other two.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:47
Joined
May 7, 2009
Messages
19,169
you can use Data macro to do it.
since i am noy on my pc, i suggest use form.

add code the the form's afterupdate event:

private sub form_afterupdate()
if me.registration & me.sequence <> _
me.registration & me.sequence & _
me.[ipc p/n] then
currentdb.execute "delete * from yourtable where registration='" me.registration &"' and sequence=" & me.sequence and [ipc p/n] is null;"
end if
end sub
 

cheekybuddha

AWF VIP
Local time
Today, 06:47
Joined
Jul 21, 2014
Messages
2,237
Is there not a way to avoid this in the first place?

If you must have placeholder records (never really necessary IMHO, you can use an outer join query to create them virtually) then why not just use logic to fill data in to an empty placeholder if it exists, rather than inserting a new record?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:47
Joined
May 7, 2009
Messages
19,169
sample form.
 

Attachments

  • registration.zip
    36.3 KB · Views: 74

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:47
Joined
Feb 19, 2002
Messages
42,981
cheekybuddha has the right solution. Fix the underlying problem. Do not try to compensate for it.
 

Users who are viewing this thread

Top Bottom