Delete Record

K

kealaz

Hello,
I have a form that is getting it's information from one table,
tblPOTODO and on the form, the user makes some choices for which manuf and
vendor we will purchase this part from. Then, the information is written to
another table, tblBUY. AMAZINGLY I got all this to work.
Now I need to get it to delete the record from the first table
(tblPOTODO) once the "processing" of the part is completed so that the record
is deleted from tblPOTODO when it is added to tblBUY, so that it is not in
two places at one time.
Please help me with this. I am very new, so please include any code
that I might need and where to place it.

Thank you very, very much!
 
G

Gina Whipp

Kealaz,

I am not sure why you have tblPOTODO AND a tblBuy? You could have a
tblPOTODO with a Order/Buy Yes/No field. Is there some reason you have
these two different tables?

To answer your question it would help to know which field in tblPOTODO lets
one know it is to be deleted or is it just because you moved it to tblBuy?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
K

kealaz

Hi Gina,
I am converting a database from dbase IV to access. This was written
many moons ago (20yrs? +). I'm sure there are better ways to do things, but
since I'm not a db programmer, I am just following the lead of the original
programmer and doing the best I can. I know there are flaws in the actual
design of the program and there are more efficient ways to do things, but I
am not capable of redesigning the entire thing. Sooo, I'm just trying to
take it bite by bite and hopefully I'll get there with something that works.
There are two tables and here is what they look like.

tblPOTODO
PARTNO
MANUF1
MANUF1PN
MANUF2
MANUF2PN
MANUF3
MANUF3PN

tblBUY
PARTNO
MANUF
VENDORNAME

My form brings in the information from tblPOTODO and the user can choose
which manuf to purchase. Once that selection is made, then a combo box is
populated with the vendors that sell that manuf part, and the user can choose
which vendor to purchase the part from. Then, the information... PARTNO, ONE
of the MANUF and the VENDOR... is written to tblBUY. My purchase order
subform gets it's information from tblBUY.

Most... or a lot of the above is working right now. What I am asking is how
do I get my form to delete the record from tblPOTODO once I have selected the
manuf and vendor and passed that information to tblBUY?

Thank you so much for your time and consideration with this!!!
 
G

Gina Whipp

Kealaz,

You can use a Delete query once it is confirmed that the Part was ordered...
This query assumes tblPOTODO only contains the records to be deleted.

DELETE tblPOTODO.*
FROM tblPOTODO INNER JOIN tblBuy ON tblPOTODO.PARTNO = tblBuy.PARTNO;

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
K

kealaz

Thank you Gina. Where would I put this code? I currently have a command
button that saves (to tblBUY) and closes the form. Is there anyway that I
can get it to run this bit of code also? Or do I need to create a new query
by going to the queries window and making a new query and then calling it
somehow? Thank you for the code, please help me by letting me know where to
place it.

~Kealaz
 
K

kealaz

I'm not sure what you are asking. I will use the code you gave in the prior
post, but I just don't know where to put it. I would like to use it on the
command button, but I don't know where to put your code to make it work.

DELETE tblPOTODO.*
FROM tblPOTODO INNER JOIN tblBuy ON tblPOTODO.PARTNO = tblBuy.PARTNO;


Thank you and sorry for being so "new".

~Angel
 
K

kealaz

Where do I put the code to add it to the command button? Please help? I am
not understanding.

Thank you,
Kealaz
 
D

Dirk Goldgar

kealaz said:
I'm not sure what you are asking. I will use the code you gave in the
prior
post, but I just don't know where to put it. I would like to use it on
the
command button, but I don't know where to put your code to make it work.

DELETE tblPOTODO.*
FROM tblPOTODO INNER JOIN tblBuy ON tblPOTODO.PARTNO = tblBuy.PARTNO;
Click to expand...


Hmm, that will delete every record in tblPOTODO that has a matching PARTNO
in tblBuy. That may or may not be what you want. If you only want to
delete the specific PARTNO that is current on this form, I would specify
that PARTNO in the SQL statement.

I don't see where you've posted your command button's current code, but I
gather it does something like this:

'--------------
Private Sub cmdFinish()

' Save the current record.
If Me.Dirty Then Me.Dirty = False
' or maybe:
' RunCommand acCmdSaveRecord

' Close this form.
DoCmd.Close acForm, Me.Name, acSaveNo

End Sub
'--------------

To add the delete to that code, you would modify it something like this:

'--------------
Private Sub cmdFinish()

' Save the current record.

If Me.Dirty Then Me.Dirty = False
' or maybe:
' RunCommand acCmdSaveRecord

' Delete the current part from tblPOTODO.

If Not IsNull(Me.PARTNO) Then
CurrentDb.Execute _
"DELETE FROM tblPOTODO WHERE PARTNO='" & Me.PARTNO & "'", _
dbFailOnError
End If

' Close this form.

DoCmd.Close acForm, Me.Name, acSaveNo

End Sub
'--------------

