Solved

How to use me.requery with afterupdate on checkbox

Posted on 2014-04-16
8
52 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:Skip Sleeper
  • 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:Skip Sleeper
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 

Author Comment

by:Skip Sleeper
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:Skip Sleeper
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

Suggested Solutions

Title # Comments Views Activity
sql statement - 9 27
Dlookup MSACCESS 5 25
date criteria to pull up records for the last 3 months 39 42
Dirty form - conditional formatting 5 28
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

832 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