Delete Blank (1 Viewer)

Gismo

Registered User.
Local time
Today, 02:17
Joined
Jun 12, 2017
Messages
418
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

theDBguy

I’m here to help
Local time
Yesterday, 16:17
Joined
Oct 29, 2018
Messages
10,005
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

error reading drive A:
Local time
Today, 07:17
Joined
May 7, 2009
Messages
9,273
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, 00:17
Joined
Jul 21, 2014
Messages
353
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?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom