Retrieving values 1

[jamez05]

Hi,

I'm using Access 2003. I have a main form that has a dropdown box of site IDs. When a user selects a site ID, and event is triggered that sets the recordsource on a subform. This is a one to many relationship, so the subform can have multiple values.

I have a hidden field that holds a value based on the recordsource for each record on the subform. Right now, I get a 2424 error when I try to run the form. It says the form control doesn't exist, but it does. Also, if I hit end instead of debug it finds the field and the value.

My question is, instead of even having to mess with a hidden value, can I get the value of the record from the recordsource directly from the oncurrent event of the subform?

 

[Remou]

Probably. It is a little unclear; what is the purpose of the hidden value?

 

[jamez05]

The hidden value is used to figure out what options in a select box need to be highlighted:

Main forms dropdown on click:

    strSQL = "SELECT DISTINCT D.[Site ID], Chem.Name, Chem.CAS, Chem.CLASS,Chem.UserEntered FROM DATA D INNER JOIN "
    strSQL = strSQL & "tblChemClass Chem ON D.CAS = Chem.CAS WHERE D.[Site ID] = '" & SiteID & "'"

Form![UpdateChemClassSub].Form.RecordSource = strSQL

The subform has a hidden value for each record that gets set on the oncurrent event. The hidden value is set to Chem.Class from the recordsource. Not sure why I get the 2424 error because the hidden field exists. Here's what I'm trying to do:
subform's oncurrent:

    For i = 0 To Me.lstClass.ListCount - 1 Step 1
        If Me.lstClass.ItemData(i) = Me.TxtClass.Value Then
            Me.lstClass.Selected(i) = True
        End If
    Next i

 

[Remou]

It all seems a little difficult, for example, why not have link child and link master fields? Why not bind lstClass to Chem.Class?

 

[jamez05]

That sound like an excellent idea (been awhile since worked with access and didn't know you could do that). Thanks, I will look into that.