By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

delete record

100+
P: 250
i have a form, when a radio button is checked and a command button is clicked, the information from that form is then transferred through an append query to a different table. what i need, is when i click that command button, i would also like it to delete that record from the underlying table... please help! thanks!
Feb 18 '09 #1
Share this Question
Share on Google+
40 Replies


Expert 100+
P: 374
Hey didacticone,

Since you don't provide a great deal of detail here. I'm going to assume a few things.

1. I'm assuming that when you're entering this information into the form, it is either not save in the current table yet, or depending on which radio button is select and which command button clicked, you're wanting the information to be stored in a particular table? Am I correct?

2. Assuming that is correct, first thing is we're going to have to make sure that the current form that you're using isn't bound to any table, because that is causing you a great deal of problems, I'm beating?

3. Then you're going to have to create and a connection and open to the correct table that you're wanting to put the information into.

4. Save the record

5. close the connection

6. clear the form.

If you need code example of any of this, please let me know.

Thanks,

Joe P.
Feb 18 '09 #2

100+
P: 250
sorry, i always have trouble fully explaining what exactly i need help with... let me try again... ill give an example... my form is based off of a table that has a list of appointments for a given day. next to each appointment on the form i have a radio button and a command button. the functions i have no are when a radio button is checked for an appointment and the corresponding control button is clicked, it runs an append query to transfer that record into another table and opens up another form which is looking at the table that the record was just appended too... i know its a little confusing... and if you have any suggestions with that... im open... but what i would like is... when i click that command button it also will delete the record that was originally checked from the form... i hope that clarifies a little better for you... if youd like i could even send you the database so you can get a better idea... thanks for your help!
Feb 18 '09 #3

Expert 100+
P: 374
@didacticone
Hey didacticone,

If you would like to attached your database, I would be more than happy to take a look at it.

From what you're talking about is, if you have an pending appointment, and that appointment is moved into active status, you want to be able to send that action over to another form to make additional modifications to the entry? Correct?

If so, then here's what you're going to have to do:

1. Create a list control with all of the pending appointments.
2. Using the Primary Key value from the table that has the pending appointments, lookup the selected record and pull whatever information from that form into the current form and populate corresponding fields with those values.

Again, if you need help with code examples, please let me know.

Thanks,

Joe P.
Feb 19 '09 #4

100+
P: 250
it wont allow me to post it in the forum... size limit it says... can i just email it to you... if youd like to private message me with your email then i can send it to you... im really confused with this problem... so your help is greatly appreciated
Feb 19 '09 #5

NeoPa
Expert Mod 15k+
P: 31,418
I don't want to get in the way here but, while your current design is not something I'd advise, it is nevertheless possible to do pretty well exactly as asked, which is to remove the record from the original dataset after it's been copied to the other table (You'd be far better off using the one table and setting a status field to indicate which part of the process it's in - but enough said on that for now).

Unfortunately, as you mentioned earlier, you don't really provide much in the way of information to work with. Bearing in mind your difficulty expressing (and it's also just much easier) I suggest you post the code you currently use behind your form's command button. This will probably have the references to the various names that we'll need to progress.

Again, I don't want to divert this away from what Joe is doing, but this could be considered as an alternative.
Feb 19 '09 #6

100+
P: 250
i definitly appreciate any help that anyone can provide.

i ultimately need the correct form to open based upon what is entered in the action field. it then runs the query to send to info from the one table that is just for appointments, to the other table where the data will be entered on the newly opened form.

the purpose of the whole thing is to have a worklog form so to speak for our guys that will be populated by me daily. this is displayed on the first form... it lists all their appointments. what i want them to be able to do, is click a radio button next to a the job they are going to work on and then click the button to send them to the proper form for them to complete their entry.. meanwhile sending the info from the checked apointment over to the new form that opens so they dont have to retype it... im trying to be as thorough as possible and again if you need more info ill try my best! thanks for everything guys.

here is my code behind the command button


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command78_Click()
  2.  
  3. DoCmd.OpenQuery "work log query"
  4. Select Case Me!Action
  5. Case "Change"
  6. DoCmd.OpenForm "Meter Change"
  7. Case "set"
  8. DoCmd.OpenForm "set"
  9. End Select
  10.  
  11. End Sub
Feb 19 '09 #7

NeoPa
Expert Mod 15k+
P: 31,418
Am I right to assume then that the query [work log query] copies the data to the other table for you, and that both the [Meter Change] and the [Set] forms access the same table (the one the data was copied to)?

If so, you need to design a delete query that will delete the relevant record from your original table. Add a call to this query, and then issue a call to Me.Requery, to do what you need.

To help further I would need the SQL from your [work log query] query. I assumed the SQL would be embedded in your code, but as it's not I need this too. That should give me the names of the objects to work with.
Feb 19 '09 #8

