vlookup

[Gavin Eldridge]

Hi,

I am used to creating some quite complicated formulae in excel and now I'm
trying to set up a database in access and finding the language very
different. I do not know VBA or any other code.

The first thing that I have come up against is the performing he same
function as a vlookup in excel. I have 3 tables, "assets", "bookings" and
"personnel" in the assets form, I have a bookings subform linked to the
bookings table and within that I have the field "contact name". This is a
combobox and when the name is entered, I would like the form to check this
name against the personnel table and return the contact telephone number from
the personnel table into the next field.

eg
enter contact name: John Smith
Contact number: (this is then returned from the personnel table)

Thanks for your help in advance.

Gavin

 

[Rick A.B.]

Hi,

I am used to creating some quite complicated formulae in excel and now I'm
trying to set up a database in access and finding the language very
different. I do not know VBA or any other code.

The first thing that I have come up against is the performing he same
function as a vlookup in excel. I have 3 tables, "assets", "bookings" and
"personnel" in the assets form, I have a bookings subform linked to the
bookings table and within that I have the field "contact name". This is a
combobox and when the name is entered, I would like the form to check this
name against the personnel table and return the contact telephone number from
the personnel table into the next field.

eg
enter contact name: John Smith
Contact number: (this is then returned from the personnel table)

Thanks for your help in advance.

Gavin

Gavin,

Look up DLookup in the Help menu. That should give you everything you
need.

 

[Pat Hartman]

The Access equivalent is DLookup(). However, the vast majority of times
DLookup() is used, a query with a join should have been used instead. We
talk a lot here about database design and normalization but we don't talk
much about how you pull all those spread out attributes back into meaningful
data. Change the query your form is bound to join to the lookup table and
select the extra fields you want to display from there. Add them as bound
controls to your form. To prevent accidental updates to the lookup values,
set the locked property of lookup controls to yes. This will prevent a user
from changing a value here and impacting other records by accident.
Luckily, the query can be easily built with the QBE and using it does not
require any code. The other suggestion is a property setting which is done
by changing the value of a combo on the properties sheet for a control.

 

[Dale Fye]

I agree with Pat,

One of the nice things about Access is that you can create combo boxes that
contain more data than is visible in the combo box. For example, the query
that supports your combo box might look like:

SELECT ContactID, ContactName, ContactPhone
FROM tblPersonnel
ORDER BY ContactName

Then, you set the properties of the combo box to:

Columns:3
ColumnWidths: 0; 2 in; 0

Then you add an UNBOUND textbox (txt_ContactPhone) to the form. Set this
controls Locked property to True. In the AfterUpdate event of the combo box,
enter code that looks like:

Private Sub cbo_Contact_AfterUpdate()

'The combo boxes column property is zero based so the ContactID
'would be in column(0), ContactName in column(1), ...
me.txt_ContactPhone = me.cbo_Contact.column(2)

End Sub

Assuming that your cbo_Contact is bound to a ContactID or ContactName field
in your subform, then you would also have to add code (same line as above) to
the subforms Current event so that the phone number will be populated with
the appropriate value when you move between records within that form.

HTH
Dale