find and delete records in unrelated table

[Birderman]

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

[pietlinden]

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]![LinkingFieldOnF orm]

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_Clic k()
If Me.MyCheckBox Then
docmd.setwarnin gs False
docmd.openquery "MyQuery"
docmd.setwarnin gs True
End If
End Sub

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

[Birderman]

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********@hot mail.comwrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.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]![LinkingFieldOnF orm]

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_Clic k()
If Me.MyCheckBox Then
docmd.setwarnin gs False
docmd.openquery "MyQuery"
docmd.setwarnin gs 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