100+
P: 250
ok, now this leads me to another question.. which i will as after the sql for the query

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [test pink table] ( checked, Acct, [Date], [Action], [Size], [Number], Street, Town, Comments )
  2. SELECT [work log table].checked, [work log table].Acct, [work log table].Date, [work log table].Action, [work log table].Size, [work log table].Number, [work log table].Street, [work log table].Town, [work log table].Comments
  3. FROM [work log table]
  4. WHERE ((([work log table].checked) Like "-1"));
i have the radio button linked to a field in the table... and the criteria in the query is set to "-1" ... the problem is... that this doesnt update instantly... i can check the button and hit my command button but it says updating 0 rows... then if i do it again it works... do i need coding somewhere else to accomplish this? thanks again
Feb 19 '09 #9

NeoPa
Expert Mod 15k+
P: 31,418
I would have that as :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [test pink table] ( checked,
  2.                                 Acct,
  3.                                 [Date],
  4.                                 [Action],
  5.                                 [Size],
  6.                                 [Number],
  7.                                 Street,
  8.                                 Town,
  9.                                 Comments )
  10.  
  11. SELECT checked,
  12.        Acct,
  13.        [Date],
  14.        [Action],
  15.        [Size],
  16.        [Number],
  17.        Street,
  18.        Town,
  19.        Comments
  20.  
  21. FROM [work log table]
  22.  
  23. WHERE (checked);
PS. I'll look at the question later.
Feb 19 '09 #10

100+
P: 250
ok i have changed the query, as you said. but the problem that i stated above still remains.. so yeah when you have a chance and can help that would be great. thanks!
Feb 19 '09 #11

NeoPa
Expert Mod 15k+
P: 31,418
If we start with the original question. You need to run another query after this one has run. The new query should contain the following SQL :
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2.  
  3. FROM [work log table]
  4.  
  5. WHERE (checked);
Feb 20 '09 #12

NeoPa
Expert Mod 15k+
P: 31,418
Assuming your delete query is called [ClearLog], your code might look something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command78_Click()
  2.   With CurrentDB
  3.     Call .QueryDefs("work log query").Execute
  4.     Call .QueryDefs("ClearLog").Execute
  5.     Call Me.Requery
  6.   End With
  7.  
  8.   Select Case Me!Action
  9.   Case "Change"
  10.     Call DoCmd.OpenForm("Meter Change")
  11.   Case "set"
  12.     Call DoCmd.OpenForm("set")
  13.   End Select
  14. End Sub
Feb 20 '09 #13

100+
P: 250
ok i did everything you said.. but im still having the same issue. when i open my form and click the radio button and then the command button the form opens up but nothing was transferred over. if i close the worklog form while still leaving the radio button checked and then open it again, then it works... it seems as though it needs a refresh or something... (i know thats not the technical term im just trying to explain it lol) any ideas?
Feb 20 '09 #14

NeoPa
Expert Mod 15k+
P: 31,418
Firstly, am I right in assuming there are 3 forms involved so far? The [WorkLog] form, and the two optional forms in the code [Meter Change] & [Set]?

Your post does remind me that I omitted a line that I'd considered was required (must have forgotten when I got around to coding it up - sorry).

I've edited the original post to reflect the change (See new line #5).
Feb 20 '09 #15

100+
P: 250
yes your right... those are the only forms involed right now.. thanks!
Feb 20 '09 #16

100+
P: 250
ok i added that line. and im still having the same problem. if i reopen the form with the radio still checked then the query works.. but if i just open the form, check the button and hit the command button... it doesnt send any info over
Feb 20 '09 #17

NeoPa
Expert Mod 15k+
P: 31,418
OK. Try redoing it with the updated code.

If you have problems, try to explain exactly what they are, clearly and with references to error messages where appropriate and / or line numbers in the code.
Feb 20 '09 #18

100+
P: 250
im not getting any error messages... the query just doesnt grab any information when i just open the form click the radio button and hit the command button... it takes reopening the form with the radio button still clicked for it to then grab the information and send it to the [meter change] form
Feb 20 '09 #19

NeoPa
Expert Mod 15k+
P: 31,418
I'm afraid your description doesn't leave me with much better of an understanding of what may be going wrong here. I'm left with guessing what it is you haven't said.

Is it possible that the [Meter Change] form is already open BEFORE you open it in your code? If so it, too, needs to be requeried.

I'm afraid that's the only scenario I can come up with that would cause the results you describe, and I suspect that's not the case. I wouldn't assume you'd leave out something like that if it were the case. I just mention it in case, and because it's all I can think of that would explain the symptoms.
Feb 22 '09 #20

100+
P: 250
since i cant seem to explain it well enough, would i be able to send you the file so you can see exactly what its doing... it might make more sense that way... so then i dont have to try and explain anymore... lol ... let me know
Feb 23 '09 #21

NeoPa
Expert Mod 15k+
P: 31,418
Please first answer the question.
@NeoPa
If you still wish me to look at your project, then you will need to attach it to a post in this thread. This is done from the Manage Attachments link on the Edit page. Please Zip up the database first, to take as little space as possible.
Feb 23 '09 #22

