Solved

How to use me.requery with afterupdate on checkbox

Posted on 2014-04-16
8
47 Views
Last Modified: 2015-12-27
Hi Experts,

I have created a db to only print labels where a checkbox equals yes.  When a user prints the first page of labels and returns to the form to select another item to print, the query results aren't requeried unless the form is closed and reopened.  I'm assuming that the best way to handle this is to use me.requery in vba but I can't figure out to start the procedure.  Any help would be greatly appreciated.  Thank you in advance!
Laundry-PiggyBack-Labels.accdb
0
Comment
Question by:fargus47
  • 5
  • 3
8 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40004310
The database has some kind of security so I can't open the forms in design  view.  Are the checkboxes bound to a Yes/No field?  If so, the code that opens the report could set that field to False and then requery the form, before opening the report.
0
 

Author Comment

by:fargus47
ID: 40004323
Hi,

I thought I had stripped the db of the switchboard options.  If you hold down the shift key on opening it should bypass that.  I'm not sure if the check boxes are bound to the field.  How would your method handle the printing of a page of labels and then have the user return to select another item in the list?

Thanks!
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40004565
I already tried Shift-enter to open the database -- it didn't help.  But I just tried again, and this time it worked.   The checkboxes are bound,  and because the report is filtered for True values, I put the code to clear them on the report's Close event.  Bear in mind that if you are still in Edit mode, that record's checkbox won't be cleared, so make sure the little pencil in the record selector has gone away before you preview the report.  Here is the code:

Private Sub Report_Close()
'Created by Helen Feddema 16-Apr-2014
'Last modified by Helen Feddema 16-Apr-2014

On Error GoTo ErrorHandler

   Dim strSQL As String
   
   strSQL = "UPDATE tblLinens SET tblLinens.Print_YES_NO = False;"
   CurrentDb.Execute strSQL
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in rptLabels Close procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


I found that the checkboxes were cleared without an explicit Requery command, but you could add one if needed.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40004571
Here is the database with the Close event on rptLabels.
HBF-Modified-Laundry-PiggyBack-L.accdb
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:fargus47
ID: 40004820
Hi,

I just tried the modifications and the labels are not populated now.  Any ideas what is causing it?

Thank you!
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40011402
What labels are you referring to?
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40011409
If you meant the labels report, it works when I test it.  I saved the report record source to a named query so I could inspect it separately.  Here is the database with that minor change (also I switched to overlapping windows so I could see two windows simultaneously).
HBF-Modified-Laundry-PiggyBack-L.accdb
0
 

Author Comment

by:fargus47
ID: 40012407
Good Morning Helen,
I'm still experiencing the same issue with the modifications you made yesterday.  When an item is checked to print the labels report, the preview shows the populated labels to print.  But if you return to the form to select another product to create labels for, the labels report isn't populated.  The user must close out the form and reopen, reselect and run the labels report.  That is why I think there is a need for a requery on the selection change.   Thank you for your patience and help--it's really appreciated!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now