Need help figuring out why Access can't find the form.

[Garren.Shannon]

Hey all,

I am testing to see if my app is connected to the SQL server or not. If it is not, I want to hide the upload button on frm_TripsEditOrUpload. The command Set UpLoadButtonVisible = Forms!FrmTripsEditOrUpload!.UploadBtn fails with a debug statement saying it can't find the form. In the image, you can see the list of forms and such.

I have read through several posts on how to reference an object on a form and I believe this should be working... but it blows.

The code is coming from the Form_Load() for the Frm_StartUp form which auto-loads on start.

Private Sub Form_Load()
    Dim UpLoadButtonVisible As TextBox
    OnNet = False
    
    On Error GoTo Form_Open_Error

    opt = MesgBox("Checking your Network connection... Please wait", 5)
    
    
    If DCount("*", "dbo_Vehicles") > 0 Then
        DoCmd.SetWarnings (False)
        DoCmd.OpenQuery ("ResetVehiclesQuery")
        
        DoCmd.OpenQuery ("AppendFromDBO_Vehicles")
        DoCmd.OpenQuery ("ResetDriversQuery")
        DoCmd.OpenQuery ("AppendFromDBO_Drivers")
        DoCmd.OpenQuery ("ResetSchoolYrDatesQuery")
        DoCmd.OpenQuery ("AppendFromDBO_SchoolYrDates")
        'Turn the warnings back on
        DoCmd.SetWarnings (True)
        OnNet = True
    End If
    
'    Else
'        MsgBox ("I can't reach the master database at this time but you can still add trips!")
'    End If
Form_Open_Error:
      
    If OnNet = False Then
        MsgBox ("Your not on the network but you can still add your trips")
        Set UpLoadButtonVisible = Forms!FrmTripsEditOrUpload!.UploadBtn
        UpLoadButtonVisible = False
        
    Else
        MsgBox ("Connected!")
    End If
    
    DoCmd.Close acForm, "StartUp", acSaveNo
    DoCmd.OpenForm "Frm_MainMenu", acNormal
    Forms("Frm_MainMenu").Move 0, 0

End Sub

 

[CJ_London]

The forms collection only has open forms - so is the form open?

and what is uploadbuttonvisible? It appears to be a string and you only use set for objects

 

[Gasman]

Does that even compile?
Msgbox is mispelt?
Form reference has bang and dot together?

 

[MajP]

Agree, no way this compiles ensure you are using
Option Explicit
This is wrong

        Set UpLoadButtonVisible = Forms!FrmTripsEditOrUpload!.UploadBtn
        UpLoadButtonVisible = False

I think
Uploadbuttonvisible.visible = false

 

[Garren.Shannon]

The forms collection only has open forms - so is the form open?

and what is uploadbuttonvisible? It appears to be a string and you only use set for objects

Hey CJ... The form is not open yet at that time and I did not know the forms collection only worked on open forms. In that case, I guess one would simply use the Me. or does that only work on forms that have focus? I wanted to set the button to invisible at the start of the program. Though I think I have to rethink this approach.

Thanks...

 

[Garren.Shannon]

Agree, no way this compiles ensure you are using
Option Explicit
This is wrong

        Set UpLoadButtonVisible = Forms!FrmTripsEditOrUpload!.UploadBtn
        UpLoadButtonVisible = False

I think
Uploadbuttonvisible.visible = false

Does that even compile?
Msgbox is mispelt?
Form reference has bang and dot together?

Oddly, it does compile but blows on run.

And the MesgBox() is a wscript wait function I added to test a popup box that auto-closed after 5 seconds. Here's the function.

Public Function MesgBox(ByVal msgText As String, Optional ByVal intSeconds As Integer, Optional ByVal intButtons = vbDefaultButton1, Optional TitleText As String = "WScript") As Integer

    Dim winShell As Object

    Set winShell = CreateObject("WScript.Shell")

    MesgBox = winShell.PopUp(msgText, intSeconds, TitleText, intButtons)

End Function

 

[Garren.Shannon]

Agree, no way this compiles ensure you are using
Option Explicit
This is wrong

        Set UpLoadButtonVisible = Forms!FrmTripsEditOrUpload!.UploadBtn
        UpLoadButtonVisible = False

