Run For Loop Even If Form Is Closed (1 Viewer)

StanJx

Registered User.
Local time
Tomorrow, 00:58
Joined
Apr 5, 2012
Messages
21
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:28
Joined
Feb 28, 2001
Messages
26,999
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.
 

StanJx

Registered User.
Local time
Tomorrow, 00:58
Joined
Apr 5, 2012
Messages
21
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:28
Joined
Feb 28, 2001
Messages
26,999
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).
 

StanJx

Registered User.
Local time
Tomorrow, 00:58
Joined
Apr 5, 2012
Messages
21
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?
 

Users who are viewing this thread

Top Bottom