Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-12-2017, 07:38 PM   #1
StanJx
Newly Registered User
 
Join Date: Apr 2012
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
StanJx is on a distinguished road
Run For Loop Even If Form Is Closed

Hi,

I have a filtered form called frmAssing on which I have implemented a for loop to check and enter some values. So far this is working when the form is open. Is there a way i can get this function to work when that form is closed. Would appreciate if someone can provide the code required for such a function. Thanks in advance.

StanJx is offline   Reply With Quote
Old 02-12-2017, 08:54 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,575
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Run For Loop Even If Form Is Closed

When a form is closed, it will not be able to run any code.

However, you could move the code to a general module and call it from any open form, report, module, or even a macro (if the macro has a RunCode action). Note that if you run code from another module, there is still the question of what would be running THAT module.

In the absence of something being open (like a form or report) or something running (like a macro), you have no foundation on which code CAN run.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-12-2017, 09:41 PM   #3
StanJx
Newly Registered User
 
Join Date: Apr 2012
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
StanJx is on a distinguished road
Re: Run For Loop Even If Form Is Closed

DOC MAN,

Thanks for your reply. I have a form that is open and stays hidden. Is it possible to run this from that form. Even if it's a login form.. What is the best method? A form or a macro.. I have not worked with macro's that much. Here is my for loop code. It runs on the timer event.

Code:
Private Sub Form_Timer()

    Me.txtTime.Value = Format(Time, "Medium Time")
    If Me.txtTime < Me.txtStart Or Me.txtTime > Me.txtEnd Then
    Me.cmdAll.Enabled = False
    End If
    
    Dim lRqsCount As Long
    Dim lTblCount As Long
    Dim sWhere As String
    
    sWhere = "(((tblRequest.Approved)=True) AND ((tblRequest.RequiredDate)=Date())) OR (((tblRequest.Approved)=True) AND ((Date())<[tblRequest].[RequiredDate]))"
    
    lRqsCount = lstRequest.ListCount
    lTblCount = DCount("RequestID", "tblRequest", sWhere)
    
    If lTblCount > lRqsCount Then
    Me.lstRequest.Requery
    End If
    
    
    Me.lstOut.Requery
    Me.lstIn.Requery
    
    
    
    Dim intOutCounter As Integer
    Dim intInCounter As Integer
    Dim intOutCount As Integer
    Dim intInCount As Integer
    Dim varOut As Variant
    Dim varIn As Variant
    Dim sOutVehicles As String
    Dim sInVehicles As String
    Dim sVOut As String
    Dim sVIn As String
    

    
    sVOut = "Out"
    sVIn = "Returned"
    
    intOutCount = lstOut.ListCount

    If intOutCount > 0 Then

    For intOutCounter = 0 To lstOut.ListCount
    lstOut.Selected(intOutCounter) = True
    Next intOutCounter
        For Each varOut In lstOut.ItemsSelected

        sOutVehicles = Me.lstOut.ItemData(varOut)

        CurrentDb.Execute "UPDATE tblDriver SET VehicleAvailable = False WHERE VehicleNo='" & sOutVehicles & "'"
        CurrentDb.Execute "UPDATE tblRequest SET VehicleStatus = '" & sVOut & "' WHERE  RequestID=" & Me.lstOut.Column(4, varOut)
        
            Me.lstRequest.Requery
            Me.lstVehicles.Requery

        Next varOut

    End If
    
    intInCount = lstIn.ListCount

    If intInCount > 0 Then

    For intInCounter = 0 To lstIn.ListCount
    lstIn.Selected(intInCounter) = True
    Next intInCounter
        For Each varIn In lstIn.ItemsSelected

        sInVehicles = Me.lstIn.ItemData(varIn)


        CurrentDb.Execute "UPDATE tblDriver SET VehicleAvailable = True WHERE VehicleNo='" & sInVehicles & "'"
        CurrentDb.Execute "UPDATE tblRequest SET VehicleStatus = '" & sVIn & "' WHERE RequestID=" & Me.lstIn.Column(4, varIn)
            
            Me.lstRequest.Requery
            Me.lstVehicles.Requery

        Next varIn

    End If



    
End Sub

StanJx is offline   Reply With Quote
Old 02-13-2017, 06:43 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,575
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Run For Loop Even If Form Is Closed

Macros will work but have less capability in terms of error handling. I think I've only ever had one macro in any of my production databases because I converted all other macros to VBA code. It's easy, there's a ribbon item for that.

If you have a form that is open, it can be the host for a timed event running code. The fact that the form is hidden is not a barrier to running code. Note that if you have more than one periodic-run element, you cannot have multiple timer routines in the same form because you have only one timer slot in the form's event code. So if you found yourself in that case (not saying you have that here), you would either have to write a smart timer routine or you would have to launch some small forms that were never visible but that had the required timer routines, each with their own timing. I would recommend a smart single timer routine if that ever happened to you.

As a matter of style, an issue of data protection, but surely not an absolute requirement, my databases that actually had users logging in always had a switchboard form. I ran my timer code from the switchboard.

I did that because it is not a secure situation to allow users to see the infrastructure that is visible in the navigation panel. If they can see it, natural curiosity will cause them to ... futz ... with it and bang/zoom! There goes your database. Threats of dire consequences didn't stop the users on my first database back in Ac97 days. So in my later databases, users could ONLY see the switchboard from, from which they could launch functional forms with a command button. (The button wizard includes "Open a Form" as one of its options, so it is easy to build this.)

In deployment, you need to declare the switchboard (some would prefer to call it a "dispatcher" form) as your default opening form. In its Form_Open code, you immediately tend to issues of security such as (if required) popping up the login form, turning off the ribbon and navigation features, etc. If you choose that direction, search this forum for articles with titles like "Securing a Database."

Note also that if you require a login but have a domain-based environment, your users have already logged in. You can find out who they are by the Environ("Username") function and look up the domain name in a Users table. Then you could have other data in that table to allow the code behind each command button to decide whether to function for that person, if you had the case of some folks doing only limited operations.

If you have a dispatcher, that kind of functional selectivity is trivial (if a bit tedious).
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-14-2017, 10:55 PM   #5
StanJx
Newly Registered User
 
Join Date: Apr 2012
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
StanJx is on a distinguished road
Re: Run For Loop Even If Form Is Closed

Hi Doc Thanks for your reply. In the reply I sent you previously I attached the code on my entire timer event on the form frmAssign (The filtered form which the for loop needs to be run on). So in the scenario I am transferring the timer code to the login form how would I structure and amend the code. I have not tried this before and would appreciate if you could help me with the code for this. It won't work if I just copy it to the form frmLogin right?

StanJx is offline   Reply With Quote
Reply

Tags
for loop , form , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wrong form being closed Gkirkup Forms 6 09-24-2012 01:50 PM
Closed Form not closed bstice Modules & VBA 9 11-27-2008 12:57 AM
Closed Records - Archive or Label as closed? Darkwater Forms 9 10-18-2006 02:46 AM
How To Prevent A Form From Being Closed threeo2 Forms 2 04-17-2003 01:15 PM
Prevent a form from being closed randypuppy Forms 3 04-27-2001 07:16 PM




All times are GMT -8. The time now is 09:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World