Checkbox to delete

[raphael99]

"DescrizionePIP" is on "TabellaPIP"(tablePIP) meanwhile "DataPIP" is on "Tabella JoinPIP"(tableJoinPIP).
Is this you mean?

 

[vbaInet]

Yes.

What is the relationship between tablePIP and DataPIP?

 

[raphael99]

Here it is:

 

[vbaInet]

And which table is the checkbox on? And what is the name of this field?

 

[raphael99]

The table is "TabellaJoinPIP" and the field is "Seleziona"

 

[vbaInet]

Ok, it only makes sense to delete the record in TabellaJoinPIP because that is a junction table.

 

[RainLover]

Ok, it only makes sense to delete the record in TabellaJoinPIP because that is a junction table.

Can I see the code you use to delete.

vba is right and there is no reason why you cannot delete a record.

 

[raphael99]

Maybe I'm missing something.
I got delete query. But this one is not run because I cannot build a form from it. In wizard I cannot choose that query.
So the only code I have is

If Dirty Then Me.Dirty = False

on click event on the button command .
You says to delete "the record in TabellaJoinPIP because that is a junction table" but how can I relate my delete query to that command button deletion?
Sorry to be hard.

 

[RainLover]

For now forget the Button.

Does the query work by running it manually.

 

[vbaInet]

Just to save time, see attached.

 

[RainLover]

The Record Source is incorrect.

Try This

 SELECT TabellaJoinPIP.IDPIP, TabellaJoinPIP.DataPIP, TabellaJoinPIP.Seleziona, TabellaJoinPIP.IDAnagrafica
FROM TabellaJoinPIP;

 

[raphael99]

VbaInet...That's simply works!!! It is for that I was trying to build a delete query that brought me to no land man! :banghead:
Your solution is so simply and clear I never figured out.

@RainLover.
I created a delete query that even not run. Message error was "could not delete from specified table"
The fact as I can see in VbaInet's solution I was complicated the delete query.
I was even stuck on building a form can runs the delete query. But Access does not permit.

VbaInet. Thanks a lot. You also show me how much miles I have to run before to understand how Access works

 

[vbaInet]

... how much miles I have to run before to understand how Access works

You will get there one day

 

[raphael99]

You will get there one day

Thanks.


@RainLover. I go to try your solution also. Thanks to you

 

[RainLover]

If you look at the Relationships window you will see that a Primary Key can have more than one link to another table.

You therefore cannot delete the Primary Key as it is required as part of a relationship with other records in the related table. The Primary Key therefore must not be included in any delete query. It simply won't work as it cannot be deleted.

 

[raphael99]

Thanks RainLover. I'll keep in mind.
But what I'm wondering if it is possible to use a checkbox to select a Record in a Union Query and delete the selected record.
I'm asking because I'm unable to get checkbox working on a Union Query. Access says "recordset not updatable"
Am I wasting time around?

 

[vbaInet]

A Union Query is a read-only recordset.

Let's see the SQL.

@Rain, the db in post #30 shows the correct method to use and references the right table. You may have missed that post.

 

[raphael99]

Hi VbaInet. Here it is:

SELECT tblAnagrafica.ID, tblAnagrafica.[Tessera Sanitaria], tblAnagrafica.[Cognome Nome], TabellaTipoPrestazione.DettaglioPrestazioni, tblPrestazioni.DataPrestazioni, tblPrestazioni.[Checkbox]
FROM TabellaTipoPrestazione INNER JOIN (tblAnagrafica INNER JOIN tblPrestazioni ON tblAnagrafica.ID = tblPrestazioni.[ID Pazienti]) ON TabellaTipoPrestazione.TipoPrestazione = tblPrestazioni.TipoPrestazione
UNION
SELECT tblAnagrafica.ID, tblAnagrafica.[Tessera Sanitaria], tblAnagrafica.[Cognome Nome], TabellaPIP.[Descrizione PIP], TabellaJoinPIP.DataPIP, TabellaJoinPIP.Seleziona
FROM tblAnagrafica INNER JOIN (TabellaPIP INNER JOIN TabellaJoinPIP ON TabellaPIP.ID = TabellaJoinPIP.IDPIP) ON tblAnagrafica.ID = TabellaJoinPIP.IDAnagrafica;

 

[vbaInet]

tblPrestazioni and TabellaJoinPIP are two different tables. The only time you join them is if you want to run a report with the data. You will need two separate subforms one for each table and you can delete it using the method show in post #30.

 

[raphael99]

Maybe I'm going wrong. But if I select the record with a multiselect listbox can I apply then solution like in post#30?

Totally wrong . The item must be on a table before create a delete query.