Not that the above code assumes that PARTNO is a text field. If it's a
numeric field, then you don't need the surrounding quotes in the SQL
statement, and should write it like this:

CurrentDb.Execute _
"DELETE FROM tblPOTODO WHERE PARTNO=" & Me.PARTNO, _
dbFailOnError
 
K

kealaz

I'm hoping this is the code. I copied this from the command button.
On Click: [Event Procedure]


'--------------
Private Sub SaveClose_Click()
On Error GoTo Err_SaveClose_Click

Dim stDocName As String

stDocName = "mcSaveClose"
DoCmd.RunMacro stDocName

Exit_SaveClose_Click:
Exit Sub

Err_SaveClose_Click:
MsgBox Err.Description
Resume Exit_SaveClose_Click

End Sub

'--------------

It's calling a macro. The macro is simply "Save" and then "Close". The
only reason I used a macro is because I wanted to combine two command buttons
(you know, the ready made ones) into one click for the user.

Thanks for your help!!!

~Kealaz
 
K

kealaz

Hi Dirk / Gina,

Thank you BOTH for all of your help with this. I have completely
abandoned the macro and replaced all of the code I had with what Dirk gave
me. It works (yay!) IF I am only processing one part number... however, if I
process more than one part number and tab to the next record (using the form)
to do another one, then it only deletes, from tblPOTODO, the last one I was
working on, which makes sense from the way Dirk described it. I think what
Gina wrote for me might be better. (Yes, there will only be one part number
in tblPOTODO at any given time.) I just, still, don't know where to put her
line of code. When I tried to insert what Gina gave me into what Dirk gave
me, I got a bunch of red text and error messages.

The following does NOT work. Please let me know where I'm going wrong.


'--------------
Private Sub SaveClose_Click()

If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.PART_NO) Then
CurrentDb.Execute _
DELETE tblPOTODO.*
FROM tblPOTODO INNER JOIN tblBuy ON tblPOTODO.PART_NO = tblBuy.PART_NO;
dbFailOnError
End If

DoCmd.Close acForm, Me.NAME, acSaveNo


End Sub
'--------------

Thank you BOTH again for all of your time and consideration with this.

~Kealaz
 
K

kealaz

The error I get is

Compile Error: Syntax Error


Please look at the code, and let me know what I should change.

Thank you,
Kealaz
 
J

John W. Vinson

Where do I put the code to add it to the command button? Please help? I am
not understanding.
Click to expand...

Open the form in design view.
Select the command button.
View its Properties.
On the Events tab find the Click event. It should say [Event Procedure].
Click the ... icon by it to open the VBA editor.
Copy and paste the code.
 
D

Dirk Goldgar

kealaz said:
Hi Dirk / Gina,

Thank you BOTH for all of your help with this. I have completely
abandoned the macro and replaced all of the code I had with what Dirk gave
me. It works (yay!) IF I am only processing one part number... however,
if I
process more than one part number and tab to the next record (using the
form)
to do another one, then it only deletes, from tblPOTODO, the last one I
was
working on, which makes sense from the way Dirk described it.
Click to expand...

Yes, I assumed from what you wrote that you were processing only one part at
a time.
I think what
Gina wrote for me might be better. (Yes, there will only be one part
number
in tblPOTODO at any given time.) I just, still, don't know where to put
her
line of code. When I tried to insert what Gina gave me into what Dirk
gave
me, I got a bunch of red text and error messages.

The following does NOT work. Please let me know where I'm going wrong.


'--------------
Private Sub SaveClose_Click()

If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.PART_NO) Then
CurrentDb.Execute _
DELETE tblPOTODO.*
FROM tblPOTODO INNER JOIN tblBuy ON tblPOTODO.PART_NO = tblBuy.PART_NO;
dbFailOnError
End If

DoCmd.Close acForm, Me.NAME, acSaveNo


End Sub
'--------------
Click to expand...


You need to put the SQL statement into quotes, because it is a string
argument being passed to CurrentDb.Execute. Try this:

'----- start of revised code -----
Private Sub SaveClose_Click()

' Force this record to be saved, if it hasn't been already.
If Me.Dirty Then Me.Dirty = False

' Delete all records from tblPOTODO that we have processed
' into tblBuy.
CurrentDb.Execute _
"DELETE tblPOTODO.* " & _
"FROM tblPOTODO INNER JOIN tblBuy " & _
"ON tblPOTODO.PART_NO = tblBuy.PART_NO", _
dbFailOnError

' Close this form.
DoCmd.Close acForm, Me.Name, acSaveNo

End Sub
'----- end of revised code -----
 

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

DELETE record 2
Writing to a table from a form 1
How do I write to a DIFFERENT table than the Record Source table? 3
HOW do I make selections and carry only those selections thru proc 1
Delete Query -- QUESTION 1
POPULATE a textbox based on combo box selection 3
HOW do I make selections and carry only those selections thru proc 5
How do I add a COLUMN to combo box 2

Top