son't want to overwrite source record after combo box selection

[Frances]

I have a form with two combo boxes, one selection is based on the other.
However, it always overwrites a source record after the seconde selection is
done. How do I tell the form NOT to write selections from combo boxes back
to source table? Should I not use After_update()? Please help. Thanks.

Private Sub Select1_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![Select1_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Select2_Combo].Requery
End Sub
Private Sub Select2_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field2] = '" & Me![Select2_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Klatuu]

Your problem here is that the combo boxes are bound controls. When you
change a value in a bound control, it will update the record. The solution
is to make the combo boxes unbound, create hidden text boxes that are bound,
and programmatically update the hidden text boxes with the combo box values
only when you want the table updated.

 

[Frances]

Klatuu, Thanks for the reply. I'm sure you are right. But could you please
explain it a little more? How do I implement it? Thanks.

Your problem here is that the combo boxes are bound controls. When you
change a value in a bound control, it will update the record. The solution
is to make the combo boxes unbound, create hidden text boxes that are bound,
and programmatically update the hidden text boxes with the combo box values
only when you want the table updated.

I have a form with two combo boxes, one selection is based on the other.
However, it always overwrites a source record after the seconde selection is
done. How do I tell the form NOT to write selections from combo boxes back
to source table? Should I not use After_update()? Please help. Thanks.

Private Sub Select1_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![Select1_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Select2_Combo].Requery
End Sub
Private Sub Select2_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field2] = '" & Me![Select2_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Klatuu]

Create two new text boxes on your form. Set the Visible and Tab
Stopproperties to No. Bind the fields in the recordset to the fields
currently bound to the combo boxes. Make the combo boxes unbound.
In the After Update event of each of the combo boxes:
Me.TextBox1 = Me.ComboBox1

Then in the current event of the form:

Me.ComboBox1 = Me.TextBox1
Me.ComboBox2 = Me.TextBox2

Klatuu, Thanks for the reply. I'm sure you are right. But could you please
explain it a little more? How do I implement it? Thanks.

Your problem here is that the combo boxes are bound controls. When you
change a value in a bound control, it will update the record. The solution
is to make the combo boxes unbound, create hidden text boxes that are bound,
and programmatically update the hidden text boxes with the combo box values
only when you want the table updated.

I have a form with two combo boxes, one selection is based on the other.
However, it always overwrites a source record after the seconde selection is
done. How do I tell the form NOT to write selections from combo boxes back
to source table? Should I not use After_update()? Please help. Thanks.

Private Sub Select1_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![Select1_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Select2_Combo].Requery
End Sub
Private Sub Select2_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field2] = '" & Me![Select2_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Frances]

Klatuu, Thank you. It works very well.

Create two new text boxes on your form. Set the Visible and Tab
Stopproperties to No. Bind the fields in the recordset to the fields
currently bound to the combo boxes. Make the combo boxes unbound.
In the After Update event of each of the combo boxes:
Me.TextBox1 = Me.ComboBox1

Then in the current event of the form:

Me.ComboBox1 = Me.TextBox1
Me.ComboBox2 = Me.TextBox2

Klatuu, Thanks for the reply. I'm sure you are right. But could you please
explain it a little more? How do I implement it? Thanks.

Your problem here is that the combo boxes are bound controls. When you
change a value in a bound control, it will update the record. The solution
is to make the combo boxes unbound, create hidden text boxes that are bound,
and programmatically update the hidden text boxes with the combo box values
only when you want the table updated.

:

I have a form with two combo boxes, one selection is based on the other.
However, it always overwrites a source record after the seconde selection is
done. How do I tell the form NOT to write selections from combo boxes back
to source table? Should I not use After_update()? Please help. Thanks.

Private Sub Select1_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![Select1_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Select2_Combo].Requery
End Sub
Private Sub Select2_Combo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field2] = '" & Me![Select2_Combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub