How to work with records in a subform ? 1

[KerryMarie]

I created a subform whose Source Object is a Security Table. One of the fields on the Security table is Rights.

The rights field has the following possible values:
These rights are assigned to individuals whose last name and first name also apear in this Security Table.

Rights (meaning)
----------------------------
Admin Adminstrator
View View
Update Update
None None

The subform displays the fields from this security table. Alongside the subform is the following Option Group titled Access Level:

Access Level
---------------
X Admin User
X Update User
X View User
X No Access

There are radio buttons represented by the letter X next to each Access Level so the user can select one of these 4 Access Levels. The access level selected will be assigned to the individual whose row is pointed to in the adjacent subform which displays the records in the Security Table.

I created a hidden text box that is bound to the Rights field to which the Option Group refers. The purpose of the text box is to hold the translated value from the option group and pass that value to the Security table's Rights field.

For ex: If John Smith's record is pointed to by the record selector in the subform and the user went to the option group and selected the radio button Admin. Then I want to assign the value Admin to the hidden text box. Then I would assign this value of Admin to the Rights field of the Security Table for John Smith.

When the user makes a selection from the option group, I inserted the following VBA code to translate the Radio button value selected into a translated value. For example, a value of 1 in the Option Group equates to value Admin for the text box value and ulitmately for the value to be inserted into the Righrs field for the Security table for the individual whose record is pointed to in the subform by the record selector.

Private Sub Frame203_AfterUpdate()
Select Case Frame203.Value
Case 1
Me.txtRights.Value = "Admin"
Case 2
Me.txtRights.Value = "Update"
Case 3
Me.txtRights.Value = "View"
Case 4
Me.txtRights.Value = "None"
End Select
End Sub

Now everytime the user clicks on a radio button, the corresponding translated value get populated into the textbox.

Also I would use the following code to translate the existing Rights field value for individual users that exist in the Security table to populate thier equivalent value into the Options Group:

Select Case txtRights.Value
Case "Admin"
Frame203.Value = 1
Case "Upate"
Frame203.Value = 2
Case "View"
Frame203.Value = 3
Cases "None"
Frame203.Value = 4
End Select

My problem is that I don't know how to retrive the Rights value from the Security table for the individual pointed to by the record selector when the subform is intially displayed. The subform is displayed when the user selects on of the tabs from the tab control.

Also I don't know how to point to the Security table record when it is time to update a record based on the Option Group update made by the user.

As I stated earlier, I created a subform whose Source Object is a Security Table but I don't know how to get at the records pointed to by the record selector of the subform.

 

[Zion7]

KerryMarie,
Sorry it's very late over here, so I'm not sure If I understood your question.

In short, I think it's a referencing issue???

to refer to any control on your subform, from the main form use...

Me!SubFormName.Form!txtControl

& vica versa, use
Forms!MainFormname!ControlName

you may not need your hidden box after all.

again, sorry KerryMarie, if I misunderstood your objective.

good luck either way!

 

[VBslammer]

Main form code:

Private Sub Frame203_AfterUpdate()
On Error Resume Next
  Dim strRights As String

  Select Case Frame203.Value
    Case 1
      strRights = "Admin"
    Case 2
      strRights = "Update"
    Case 3
      strRights = "View"
    Case 4
      strRights = "None"
  End Select

  If Me!subControlName.Form!Rights <> strRights Then
    Me!subControlName.Form!Rights = strRights
  End If

End Sub

Subform code:

Private Sub Form_Current()
  Call UpdateFrame
End Sub

Private Sub Rights_AfterUpdate()
  Call UpdateFrame
End Sub

Private Sub Rights_Change()
  Call UpdateFrame
End Sub

Private Sub UpdateFrame()
Dim intRights As Integer

  Select Case Me!Rights
    Case "Admin"
      intRights = 1
    Case "Update"
      intRights = 2
    Case "View"
      intRights = 3
    Case "None"
      intRights = 4
  End Select

  Forms!ParentFormName!Frame203 = intRights

End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob