Copy part of existing record; selected via combo (1 Viewer)

[sparkyrose]

Hi all,

My Db has a form in which I'd like the user to be able to click a button to copy name and address details from an existing record to the current record. The idea is that the button opens a small data-collection form containing a combo, they select the source record by name (no need to review the data before updating), hit Ok, and the current record's name and address is populated.

I have the data-collection form but currently it runs a standard query which opens when you hit Ok, as per the following:

Private Sub btnOk_Click()

Me.Visible = False

DoCmd.OpenQuery "qryCopyAddress", acViewNormal, acEdit

DoCmd.Close acForm, "frmCopyAddress"

End Sub

I don't want to view the query results, just put them into the current record. I assume I need this event to run a select query based on the value of the combo, and then update the current record with those values. I'm afraid I'm just not familiar enough with VBA to do that!

Any advice greatly appreciated.

MCR

 

[Trevor G]

If the combo box has been created and you have several columns in it, like the name and address then you can use the ComboBox AfterUpdate Event to add the relevant information into the fields on the form.

So Say you have 3 Columns in the ComboBox showing UserName, Address, TelNo

and on your form you have 3 Textbox you want to change say they are named as txtUserName, txtAddress, txtTelNo then behind the combo in the Event Tab you click AfterUpdate and then use the elipse button and select Code Builder and the code would be:

txtUserName = me.ComboBox.Columns(0)
txtAddress=me.ComboBox.Columns(1)
txtTelNo=me.ComboBox.Columns(2)

I would ask are you sure you want the data on the form to change as this will effect your table.

 

[sparkyrose]

Thanks Trevor,

It seems to be updating the first field (i.e. FirstName) only.

The SQL for the Combo row source is

SELECT tblMaster.ID, tblMaster.Network, tblMaster.FirstName, tblMaster.LastName, tblMaster.Title, tblMaster.Address1, tblMaster.Address2, tblMaster.Address3, tblMaster.City, tblMaster.State, tblMaster.CountryName, tblMaster.PostalCode, tblMaster.Phone
FROM tblMaster
ORDER BY tblMaster.Network;

And the code for the AfterUpdate is

Private Sub cboSelNetwork_AfterUpdate()

Form_frmMainForm.FirstName = Me.cboSelNetwork.Column(2)
Form_frmMainForm.LastName = Me.cboSelNetwork.Column(3)
Form_frmMainForm.Title = Me.cboSelNetwork.Column(4)
Form_frmMainForm.Address1 = Me.cboSelNetwork.Column(5)
Form_frmMainForm.Address2 = Me.cboSelNetwork.Column(6)
Form_frmMainForm.Address3 = Me.cboSelNetwork.Column(7)
Form_frmMainForm.City = Me.cboSelNetwork.Column(8)
Form_frmMainForm.State = Me.cboSelNetwork.Column(9)
Form_frmMainForm.CountryName = Me.cboSelNetwork.Column(10)
Form_frmMainForm.PostalCode = Me.cboSelNetwork.Column(11)
Form_frmMainForm.Phone = Me.cboSelNetwork.Column(12)

End Sub

I specified the form name because the combo actually pops up on a different form.

Any thoughts?

 

[Trevor G]

What if you change the code from:

Form_

To

Forms!frmMainForm!FirstName = Me.cboSelNetwork.Column(2)

 

[sparkyrose]

I worked out the issue which was that the column count property was still set to 2. I adjusted to 12 and the update now works but unfortunately, now the dropdown looks a bit of a mess as it has all the additional columns bunched up to the right.

Is there any way to have them included in the SELECT statement but not appear in the dropdown?

 

[Trevor G]

Look at the properties of the combo and you will see you can adjust the column width so you can appear to hide the ones you don't want to see with setting the width to 0;0; etc

 

[sparkyrose]

Perfect.

This actually works better than I had originally planned because once I select the combo choice the fields are populated so I can see what the changes are. I set the Cancel button to set all fields to Null If I don't want to keep the changes.

Thanks very much for your help today!