delete record

**PianoMan64** · Feb 18 '09, 09:37 PM

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** · Feb 18 '09, 10:10 PM

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** · Feb 19 '09, 06:37 AM

Originally posted by 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!

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** · Feb 19 '09, 12:47 PM

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** · Feb 19 '09, 02:03 PM

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** · Feb 19 '09, 02:16 PM

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

Code:

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** · Feb 19 '09, 02:50 PM

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** · Feb 19 '09, 03:51 PM

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

Code:

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** · Feb 19 '09, 06:38 PM

I would have that as :

Code:

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** · Feb 19 '09, 07:17 PM

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** · Feb 20 '09, 12:07 AM

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 :

Code:

DELETE *

FROM [work log table]

WHERE (checked);

**NeoPa** · Feb 20 '09, 12:18 AM

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

Code:

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** · Feb 20 '09, 12:27 PM

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** · Feb 20 '09, 01:45 PM

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