Form RecordSet Select based on Combo Box value

[jtertin]

I have a form with a Record Set of "TableA", which has a primary key
of "ID"

I also have a drop down box which is populated by querying "TableA" -
it's Bound Column property is set to column "ID".

I have a couple of other controls on the form (which are refreshed
after the drop down is changed) with their Control Sources bound to
other columns of "TableA".

Question:

Is there a way to use the value of the drop down box to "select" the
corresponding record in the form's Record Set so that it's other
column data (displayed in the other controls) is updated on the form
with their Control Sources bound to "TableA"?

 

[Scott Lichtenberg]

I'm not really sure what you are trying to do. It sounds like you want to
use a combo box in the header of the form to move to a record in your
underlying recordset. If this is what you want, it's actually pretty easy.
You are going to use the recordset clone property of your form's
recordsource and a bookmark. Try the following in the AfterUpdate event of
your combo box.

Dim rs as Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "MyIDField = " & Me!cboIDField 'You may need
embedded quotes if your ID is a string
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing


Setting the bookmark property of the recordset in your form moves you to the
selected record.


'This is how you embed quotes. I put spaces in for clarity. You don't need
the spaces.
FindFirst "MyIDField = ' " & Me!cboIDField & " ' "


Hope this helps.

 

[jtertin]

Thank you for your response.

Your approach works so far as displaying data for that record in other
objects on the form. However, the data displayed in the objects is
not editable.

Aside from dynamically updating and executing a query based on the
drop down selection and binding all of the controls to that query, is
there another way to accomplish this functionality that would use the
currently available record set?

Thank you.

 

[Scott Lichtenberg]

Moving between records by setting the bookmark doesn't affect whether the
data is modifiable. Take a look at the following:

1. On the data tab of the form's properties, is your recordsource a dynaset
or snapshot? Snapshots are not modifiable.
2. Is your recordsource a table or query (or SQL statement)? Some queries
and SQL statements return data that is not modifiable, especially if you
have sums or similar grouping functions in the query.
3. Is the AllowEdits property of the form set to true?
4. When you open the form, are you using the acReadOnly function of the
DoCmd.OpenForm command? This will override the AllowEdits property.
5. Are your fields locked or disabled?

One other note - your combo box should not be a bound field. If it is a
bound field, when you select a new value, you will be updating the current
record. Since you are picking from existing values, you are effectively
trying to change your primary key to the primary key of another record. You
will get an error if you do this.