Full Version: Checkbox.value = True Based On Presense Of Record
UtterAccess Forums > Microsoft® Access > Access Forms
voodoort
I have three tabs worth of check boxes I'm trying to get to load as checked if there's a record in a table corresponding to their value. It works just fine for the first page tab but the second two it's giving me a "Type Mismatch" error then it stops loading the values on the tabs altogether. Here's the code I'm using to load the tabs:
!--c1-->
CODE
'''''''''''''''Reoccuring Tab'''''''''''''''
    strSQLIEU = "SELECT IEUID, ShortName FROM tblIEUItems WHERE (InitialIssue = False AND PermIssue = False) ORDER BY ShortName"
    Set rsIEU = CurrentDb.OpenRecordset(strSQLIEU)
With rsIEU
    Do While Not .EOF
      xIEU = xIEU + 1
    If x1297 > 20 Then
        MsgBox "There are too many items set as Reoccuring issue.  You must edit their properties to less than 20"
        Exit Do
    End If
    With Me("chk" & xIEU)
        .Tag = rsIEU!IEUID
        .Visible = True
    End With
    With Me("lbl" & xIEU)
        .Caption = rsIEU!ShortName & " " & rsIEU!IEUID
        .Visible = True
    End With
Dim strQty As String
Dim rsQty As DAO.Recordset

    With Me("cbo" & xIEU)
        strQty = "SELECT nz(QTY,1) AS nzQTY FROM tblIEU WHERE PersonnelID=" & Me!txtID & "And IEUID = " & rsIEU!IEUID
        Set rsQty = CurrentDb.OpenRecordset(strQty)
            If rsQty.RecordCount > 0 Then
                .Value = CStr(rsQty!nzQTY)
            Else
                .Value = "1"
            End If
            .Visible = True
    End With
    .MoveNext
    Loop
.Close
End With
''''''''''''''End Reoccuring Tab''''''''''''''''
'''''''''''''Initial Issue Tab'''''''''''''''''''''
strInitial = "SELECT IEUID, ShortName FROM tblIEUItems WHERE InitialIssue = True ORDER BY ShortName"
  Set rsInitial = CurrentDb.OpenRecordset(strInitial)
With rsInitial
    Do While Not .EOF
      xInitial = xInitial + 1
    If xInitial > 20 Then
        MsgBox "There are too many items set as Initial Issue.  You must edit their properties to less than 20."
        Exit Do
    End If
    With Me("chkInitial" & xInitial)
        .Tag = rsInitial!IEUID
        .Visible = True
    End With
    With Me("lblInitial" & xInitial)
        .Caption = rsInitial!ShortName & " " & rsInitial!IEUID
        .Visible = True
    End With
Dim strInitialQty As String
Dim rsInitialQty As DAO.Recordset

    With Me("cboInitial" & xInitial)
        strInitialQty = "SELECT nz(QTY,1) AS nzQTY FROM tblIEU WHERE PersonnelID=" & Me!txtID & "And IEUID = " & rsInitial!IEUID
        Set rsInitialQty = CurrentDb.OpenRecordset(strInitialQty)
            If rsInitialQty.RecordCount > 0 Then
                .Value = CStr(rsInitialQty!nzQTY)
            Else
                .Value = "1"
            End If
            .Visible = True
    End With
    .MoveNext
    Loop
.Close
End With
Set rsInitial = Nothing
'''''''''''''''End Initial Issue Tab''''''''''''''''
'''''''''''''1297 Issue Tab'''''''''''''''''''''
str1297 = "SELECT IEUID, ShortName FROM tblIEUItems WHERE PermIssue = True ORDER BY ShortName"
  Set rs1297 = CurrentDb.OpenRecordset(str1297)
With rs1297
    Do While Not .EOF
      x1297 = x1297 + 1
    If x1297 > 20 Then
        MsgBox "There are too many items set as 1297 Issue.  You must edit their properties to less than 20"
        Exit Do
    End If
    With Me("chk1297" & x1297)
        .Tag = rs1297!IEUID
        .Visible = True
    End With
    With Me("lbl1297" & x1297)
        .Caption = rs1297!ShortName & " " & rs1297!IEUID
        .Visible = True
    End With
    
Dim str1297Qty As String
Dim rs1297Qty As DAO.Recordset

    With Me("cbo1297" & x1297)
        str1297Qty = "SELECT nz(QTY,1) AS nzQTY FROM tblIEU WHERE PersonnelID=" & Me!txtID & "And IEUID = " & rs1297!IEUID
        Set rs1297Qty = CurrentDb.OpenRecordset(str1297Qty)
            If rs1297Qty.RecordCount > 0 Then
                .Value = CStr(rs1297Qty!nzQTY)
            Else
                .Value = "1"
            End If
            .Visible = True
    End With
    .MoveNext
    Loop
.Close
End With
Set rs1297 = Nothing
'''''''''''''''End 1297 Issue Tab''''''''''''''''

And here's what's "supposed" to be loading the marked checkboxes.
CODE
'''''''''''''''Mark Checkboxes'''''''''''''''''''
  strSQLIEU = "SELECT IEUID, QTY FROM tblIEU WHERE PersonnelID = " & Me.txtID
  Set rsIEU = CurrentDb.OpenRecordset(strSQLIEU)
  With rsIEU
    If Not .EOF And Not .BOF Then
      .MoveFirst
        Do While Not .EOF
            For Each ctl In Me.Controls
            If ctl.ControlType = acCheckBox Then
                If CLng(ctl.Tag) = rsIEU!IEUID Then
                ctl.Value = True
            Exit For
                End If
            End If
          Next ctl
        .MoveNext
        Loop
    End If
  .Close
  End With
  Set rsIEU = Nothing
'''''''''''''''End Checkboxes''''''''''''''

Hopefully this makes sense as to what I'm trying to do. I can upload the entire database if need be.
Also, when I remove the code to mark the checkboxes the form loads without errors at all.
Jeff B.
"three tabs of checkboxes" sounds like a potentially confusing user interface. Is there (also) a chance that your underlying data is set up the same way? That is, does your table have multiple Yes/No fields? If so, that may fit better into a spreadsheet than a relational database...
voodoort
The item table (tblIEUItems) has two yes/no fields; PermIssue, and InitialIssue. The other table in question (tblIEU) saves an entry for each checked box. For each IEUID in tblIEU that there's an entry for the particular PersonnelID the check box will show as marked or unmarked if there's no entry in tblIEU. The three tabs is a bit confusing but it's exactly how the user wanted the form set up and it all comes together once it's loaded.
asically what happens is If PermIssue = True then it'll load the item on tab two, if InitialIssue = True then it'll load on tab three. If both are false it'll load on Tab one.
Jeff B.
I guess I'm not understanding why that takes three separate tabs...
voodoort
Lack of room I guess is the best way to explain it. I'll attach the database to give you a look at what I mean. I'll also post a picture or three in case you don't want to download the database.
On the pictures, you can see the checkboxes are loading fine for Tab 1, but Tab 2 and Tab 3 should both be showing the first selection as checked, the rest unchecked.
voodoort
Did the pictures help or would you like to take a look at the database itself?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.