delete record

[didacticone]

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!

[PianoMan64]

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.

[didacticone]

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!

[PianoMan64]

@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.

[didacticone]

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

[NeoPa]

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.

[didacticone]

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

Private Sub Command78_Click()
 
DoCmd.OpenQuery "work log query"
Select Case Me!Action
Case "Change"
DoCmd.OpenForm "Meter Change"
Case "set"
DoCmd.OpenForm "set"
End Select
 
End Sub

[NeoPa]

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.

[didacticone]

ok, now this leads me to another question.. which i will as after the sql for the query

INSERT INTO [test pink table] ( checked, Acct, [Date], [Action], [Size], [Number], Street, Town, Comments )
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
FROM [work log table]
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

[NeoPa]

I would have that as :

INSERT INTO [test pink table] ( checked,
                                Acct,
                                [Date],
                                [Action],
                                [Size],
                                [Number],
                                Street,
                                Town,
                                Comments )
 
SELECT checked,
       Acct,
       [Date],
       [Action],
       [Size],
       [Number],
       Street,
       Town,
       Comments
 
FROM [work log table]
 
WHERE (checked);

PS. I'll look at the question later.

[didacticone]

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!

[NeoPa]

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 :

DELETE *
 
FROM [work log table]
 
WHERE (checked);

[NeoPa]

Assuming your delete query is called [ClearLog], your code might look something like :

Private Sub Command78_Click()
  With CurrentDB
    Call .QueryDefs("work log query").Execute
    Call .QueryDefs("ClearLog").Execute
    Call Me.Requery
  End With
 
  Select Case Me!Action
  Case "Change"
    Call DoCmd.OpenForm("Meter Change")
  Case "set"
    Call DoCmd.OpenForm("set")
  End Select
End Sub

[didacticone]

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?

[NeoPa]

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).

[didacticone]

yes your right... those are the only forms involed right now.. thanks!

[didacticone]

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

[NeoPa]

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.

[didacticone]

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

[NeoPa]

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.

[didacticone]

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

[NeoPa]

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.

[didacticone]

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

[NeoPa]

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.

[didacticone]

it says 5mb for me as well... thats why im confused

[NeoPa]

Let me make some enquiries.

[didacticone]

any luck with that???

[NeoPa]

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.

[didacticone]

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!

[NeoPa]

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.

[didacticone]

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!

[NeoPa]

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.

[didacticone]

ok ill try again in the morning.. ill let you know

[didacticone]

Copy of Appt.zip

ok i attached the file for you... it worked now.. thanks for your help!

[NeoPa]

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.

[didacticone]

not a problem... i appreciate your help!

[NeoPa]

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).

[NeoPa]

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 :

Private Sub Command78_Click()
  'The following line seems strange, but actually saves the unsaved data
  If Me.Dirty Then Me.Dirty = False
  With CurrentDb
    Call .QueryDefs("work log query").Execute
    'Call .QueryDefs("delete work log").Execute
    Call Me.Requery
  End With
 
  Select Case Me!Action
  Case "Change"
    Call DoCmd.OpenForm("Meter Change")
  Case "set"
    Call DoCmd.OpenForm("set")
  End Select
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.

[didacticone]

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!

[NeoPa]

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.

[didacticone]

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!