Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

drop down field

I have 2 tables with a one to many relationship in Access 2007
With form entry I want one of the tables to have adrop down list of values to select from the other table of same field.

How do i set up the form field to link to anthor table and display values to select from?
0
jagguy
Asked:
jagguy
1 Solution
 
MINDSUPERBCommented:
Open the table in design view, select the field that you want to have the drop down.
Then click Lookup tab. Select Combo box in Display Control.

RowSource type: Table/Query
Row Source: The name of your table.


When you put that field into the entry form, it will create a combo box that looking up the data of the table you specify.


sincerely,
Ed
0
 
jagguyAuthor Commented:
ok i get that.

what i also need is when i select a value from drop down box , I want another associated  field to display.

For example I might select Item ID from a combo box and I want the Item name to also appear somewhere whenever i select an item ID (the ItemID and name is defined in another table of course)
0
 
MINDSUPERBCommented:
You may try to use this vba:

At the after update event of the combo box

Me.YourFieldName = DLookUp("YourItemName","tblYourTableName","[ItemID] = & Nz(Me.cboNameOfCombo,0) ")

Ed
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
MINDSUPERBCommented:

Try to use this instead:

Me.ItemName = DLookUp("ItemName",YourTable","[ItemID] = Nz(cboName,0)")

ItemName = the fieldname of Item Name
YourTable = name of your table where ItemID and ItemName are found
ItemID = the ItemID field
cboName = the name of your combo box

Ed
0
 
jagguyAuthor Commented:
i dont want VBA code so is there another way i could do it?
0
 
MINDSUPERBCommented:
You can create a subform and set the Link Master Fields and Link Child Fields Properties to ID and ItemID.

But, I suggest to use the VBA and just invest some of your time on it. It is always nice to learn something new.

Ed
0
 
wobblynutCommented:
On the form control  where the id dropdown is (say called cmbID)
set the rowsource to SELECT table2.ID, table2.name FROM table2;
set bound column =1
column count=2
column widths 2.5,0

Set the control source on the form's control where you want the name to appear to =[cmbID].[column](1)

0
 
Helen FeddemaCommented:
Put the other field you want to display in the combo box's row source, and then use something like this as the control source of an unbound and locked textbox:

=[cboSelect].Column(2)

Column numbering is zero-based, so this refers to the 3rd column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now