Automating Checkboxes (1 Viewer)

[JTQ911]

Hello all, I have a new project and am running into new problems. Thanks in advance for any and all help.

BACKGROUND
I have 5 tables, each with an identifying field "SN". Each table contains multiple fields represented on forms by checkboxes (yes/no) that mark manufacturing progress for an individual project.

QUESTION
Hwo do I have access automatically uncheck the previous field from any of the five tables when a new field is checked in a form.

EXAMPLE: A unit may be checked TRUE for fld_Stage1 in tblPrePreProduction. When a user updates the database and checks fld_stage5 in tblPostProduction, how do I have access automatically uncheck fld_Stage1 in tblPrePreProduction

 

[dkinley]

If it's a onesy style of update, then an SQL statement should get it done, if multiple at once - probably an update query.

Questions though ... why would you want to uncheck the previous check box? Just some food for thought and clarification ...

In those types of settings wouldn't you have a date field associated with each check box to track the process? In this manner, let's say after you completed 1000 units, you could then measure your process based on time averages between processes as a feedback loop to better manage the process (from a quality perspective). Also, you could build better models to simulate your process for improvement mechanisms based on the accurate observations of your data set.

Also, if you uncheck the field, then how did you know it went through that process and didn't 'skip around' a critical step?

You can always take snapshots of any item in the process by looking at the last checkbox updated.

Just my .02,

-dK

 

[JTQ911]

Good point. I was trying to avoid this problem then. Assuming I had a date associated with every checkmark... How would I run a query to display only the latest stage associated with a unit. For example, if there are 100 stages of production broken into 5 separate tables, can I have Access return the Unit number and only the latest most up to date stage of production, no matter what table this information is in? THANKS!

 

[dkinley]

I've attached a sample database that may or may not give you what you are looking for (it works in '07, but saved it in '02). It does what you ask but might not present it as you wish, but it should give you a start. It has 3 tables so you will need to extrapolate for your application (the process search does not discriminate on how many tables, just as long as the data is in the query it will reiterate and find the process.) Last, it does not check or verify the checkboxes to ensure the preceding ones are checked to ensure contigous checkboxes in the record.

Open frmUnitProcess for the demo. There is code behind the form that populates the text box below it.

There are others much more knowledgable than I in this arena and, without a doubt, could offer a better more optimized solution. An advanced thanks to all who else who contribute/critique, I know I will get some education out of it.

-dK

 

[JTQ911]

Company is still using 97. I could not open the file, it said it was not a valid database file type.

 

[dkinley]

Bummer ... I don't have '97 anymore ... hmmm. For what it's worth --

I've attached a picture of my table set-up, assigned a serial number (and then just clicked on some checkboxes).

On an unbound form, I had a combo box (cmbUnitID) that looked up the pkUnitID and SerialNumber from tblUnit. On the AfterUpdate() event of the combo box, here is the code I used:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSearchName As String
    Dim iRecField As Integer
    Dim iCol As Integer
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryPhase2Query", dbOpenSnapshot)
 
    strSearchName = CStr(Me!cmbUnitID)
    rst.FindFirst "pkUnitID = " & strSearchName
 
    If rst.NoMatch Then
        MsgBox "Record not found"
    Else
 
        iCol = 0
        iRecField = 0
 
        For iCol = iCol To rst.Fields.Count
            If iRecField = rst.Fields.Count Then
                Me!txtLastProcessField = "Finished Processes"
            Else
                If rst.Fields(iRecField) = False Then
                    If iRecField = 1 Then
                        Me!txtLastProcessField = "Not Began Processes"
                    Else
                        Me!txtLastProcessField = rst.Fields(iRecField - 1).Name
                    End If
                    Exit For
                End If
            End If
 
            iRecField = iRecField + 1
        Next
    End If
 
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

On the forms OnCurrent() event I put in the code:

Me.Refresh

And here is the SQL view of my query:

SELECT qryPhase2SubQuery.pkUnitID, qryPhase2SubQuery.Process_1, qryPhase2SubQuery.Process_2, qryPhase2SubQuery.Process_3, tblPhase2.Process_4, tblPhase2.Process_5, tblPhase2.Process_6
FROM qryPhase2SubQuery LEFT JOIN tblPhase2 ON qryPhase2SubQuery.pkUnitID = tblPhase2.fkUnitID;

On the same unbound form I had a text box (Me!txtLastProcessField). Once you selected the serial number in the combo box, the text box would display what process step the unit had completed.


-dK