I think
Uploadbuttonvisible.visible = false

The UpLoadButtonVisible variable is created at the start of Form_Load(). Option Explicit is included. I will try the .visible... that's one approach I have not tried. But it isn't event getting to that line. It locks up at the SET command.

 

[Garren.Shannon]

Yea... same result.

 

[CJ_London]

Me relates to the form the code is on

 

[Garren.Shannon]

Me relates to the form the code is on

So you can't change a property of a an object on a form if the form is not open in memory?

 

[CJ_London]

No, you can modify most properties with code when the form is open. Or all properties when in design view

 

[Pat Hartman]

It helps us ever so much if you use Me. when referring to controls on a form.

I've given you alternative solutions for determining if the BE is connected but here we are at a new thread so you must not have liked them or gotten them to work. It is probably better to stick with one thread rather than starting multiples.

I've also explained in great detail why I think the process should not even work this way. Are you beginning to understand why yet?

Assuming you had a login or switchboard/menu that opened when the app opened, you should be checking the status of the BE in THAT form. Use the technique I suggested in one of your other threads. If you don't get an error with the domain function, then the BE is connected. Set a Tempvar. Then when the driver opens the form to do the sync, check the Tempvar in the form's Open event to hide/show the button.

 

[Garren.Shannon]

It helps us ever so much if you use Me. when referring to controls on a form.

I've given you alternative solutions for determining if the BE is connected but here we are at a new thread so you must not have liked them or gotten them to work. It is probably better to stick with one thread rather than starting multiples.

I've also explained in great detail why I think the process should not even work this way. Are you beginning to understand why yet?

Assuming you had a login or switchboard/menu that opened when the app opened, you should be checking the status of the BE in THAT form. Use the technique I suggested in one of your other threads. If you don't get an error with the domain function, then the BE is connected. Set a Tempvar. Then when the driver opens the form to do the sync, check the Tempvar in the form's Open event to hide/show the button.

Got it Pat... thanks for the pro tip. As you can tell, I don't quite have the ebb and flow of this forums etiquette down yet. I will stick to one thread.

Your code worked fine but I could not find a way to shorten the retry time. If done in query, I could set the timeout to be as low as 1 second but that did not seem to impact the ODBC call time at all. I went back to the ping method because the 30 sec delay was a killer. I will test to see if it will be reliable enough to continue forward.

Following your lead, I removed the process of deleting and recreating the linked tables. They are now permanently attached and yes, that makes total sense why it should be so. Now, the only time the app checks tries to access the linked tables is when it can verify it is on the net.

I did create a "StartUp" form to do all the crosschecks on load. I do a ping check in the startup form and I added a LastCheckIn table with a single date that only checks for updates every two weeks. The update cycle pulls in updated bus, driver and school year dates into the local DB so it does not have to happen every time someone starts the app. Now I am going to move the Driver login form to launch before the main menu to capture who is logged in rather than doing it every time the driver goes to add a trip.

I am, albeit slowly, getting the gist of coding in Access and VBA. Hopefully, I will be able to beta test this app in a week or so.

Below is the code from the Startup load.

Private Sub Form_Load()
    
    OnNet = False
    opt = MesgBox("Checking your Network connection... Please wait", 5)
    If PingOk("mssql2019") Then
        OnNet = True
    End If
        
    
    Dim db As DAO.Database
    Dim rsCheckIn As DAO.Recordset
    Set db = CurrentDb
    Set rsCheckIn = db.OpenRecordset("CheckIN", dbOpenDynaset, dbSeeChanges)
    rsCheckIn.MoveLast
    
