linking tables

  • Thread starter Thread starter Shawna
  • Start date Start date
S

Shawna

I have two tables linked on a field ItemCode.
One is the ItemMaster and the other is POLines. I would
like user to look up item description on PO and Access to
update the ItemCode field in POLines. I have POLines
Desc. field using a combo box with lookup to ItemMaster
description - so user can search on desc. When user
picks Item to order (by description) can I have Access
update the Itemcode field in POLines based on ItemMaster?
Any help is appreciated. So far I can't get this to
work - tried using a query to solve as well.
 
Shawna said:
I have two tables linked on a field ItemCode.
One is the ItemMaster and the other is POLines. I would
like user to look up item description on PO and Access to
update the ItemCode field in POLines. I have POLines
Desc. field using a combo box with lookup to ItemMaster
description - so user can search on desc. When user
picks Item to order (by description) can I have Access
update the Itemcode field in POLines based on ItemMaster?
Any help is appreciated. So far I can't get this to
work - tried using a query to solve as well.
Click to expand...

Look at the column property of the comboBox.
Add the item number to the combo box and hide it.
Then in the AfterUpdate of the combo box do something like.

ItemID=Me!ComboBox.Column(1)

Remember the column index for the combo box starts a zero (0),
so the second column is 1.

HTH
Ron
 
-----Original Message-----


Look at the column property of the comboBox.
Add the item number to the combo box and hide it.
Then in the AfterUpdate of the combo box do something like.

ItemID=Me!ComboBox.Column(1)

Remember the column index for the combo box starts a zero (0),
so the second column is 1.

HTH
Ron
--
Ronald W. Roberts
Roberts Communication
(e-mail address removed)
To reply remove "_at_robcom_dot_com"

Thank you for your suggestion, I can't seem to get it
Click to expand...
to work. The combo box RowSource Property is
SELECT tblItemMaster.ItemNo, tblItemMaster.Description
FROM tblItemMaster; but all you see in the combo box is
the Description.

I added
ItemNo = Me!Description.Column(0) to the AfterUpdate Sub
but I get an error on this saying that
"You cannot assign a value to this object" and I get
sent to the debugger. I used Column (0) as ItemNo seems
to be the first column in the select. (Bound column is 1)

Thanks again
 
Shawna said:
to work. The combo box RowSource Property is
SELECT tblItemMaster.ItemNo, tblItemMaster.Description
FROM tblItemMaster; but all you see in the combo box is
the Description.

I added
ItemNo = Me!Description.Column(0) to the AfterUpdate Sub
but I get an error on this saying that
"You cannot assign a value to this object" and I get
sent to the debugger. I used Column (0) as ItemNo seems
to be the first column in the select. (Bound column is 1)

Thanks again
Click to expand...

I know this will work.
If cboControl is the name of the comboBox on your form.

dim txtDesc as String
Dim MyControl As Control
Set MyControl = cboControl

Me!SomeControlNameOnYourForm=MyControl.Column(0)
or
txtDesc=MyControl.Column(0)

Also, if cboControl is the name of the comboBox,
then you should be able to do this.

Me!SomeControlNameOnYourForm=Me!cboControl.Column(0)

Check Help under "Column Property"

You can use the Column property to assign the contents
of a combo box or list box to another control, such
as a text box. For example, to set the ControlSource
property of a text box to the value in the second
column of a list box, you could use the following
expression:

=Forms!Customers!CompanyName.Column(1)
or you could say
=Me!CompanyName.Column(1)


Ron
 
Back
Top