Command button help

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

I have a form containing two subforms, each bound to a
different table. The tables are not linked to one
another. Each subform has a command button to delete the
displayed record.

Anytime the form is displayed, the record in each subform
is to be deleted.

Is it possible to make a button, resident on the parent
form that would invoke both the delete command buttons
without having to make the two separate clicks, thus
rendering moot the delete buttons on the subforms?

This would ensure that both are deleted with no chance of
forgetting one or the other.

As always, group help is appreciated.

Mary
 
What if there is more than one record in the subform? Do you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the button on the main
form. Use the same parameters in the delete query as are used to supply data
to the subform. Add the ID field of the subform to the parameters if you
only want to delete the current record in the subform instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be able to substitute the
query feeding the subform for the table in the SQL string. This may simplify
the parameters needed.
 
Wayne, thanks. Big help. I know I'm close, but not quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round here,
with CRInvNum (receipts) being iterated by +.01 for each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further define by
adding in CRInvNum, but I guess I don't get it enough to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor] WHERE '[InvNum]=
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary
 
I was too quick on the previous post.

My code empties the tables, both of them.

What am I doing wrong?

Thanks,
Mary
 
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
Click to expand...

When you run this SQL, you are asking the SQL interpreter to know what Me
is. Instead, try concatenating the value into the string.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]=' & Me![InvNum] & "
And [CRInvNum] =" & Me![CRInvNum] & "'"

This would be correct if the values are number. It'll take a little more
playing with the quotes if the values are strings.


--
Wayne Morgan
Microsoft Access MVP


Mary said:
Wayne, thanks. Big help. I know I'm close, but not quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round here,
with CRInvNum (receipts) being iterated by +.01 for each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further define by
adding in CRInvNum, but I guess I don't get it enough to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor] WHERE '[InvNum]=
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary



-----Original Message-----
What if there is more than one record in the subform? Do you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the button on the main
form. Use the same parameters in the delete query as are used to supply data
to the subform. Add the ID field of the subform to the parameters if you
only want to delete the current record in the subform instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be able to substitute the
query feeding the subform for the table in the SQL string. This may simplify
the parameters needed.

--
Wayne Morgan
Microsoft Access MVP





.
Click to expand...
Click to expand...
 
Again thanks. I'm still not getting it.

This code:
*******************************************************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
='& Me![InvNum]& " And [CRInvNum] = " & Me![CRInvNum]
& " '"
*******************************************************
results in error 2465, MS can't find the field "|"...

I'm sorry to be thick, but I am far from a master at
this. BTW, I am very grateful for your help.

Thanks,
Mary
-----Original Message-----
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
Click to expand...

When you run this SQL, you are asking the SQL interpreter to know what Me
is. Instead, try concatenating the value into the string.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =' & Me![InvNum] & "
And [CRInvNum] =" & Me![CRInvNum] & "'"

This would be correct if the values are number. It'll take a little more
playing with the quotes if the values are strings.


--
Wayne Morgan
Microsoft Access MVP


Wayne, thanks. Big help. I know I'm close, but not quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round here,
with CRInvNum (receipts) being iterated by +.01 for each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further define by
adding in CRInvNum, but I guess I don't get it enough to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor] WHERE '[InvNum] =
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary



-----Original Message-----
What if there is more than one record in the subform?
Click to expand...
Do
you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the button on the main
form. Use the same parameters in the delete query as
Click to expand...
are
used to supply data
to the subform. Add the ID field of the subform to the parameters if you
only want to delete the current record in the subform instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be
Click to expand...
able
to substitute the
query feeding the subform for the table in the SQL string. This may simplify
the parameters needed.

--
Wayne Morgan
Microsoft Access MVP


I have a form containing two subforms, each bound to a
different table. The tables are not linked to one
another. Each subform has a command button to delete the
displayed record.

Anytime the form is displayed, the record in each subform
is to be deleted.

Is it possible to make a button, resident on the parent
form that would invoke both the delete command buttons
without having to make the two separate clicks, thus
rendering moot the delete buttons on the subforms?

