Delete a record from a form and its subform

Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this written, the
field should be a number data type. If the field is a string, the syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e. O'Hare), then
this will need to be modified more. Also, if the value is actually a date or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have the same
need as John. But I guess I have made an error somewhere as an error
message
says "Data type mismatch in criteria expression". Could you please kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from subform
' where the Me.FormerDWGNo is a textbox that has the Master Link field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem line!

' Now that the relative record in subform is deleted, we now delete the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You should have
Referential Integrity enforced on the link between the tables for the
main
and sub forms, so it won't let you delete the record from the main form
as
long as there are associated records in the subform (unless you also set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in the
main
form
'and ID is the field in the subform's table that matches the Child Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query, you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this written,
the
field should be a number data type. If the field is a string, the syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e. O'Hare), then
this will need to be modified more. Also, if the value is actually a date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have the
same
need as John. But I guess I have made an error somewhere as an error
message
says "Data type mismatch in criteria expression". Could you please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from subform
' where the Me.FormerDWGNo is a textbox that has the Master Link field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem line!

' Now that the relative record in subform is deleted, we now delete the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You should
have
Referential Integrity enforced on the link between the tables for the
main
and sub forms, so it won't let you delete the record from the main
form
as
long as there are associated records in the subform (unless you also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in the
main
form
'and ID is the field in the subform's table that matches the Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query, you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I want to
cancel. Any ideas?

Thanks!

"Douglas J. Steele" wrote:

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this written,
the
field should be a number data type. If the field is a string, the syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e. O'Hare), then
this will need to be modified more. Also, if the value is actually a date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have the
same
need as John. But I guess I have made an error somewhere as an error
message
says "Data type mismatch in criteria expression". Could you please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from subform
' where the Me.FormerDWGNo is a textbox that has the Master Link field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem line!

' Now that the relative record in subform is deleted, we now delete the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You should
have
Referential Integrity enforced on the link between the tables for the
main
and sub forms, so it won't let you delete the record from the main
form
as
long as there are associated records in the subform (unless you also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in the
main
form
'and ID is the field in the subform's table that matches the Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query, you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

It's unusual to be deleting from a query: why not delete from the table?

What happens when you try to run qryOrderDetailsSource: are you prompted for
a parameter?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I want to
cancel. Any ideas?

Thanks!

"Douglas J. Steele" wrote:

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this
written,
the
field should be a number data type. If the field is a string, the
syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e. O'Hare),
then
this will need to be modified more. Also, if the value is actually a
date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have the
same
need as John. But I guess I have made an error somewhere as an error
message
says "Data type mismatch in criteria expression". Could you please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from
subform
' where the Me.FormerDWGNo is a textbox that has the Master Link
field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem
line!

' Now that the relative record in subform is deleted, we now delete
the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You should
have
Referential Integrity enforced on the link between the tables for
the
main
and sub forms, so it won't let you delete the record from the main
form
as
long as there are associated records in the subform (unless you
also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the
subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in
the
main
form
'and ID is the field in the subform's table that matches the Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query,
you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

I have also tried deleting straight from the table. I tried the query
because the other person that was using this had a query. When I tryo to
delete from the tabe, I have the same error message.

When I run the query, it brings up all Purchase Orders that have ever been
created and I don't get prompted for anything. Ideas?

Thanks!

"Douglas J. Steele" wrote:

It's unusual to be deleting from a query: why not delete from the table?

What happens when you try to run qryOrderDetailsSource: are you prompted for
a parameter?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I want to
cancel. Any ideas?

Thanks!

"Douglas J. Steele" wrote:

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this
written,
the
field should be a number data type. If the field is a string, the
syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e. O'Hare),
then
this will need to be modified more. Also, if the value is actually a
date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have the
same
need as John. But I guess I have made an error somewhere as an error
message
says "Data type mismatch in criteria expression". Could you please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from
subform
' where the Me.FormerDWGNo is a textbox that has the Master Link
field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem
line!

' Now that the relative record in subform is deleted, we now delete
the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You should
have
Referential Integrity enforced on the link between the tables for
the
main
and sub forms, so it won't let you delete the record from the main
form
as
long as there are associated records in the subform (unless you
also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the
subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in
the
main
form
'and ID is the field in the subform's table that matches the Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query,
you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

Is PurchaseOrderId a text field?

If so, you'll need strSQL to contain

Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062'

as Wayne indicated some time ago.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jped" wrote in message
...
I have also tried deleting straight from the table. I tried the query
because the other person that was using this had a query. When I tryo to
delete from the tabe, I have the same error message.

When I run the query, it brings up all Purchase Orders that have ever been
created and I don't get prompted for anything. Ideas?

Thanks!

"Douglas J. Steele" wrote:

It's unusual to be deleting from a query: why not delete from the table?

What happens when you try to run qryOrderDetailsSource: are you prompted
for
a parameter?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
strSQL = "Delete * From qryOrderDetailsSource Where
PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I want
to
cancel. Any ideas?

Thanks!

"Douglas J. Steele" wrote:

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this
written,
the
field should be a number data type. If the field is a string, the
syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e.
O'Hare),
then
this will need to be modified more. Also, if the value is actually
a
date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have
the
same
need as John. But I guess I have made an error somewhere as an
error
message
says "Data type mismatch in criteria expression". Could you
please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from
subform
' where the Me.FormerDWGNo is a textbox that has the Master Link
field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query
that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem
line!

' Now that the relative record in subform is deleted, we now
delete
the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You
should
have
Referential Integrity enforced on the link between the tables
for
the
main
and sub forms, so it won't let you delete the record from the
main
form
as
long as there are associated records in the subform (unless you
also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the
subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field
in
the
main
form
'and ID is the field in the subform's table that matches the
Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting
from
'the table for the main form. In this case, instead of the
query,
you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

PurchaseOrderID is a number field.

"Douglas J Steele" wrote:

Is PurchaseOrderId a text field?

If so, you'll need strSQL to contain

Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062'

as Wayne indicated some time ago.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jped" wrote in message
...
I have also tried deleting straight from the table. I tried the query
because the other person that was using this had a query. When I tryo to
delete from the tabe, I have the same error message.

When I run the query, it brings up all Purchase Orders that have ever been
created and I don't get prompted for anything. Ideas?

Thanks!

"Douglas J. Steele" wrote:

It's unusual to be deleting from a query: why not delete from the table?

What happens when you try to run qryOrderDetailsSource: are you prompted
for
a parameter?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
strSQL = "Delete * From qryOrderDetailsSource Where
PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I want
to
cancel. Any ideas?

Thanks!

"Douglas J. Steele" wrote:

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this
written,
the
field should be a number data type. If the field is a string, the
syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e.
O'Hare),
then
this will need to be modified more. Also, if the value is actually
a
date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have
the
same
need as John. But I guess I have made an error somewhere as an
error
message
says "Data type mismatch in criteria expression". Could you
please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from
subform
' where the Me.FormerDWGNo is a textbox that has the Master Link
field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query
that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem
line!

' Now that the relative record in subform is deleted, we now
delete
the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You
should
have
Referential Integrity enforced on the link between the tables
for
the
main
and sub forms, so it won't let you delete the record from the
main
form
as
long as there are associated records in the subform (unless you
also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the
subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field
in
the
main
form
'and ID is the field in the subform's table that matches the
Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting
from
'the table for the main form. In this case, instead of the
query,
you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

The only thing I can thing of is that your query is too complex to allow it
to be used in a delete statement (although you say you can't delete directly
from the table either).

Sorry.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
PurchaseOrderID is a number field.

"Douglas J Steele" wrote:

Is PurchaseOrderId a text field?

If so, you'll need strSQL to contain

Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062'

as Wayne indicated some time ago.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jped" wrote in message
...
I have also tried deleting straight from the table. I tried the query
because the other person that was using this had a query. When I tryo
to
delete from the tabe, I have the same error message.

When I run the query, it brings up all Purchase Orders that have ever
been
created and I don't get prompted for anything. Ideas?

Thanks!

"Douglas J. Steele" wrote:

It's unusual to be deleting from a query: why not delete from the
table?

What happens when you try to run qryOrderDetailsSource: are you
prompted
for
a parameter?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
strSQL = "Delete * From qryOrderDetailsSource Where
PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I
want
to
cancel. Any ideas?

Thanks!

"Douglas J. Steele" wrote:

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this
written,
the
field should be a number data type. If the field is a string,
the
syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e.
O'Hare),
then
this will need to be modified more. Also, if the value is
actually
a
date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to
have
the
same
need as John. But I guess I have made an error somewhere as
an
error
message
says "Data type mismatch in criteria expression". Could you
please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings
from
subform
' where the Me.FormerDWGNo is a textbox that has the Master
Link
field
' in the main form
' and (another) FormerDWGNo is the field in the subform's
query
that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the
problem
line!

' Now that the relative record in subform is deleted, we now
delete
the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You
should
have
Referential Integrity enforced on the link between the
tables
for
the
main
and sub forms, so it won't let you delete the record from
the
main
form
as
long as there are associated records in the subform (unless
you
also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the
subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link
field
in
the
main
form
'and ID is the field in the subform's table that matches the
Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are
deleting
from
'the table for the main form. In this case, instead of the
query,
you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the
relative
informations
in
the subform.

Many thanks
Regards
John

Thank you so much for looking into this and helping me out. I think I'll
just scrap the cancel button. It's not a huge deal, just a convience for the
user. The query I am using is the one Access automatically creates when you
base a form on a table. I just actually saved it to the query section in
Access for testing this out. I never created the query myself. That could
also be the problem. Thanks again!

"Douglas J. Steele" wrote:

The only thing I can thing of is that your query is too complex to allow it
to be used in a delete statement (although you say you can't delete directly
from the table either).

Sorry.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
PurchaseOrderID is a number field.

"Douglas J Steele" wrote:

Is PurchaseOrderId a text field?

If so, you'll need strSQL to contain

Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062'

as Wayne indicated some time ago.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jped" wrote in message
...
I have also tried deleting straight from the table. I tried the query
because the other person that was using this had a query. When I tryo
to
delete from the tabe, I have the same error message.

When I run the query, it brings up all Purchase Orders that have ever
been
created and I don't get prompted for anything. Ideas?

Thanks!

"Douglas J. Steele" wrote:

It's unusual to be deleting from a query: why not delete from the
table?

What happens when you try to run qryOrderDetailsSource: are you
prompted
for
a parameter?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
strSQL = "Delete * From qryOrderDetailsSource Where
PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I
want
to
cancel. Any ideas?

Thanks!

"Douglas J. Steele" wrote:

What's the value of strSQL when it fails?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"jped" wrote in message
...
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!

"Wayne Morgan" wrote:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this
written,
the
field should be a number data type. If the field is a string,
the
syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e.
O'Hare),
then
this will need to be modified more. Also, if the value is
actually
a
date
or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to
have
the
same
need as John. But I guess I have made an error somewhere as
an
error
message
says "Data type mismatch in criteria expression". Could you
please
kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings
from
subform
' where the Me.FormerDWGNo is a textbox that has the Master
Link
field
' in the main form
' and (another) FormerDWGNo is the field in the subform's
query
that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where
FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the
problem
line!

' Now that the relative record in subform is deleted, we now
delete
the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You
should
have
Referential Integrity enforced on the link between the
tables
for
the
main
and sub forms, so it won't let you delete the record from
the
main
form
as
long as there are associated records in the subform (unless
you
also
set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the
subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link
field
in
the
main
form
'and ID is the field in the subform's table that matches the
Child
Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are
deleting
from
'the table for the main form. In this case, instead of the
query,
you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the
relative
informations
in
the subform.

Many thanks
Regards
John