deleting record in continuous form

[Jean-Paul]

Hi,

I have following situation:

I created a continuous form based upon a query.
All records are selected if a date field fals between 2 different dates.

The records are displayed correctly.

Each record has a "delete" checkbox.
The idea is that when a user checks this checkbox, the date field is set
to null and the record immediatly disappears from the continuous form.

In the afterupdate property of the checkbox I wrote:

Private Sub Bezoeken_AfterUpdate()
DoCmd.RunSQL "UPDATE [Adressen metaal] SET [Adressen
metaal].Datum_bezoek = null WHERE [Adressen metaal].Bezoeken = True AND
[Adressen metaal].Nummer=" & Forms.weekplanning.Volgnr & ";"
Forms!weekplanning.Requery
End Sub

This doesn't seem to work...
I need your advise...

It all has to do with the planning of our sales people.
They get a list of firms to visit
They enter in the date field the date they plan to visit the firm

Then for the planning I create a form with 2 dates between the planning
was made
All records are displayed.

No problem so far...

Suppose they entered a wrong firm...., the firm should be deleted form
the "to visit list"
So I created a "delete" checkbox
Normally its false
If the sales-guy want's it deleted from the list, check the box and...
Tara, gone is the record.

Thanks
JP

 

[Marshall Barton]

I created a continuous form based upon a query.
All records are selected if a date field fals between 2 different dates.

The records are displayed correctly.

Each record has a "delete" checkbox.
The idea is that when a user checks this checkbox, the date field is set
to null and the record immediatly disappears from the continuous form.

In the afterupdate property of the checkbox I wrote:

Private Sub Bezoeken_AfterUpdate()
DoCmd.RunSQL "UPDATE [Adressen metaal] SET [Adressen
metaal].Datum_bezoek = null WHERE [Adressen metaal].Bezoeken = True AND
[Adressen metaal].Nummer=" & Forms.weekplanning.Volgnr & ";"
Forms!weekplanning.Requery
End Sub

This doesn't seem to work...
I need your advise...

It all has to do with the planning of our sales people.
They get a list of firms to visit
They enter in the date field the date they plan to visit the firm

Then for the planning I create a form with 2 dates between the planning
was made
All records are displayed.

No problem so far...

Suppose they entered a wrong firm...., the firm should be deleted form
the "to visit list"
So I created a "delete" checkbox
Normally its false
If the sales-guy want's it deleted from the list, check the box and...
Tara, gone is the record.

First, the record would have to be saved before the query
will see the new check box value. This can be done by
using:
Me.Dirty = False

Second, RunSQL runs the query asynchronously so the query
may not have finished before the Requery executes. Use the
Execute method instead.

This seems a rather roundabout way of deleting a record. Is
there a reason why you don't just use:
Me.Recordset.Delete

 

[Jean-Paul]

Hey... thank you very much for your kind help...
My problem is solved withe the Me.dirty stuff....

I can't use the Me.Recordset.Delete because I actually don't want to
delete the record, but change a field from the record...

Again, you've been an enormous help... whish I coudl do something back

JP, Belgium, Europe

I created a continuous form based upon a query.
All records are selected if a date field fals between 2 different dates.

The records are displayed correctly.

Each record has a "delete" checkbox.
The idea is that when a user checks this checkbox, the date field is set
to null and the record immediatly disappears from the continuous form.

In the afterupdate property of the checkbox I wrote:

Private Sub Bezoeken_AfterUpdate()
DoCmd.RunSQL "UPDATE [Adressen metaal] SET [Adressen
metaal].Datum_bezoek = null WHERE [Adressen metaal].Bezoeken = True AND
[Adressen metaal].Nummer=" & Forms.weekplanning.Volgnr & ";"
Forms!weekplanning.Requery
End Sub

This doesn't seem to work...
I need your advise...

It all has to do with the planning of our sales people.
They get a list of firms to visit
They enter in the date field the date they plan to visit the firm

Then for the planning I create a form with 2 dates between the planning
was made
All records are displayed.

No problem so far...

Suppose they entered a wrong firm...., the firm should be deleted form
the "to visit list"
So I created a "delete" checkbox
Normally its false
If the sales-guy want's it deleted from the list, check the box and...
Tara, gone is the record.

First, the record would have to be saved before the query
will see the new check box value. This can be done by
using:
Me.Dirty = False

Second, RunSQL runs the query asynchronously so the query
may not have finished before the Requery executes. Use the
Execute method instead.

This seems a rather roundabout way of deleting a record. Is
there a reason why you don't just use:
Me.Recordset.Delete