Dlookup Function

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

Guest

I have a database called Products and another called
Orderdetail

On my form called order, I have a lookup box setup which
shows me the product code, name and price of all my
inventory. I want to be able to select the productcode and
have it autofill the name and price on two other fields in
the form. How do I do this?

Thanks in advance.
 
If the Name and Price are the second and third columns
respectively, the following should work in the After Update
event of the combobox...

Me!Name = Me!YourComboName.Column(1)
Me!Price = Me!YourComboName.Column(2)

Gary Miller
Sisters, OR
 
In the AfterUpdate event of the ProductCode control you can copy the values
from the appropriate columns of the combo/listbox to the other controls on
your form. For example, to copy the value from the 3rd column of a combo
named cboProductCode to a control named txtPrice

me.txtPrice=me.cboProductCode.Column(2)

I would copy the value from the price field since the price of a product may
change over time and it is appropriate to capture the current price in an
invoice/order detail record.

For the product name, I would go with a calculated control. In other words,
the value is not stored with the detail record, just displayed with it. For
this, just put the following into the controlSource of the text box:

=cboProductCode.Column(3)
 
One other thing, if you actually have a field named 'Name', I strongly
recommend renaming it to something else (ie productName). Name is a very
commonly used property name for most Objects in Access and you are likely
to have a conflict somewhere along the line.
 
I tried it and got the message 'The object doesn't contain
the Automation object 'Me". What is the ME in your statement?
 
Please excuse my lack of knowledge but this is something
sort of new to me.

what is the me and the cbo actually mean?

-----Original Message-----
In the AfterUpdate event of the ProductCode control you can copy the values
from the appropriate columns of the combo/listbox to the other controls on
your form. For example, to copy the value from the 3rd column of a combo
named cboProductCode to a control named txtPrice

me.txtPrice=me.cboProductCode.Column(2)

I would copy the value from the price field since the price of a product may
change over time and it is appropriate to capture the current price in an
invoice/order detail record.

For the product name, I would go with a calculated control. In other words,
the value is not stored with the detail record, just displayed with it. For
this, just put the following into the controlSource of the text box:

=cboProductCode.Column(3)


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

[email protected] said:
I have a database called Products and another called
Orderdetail

On my form called order, I have a lookup box setup which
shows me the product code, name and price of all my
inventory. I want to be able to select the productcode and
have it autofill the name and price on two other fields in
the form. How do I do this?

Thanks in advance.
Click to expand...

.
Click to expand...
 
"Me" is a keyword that refers to the specific instance of a class that is
executing. More simply, in this case it is a shortcut way to reference the
current form. So the following reference is to a control named 'MyControl'
which is on the form that goes with the class module that contains the
reference:

me.MyControl=3

'cbo' is merely a prefix that is often used by developers when naming combo
box controls. Typically when you add a bound control to a form, Access will
give the control the same name as the field to which it is bound. So in your
case, your combo (or listbox) probably has the same name as the field. Sorry
to have confused you.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Please excuse my lack of knowledge but this is something
sort of new to me.

what is the me and the cbo actually mean?

-----Original Message-----
In the AfterUpdate event of the ProductCode control you can copy the values
from the appropriate columns of the combo/listbox to the other controls
on your form. For example, to copy the value from the 3rd column of a
combo named cboProductCode to a control named txtPrice

me.txtPrice=me.cboProductCode.Column(2)

I would copy the value from the price field since the price of a product may
change over time and it is appropriate to capture the current price in an
invoice/order detail record.

For the product name, I would go with a calculated control. In other words,
the value is not stored with the detail record, just displayed with it.
For this, just put the following into the controlSource of the text box:

=cboProductCode.Column(3)


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I have a database called Products and another called
Orderdetail

On my form called order, I have a lookup box setup which
shows me the product code, name and price of all my
inventory. I want to be able to select the productcode and
have it autofill the name and price on two other fields in
the form. How do I do this?

Thanks in advance.
Click to expand...

.
Click to expand...
Click to expand...
 
I see that Sandra has given you the explanation of 'Me' and
'cbo'. Not sure why you are getting the automation error. Do
you have it working yet? If not, post the code for what you
tried with your exact control names.

Gary Miller
Sisters, OR

I tried it and got the message 'The object doesn't contain
the Automation object 'Me". What is the ME in your
Click to expand...
statement?
 
Back
Top