Get Key Value from Collection

[Peg via AccessMonster.com]

I've spent a lot of time researching how to implement a continuous subform
that will allow entry into one field. I am using a subform with a query
recordsource. The query is driven by data selected on the mainform.

To capture the data entry, I have used the MultiSelect example for
checkboxes. I needed a combo box control for entry. To simulate, I have a
textbox bound to a function set on top of an unbound combo box. I am
storing the data in a collection. Here is the code:

Public Function Set_txtGrd(varID As Variant) As String
Dim lngExists As Long

Set_txtGrd = ""

On Error GoTo Exit_StdExit:

lngExists = Forms!frmStdCrsOccGrdA_Batch.mGrdCollection(CStr(varID))
If lngExists <> 0 Then
Set_txtGrd = Me.cboGrd(lngExists, 2)
End If

Exit_StdExit:


End Function

Private Sub cboGrd_AfterUpdate()

If Set_txtGrd(Me.txtStdCrsOccAID) = "" Then
Forms!frmStdCrsOccGrdA_Batch.mGrdCollection.Add Item:=CLng(Me.cboGrd),
Key:=CStr(Me.txtStdCrsOccAID)
Else
Forms!frmStdCrsOccGrdA_Batch.mGrdCollection(CStr(Me.txtStdCrsOccAID)) =
Me.cboGrd
End If

Me.txtGrd.Requery

End Sub


I declared the collection on the main form because I don't want to save any
data until the Save command button on the main form is pressed. Basically,
the user just wants to go down her grade list entering the grades, do a
final visual check, then save.

These are my issues:

1. The key in the collection is the co-unique key needed to store the data
in the grade table, but I can't seem to find how to get it from the
collection. I set up the save procedure just to print the values so I can
see them, but all attempt to access key does not work.

Private Sub cmdSave_Click()
Dim x As Object
For Each x In mGrdCollection
Debug.Print x
Next x
End Sub

2. When I use the combobox on subsequent records, the textbox holding the
data on the previous record is covered up by the combobox, making it look
like the field is blank. I need it to look like the selected value for
that row. I plan to try to make the combobox transparent, but not sure if
that will work.

Any help, suggestions appreciated. Is there a better way?

 

[Peg via AccessMonster.com]

I forgot to mention (should never work at night) that I am working with
Access 2002 front end with Sql Server backend. I inherited a system where
it was decided to use ADO and unbound forms.
The main form is where the user selects the course, and then the subform
fills with all students enrolled in the course who do not have a grade.
The sole purpose of this form is to enter grades.

Thanks in advance for help.
Peg