Deleting Records

R

RFrechette

Any help with this would be greatly apprecidated.

My form called frm_PVD_02 is filtered by parameters from frm_PVD_subform.
Each record in frm_PVD_02 has a checkbox next to it (Control Name = Chk_Add).

When the SAVE button is clicked on frm_PVD_02, if the field called Chk_Add =
-1, that record is inserted into a table called tbl_PVD_Updates. Each record
in the frm_PVD_02 form has an ID which is part of the data inserted to the
tbl_PVD_Updates table.

This is the code I use (with much help from this forum) and it works
beautifully.

Dim strSQL As String
Dim strSQL1 As String
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone

RsC.MoveFirst

While Not RsC.EOF
Me.Bookmark = RsC.Bookmark

If Chk_Add = -1 And txt_HV_Discount <> 0 Then

strSQL = "INSERT INTO [tbl_PVD_Updates](AcctNo, ItemNo, ProjectNo, Date_PVD,
Charge_Units, Unitpr, Rprice, HV_Discounts, SpecPricing, ID_Updates)" & _
"select" & "'" & txt_AcctNo & "'," & "'" & txt_ItemNo_Real & "'," & "'" &
txt_ProjectNo & "'," & "'" & _
txt_Date_PVD & "'," & "'" & ([txt_Actual Units]) & "'," & "'" & txt_Unitpr &
"'," & "'" & _
[Forms]![frm_PVD]![frm_PVD_02].Form![txt_rprice] & "'," & "'" &
txt_HV_Discount & "'," & "'" & txt_SpecPricing & "'," & "'" & txt_ID_oss_tran
& "';"

DoCmd.RunSQL strSQL

End If

RsC.MoveNext

Wend
Me.Requery
Set RsC = Nothing

------------------------

Now the problem I’m having:

If I’ve checked off all the records I want and save them to the
tbl_PVD_Updates table, and then I need to uncheck one of the records and save
again: How do I delete the record that no longer should be in the
tbl_PVD_Updates table?

I tried many variations of the following code:

strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"

DoCmd.RunSQL strSQL1

I put this code in front of the “If Chk_Add = -1 And txt_HV_Discount <> 0
Then†line within the While statement, but I end up with a continuous loop
(or so it seems).

I hope I've explained this well enough. Can anyone help me? I’m sure it’s
something so simple, I should know it, but I haven’t been using VB for very
long and have no programming experience.

Thank you in advance.
Rachel
 
J

Jeanette Cunningham

Rachel,
this code
---------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"
---------------
has a problem in the where clause.

Try something more like this:
---------------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] ""
Debug.Print strSQL1
DoCmd.RunSQL strSQL1
---------------------
Without knowing how your tables are setup, this is an educated guess, if it
doesn't work, post back with more details.

Jeanette Cunningham

RFrechette said:
Any help with this would be greatly apprecidated.

My form called frm_PVD_02 is filtered by parameters from frm_PVD_subform.
Each record in frm_PVD_02 has a checkbox next to it (Control Name =
Chk_Add).

When the SAVE button is clicked on frm_PVD_02, if the field called Chk_Add
=
-1, that record is inserted into a table called tbl_PVD_Updates. Each
record
in the frm_PVD_02 form has an ID which is part of the data inserted to the
tbl_PVD_Updates table.

This is the code I use (with much help from this forum) and it works
beautifully.

Dim strSQL As String
Dim strSQL1 As String
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone

RsC.MoveFirst

While Not RsC.EOF
Me.Bookmark = RsC.Bookmark

If Chk_Add = -1 And txt_HV_Discount <> 0 Then

strSQL = "INSERT INTO [tbl_PVD_Updates](AcctNo, ItemNo, ProjectNo,
Date_PVD,
Charge_Units, Unitpr, Rprice, HV_Discounts, SpecPricing, ID_Updates)" & _
"select" & "'" & txt_AcctNo & "'," & "'" & txt_ItemNo_Real & "'," & "'" &
txt_ProjectNo & "'," & "'" & _
txt_Date_PVD & "'," & "'" & ([txt_Actual Units]) & "'," & "'" & txt_Unitpr
&
"'," & "'" & _
[Forms]![frm_PVD]![frm_PVD_02].Form![txt_rprice] & "'," & "'" &
txt_HV_Discount & "'," & "'" & txt_SpecPricing & "'," & "'" &
txt_ID_oss_tran
& "';"

DoCmd.RunSQL strSQL

End If

RsC.MoveNext

Wend
Me.Requery
Set RsC = Nothing

------------------------

Now the problem I'm having:

If I've checked off all the records I want and save them to the
tbl_PVD_Updates table, and then I need to uncheck one of the records and
save
again: How do I delete the record that no longer should be in the
tbl_PVD_Updates table?

I tried many variations of the following code:

strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"

DoCmd.RunSQL strSQL1

I put this code in front of the "If Chk_Add = -1 And txt_HV_Discount <> 0
Then" line within the While statement, but I end up with a continuous loop
(or so it seems).

I hope I've explained this well enough. Can anyone help me? I'm sure it's
something so simple, I should know it, but I haven't been using VB for
very
long and have no programming experience.

Thank you in advance.
Rachel
Click to expand...
 
R

RFrechette

Hi Jeanette,

Thank you for your response.

When I entered the code you gave me, I got the compile error: Expected:
end of statement. The cursor then went to the "" at the end of the 4th line.

Also, I was wondering about the line "AND
[Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0" that is in my original
code. I don't see where that is in the code you gave me. There are 2
conditions that need to be met.

Any ideas? Again, thank you.
Rachel

Jeanette Cunningham said:
Rachel,
this code
---------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"
---------------
has a problem in the where clause.

Try something more like this:
---------------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] ""
Debug.Print strSQL1
DoCmd.RunSQL strSQL1
---------------------
Without knowing how your tables are setup, this is an educated guess, if it
doesn't work, post back with more details.

Jeanette Cunningham

RFrechette said:
Any help with this would be greatly apprecidated.

My form called frm_PVD_02 is filtered by parameters from frm_PVD_subform.
Each record in frm_PVD_02 has a checkbox next to it (Control Name =
Chk_Add).

When the SAVE button is clicked on frm_PVD_02, if the field called Chk_Add
=
-1, that record is inserted into a table called tbl_PVD_Updates. Each
record
in the frm_PVD_02 form has an ID which is part of the data inserted to the
tbl_PVD_Updates table.

This is the code I use (with much help from this forum) and it works
beautifully.

Dim strSQL As String
Dim strSQL1 As String
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone

RsC.MoveFirst

While Not RsC.EOF
Me.Bookmark = RsC.Bookmark

If Chk_Add = -1 And txt_HV_Discount <> 0 Then

strSQL = "INSERT INTO [tbl_PVD_Updates](AcctNo, ItemNo, ProjectNo,
Date_PVD,
Charge_Units, Unitpr, Rprice, HV_Discounts, SpecPricing, ID_Updates)" & _
"select" & "'" & txt_AcctNo & "'," & "'" & txt_ItemNo_Real & "'," & "'" &
txt_ProjectNo & "'," & "'" & _
txt_Date_PVD & "'," & "'" & ([txt_Actual Units]) & "'," & "'" & txt_Unitpr
&
"'," & "'" & _
[Forms]![frm_PVD]![frm_PVD_02].Form![txt_rprice] & "'," & "'" &
txt_HV_Discount & "'," & "'" & txt_SpecPricing & "'," & "'" &
txt_ID_oss_tran
& "';"

DoCmd.RunSQL strSQL

End If

RsC.MoveNext

Wend
Me.Requery
Set RsC = Nothing

------------------------

Now the problem I'm having:

If I've checked off all the records I want and save them to the
tbl_PVD_Updates table, and then I need to uncheck one of the records and
save
again: How do I delete the record that no longer should be in the
tbl_PVD_Updates table?

I tried many variations of the following code:

strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"

DoCmd.RunSQL strSQL1

I put this code in front of the "If Chk_Add = -1 And txt_HV_Discount <> 0
Then" line within the While statement, but I end up with a continuous loop
(or so it seems).

I hope I've explained this well enough. Can anyone help me? I'm sure it's
something so simple, I should know it, but I haven't been using VB for
very
long and have no programming experience.

Thank you in advance.
Rachel
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Rachel,
We can't see your form, and the post doesn't give enough detail.
It would help if we had the following:
Name of each form involved and whether they are all open
Name of relevant tables and relevant fields

