DAO Recordset - Edit and Update

[scottydel]

Hello,

I have a continuous form bound to a table. I have one control on the form, a combobox. When I select a new value in the form's combobox (which is bound to a field in the table) I would like to update a "hidden" field on the table, in the same record. By "hidden" I mean not visibly bound to any control on the form.

So for example, my bound table has two fields, and is empty to begin with:

Fruit Size
---- ----


I have another table (not bound to any form) like so:

Fruit Color Size Seedless
---- ---- --- --------
Apple Red M N
Pear Grn M N
Grape Grn S Y


If I select "Apple" from the form's combobox, I would like the bound table to update to:

Fruit Size
---- ----
Apple M

I have no problem populating the "Fruit" field, since it is bound directly to the combobox, but am having a problem also populating the "Size" field, because it is not bound to any control.

I have populated the combobox's Row Source with a query having 2 columns, Fruit and Size, Fruit being visible and Size being hidden. I'm trying to use the Size column's value to populate a record in the table's Size field. I've taken this approach, and had some luck, but the code seems to trip itself up sometimes and is not consistently working (sometimes Size is missing, sometimes it's there):

After a few goes, my table might look like:

Fruit Size
---- ----
Apple
Grape S
Pear

Here is the code (I'm using the AfterUpdate event on the combobox):

Private Sub cmbFruits_AfterUpdate()

Dim rs As Recordset
Set rs = Me.Recordset.Clone

rs.FindFirst "[Fruit] = '" & Me![cmbFruits] & "'"
If Not rs.EOF Then
rs.Edit
rs.Fields("SIZE").Value = Me.cmbManagers.Column(1)
rs.Update
Set Me.Recordset = rs
End If

Set rs = Nothing

End Sub

I'm not quite sure about the placement of the Edit and Update methods, and should I be setting one recordset to another?

Hope I haven't made this confusing...

Any help would be appreciated!

Thanks,

Scott

 

[boblarson]

Is there a reason why you don't do it the real easy way and bind the field to a text box and have its Visible property set to NO. Then you can update it in the after update event of the combo box and it will write it to the table with no more than one line of code necessary.

 

[scottydel]

Bob,

Thanks for the insight. Not sure why I didn't see a simpler solution at first Are you still assuming the form is using a multi-column query that populates the combobox?

So the combobox will still be populated with two columns, like:

Apple, M
Grape, S
Banana, M

etc.

Where the first column (or column 0 in VBA) above is bound to the table's Fruit field, and the second column (or column 1 in VBA) could be set with one line of code in the combobo's after update event, like this:

Me.NewTextBoxBoundtoSize.Value = Me.cmbFruits.Column(1)

And the NewTextBoxBoundtoSize is (obviously) bound to the table's Size field. Is this what you're suggesting?

Thanks,

Scott

 

[boblarson]

By George (I mean Scott ) I think you've got it.

 

[scottydel]

Much easier indeed, thank you!!

-"George" a.k.a. Scott