100+
P: 250
no the meter change form is not open before im calling to it in my code...

when i try to upoad the file i get
"Exceeds forum quota by 418.0 KB"

the file is only 422kb... i cant seem to get anything to work lol
Feb 23 '09 #23

NeoPa
Expert Mod 15k+
P: 31,418
Can you tell me what the max size is as listed for Zip files when you've clicked on Manage Attachments. It says 5MB for me, but that may be different for me as I'm an administrator.
Feb 23 '09 #24

100+
P: 250
it says 5mb for me as well... thats why im confused
Feb 23 '09 #25

NeoPa
Expert Mod 15k+
P: 31,418
Let me make some enquiries.
Feb 23 '09 #26

100+
P: 250
any luck with that???
Feb 23 '09 #27

NeoPa
Expert Mod 15k+
P: 31,418
Not yet. I suspect this is a bug in the site software somewhere. We'll be very lucky if it get's fixed overnight. I'm also asking for a work-around that doesn't involve my sharing my email address.

We may have to wait a while for something, but at least the question's asked.
Feb 23 '09 #28

100+
P: 250
i can try uploading it to a website for you and you can download it from there.. or i can create a free email with the pw of your choice and you can retrieve it that way... whatever your more comfortable with... i have sent a file to someone on here before, but i understand your concern... if you like either of those options i will set them up and let you know tomorow... let me know... thanks again!
Feb 23 '09 #29

NeoPa
Expert Mod 15k+
P: 31,418
If you post a link to it on the web somewhere, I will let you know when I've downloaded it.

I should point out though, that downloaded databases are very much a last resort. My time available to work on such databases is much more restricted than my normal work. I only ever dl them at home. Evening time this week is fairly tight until Thursday at least. I may get some free time, then again I may not. It's in the lap of the gods (as it were).

PS. If you're quick tonight I may be able to get it downloaded at least tonight.
Feb 23 '09 #30

100+
P: 250
i dont have the file on the computer im currently on unfortunately, otherwise i would. i should have it up by 8am (EST) tomorow (tuesday) morning... ill make a post on here to let you know... i think it would just help if you can actually see the problem... and i completly understand it is at your leisure ... thanks a ton!
Feb 24 '09 #31

NeoPa
Expert Mod 15k+
P: 31,418
Try attaching first.

I have seen a test where a 2MB Zip file has been attached. That's the limit so far, but should be plenty for your database.
Feb 24 '09 #32

100+
P: 250
ok ill try again in the morning.. ill let you know
Feb 24 '09 #33

100+
P: 250
Copy of Appt.zip

ok i attached the file for you... it worked now.. thanks for your help!
Feb 24 '09 #34

NeoPa
Expert Mod 15k+
P: 31,418
OK. I'm GMT and it's 12:40 here.

I will not be able to download until at least 18:30 when I get home. I'll try to look at it then for you.
Feb 24 '09 #35

100+
P: 250
not a problem... i appreciate your help!
Feb 24 '09 #36

NeoPa
Expert Mod 15k+
P: 31,418
The record's not updated before you try to process it in the query.

In your case (you have a form in continuous mode) I would take the CommandBox off the detail line and add it in the form header or footer.

That way you can select as many records as you like, then hit the single command button afterwards (ensuring of course, that each is fully updated before processing).
Feb 24 '09 #37

NeoPa
Expert Mod 15k+
P: 31,418
I did a little digging to find how (best) to save the record from your code. I added line #3 to your code to handle this for you :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command78_Click()
  2.   'The following line seems strange, but actually saves the unsaved data
  3.   If Me.Dirty Then Me.Dirty = False
  4.   With CurrentDb
  5.     Call .QueryDefs("work log query").Execute
  6.     'Call .QueryDefs("delete work log").Execute
  7.     Call Me.Requery
  8.   End With
  9.  
  10.   Select Case Me!Action
  11.   Case "Change"
  12.     Call DoCmd.OpenForm("Meter Change")
  13.   Case "set"
  14.     Call DoCmd.OpenForm("set")
  15.   End Select
  16. End Sub
I fuller reference can be found on Allen Browne's site at Losing data when you close a form. It's not as straightforward as I expected, and certainly not as it should be.
Feb 24 '09 #38

100+
P: 250
your awesome... sorry i can describe my problems to you more clearly as maybe it would have helped.. but it works great with that new code you have given me... i greatly apreciate your help with this and for the website! and im sure ill be back lol... thanks again!
Feb 24 '09 #39

NeoPa
Expert Mod 15k+
P: 31,418
I'm very pleased to hear you're so happy :)

Don't forget the record deletin query is currently commented out. That will need to be undone if it's to work fully as required.

Good luck with your project.
Feb 24 '09 #40

100+
P: 250
yeah i didnt want to have to keep adding records while testing it... as it was becoming a pain lol... youve been very helpful... thanks a ton!
Feb 24 '09 #41

Post your reply

Sign in to post your reply or Sign up for a free account.