rmission
asked on
Requery then Refresh my sub-form.
I have a subform whose source is a query. The source query has only one (1) table and it returns only those records from the table that are not 'marked_for_delete'. Obviously my table (the source for this query) has a field called marked_for_delete which is set to the current date when the user deletes a record via the subform.
My application does not allow the user to actually delete a record via the subfom, but instead it marks the record as deleted by setting the marked_for_delete field to the current_date.
In my ON_DEL;ETE event of my subform, I have written visual basic code to mark the deleted record as deleted (by setting the marked_for_delete field to the current date) and then cancel the ON_DELETE event (docmd.cancelevent).
A custom message pops up telling the user that the record was deleted.
I want to give the user the impression that he/she has indeed deleted the record (although in reality, it is only marked for delete). However, this is where my problem lies. How can I refresh my subform after the ON_DELETE event has completed, so that the subform no longer displays the 'deleted' record?
I tried ME.REFRESH in my code, but this didn't refresh the displayed records. The 'deleted' record was still displayed. I got an error when I attempted ME.REQUERY.
Can someone tell me how I can requery and then refresh my subform after a record is 'deleted', so that it excludes the record that was 'deleted' from its display.
Your help will be appreciated.
My application does not allow the user to actually delete a record via the subfom, but instead it marks the record as deleted by setting the marked_for_delete field to the current_date.
In my ON_DEL;ETE event of my subform, I have written visual basic code to mark the deleted record as deleted (by setting the marked_for_delete field to the current date) and then cancel the ON_DELETE event (docmd.cancelevent).
A custom message pops up telling the user that the record was deleted.
I want to give the user the impression that he/she has indeed deleted the record (although in reality, it is only marked for delete). However, this is where my problem lies. How can I refresh my subform after the ON_DELETE event has completed, so that the subform no longer displays the 'deleted' record?
I tried ME.REFRESH in my code, but this didn't refresh the displayed records. The 'deleted' record was still displayed. I got an error when I attempted ME.REQUERY.
Can someone tell me how I can requery and then refresh my subform after a record is 'deleted', so that it excludes the record that was 'deleted' from its display.
Your help will be appreciated.
You may need to do an update after setting the to_delete date, then requery.
hi
If you just want to marks ur record as deleted and dont want to delete them in actual and want to show user that the record is deleted then you can do
You must have field in subform in which you do the marking for deletion
afterupdate event of that field u can check for deletion
and set the record source of both mainform and subform
like
select * from table where not markfordeletion
doing this only those record not mark for deletion will be visible
hoping it will solve ur problem
If you just want to marks ur record as deleted and dont want to delete them in actual and want to show user that the record is deleted then you can do
You must have field in subform in which you do the marking for deletion
afterupdate event of that field u can check for deletion
and set the record source of both mainform and subform
like
select * from table where not markfordeletion
doing this only those record not mark for deletion will be visible
hoping it will solve ur problem
How are you setting the flag and updating the record? Via the field on the subform or through recordset?
Have you tried:
Me!SubformName.Refresh
lmerrell
Me!SubformName.Refresh
lmerrell
... or:
Me!SubformName.Requery
lmerrell
Me!SubformName.Requery
lmerrell
ASKER
My main form is not bound to a table nor a query. In the Form Header section of my main form, I have unbound controls into which the user is required to enter 'key' information for the record(s) that will be displayed in my subform.
The Detail section of my main form contains my subform. My subform opens in Datasheet View (so the user is able to see more than 1 record at once).
The ON_DELETE event that I need to trap belongs to the subform, not to the main form - and so I was not able to use ME!SubformName.REQUERY or ME!SubformName.REFRESH (as specified by Imerrell) because ME would be referring to the subform and not the main form.
Srauda,
I am setting my flag (and updating my record) through a recordset.
Hope very much that this extra information will help!
Thanks all for you help so far.
The Detail section of my main form contains my subform. My subform opens in Datasheet View (so the user is able to see more than 1 record at once).
The ON_DELETE event that I need to trap belongs to the subform, not to the main form - and so I was not able to use ME!SubformName.REQUERY or ME!SubformName.REFRESH (as specified by Imerrell) because ME would be referring to the subform and not the main form.
Srauda,
I am setting my flag (and updating my record) through a recordset.
Hope very much that this extra information will help!
Thanks all for you help so far.
The solution would probably be :
Me.frmSubform.recordsource
Sounds silly but seems to be a bug, stumbled over it a couple of times.
Another thing you could do is using the table as a recordsource and in the filter specify "marked_for_delete is null"
Me.frmSubform.recordsource
Sounds silly but seems to be a bug, stumbled over it a couple of times.
Another thing you could do is using the table as a recordsource and in the filter specify "marked_for_delete is null"
O yes, forgot to mension : you can refer from a subform's code to the subform itself either by :
me.recordsource = me.recordsource
or
me.parent.frmSubform.form.
me.recordsource = me.recordsource
or
me.parent.frmSubform.form.
Well, if you updating the record via a recordset, then immediately do a parent refresh. Your code should look something like:
'Open recordset with desired record using sql statement
recordset.edit
recordset!marked_for_delet
recordset.update
Parent.Refresh
'Open recordset with desired record using sql statement
recordset.edit
recordset!marked_for_delet
recordset.update
Parent.Refresh
ASKER
IvanD,
Thankyou but sorry that I won't be awarding you the points. Your solutions did not really quiet solve my query. The following errors were returned for each solution you provided:
1. me.frmsubform.recordsource
This gave me a compilation error:
Compile Error:
Method or Data Member not found.
The name of my subform (which I substituted for frmsubform) after the = sign was highlighted after this error poped up.)
2. me.recordsource = me.recordsource
I got a runtime error when I used this line in my code:
Error # 3246 : Operation not supported in transactions.
3. me.parent.frmSubform.form.
I got the same runtime error as point #2 above.
Srauda,
Thanks alot. Your comment fixed my query. I use Parent.Refresh and 'bingo', my subform was refreshed - the 'deleted' record was removed from the display in my subform.
There are 2 things that I want you to do though before I can award you the points.
1. Please post your comment as an answer.
2. Although Parent.Refresh worked alright, I received an error when attempting to move the focus (using the mouse) to the main form after the last record was deleted from the subform. The error I got was:
You can't go to the specified record. You may be at the end of the recordset.
I do understand this message (since there are not more records lef in my recordset), but can you tell me how I can 'safely' set the focus on the main form, after all the records in my subform have been deleted, without getting this error message?
Thanks alot.
Thankyou but sorry that I won't be awarding you the points. Your solutions did not really quiet solve my query. The following errors were returned for each solution you provided:
1. me.frmsubform.recordsource
This gave me a compilation error:
Compile Error:
Method or Data Member not found.
The name of my subform (which I substituted for frmsubform) after the = sign was highlighted after this error poped up.)
2. me.recordsource = me.recordsource
I got a runtime error when I used this line in my code:
Error # 3246 : Operation not supported in transactions.
3. me.parent.frmSubform.form.
I got the same runtime error as point #2 above.
Srauda,
Thanks alot. Your comment fixed my query. I use Parent.Refresh and 'bingo', my subform was refreshed - the 'deleted' record was removed from the display in my subform.
There are 2 things that I want you to do though before I can award you the points.
1. Please post your comment as an answer.
2. Although Parent.Refresh worked alright, I received an error when attempting to move the focus (using the mouse) to the main form after the last record was deleted from the subform. The error I got was:
You can't go to the specified record. You may be at the end of the recordset.
I do understand this message (since there are not more records lef in my recordset), but can you tell me how I can 'safely' set the focus on the main form, after all the records in my subform have been deleted, without getting this error message?
Thanks alot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, I'm not setting the focus to the main form programmatically. (I may have to do this, but I don't know how at the moment). I am setting the focus on the main form manually via the mouse.
I did try the command you gave me (inserted it into my code), but it made no difference - I still got the error).
I'll try and illustrate what causes the error:
If for instance there are 2 records displayed in my subform and my subform has the focus.
I then select a whole record on the subform (by clicking on the left-most side of my datasheet) and then delete a record using the DEL key on my keyboard, this record is removed from my display (form is refreshed correctly). I then select the second (and last) record that is displayed and delete it the same way (via DEL key on keyboard). After the last record is deleted (the second in this case), I cannot tell which control has the focus anymore (I can only guess that the focus is still on the subform, but it is not obvious by looking at the screen). Pressing the TAB key doesn't change anything either.
Because I cannot tell which control has the focus, I then go ahead and click on the main form using the mouse. Doing so results in the error message I mentioned.
PS - I have turned off the ability of the subform to allow additions of new records (AllowAdditions = NO). (I have another form that takes care of new entries). The subform is used for viewing. update and 'deletion' only.
Therefore, when the last record is deleted from the subform, there is no more record displayed. What's displayed though are only the Field names (please remember also that my subform is displayed in datasheet view).
Hope this helps abit.
I did try the command you gave me (inserted it into my code), but it made no difference - I still got the error).
I'll try and illustrate what causes the error:
If for instance there are 2 records displayed in my subform and my subform has the focus.
I then select a whole record on the subform (by clicking on the left-most side of my datasheet) and then delete a record using the DEL key on my keyboard, this record is removed from my display (form is refreshed correctly). I then select the second (and last) record that is displayed and delete it the same way (via DEL key on keyboard). After the last record is deleted (the second in this case), I cannot tell which control has the focus anymore (I can only guess that the focus is still on the subform, but it is not obvious by looking at the screen). Pressing the TAB key doesn't change anything either.
Because I cannot tell which control has the focus, I then go ahead and click on the main form using the mouse. Doing so results in the error message I mentioned.
PS - I have turned off the ability of the subform to allow additions of new records (AllowAdditions = NO). (I have another form that takes care of new entries). The subform is used for viewing. update and 'deletion' only.
Therefore, when the last record is deleted from the subform, there is no more record displayed. What's displayed though are only the Field names (please remember also that my subform is displayed in datasheet view).
Hope this helps abit.
Is it possible to send me you mdb file?
ASKER
No problem. I can send you a copy of my MDB file. How do I send it to you then?
Send it to srauda@sornson.com. Preferably in a zip file.
ASKER
srauda,
I'm now sending my mdb file (zipped format). Please look out for it. (I have also included the mdw file).
I'm now sending my mdb file (zipped format). Please look out for it. (I have also included the mdw file).
ASKER
To srauda only,
Did you receive my mdb file? I still have to hear from you after sending my file.
I can only assume that you are working on my mdb.
Did you receive my mdb file? I still have to hear from you after sending my file.
I can only assume that you are working on my mdb.