This would ensure that both are deleted with no
Click to expand...
chance
of
forgetting one or the other.

As always, group help is appreciated.

Mary


.
Click to expand...
Click to expand...


.
Click to expand...
 
Let's try without in internal single quotes. Also check for typos, such as
no space before or after the & signs or misspelled names. Also, this may
have a problem if one of the values being picked up from the form is Null.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum] = " & Me![InvNum] &
" And [CRInvNum] = " & Me![CRInvNum] & ")"

--
Wayne Morgan
Microsoft Access MVP


Mary said:
Again thanks. I'm still not getting it.

This code:
*******************************************************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
='& Me![InvNum]& " And [CRInvNum] = " & Me![CRInvNum]
& " '"
*******************************************************
results in error 2465, MS can't find the field "|"...

I'm sorry to be thick, but I am far from a master at
this. BTW, I am very grateful for your help.

Thanks,
Mary
-----Original Message-----
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
Click to expand...

When you run this SQL, you are asking the SQL interpreter to know what Me
is. Instead, try concatenating the value into the string.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =' & Me![InvNum] & "
And [CRInvNum] =" & Me![CRInvNum] & "'"

This would be correct if the values are number. It'll take a little more
playing with the quotes if the values are strings.


--
Wayne Morgan
Microsoft Access MVP


Wayne, thanks. Big help. I know I'm close, but not quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round here,
with CRInvNum (receipts) being iterated by +.01 for each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further define by
adding in CRInvNum, but I guess I don't get it enough to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor] WHERE '[InvNum] =
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary




-----Original Message-----
What if there is more than one record in the subform? Do
you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the
button on the main
form. Use the same parameters in the delete query as are
used to supply data
to the subform. Add the ID field of the subform to the
parameters if you
only want to delete the current record in the subform
instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your
parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be able
to substitute the
query feeding the subform for the table in the SQL
string. This may simplify
the parameters needed.

--
Wayne Morgan
Microsoft Access MVP


message
I have a form containing two subforms, each bound to a
different table. The tables are not linked to one
another. Each subform has a command button to delete
the
displayed record.

Anytime the form is displayed, the record in each
subform
is to be deleted.

Is it possible to make a button, resident on the parent
form that would invoke both the delete command buttons
without having to make the two separate clicks, thus
rendering moot the delete buttons on the subforms?

This would ensure that both are deleted with no chance
of
forgetting one or the other.

As always, group help is appreciated.

Mary


.
Click to expand...


.
Click to expand...
Click to expand...
 
Change, but not there yet. It cannot find the field
CRInvNum.

a. Parent gets InvNum -- CRInvNum not available in query
b. Child1 (labor, one record per InvNum) -- no CRInvNum
c. Child2 (receipts, multiples per IncNum, CRInvNum
unique, but Child2 linked to parent via InvNum and lists
the most recent.

InvNum and CRInvNum are currency.

No typos, no nulls.

Thanks again.

***********************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum]
= " & Me![InvNum] & " And [CRInvNum] = " & Me![CRInvNum]
& ")"
************************






-----Original Message-----
Let's try without in internal single quotes. Also check for typos, such as
no space before or after the & signs or misspelled names. Also, this may
have a problem if one of the values being picked up from the form is Null.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum] = " & Me![InvNum] &
" And [CRInvNum] = " & Me![CRInvNum] & ")"

--
Wayne Morgan
Microsoft Access MVP


Again thanks. I'm still not getting it.

This code:
*******************************************************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
='& Me![InvNum]& " And [CRInvNum] = " & Me![CRInvNum]
& " '"
*******************************************************
results in error 2465, MS can't find the field "|"...

I'm sorry to be thick, but I am far from a master at
this. BTW, I am very grateful for your help.

Thanks,
Mary
-----Original Message-----
strSQL = "DELETE * FROM [tbl 4 Receipt]
Click to expand...
WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"

When you run this SQL, you are asking the SQL interpreter to know what Me
is. Instead, try concatenating the value into the string.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =' & Me![InvNum] & "
And [CRInvNum] =" & Me![CRInvNum] & "'"

