Update record with save button after populating a textbox from combobox?

  • Thread starter Thread starter Sonicman
  • Start date Start date
S

Sonicman

Ok here's what I am trying to do. I am sure it's already been posted many
times but i have been searching for an hour now and nothing seems to be quite
like I want.

I have a ComboBox here filled with this SQL command:
SELECT [TableMiroir].[Name_mir], [TableMiroir].[NoCie_mir], [TableMiroir].
[Address_mir], [TableMiroir].[City_mir] FROM [TableMiroir] ORDER BY [Name_mir]
;

On the ComboBox Change() event: I have this code:
Texte4.Value = ComboBox2.Column(1) '(where Texte4 is an unbound textbox)

This way I can mofify the content of the textbox after it's been updated. I
couldn't use the rowsource = Combobox2.Column(1) property of the TextBox
because it won't allow be to modify the value in the textbox.

My question is, If I use more textboxes like this (for name, address, city
etc) all populated from a column on the combobox, and then modify some text
(ex: make a correction to the address..), Is there a way to update the
selected record in the combobox using a save button ?

Unless someone have a better idea of achieving this ?

Thanks in advance for the help

Sonicman
 
First thing is you probably want to move your code from the Change event to
the After Update event. the Change event fires on every keystroke. Based on
what you posted, that seems a bit of overkill and will only slow your form
down.

What you are doing seems to be a very strange approach and I don't really
understand the reason behind it. Would it not be just as easy to use a
traditional bound form with the text boxes on the form bound to the fields in
your table?

Is there a reason you need all those columns in your combo other than to
load the text boxes?

If what you are trying to accomplish is to use the combo to locate a record
in your table so you can present it for editing, the typical way to do that
is in the After Update event of the combo. The combo would only have to be
based on a field of unique values in your table. Here is some sample code:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.cboActivity = Null
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

If this wont do what you need to accomplish, post back and we can show you
how to do what you want using VBA; however, unless there is a valid, specific
reason for the way you are going, I would seriously consider changing to this
approach.

End Sub


Sonicman said:
Ok here's what I am trying to do. I am sure it's already been posted many
times but i have been searching for an hour now and nothing seems to be quite
like I want.

I have a ComboBox here filled with this SQL command:
SELECT [TableMiroir].[Name_mir], [TableMiroir].[NoCie_mir], [TableMiroir].
[Address_mir], [TableMiroir].[City_mir] FROM [TableMiroir] ORDER BY [Name_mir]
;

On the ComboBox Change() event: I have this code:
Texte4.Value = ComboBox2.Column(1) '(where Texte4 is an unbound textbox)

This way I can mofify the content of the textbox after it's been updated. I
couldn't use the rowsource = Combobox2.Column(1) property of the TextBox
because it won't allow be to modify the value in the textbox.

My question is, If I use more textboxes like this (for name, address, city
etc) all populated from a column on the combobox, and then modify some text
(ex: make a correction to the address..), Is there a way to update the
selected record in the combobox using a save button ?

Unless someone have a better idea of achieving this ?

Thanks in advance for the help

Sonicman
Click to expand...
 
Thanks for replying

You are right, what I am actually trying to do is Use the combobox to locate
a record
in my table so I can present it for editing, but not for editing "live". I
want a save button to make the changes.

I was actually trying to do it using an unbound Form. I don't mind doing it
on a Form bound to a query or table, as long as I can type text that doesn't
change the record if I don't hit the save button.

I was also using the Change event because I prefer to see the fields
populated at every keystrokes as we have multiple customer with "look-alike"
names. This way I can pinpoint wich one I want to edit.

Tell me what you think
 
You can still use a bound form and require a save button. All you need to do
is set a module level boolean variable to false in the Current event of the
form. Lets call it blnOkToSave. Then in the Click Event of the Save button
all you need to do is set it to True. Then in the Before Update event of the
form:
If Not blnOkToSave Then
Cancel = True
End if
and the record will not be saved.

As to seeing the changes, rather than using the
sssssssssslllllllllooooooowwwwww change event, use the After Update as I
suggested, and set the combo box's Auto Expand property to Yes in design
view. This will do what you are trying to accomplish with what the user sees.

What you are wanting can be done with an unbound form, but it takes a lot
more coding.
 
I don't really know what you mean by "set a module level boolean variable to
false"

I undestand I need to put it in the Current Event of the Form, but what code
do I write ?

As for the ComboBox, how do I make the form text field match with the value
in the combobox ?
I usually put a " WHERE NameField = Forms!formname.Combobox.Column(0) " in
the sql statement of the query.... Is this the right way to do it?


Thanks again
 
Back
Top