'    MsgBox (rsCheckIn!LastCheckinDate)
    If OnNet = True Then
        'Your on the net, you can update files. Next, check if its been more than 2 weeks since last update
        
        If DateDiff("ww", rsCheckIn!LastCheckinDate, Now()) > 2 Then
            'Update the last checkin date to now
            rsCheckIn.Edit
            rsCheckIn!LastCheckinDate = Now()
            rsCheckIn.Update
              
            On Error GoTo Form_Open_Error

            If DCount("*", "dbo_Vehicles") > 0 Then
                'Turn off warnings
                DoCmd.SetWarnings (False)
                DoCmd.OpenQuery ("ResetVehiclesQuery")
                DoCmd.OpenQuery ("AppendFromDBO_Vehicles")
                DoCmd.OpenQuery ("ResetDriversQuery")
                DoCmd.OpenQuery ("AppendFromDBO_Drivers")
                DoCmd.OpenQuery ("ResetSchoolYrDatesQuery")
                DoCmd.OpenQuery ("AppendFromDBO_SchoolYrDates")
                'Turn the warnings back on
                DoCmd.SetWarnings (True)
    
            Else
                MsgBox ("Something is up... I can't reach the master database at this time but you can still add trips!")
            End If
        End If
    Else
        MsgBox ("Your not on the network right now but you can still add trips.")
    End If
    
            
Form_Open_Error:
      
    If OnNet = False Then
        MsgBox ("Something is up... I can't reach the master database at this time but you can still add trips!")
      
    Else
        MsgBox ("Connected!")
    End If
        
    DoCmd.Close acForm, "StartUp", acSaveNo
    DoCmd.OpenForm "Frm_MainMenu", acNormal
    Forms("Frm_MainMenu").Move 0, 0

End Sub

 

[Pat Hartman]

OK, you seem to be getting closer. I don't like your method but it isn't my application.

Does the driver connect to the LAN at times other than when he wants to sync? If not, you are doing the two week check in the wrong place.

Every time the driver opens the app, you do the two week check. Then if he is connected to the LAN you ping, etc. and if he isn't already connected to the LAN, tell him it is time to log in to the LAN so he can sync

The way you have this coded, the driver may never sync with the LAN because you are only dong the 2 week check if he is already logged in. So, if he never logs in, he never gets reminded to sync.

 

[Garren.Shannon]

OK, you seem to be getting closer. I don't like your method but it isn't my application.

Does the driver connect to the LAN at times other than when he wants to sync? If not, you are doing the two week check in the wrong place.

Every time the driver opens the app, you do the two week check. Then if he is connected to the LAN you ping, etc. and if he isn't already connected to the LAN, tell him it is time to log in to the LAN so he can sync

The way you have this coded, the driver may never sync with the LAN because you are only dong the 2 week check if he is already logged in. So, if he never logs in, he never gets reminded to sync.

Great questions Pat. The two weeks test is to download any Bus or staffing record changes from the master LB to the local FB. But your right in that it could be that a driver never syncs.

Technically, drivers are required to enter their trips at the end of each day. Often, they come in late and to avoid budget impacts for overtime, they are allowed to skip entry that day. However, the administration has let that slack to a point where they enter records weekly and some even go a month. I believe they are allowed to do this because we have a critical driver shortage and drivers find entering trips from paper sheets into excel workbooks a tedious job.

The purpose of this app is to make it easier for them to log trips. My plan is to allow drivers to upload when they chose to because they have to review the trips prior to uploading them to the permanent DB.

With that said, here is how I believe work will go;

1. Driver will log into the tablet and auto-launch the driver app. At the end of the day, they will sync to the LB, log out and plug the tablet in to charge it for the next day.
2. Driver will log into the tablet and auto-launch the driver app... but will never really log off or close the app. They will simply plug it in and remain logged into the device. leaving the app running all the time.

In both scenarios, I am relying on the driver to start the sync process by reviewing the trips to upload then hitting send when everything looks good. They may find that they entered a weekend date which the district does not get paid for. On the pre-upload and/or edit form, I apply conditional formatting to highlight weekend dates. This gives them a chance to correct the data prior to forwarding it to the SQL server.

On the main menu, I modify a label related to how many trips are waiting to be sync'd. If it passes 20, the label turns yellow. At 30, I am now thinking that I will hide the "Add Trips" button and place a warning label saying "You must sync trips before you can add more trips".

Is that what your suggesting? Or are you suggesting that I auto-sync whenever the SQL server is pingable?

 

[Pat Hartman]

I thought I was being clear. Let me try again. You need to check the date of the last sync EVERY time the driver opens the application. NOT when he tries to sync. You are doing the two week check in the WRONG place. It will not serve any purpose where it is. As soon as the driver opens the app, you check for the date of the last sync. If it is time, then you can go into checking for the LAN connection and doing the sync. There is no point at all with doing the two week check AFTER the driver has pushed the "sync" button!

 