This would be correct if the values are number. It'll take a little more
playing with the quotes if the values are strings.


--
Wayne Morgan
Microsoft Access MVP


Wayne, thanks. Big help. I know I'm close, but not quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round here,
with CRInvNum (receipts) being iterated by +.01 for each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further
Click to expand...
define
by
adding in CRInvNum, but I guess I don't get it enough to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt]
Click to expand...
WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor]
Click to expand...
WHERE '[InvNum]
=
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary




-----Original Message-----
What if there is more than one record in the subform? Do
you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the
button on the main
form. Use the same parameters in the delete query as are
used to supply data
to the subform. Add the ID field of the subform to the
parameters if you
only want to delete the current record in the subform
instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your
parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be able
to substitute the
query feeding the subform for the table in the SQL
string. This may simplify
the parameters needed.

--
Wayne Morgan
Microsoft Access MVP


message
I have a form containing two subforms, each bound to a
different table. The tables are not linked to one
another. Each subform has a command button to delete
the
displayed record.

Anytime the form is displayed, the record in each
subform
is to be deleted.

Is it possible to make a button, resident on the parent
form that would invoke both the delete command buttons
without having to make the two separate clicks, thus
rendering moot the delete buttons on the subforms?

This would ensure that both are deleted with no chance
of
forgetting one or the other.

As always, group help is appreciated.

Mary


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Change, but not there yet. It cannot find the field
CRInvNum.

a. Parent gets InvNum -- CRInvNum not available in query
b. Child1 (labor, one record per InvNum) -- no CRInvNum
c. Child2 (receipts, multiples per IncNum, CRInvNum
unique, but Child2 linked to parent via InvNum and lists
the most recent.

InvNum and CRInvNum are currency.

No typos, no nulls.

Thanks again.

***********************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum]
= " & Me![InvNum] & " And [CRInvNum] = " & Me![CRInvNum]
& ")"
************************









-----Original Message-----
Let's try without in internal single quotes. Also check for typos, such as
no space before or after the & signs or misspelled names. Also, this may
have a problem if one of the values being picked up from the form is Null.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum] = " & Me![InvNum] &
" And [CRInvNum] = " & Me![CRInvNum] & ")"

--
Wayne Morgan
Microsoft Access MVP


Again thanks. I'm still not getting it.

This code:
*******************************************************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
='& Me![InvNum]& " And [CRInvNum] = " & Me![CRInvNum]
& " '"
*******************************************************
results in error 2465, MS can't find the field "|"...

I'm sorry to be thick, but I am far from a master at
this. BTW, I am very grateful for your help.

Thanks,
Mary
-----Original Message-----
strSQL = "DELETE * FROM [tbl 4 Receipt]
Click to expand...
WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"

When you run this SQL, you are asking the SQL interpreter to know what Me
is. Instead, try concatenating the value into the string.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum] =' & Me![InvNum] & "
And [CRInvNum] =" & Me![CRInvNum] & "'"

This would be correct if the values are number. It'll take a little more
playing with the quotes if the values are strings.


--
Wayne Morgan
Microsoft Access MVP


Wayne, thanks. Big help. I know I'm close, but not quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round here,
with CRInvNum (receipts) being iterated by +.01 for each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further
Click to expand...
define
by
adding in CRInvNum, but I guess I don't get it enough to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt]
Click to expand...
WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor]
Click to expand...
WHERE '[InvNum]
=
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary




-----Original Message-----
What if there is more than one record in the subform? Do
you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the
button on the main
form. Use the same parameters in the delete query as are
used to supply data
to the subform. Add the ID field of the subform to the
parameters if you
only want to delete the current record in the subform
instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your
parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be able
to substitute the
query feeding the subform for the table in the SQL
string. This may simplify
the parameters needed.

--
Wayne Morgan
Microsoft Access MVP


message
I have a form containing two subforms, each bound to a
different table. The tables are not linked to one
another. Each subform has a command button to delete
the
displayed record.

Anytime the form is displayed, the record in each
subform
is to be deleted.

Is it possible to make a button, resident on the parent
form that would invoke both the delete command buttons
without having to make the two separate clicks, thus
rendering moot the delete buttons on the subforms?

