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

Deleting selected records in subform (query)

P: 21
Good day sir
Please I have a subform composed of 3 tables merged together to form a query to develop the subform.
Please how do I delete record from this subform?
As I delete, I want the remaining records updated immediately
Thanks sir
Mar 15 '12 #1
Share this Question
Share on Google+
22 Replies


NeoPa
Expert Mod 15k+
P: 31,419
If you want to delete the current record from a form then the following code will ensure that the update is reflected in the current session without the need for a Me.Requery :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)
Mar 15 '12 #2

P: 21
Thank you sir for the prompt reply
Please where do I place this code?
Is it going to be on the form or on the Subform?

The Subform in the main form is formed from 3 different tables.
The subform is a continuos form, I want to put a delete button on it that will delete record immediately
Please any help rendered will be appreciated
Thanks
Mar 15 '12 #3

NeoPa
Expert Mod 15k+
P: 31,419
If you need to delete a record from the form on your subform then you'd need to create a Command Button control on that form and place the code in the Click event procedure for the Command Button.
Mar 15 '12 #4

P: 21
Thank you sir for your replies
Please am very new to ms access vba, I don't the code to write to be able to delete on a continuous subform.
Please anyhelp rendered will be appreciated
Thanks sir
Mar 15 '12 #5

NeoPa
Expert Mod 15k+
P: 31,419
You need to show you've made some sort of attempt to make progress. Simply sitting there asking for it all to be shown to you won't get you very far I'm afraid. It's not an attitude we appreciate here.
Mar 15 '12 #6

P: 21
Thank you sir
The thing is before coming to the site to post this question, I have tried so many different things.
I have tried to create a command button to delete the line I want to delete on my subform
Here is the code I placed on the commend button of my subform
Expand|Select|Wrap|Line Numbers
  1. BtnDelete_Click
  2. Me.sbfOrderDetails.SetFocus
  3. With Me.sbfOrderDetails
  4. DoCmd.RunCommand acCmdDeleteRecord
  5. End With
  6. End Sub
After using this it keeps giving me errors and bring me back to the vba code page so that is why I felt I have written a wrong code.
Thanks sir for your time
Mar 15 '12 #7

NeoPa
Expert Mod 15k+
P: 31,419
That is reasonable for before you asked the question, but what did you try after I responded saying that the button should be added to the form used in your SubForm control? That code is on the main form, so the button, also, must be on the main form. I can show you the code now, but I can't show you where the button should be. I've told you that, but you've shown no signs that you've understood :

Expand|Select|Wrap|Line Numbers
  1. Private Sub BtnDelete_Click()
  2.     Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)
  3. End Sub
This is one reason why you must be involved and not simply allow me to do it for you. That won't even work unless you understand what I'm saying.
Mar 16 '12 #8

P: 21
Thank you sir for the help
I have tried it sir, I placed the button and the code in the form in my subform.
It didn't even run at all
Thank you sir
Mar 16 '12 #9

P: 21
Thanks sir for allowing me try it on my own
I really appreciate it.
When the record was not deleted,I went to the relationship table and I ticked cascade update and cascade delete.
I went back to the form, I clicked on delete ,it sends a message 'do I want to delete record,that after deleting I won't be able to retrieve it back. I clicked yes, it practically deleted it on the form but when I checked the record it was not deleted because the record was not updated.
Thank you sir
Mar 16 '12 #10

NeoPa
Expert Mod 15k+
P: 31,419
Something is not right here Damola. I don't know what you're doing wrong, but certainly something.
  1. How many records show on the subform when it's full?
  2. How many copies of this button are shown?
  3. What has Cascade-Delete got to do with this question? You haven't said anything to lead me to believe it should be involved.

Perhaps it would be a good idea at this stage to indicate what data you're dealing with on the main form and the sub form and how they are related. It's hard to work with so little information, especially when some of it appears to be inaccurate.
Mar 16 '12 #11

P: 21
Thanks sir for the reply
Actually it is an order details form that has a subform in it.
The order details form is composed of 3tables(customers,orders,orders status).
On the subform is composed of 3 tables(drugs,order details,order details status).
On My main form I have
.The customer's name
.The address
.Employee's name
.Date

On the Subform I have
.Item ID which is a combo box
.The quantity the customer wants to buy
.The selling price
.Discount
.Total price

Thank you sir for the help
Mar 16 '12 #12

NeoPa
Expert Mod 15k+
P: 31,419
Damola:
Actually it is an order details form that has a subform in it.
That doesn't seem right, and the rest of your post confirms it. You have an Orders main form with an Order Details subform right? Remember, I only have what you post to work with, so if you post things which are wrong then it will be very hard to help you.

Now, please do all of the following things before I can help you further :
  1. Go back to post #11 and answer both of the first two points which are simple questions.
  2. On the same post, respond to point #3 and explain why you included Cascade Delete as part of the discussion, or confirm that it is irrelevant and I can simply ignore all references to it.
  3. Confirm that my initial statement in this post is correct and we are actually dealing with something that makes sense.
