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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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.

Klatuu said:
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 said:
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
Click to expand...
Click to expand...
 
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

Frances said:
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.

Klatuu said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Klatuu, Thank you. It works very well.

Klatuu said:
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

Frances said:
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.

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top