This would ensure that both are deleted with no chance
of
forgetting one or the other.

As always, group help is appreciated.

Mary


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
You will need to put in the correct "path" to CRInvNum. If it isn't on the
form running the code, then Me won't work. If it is on the Parent form you
could try Me.Parent.CRInvNum. If it is a field and not a control, you may
need to change the name to point to the control on the parent form.

If running from a subform and it is on the Parent:
Me.Parent.CRInvNum

If running from the parent and it is on a subform:
Me.NameOfSubformControl.Form.CRInvNum

If running on a subform and it's on the other subform:
Me.Parent.NameOfOtherSubformControl.Form.CrInvNum

--
Wayne Morgan
MS Access MVP


Mary said:
Change, but not there yet. It cannot find the field
CRInvNum.

a. Parent gets InvNum -- CRInvNum not available in query
b. Child1 (labor, one record per InvNum) -- no CRInvNum
c. Child2 (receipts, multiples per IncNum, CRInvNum
unique, but Child2 linked to parent via InvNum and lists
the most recent.

InvNum and CRInvNum are currency.

No typos, no nulls.

Thanks again.

***********************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum]
= " & Me![InvNum] & " And [CRInvNum] = " & Me![CRInvNum]
& ")"
************************









-----Original Message-----
Let's try without in internal single quotes. Also check for typos, such as
no space before or after the & signs or misspelled names. Also, this may
have a problem if one of the values being picked up from the form is Null.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum] = " & Me![InvNum] &
" And [CRInvNum] = " & Me![CRInvNum] & ")"

--
Wayne Morgan
Microsoft Access MVP


Again thanks. I'm still not getting it.

This code:
*******************************************************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
='& Me![InvNum]& " And [CRInvNum] = " & Me![CRInvNum]
& " '"
*******************************************************
results in error 2465, MS can't find the field "|"...

I'm sorry to be thick, but I am far from a master at
this. BTW, I am very grateful for your help.

Thanks,
Mary

-----Original Message-----
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"

When you run this SQL, you are asking the SQL
interpreter to know what Me
is. Instead, try concatenating the value into the string.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
=' & Me![InvNum] & "
And [CRInvNum] =" & Me![CRInvNum] & "'"

This would be correct if the values are number. It'll
take a little more
playing with the quotes if the values are strings.


--
Wayne Morgan
Microsoft Access MVP


message
Wayne, thanks. Big help. I know I'm close, but not
quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round
here,
with CRInvNum (receipts) being iterated by +.01 for
each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL
the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further define
by
adding in CRInvNum, but I guess I don't get it enough
to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor] WHERE '[InvNum]
=
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary




-----Original Message-----
What if there is more than one record in the subform?
Do
you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the
button on the main
form. Use the same parameters in the delete query as
are
used to supply data
to the subform. Add the ID field of the subform to the
parameters if you
only want to delete the current record in the subform
instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your
parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be
able
to substitute the
query feeding the subform for the table in the SQL
string. This may simplify
the parameters needed.

--
Wayne Morgan
Microsoft Access MVP


message
I have a form containing two subforms, each bound to a
different table. The tables are not linked to one
another. Each subform has a command button to delete
the
displayed record.

Anytime the form is displayed, the record in each
subform
is to be deleted.

Is it possible to make a button, resident on the
parent
form that would invoke both the delete command
buttons
without having to make the two separate clicks, thus
rendering moot the delete buttons on the subforms?

This would ensure that both are deleted with no
chance
of
forgetting one or the other.

As always, group help is appreciated.

Mary


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks, Wayne, for all your help. I now know what I need
to do.


-----Original Message-----
You will need to put in the correct "path" to CRInvNum. If it isn't on the
form running the code, then Me won't work. If it is on the Parent form you
could try Me.Parent.CRInvNum. If it is a field and not a control, you may
need to change the name to point to the control on the parent form.

If running from a subform and it is on the Parent:
Me.Parent.CRInvNum

If running from the parent and it is on a subform:
Me.NameOfSubformControl.Form.CRInvNum

