Checking for entries in a table

[iwasinnihon]

How do I write code that would search through a table and remove all
the rows that have the value found in the selected combo box?
In other words I have a combo box with a list of items from another
table. I want to select them and then have a loop that checks a
different table for entries that have share that same information and
then delete them from those tables.
In addition I want to have a pop up box that asks for confirmation.
Also, for each row that is deleted I want to increment a counter in yet
a different table.

Thanks in advance for your help.

 

[Allen Browne]

Use a Delete query rather than code.

Look in the lookup table to find the matching value.
For example, "dog" might have id value 4.

Now create a query like this:
DELETE FROM Table1 WHERE PetCategory = 4;

If you prefer, you could put a combo on a form, with its bound column
hidden. Choose the record for the dog in the combo, and run the query:
DELETE FROM Table1 WHERE PetCategory = [Forms].[Form1].[Combo2];

If you want something more complex, you could use a subquery in the WHERE
clause to lookup the value for the "dog." If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[iwasinnihon]

This would work great. The problem is how do I increment my counter.
For every deletion I make I need to increment a counter in another
table. How do I keep track of that?

Use a Delete query rather than code.

Look in the lookup table to find the matching value.
For example, "dog" might have id value 4.

Now create a query like this:
DELETE FROM Table1 WHERE PetCategory = 4;

If you prefer, you could put a combo on a form, with its bound column
hidden. Choose the record for the dog in the combo, and run the query:
DELETE FROM Table1 WHERE PetCategory = [Forms].[Form1].[Combo2];

If you want something more complex, you could use a subquery in the WHERE
clause to lookup the value for the "dog." If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How do I write code that would search through a table and remove all
the rows that have the value found in the selected combo box?
In other words I have a combo box with a list of items from another
table. I want to select them and then have a loop that checks a
different table for entries that have share that same information and
then delete them from those tables.
In addition I want to have a pop up box that asks for confirmation.
Also, for each row that is deleted I want to increment a counter in yet
a different table.

Thanks in advance for your help.

 

[BALINT]

Before you delete, have an Append Query append the value that you are
deleting for into the other table, and set an ID field with AutoNumber Data
Type????

This would work great. The problem is how do I increment my counter.
For every deletion I make I need to increment a counter in another
table. How do I keep track of that?

Use a Delete query rather than code.

Look in the lookup table to find the matching value.
For example, "dog" might have id value 4.

Now create a query like this:
DELETE FROM Table1 WHERE PetCategory = 4;

If you prefer, you could put a combo on a form, with its bound column
hidden. Choose the record for the dog in the combo, and run the query:
DELETE FROM Table1 WHERE PetCategory = [Forms].[Form1].[Combo2];

If you want something more complex, you could use a subquery in the WHERE
clause to lookup the value for the "dog." If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How do I write code that would search through a table and remove all
the rows that have the value found in the selected combo box?
In other words I have a combo box with a list of items from another
table. I want to select them and then have a loop that checks a
different table for entries that have share that same information and
then delete them from those tables.
In addition I want to have a pop up box that asks for confirmation.
Also, for each row that is deleted I want to increment a counter in yet
a different table.

Thanks in advance for your help.

 

[Allen Browne]

Examine the RecordsAffected property of the Database you executed this query
on.

The code would be something like this:
Dim db As DAO.Database
Dim strSql As String
Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1 WHERE PetCategory = 4;"
db.Execute strSql, dbFailOnError
Debug.Print db.RecordsAffected
Set db = Nothing

Presumably you know how to execute an Update query to update the value of
the correct record in the other table, or to OpenRecordset, Edit, and
Update.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This would work great. The problem is how do I increment my counter.
For every deletion I make I need to increment a counter in another
table. How do I keep track of that?

Use a Delete query rather than code.

Look in the lookup table to find the matching value.
For example, "dog" might have id value 4.

Now create a query like this:
DELETE FROM Table1 WHERE PetCategory = 4;

If you prefer, you could put a combo on a form, with its bound column
hidden. Choose the record for the dog in the combo, and run the query:
DELETE FROM Table1 WHERE PetCategory = [Forms].[Form1].[Combo2];

If you want something more complex, you could use a subquery in the WHERE
clause to lookup the value for the "dog." If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How do I write code that would search through a table and remove all
the rows that have the value found in the selected combo box?
In other words I have a combo box with a list of items from another
table. I want to select them and then have a loop that checks a
different table for entries that have share that same information and
then delete them from those tables.
In addition I want to have a pop up box that asks for confirmation.
Also, for each row that is deleted I want to increment a counter in yet
a different table.

Thanks in advance for your help.