Save and recall control states of subforms

douginiraq

Registered User.
Local time
Yesterday, 16:44
Joined
Nov 8, 2011
Messages
14
I have a Unbound Form with 4 bound sub-forms. Each of the sub forms is identical in function. See the code below:

frmP4RT
--------------------------
Private Sub btnStart4_Click()

Me.btnStop4.Enabled = True
Dim IAB_WATER_TEST_DB As DAO.Database
Dim tblP4RT As DAO.Recordset

Set IAB_WATER_TEST_DB = CurrentDb
Set tblP4RT = IAB_WATER_TEST_DB.OpenRecordset("tblP4RT")

tblP4RT.AddNew
tblP4RT("STARTDATETIME").Value = Now()
tblP4RT.Update


Me.btnStop4.SetFocus
Me.lblW4PC.BackColor = vbGreen
Me.btnStart4.Enabled = False


End Sub

Private Sub btnStop4_Click()

Me.btnStart4.Enabled = True
Dim IAB_WATER_TEST_DB As DAO.Database
Dim tblP4RT As DAO.Recordset

Set IAB_WATER_TEST_DB = CurrentDb
Set tblP4RT = IAB_WATER_TEST_DB.OpenRecordset("tblP4RT", dbOpenDynaset)
tblP4RT.MoveLast
tblP4RT.Edit
tblP4RT("STOPDATETIME") = Now()
tblP4RT.Update
tblP4RT.Close

Me.btnStart4.SetFocus
Me.lblW4PC.BackColor = vbRed
Me.btnStop4.Enabled = False

End Sub
-----------------------------------

What this does is simply enter a start /stop date and time into the PUMP 4 RUN TIME table and then set the enabled state and label color to give the user the visual of the pump status and prevent the controls being operated consecutively resulting in double entries on the start time and a blank stop time.

The above is working perfectly.

The Problem:

When I close the unbound form and reopen I get the appearance that the pumps are all in the "off state". I need to retain the "current state" of the controls when closed and when the form is opened again have the controls set the same as when closed so the running pumps show as still running and be able to stop them and have the record in the table be correct.

I suspect I need to use the OnClose() command and make some Module to make the save state but my research has turned up nothing significant.
 

Attachments

  • Capture.JPG
    Capture.JPG
    19 KB · Views: 74
  • Capture2.JPG
    Capture2.JPG
    38.9 KB · Views: 71
  • Capture3.JPG
    Capture3.JPG
    46.4 KB · Views: 72
You would need to store the Pump ID with the table and then have each subform take the most recent start date and check to see if there is a stop with it and assign the colors on form load for that.
 
Thank you.

Is that something I have to do on the form or for each sub-form.

I think each sub-form, but I am new enough I don't want to waste to much time experimenting.

Cheers!

Doug
 
Are you saving those records or are you just using the same row over and over again for each pump? So, when you click the start button does it delete the existing start and stop date/time in the table for the one row and add the new date/time in for it?

I just thought of that and wondered so I could give you guidance on which way to go (I don't want to go to far off on the wrong path).
 
Each piece of data is a new record so I can total the total amount of run time for a given date range.. The code below I just tried as a baseline...

I am getting a debug erro loading the form at the StopDateTime..

Private Sub Form_Open(Cancel As Integer)
Dim IAB_WATER_TEST_DB As DAO.Database
Dim tblP7RT As DAO.Recordset
Set IAB_WATER_TEST_DB = CurrentDb
DoCmd.GoToRecord , "", acLast
If Me.STOPDATETIME = "" Then
Me.btnStop7.Enabled = True
Me.btnStop7.SetFocus
Me.lblW7PC.BackColor = vbGreen
Me.btnStart7.Enabled = False

Else
Me.btnStart7.Enabled = True
Me.btnStart7.SetFocus
Me.lblW7PC.BackColor = vbRed
Me.btnStop7.Enabled = False
End If
End Sub

This is what I am trying but does not seem to work...This is each sub-form Module...
 
See my post (#4) and answer that question for me.
 
Okay, good I will have some working code for you briefly (well, you may have to change a little to fit your stuff, but we'll see).

Did you add the PumpID field like I said to do?
 
Each of the tables that stores the date/time has a primary key of "ID".



I am at 2.8MB or I would upload....

I deleted a couple of tables and the switchboard from a copy of the DB.
 

Attachments

Each of the tables that stores the date/time has a primary key of "ID".



I am at 2.8MB or I would upload....

I deleted a couple of tables and the switchboard from a copy of the DB.

I can't see your database because I have Access 2003 here at work.

I didn't ask about the primary key. I asked about PUMPID. You need to ADD that to your table (as Long Integer) and store which pump added that start/stop record.

Then you can use this code (pasted into a STANDARD MODULE) to get the state:
Code:
Function GetLastRec(lngPumpID As Long) As Long
    GetLastRec = DMax("ID", "tblP4RT", "[PumpID]=" & lngPumpID)
 
End Function
 
 
Function GetState(lngPID As Long) As Boolean
    Dim strSQL As String
    Dim lngID As Long
 
    lngID = GetLastRec(lngPID)
    Dim rst As DAO.Recordset
    strSQL = "Select * From tblP4RT WHERE ID =" & lngID
 
    Set rst = CurrentDb.OpenRecordset(strSQL)
 
            GetState = (IsNull(rst!STOPDATETIME))
 
    rst.Close
    Set rst = Nothing

Then you can use this to call it:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim blnState As Boolean
 
 
[B][COLOR=green]' if this were for pump 2 then you would use 2 in the function like this[/COLOR][/B]
blnState = GetState(2)
 
With Me
 
Select Case blnState
   Case False
        .btnStart7.Enabled = True
        .btnStart7.SetFocus
        .lblW7PC.BackColor = vbRed
        .btnStop7.Enabled = False
 
   Case True 
        .btnStop7.Enabled = True
        .btnStop7.SetFocus
        .lblW7PC.BackColor = vbGreen
        .btnStart7.Enabled = False
End Select
End With
End Sub
 
Last edited:
Each pump has it's own table so not sure about the pumpID field mentioned.

Keep in mind I am kind of a noob for Access and am learning as I go.

I uploaded a 2003 version for your reference.
 

Attachments

Okay, for this ONE table would be correct - don't use a table for each.

Then I modified the code so that you could have ONE set of code (I didn't do the On Load event code but you could do that as well to make it generic and reusable). So I put the functions that I created originally in, merged the tables to a single table with a specific generic name. Then I created the functions to be generic and then just call them when starting and stopping the database. And the state is set when re-opening the form (oh, I also changed the form's record source to be a select statement and limit it to the PumpID).
 

Attachments

You must make someone very happy with your skills :)

I was in bed (it's 0230 here) and I was thinking about the "pumpID" and it dawned on me what you were talking about. But the code you wrote is way above my current level!

Thank you Zen Master!

Where do I send the check?
 
Oh, I found a bug in the Stop Pump code. Change this:

frm.Controls("btnStart" & 4).Enabled = True

To this

frm.Controls("btnStart" & lngPumpID).Enabled = True
 

Users who are viewing this thread

Back
Top Bottom