Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Requery then Refresh my sub-form.

Posted on 1999-06-30
17
Medium Priority
?
399 Views
Last Modified: 2008-03-10
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.

0
Comment
Question by:rmission
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +3
17 Comments
 
LVL 7

Expert Comment

by:KangaRoo
ID: 1998149
You may need to do an update after setting the to_delete date, then requery.
0
 

Expert Comment

by:india_bb
ID: 1998150
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

0
 
LVL 4

Expert Comment

by:srauda
ID: 1998151
How are you setting the flag and updating the record?  Via the field on the subform or through recordset?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:lmerrell
ID: 1998152
Have you tried:

Me!SubformName.Refresh

lmerrell
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 1998153
... or:

Me!SubformName.Requery

lmerrell
0
 

Author Comment

by:rmission
ID: 1998154
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.
0
 
LVL 3

Expert Comment

by:IvanD
ID: 1998155
The solution would probably be :

Me.frmSubform.recordsource = 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"
0
 
LVL 3

Expert Comment

by:IvanD
ID: 1998156
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.recordsource = me.parent.frmSubform.form.recordsource
0
 
LVL 4

Expert Comment

by:srauda
ID: 1998157
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_delete = True
recordset.update
Parent.Refresh


0
 

Author Comment

by:rmission
ID: 1998158
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 = 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.recordsource = me.parent.frmSubform.form.recordsource

  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.


0
 
LVL 4

Accepted Solution

by:
srauda earned 320 total points
ID: 1998159
Are you setting the focus to the main form programmatically?  I do not understand what would be causing that error.

If you are setting focus programmatically, try this in the subform:

If me.currentrecord <> 0 then Parent.SetFocus
0
 

Author Comment

by:rmission
ID: 1998160
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.



0
 
LVL 4

Expert Comment

by:srauda
ID: 1998161
Is it possible to send me you mdb file?
0
 

Author Comment

by:rmission
ID: 1998162
No problem. I can send you a copy of my MDB file. How do I send it to you then?





0
 
LVL 4

Expert Comment

by:srauda
ID: 1998163
Send it to srauda@sornson.com.  Preferably in a zip file.
0
 

Author Comment

by:rmission
ID: 1998164
srauda,

I'm now sending my mdb file (zipped format). Please look out for it. (I have also included the mdw file).
0
 

Author Comment

by:rmission
ID: 1998165
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.


0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question