Mar 16 '12 #13

P: 21
Thank you sir for your reply
I just added the cascade on delete it is not relevant, I thought it will help.
I have one delete button on the mainform (order details)
I also have one delete button on the subform to delete record on the subform.
Yes what you posted is correct.
Thank you sir
Mar 16 '12 #14

NeoPa
Expert Mod 15k+
P: 31,419
Damola:
I just added the cascade on delete it is not relevant, I thought it will help.
That's fine, as long as you make that clear. It could have been useful, but in this case it wasn't.

NeoPa:
1. How many records show on the subform when it's full?
This question has still not been answered.

Damola:
I have one delete button on the mainform (order details)
I don't see any relevance to this info. It was not requested.

Damola:
I also have one delete button on the subform to delete record on the subform.
Please explain which Section this control is found in?

Damola:
Yes what you posted is correct.
Good. Please pay more attention in future. Posting incorrect information and failing to answer questions that have been asked wastes both your time and mine. It is very important that you ensure you don't continue in this manner if you hope to get answers to your questions. None of us is stupid and we recognise when a poster has wasted our time before, so are less likely to look at their threads in future. It's only common-sense really.
Mar 16 '12 #15

P: 21
Thank you sir for the replies and your time.
The subform can take 30 records but what I need is just to be able to delete one or two records from the subform.
I placed the delete button in the detail part of the form. The subform is a continuos form and when I change it into a form view all the records have the delete button on each row.
Thank you sir
Mar 16 '12 #16

NeoPa
Expert Mod 15k+
P: 31,419
OK. So we have a button on the form which is in the subform, and you have the code I suggested in post #8 in the module of that same form (The form in the subform and not the main form itself.), I can only guess, if this is not working for you, that the OnClick property of the CommandButton called BtnDelete still needs to be set to the string "[Event Procedure]".

Please check that. If that's fixed then you need to explain what is happening. Are there errors reported? If so what?

NB. Ensure the project is compiled before responding next. Before Posting (VBA or SQL) Code might help with that.
Mar 17 '12 #17

P: 21
Thank you sir for the reply
I placed the vba code in #8 in the click event on the form in the subform.
When I press on delete it tells me you are about to delete 1 record(s) I click on yes it removes it from the form.
When I checked the items remaining it didn't add the items deleted back into the inventory.
Let me give an eaxample sir
After ordering for some items and the customer says he doesn't need them anymore deleting didn't reflect addition of the items back into the inventory sir.
Thank you sir
Mar 19 '12 #18

NeoPa
Expert Mod 15k+
P: 31,419
Please reread my last post. Your post doesn't seem to be a response. You cannot add VBA code to an OnClick event property (which is a string value that points to the name of a macro or the value "[Event Procedure]").

Please try to respond to posts in a way that makes it clear you have actually read them.
Mar 19 '12 #19

P: 21
Am sorry for the wrong use of words what I meant is that I
created a Command Button control on that form and placed the code in #8 in the Click event procedure for the Command Button.
When I press delete it tells me that you are about to delete a record, I deleted the record but it doesn't update my records.
Thank you sir.
Mar 19 '12 #20

NeoPa
Expert Mod 15k+
P: 31,419
Damola:
I deleted the record but it doesn't update my records.
If it's deleted then the records have been updated. If the delete failed then it isn't deleted.

Does "my records" refer to something specific, like a recordset displayed on a form somewhere? If so, please remember I cannot understand what you are saying if you don't say it. If this is the situation, then you need to clarify what you mean by "my records". If it's on a form somewhere then what relationship does that form have to the form where your CommandButton is placed? This is very basic information and not speaking English well is no reason for not including this in your posts. It simply takes a little consideration before posting. A very little bit of your time to avoid wasting much more of ours.
Mar 19 '12 #21

P: 21
Thanks for the reply
'Updating my records' means updating the quantity of items in the inventory.
I checked through my 'orders table' I realized the quantity of items that I deleted on the form has been deleted on the table.
I decided to check my inventory transaction table,I tried deleting the quantity of an item there and to my suprise it deleted and also updated the quantity of items left in the inventory.
Please sir how can I incoporate this in the vba code
Thank you sir
Mar 22 '12 #22

NeoPa
Expert Mod 15k+
P: 31,419
This thread seems to be nothing more than you posting stuff which doesn't make sense and me explaining I can't understand what you say unless you start considering what it is you are trying to say. References in your latest post to a large number of items in your database which have never been introduced or described before leave this as unintelligible (impossible to understand) as most of your earlier posts.

Let me repeat, this is not a language problem, it is a problem with your not properly considering what it is you are trying to say. It's not rocket science, but you seem incapable of expressing your question in a format that makes sense. I advise you to give up now as I don't intend to waste more of my time trying to encourage you to exercise that small amount of thought necessary to make the question make sense.
Mar 22 '12 #23

Post your reply

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