By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

find and delete records in unrelated table

P: n/a
Hi,

Using MS ACCESS 2003, i have a form for entering data.
wih one object on the form (yes/no field) I want to inititiate vba code to
run if it changes.
I have no problem gettting it to update the table and append data (runs
macro which opens several queries) to it but if the field is changed to 'NO'
I want it to find and delete an associated record in a different table
(within the same database). The vba code samples I have found on the
interenet do not appear to work correctly. Can someone please point me in
the right direction and possibly provide sample code for findind and
deleting records according to change in particular field on a form for a
different table.

any help would be appreciated
TIA and regards
Birderman

--
Posted via a free Usenet account from http://www.teranews.com

Jul 24 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

Birderman wrote:
Hi,

Using MS ACCESS 2003, i have a form for entering data.
wih one object on the form (yes/no field) I want to inititiate vba code to
run if it changes.
I have no problem gettting it to update the table and append data (runs
macro which opens several queries) to it but if the field is changed to 'NO'
I want it to find and delete an associated record in a different table
(within the same database). The vba code samples I have found on the
interenet do not appear to work correctly. Can someone please point me in
the right direction and possibly provide sample code for findind and
deleting records according to change in particular field on a form for a
different table.

any help would be appreciated
TIA and regards
Birderman
I would start by creating a SELECT query that finds the records I want.
Something along the lines of:

SELECT ...
FROM SomeTable
WHERE SomeField = Forms![MyOpenForm]![LinkingFieldOnForm]

Then when you're sure it only selects the data you want, you can change
it to a delete query. (I'd make a backup of the tables involved before
testing... better to be safe than sorry).

That's the first step. then you'd have to write some code in the Click
event of the Checkbox

Private Sub MyCheckBox_Click()
If Me.MyCheckBox Then
docmd.setwarnings False
docmd.openquery "MyQuery"
docmd.setwarnings True
End If
End Sub

leave the SetWarnings out when testing. it shuts off the "You' are
about to ..."
HTH,
pieter

Jul 24 '06 #2

P: n/a
Hi,

Whilst this will work, I feel it is only a workaround as I was really
looking for a module level solution that can be done entirely as coded
subroutine. My preference for coded version is to reduce number of queries
stored in my database and to get experience of writing more vba code.

The samples in Access help for find and seek do not appear to function with
my database and they are not clearly explained.

Thanks for any help.
Birderman
<pi********@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>
Birderman wrote:
>Hi,

Using MS ACCESS 2003, i have a form for entering data.
wih one object on the form (yes/no field) I want to inititiate vba code
to
run if it changes.
I have no problem gettting it to update the table and append data (runs
macro which opens several queries) to it but if the field is changed to
'NO'
I want it to find and delete an associated record in a different table
(within the same database). The vba code samples I have found on the
interenet do not appear to work correctly. Can someone please point me in
the right direction and possibly provide sample code for findind and
deleting records according to change in particular field on a form for a
different table.

any help would be appreciated
TIA and regards
Birderman

I would start by creating a SELECT query that finds the records I want.
Something along the lines of:

SELECT ...
FROM SomeTable
WHERE SomeField = Forms![MyOpenForm]![LinkingFieldOnForm]

Then when you're sure it only selects the data you want, you can change
it to a delete query. (I'd make a backup of the tables involved before
testing... better to be safe than sorry).

That's the first step. then you'd have to write some code in the Click
event of the Checkbox

Private Sub MyCheckBox_Click()
If Me.MyCheckBox Then
docmd.setwarnings False
docmd.openquery "MyQuery"
docmd.setwarnings True
End If
End Sub

leave the SetWarnings out when testing. it shuts off the "You' are
about to ..."
HTH,
pieter


--
Posted via a free Usenet account from http://www.teranews.com

Jul 25 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.