If running on a subform and it's on the other subform:
Me.Parent.NameOfOtherSubformControl.Form.CrInvNum

--
Wayne Morgan
MS Access MVP


Change, but not there yet. It cannot find the field
CRInvNum.

a. Parent gets InvNum -- CRInvNum not available in query
b. Child1 (labor, one record per InvNum) -- no CRInvNum
c. Child2 (receipts, multiples per IncNum, CRInvNum
unique, but Child2 linked to parent via InvNum and lists
the most recent.

InvNum and CRInvNum are currency.

No typos, no nulls.

Thanks again.

***********************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum]
= " & Me![InvNum] & " And [CRInvNum] = " & Me! [CRInvNum]
& ")"
************************









-----Original Message-----
Let's try without in internal single quotes. Also check for typos, such as
no space before or after the & signs or misspelled names. Also, this may
have a problem if one of the values being picked up
Click to expand...
from
the form is Null.
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE ([InvNum] = " & Me![InvNum] &
" And [CRInvNum] = " & Me![CRInvNum] & ")"

--
Wayne Morgan
Microsoft Access MVP


Again thanks. I'm still not getting it.

This code:
*******************************************************
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
='& Me![InvNum]& " And [CRInvNum] = " & Me![CRInvNum]
& " '"
*******************************************************
results in error 2465, MS can't find the field "|"...

I'm sorry to be thick, but I am far from a master at
this. BTW, I am very grateful for your help.

Thanks,
Mary

-----Original Message-----
strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"

When you run this SQL, you are asking the SQL
interpreter to know what Me
is. Instead, try concatenating the value into the string.

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
=' & Me![InvNum] & "
And [CRInvNum] =" & Me![CRInvNum] & "'"

This would be correct if the values are number. It'll
take a little more
playing with the quotes if the values are strings.


--
Wayne Morgan
Microsoft Access MVP


message
Wayne, thanks. Big help. I know I'm close, but not
quite
there yet. Got more help for me? Please?

My sfrm 1 has only one record per InvNum; sfrm 2 has
potentially multiple. InvNum is the link all round
here,
with CRInvNum (receipts) being iterated by +.01 for
each
receipt. Thus the highest iteration is always the one
being worked with here and the one displayed. That is
working correctly.

The following code does the deletes, but deletes ALL
the
CRInvNum. I just want the latest to be deleted, the one
that is being displayed. I attempted to further define
by
adding in CRInvNum, but I guess I don't get it enough
to
see the problem.

My single button code on the parent form is:

strSQL = "DELETE * FROM [tbl 4 Receipt] WHERE '[InvNum]
=
Me![InvNum] And [CRInvNum] = Me![CRInvNum] '"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM [tbl 5 PRLabor] WHERE '[InvNum]
=
& Me![InvNum]'"
CurrentDb.Execute strSQL, dbFailOnError

END CODE.

Can you help me further?

Thanks,
Mary




-----Original Message-----
What if there is more than one record in the subform?
Do
you want them all
deleted or just the selected record?

Either way, you could just run a Delete Query from the
button on the main
form. Use the same parameters in the delete query as
are
used to supply data
to the subform. Add the ID field of the subform to the
parameters if you
only want to delete the current record in the subform
instead of all the
records currently displayed in the subform.

Example:
strSQL = "DELETE * FROM tblMyTable WHERE (enter your
parameters here)"
CurrentDb.Execute strSQL, dbFailOnError

Rather than going directly to the table, you may be
able
to substitute the
query feeding the subform for the table in the SQL
string. This may simplify
the parameters needed.

--
Wayne Morgan
Microsoft Access MVP


message
I have a form containing two subforms, each bound to a
different table. The tables are not linked to one
another. Each subform has a command button to delete
the
displayed record.

Anytime the form is displayed, the record in each
subform
is to be deleted.

Is it possible to make a button, resident on the
parent
form that would invoke both the delete command
buttons
without having to make the two separate clicks, thus
rendering moot the delete buttons on the subforms?

This would ensure that both are deleted with no
chance
of
forgetting one or the other.

As always, group help is appreciated.

Mary


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top