[Garren.Shannon]

Wow... busy week but I am back at this. Pat, thanks for your patience with me. As noted, I created a table in the local DB to store the date of the last check in with the master DB. On app startup, I check the local last update time and if it has been two weeks, the app pulls the updated Drivers and Vehicles tables (these tables record any changes to drivers and vehicles by the administration... i.e. assigned vehicles or drivers who are no longer active, etc.) into the local tables.

I am not doing a check for drivers or vehicle changes prior to sync. I am just testing to see if the computer can reach the SQL server.

Things appear to be working well in my testing at this time with the exception of one challenge. A query I use to filter the list of trips that are ready to be uploaded won't allow me to edit. Originally I thought it was the form but it is not. Opening the query directly does not allow me to edit there either.

I can edit the table directly but I can't edit from the query.

Is it possible that I have some sort of record lock from an OpenRecordSet command that I didn't close the database after??? I doubt it because I can, at the same time, directly edit the table. I am totally at a loss here on why I can't edit through the query.

below is the query.

SELECT Trip.[T-Date], Vehicles.VehicleName, Drivers.Intials, [Trans-Type].Code, Trip.[Pre-Insp], Trip.[Post-Insp], Trip.DepartTime, Trip.ReturnTime, Trip.OD_Depart, Trip.OD_Return, Trip.[Basic-Count], Trip.[Sped-Count], Trip.[HS-Count], Trip.[Walk-Count], Trip.MaxCount, Trip.Desc, Trip.UploadedFlag, Trip.BatchID
FROM [Trans-Type] INNER JOIN (Drivers INNER JOIN (Vehicles RIGHT JOIN Trip ON Vehicles.VehicleUniqID = Trip.VehicleUniqID) ON Drivers.DriverUniqID = Trip.DriverUniqID) ON [Trans-Type].CodeID = Trip.CodeID
WHERE (((Trip.UploadedFlag)=False));

 

[Pat Hartman]

Wow... busy week but I am back at this. Pat, thanks for your patience with me. As noted, I created a table in the local DB to store the date of the last check in with the master DB. On app startup, I check the local last update time and if it has been two weeks, the app pulls the updated Drivers and Vehicles tables (these tables record any changes to drivers and vehicles by the administration... i.e. assigned vehicles or drivers who are no longer active, etc.) into the local tables.

That is NOT what the code does. Do we have a language difference?

THIS is what the code does:

'    MsgBox (rsCheckIn!LastCheckinDate)
    If OnNet = True Then
        'Your on the net, you can update files. Next, check if its been more than 2 weeks since last update
        
        If DateDiff("ww", rsCheckIn!LastCheckinDate, Now()) > 2 Then

There is a very REAL difference between checking the date FIRST and ONLY checking the date if the driver is connected. I don't know how many ways to say this. You are ONLY checking the date if the user is on the net so he doesn't EVER have to connect and he will NEVER be prompted.

 

[Garren.Shannon]

Sorry Pat... I do think there is confusion here and I am not being very clear so my apologies.

I have two tables that hold driver and vehicle data (who/what is active and who/what has been added) that the admin team update. The driver needs a local copy of those tables incase they log in when not attached to the network. Those tables really don't change that much and so the two week pull is just to limit traffic as well as being a one-way street.

Regardless of the last download date, I still want the user to know if they are on the network when they start the app up. So I test for OnNet... and if I am on the net, AND it's been more than two weeks since I pulled down the driver and vehicle tables from the SQL server, then I download and update those tables.

If the user is NOT on the net, there is no real reason to check the last sync for vehicle and driver data date. The sync date is not used to check when the user uploaded their list of trips last, it is only used to see if the driver/vehicle tables have changed.

However, past the startup form, the main menu loads. There, I check the number of trips that have NOT been uploaded and put that on the screen for them. When the count gets over 20, the label changes yellow. Over 30 and the user can no longer add trips until they sync/upload.

I actually remove the "Add trips" and Start new trip buttons and place a note on the screen to tell them they must upload now.

Hope that makes sense. What is a better way to handle this?

 

[Pat Hartman]

It is your application. Do whatever you want. You have ignored pretty much all my suggestions so you may as well ignore this one also. Good luck with the app.