?
Solved

How to use me.requery with afterupdate on checkbox

Posted on 2014-04-16
8
Medium Priority
?
62 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
[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
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 2000 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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