this code below
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"
Click to expand...
Click to expand...

is trying to say something like
find the row where ID_Updates is equal to ID_oss_tran and Chk_Add

I don't see how the value of ID_Updates can ever be equal to ID_oss_tran and
Chk_Add

I'm not sure that you're syntax for referring to the forms is correct
either.

You seem to have 2 forms
frm_PVD and frm_PVD_02
I had thought they could be form and subform?

There is a problem with this syntax:
[Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add]

When you explain how your forms work together, we will be able to suggest
something more suitable.

Jeanette Cunningham


RFrechette said:
Hi Jeanette,

Thank you for your response.

When I entered the code you gave me, I got the compile error: Expected:
end of statement. The cursor then went to the "" at the end of the 4th
line.

Also, I was wondering about the line "AND
[Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0" that is in my
original
code. I don't see where that is in the code you gave me. There are 2
conditions that need to be met.

Any ideas? Again, thank you.
Rachel

Jeanette Cunningham said:
Rachel,
this code
---------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"
---------------
has a problem in the where clause.

Try something more like this:
---------------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] ""
Debug.Print strSQL1
DoCmd.RunSQL strSQL1
---------------------
Without knowing how your tables are setup, this is an educated guess, if
it
doesn't work, post back with more details.

Jeanette Cunningham

RFrechette said:
Any help with this would be greatly apprecidated.

My form called frm_PVD_02 is filtered by parameters from
frm_PVD_subform.
Each record in frm_PVD_02 has a checkbox next to it (Control Name =
Chk_Add).

When the SAVE button is clicked on frm_PVD_02, if the field called
Chk_Add
=
-1, that record is inserted into a table called tbl_PVD_Updates. Each
record
in the frm_PVD_02 form has an ID which is part of the data inserted to
the
tbl_PVD_Updates table.

This is the code I use (with much help from this forum) and it works
beautifully.

Dim strSQL As String
Dim strSQL1 As String
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone

RsC.MoveFirst

While Not RsC.EOF
Me.Bookmark = RsC.Bookmark

If Chk_Add = -1 And txt_HV_Discount <> 0 Then

strSQL = "INSERT INTO [tbl_PVD_Updates](AcctNo, ItemNo, ProjectNo,
Date_PVD,
Charge_Units, Unitpr, Rprice, HV_Discounts, SpecPricing, ID_Updates)" &
_
"select" & "'" & txt_AcctNo & "'," & "'" & txt_ItemNo_Real & "'," & "'"
&
txt_ProjectNo & "'," & "'" & _
txt_Date_PVD & "'," & "'" & ([txt_Actual Units]) & "'," & "'" &
txt_Unitpr
&
"'," & "'" & _
[Forms]![frm_PVD]![frm_PVD_02].Form![txt_rprice] & "'," & "'" &
txt_HV_Discount & "'," & "'" & txt_SpecPricing & "'," & "'" &
txt_ID_oss_tran
& "';"

DoCmd.RunSQL strSQL

End If

RsC.MoveNext

Wend
Me.Requery
Set RsC = Nothing

------------------------

Now the problem I'm having:

If I've checked off all the records I want and save them to the
tbl_PVD_Updates table, and then I need to uncheck one of the records
and
save
again: How do I delete the record that no longer should be in the
tbl_PVD_Updates table?

I tried many variations of the following code:

strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"

DoCmd.RunSQL strSQL1

I put this code in front of the "If Chk_Add = -1 And txt_HV_Discount <>
0
Then" line within the While statement, but I end up with a continuous
loop
(or so it seems).

I hope I've explained this well enough. Can anyone help me? I'm sure
it's
something so simple, I should know it, but I haven't been using VB for
very
long and have no programming experience.

Thank you in advance.
Rachel
Click to expand...
Click to expand...
Click to expand...
 
R

RJF

Hi Jeanette,

Thank you for your help.

I think I've got it figured out now though.
--
RJF


Jeanette Cunningham said:
Rachel,
We can't see your form, and the post doesn't give enough detail.
It would help if we had the following:
Name of each form involved and whether they are all open
Name of relevant tables and relevant fields

this code below
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"
Click to expand...
Click to expand...

