updating form fields from a table based on a combo box selection 1

[lewie]

I am new to vb. I have a shipping form with a combo box for contacts. When a selection is made I want to fill in the information for a contact from a contact table into the forms text boxes. I can do it with an update query but then I have to requery to get it to show up and I don't feel like I have control.
TIA
Lewie

 

[drctx]

use the DLookup command for each field you want to fill in data from the contact table. then do a form.refresh.

field1 = DLookup("[CompanyName]", "Contact Table", "[ID] =" _
     & Forms![Shipping Form]!ID)
field2 = DLookup("[Address]", "Contact Table", "[ID] =" _
     & Forms![Shipping Form]!ID)
field3 = DLookup("[E-mail]", "Contact Table", "[ID] =" _
     & Forms![Shipping Form]!ID)
field4 = DLookup("[PhoneNumber]", "Contact Table", "[ID] =" _
     & Forms![Shipping Form]!ID)
Form.Refresh

 

[LittleSmudge]

DLOOKUPs are Extreemly Slow - there is a quicker way


The RowSource of your combobox will probobly look something like

RowSource = "SELECT contactId, CoName FROM tblContact"
with the
ColumnWidth = 0;

Well Change that to

Rowsource = "SELECT ContactId, CoName, Address1, Address2, Address3, Town, County, PostCode FROM tblContact
ColumnWidth = 0;;0;0;0;0;0;0
ColumnCount = 8

( access may well insert inch or cm units in the width line automatically - depends on local settings - thats okay )

( rename and adjust coluumn names and count as appropriate )

( and yes there are two semicolons together in the ColumnWidth property - thats not a typo
It means Column(0) is zero width
Column(1) is with of control
all other Columns (2) to (8) are zero width )

The combo Box wil now DISPLAY just the CoName as before.
But it Knows about all of the other data for the record you have selected.

In the comboBox's AfterUpdate event you then put code :-

txtAddress1 = cboSelectContact.Column(2)
txtAddress2 = cboSelectContact.Column(3)
txtAddress3 = cboSelectContact.Column(4)
txtTown = cboSelectContact.Column(5)
txtCounty = cboSelectContact.Column(6)
txtPostcode = cboSelectContact.Column(7)

txtXXXX are the names of the text box controls on the form


'ope-that-'elps.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.