is trying to say something like
find the row where ID_Updates is equal to ID_oss_tran and Chk_Add

I don't see how the value of ID_Updates can ever be equal to ID_oss_tran and
Chk_Add

I'm not sure that you're syntax for referring to the forms is correct
either.

You seem to have 2 forms
frm_PVD and frm_PVD_02
I had thought they could be form and subform?

There is a problem with this syntax:
[Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add]

When you explain how your forms work together, we will be able to suggest
something more suitable.

Jeanette Cunningham


RFrechette said:
Hi Jeanette,

Thank you for your response.

When I entered the code you gave me, I got the compile error: Expected:
end of statement. The cursor then went to the "" at the end of the 4th
line.

Also, I was wondering about the line "AND
[Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0" that is in my
original
code. I don't see where that is in the code you gave me. There are 2
conditions that need to be met.

Any ideas? Again, thank you.
Rachel

Jeanette Cunningham said:
Rachel,
this code
---------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"
---------------
has a problem in the where clause.

Try something more like this:
---------------------
strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] ""
Debug.Print strSQL1
DoCmd.RunSQL strSQL1
---------------------
Without knowing how your tables are setup, this is an educated guess, if
it
doesn't work, post back with more details.

Jeanette Cunningham

Any help with this would be greatly apprecidated.

My form called frm_PVD_02 is filtered by parameters from
frm_PVD_subform.
Each record in frm_PVD_02 has a checkbox next to it (Control Name =
Chk_Add).

When the SAVE button is clicked on frm_PVD_02, if the field called
Chk_Add
=
-1, that record is inserted into a table called tbl_PVD_Updates. Each
record
in the frm_PVD_02 form has an ID which is part of the data inserted to
the
tbl_PVD_Updates table.

This is the code I use (with much help from this forum) and it works
beautifully.

Dim strSQL As String
Dim strSQL1 As String
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone

RsC.MoveFirst

While Not RsC.EOF
Me.Bookmark = RsC.Bookmark

If Chk_Add = -1 And txt_HV_Discount <> 0 Then

strSQL = "INSERT INTO [tbl_PVD_Updates](AcctNo, ItemNo, ProjectNo,
Date_PVD,
Charge_Units, Unitpr, Rprice, HV_Discounts, SpecPricing, ID_Updates)" &
_
"select" & "'" & txt_AcctNo & "'," & "'" & txt_ItemNo_Real & "'," & "'"
&
txt_ProjectNo & "'," & "'" & _
txt_Date_PVD & "'," & "'" & ([txt_Actual Units]) & "'," & "'" &
txt_Unitpr
&
"'," & "'" & _
[Forms]![frm_PVD]![frm_PVD_02].Form![txt_rprice] & "'," & "'" &
txt_HV_Discount & "'," & "'" & txt_SpecPricing & "'," & "'" &
txt_ID_oss_tran
& "';"

DoCmd.RunSQL strSQL

End If

RsC.MoveNext

Wend
Me.Requery
Set RsC = Nothing

------------------------

Now the problem I'm having:

If I've checked off all the records I want and save them to the
tbl_PVD_Updates table, and then I need to uncheck one of the records
and
save
again: How do I delete the record that no longer should be in the
tbl_PVD_Updates table?

I tried many variations of the following code:

strSQL1 = "DELETE * " & _
"FROM tbl_PVD_Updates " & _
"WHERE tbl_PVD_Updates.ID_Updates = " &
[Forms]![frm_PVD]![frm_PVD_02].[Form]![ID_oss_tran] & _
"AND [Forms]![frm_PVD]![frm_PVD_02].[Form]![Chk_Add] = 0"

DoCmd.RunSQL strSQL1

I put this code in front of the "If Chk_Add = -1 And txt_HV_Discount <>
0
Then" line within the While statement, but I end up with a continuous
loop
(or so it seems).

I hope I've explained this well enough. Can anyone help me? I'm sure
it's
something so simple, I should know it, but I haven't been using VB for
very
long and have no programming experience.

Thank you in advance.
Rachel
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Break Code within While Loop 2
Difference in Last Statement 2
Make Field Enterable 1
forms & subforms & deleting records 2
Attn Sprinks - clarification on VB code 6

Top