Lookup column

Hello Everyone, 

I have a lookup column in my database with two parts. One part lists the 
supplier's name with the supplier's ID number directly accross in the other 
part. i enter data via a form. What I would like to do is select the supplier 
field on the form but have the supplier's ID number auto populate in another 
field on the form. Is this possible or am I going about this the wrong way?

Thanks.
0
Utf
12/18/2007 9:58:04 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
836 Views

Similar Articles

[PageSpeed] 49

If by "lookup column" you mean a combo box, and if both the supplier name and 
supplier ID appear in the combo box list, then you can use the column method 
to accomplish what you want.

Add an unbound textbox to your form, then in the 
AfterUdate event of your combo box put;

Me.NameOfYourUnboundTextbox = Me.NameOfYourComboBox.Column(x)

where x is the number of the column that contains the supplier ID. It's a 
zero based numbering system so Column(0) is the first column, Column(1) is 
the second column, etc.

HTH
-- 
_________

Sean Bailey


"Joe" wrote:

> Hello Everyone, 
> 
> I have a lookup column in my database with two parts. One part lists the 
> supplier's name with the supplier's ID number directly accross in the other 
> part. i enter data via a form. What I would like to do is select the supplier 
> field on the form but have the supplier's ID number auto populate in another 
> field on the form. Is this possible or am I going about this the wrong way?
> 
> Thanks.
0
Utf
12/18/2007 11:44:00 PM
Joe,
You wouldn't usually need the SupplierID to populate another control on the 
form.
Usually the combo with the Supplier has 2 columns. One of the columns is 
hidden from the user - the SupplierID.
So the form already knows the SupplierID once a user selects a supplier.
If you want to catch the SupplierID to feed it back to the table, the combo 
needs to have its datasource set to the field SupplierID and the SupplierID 
will automatically go into the table. However if the combo is just an 
unbound lookup to set the form to the Supplier's details, then don't do 
this.

Jeanette Cunningham

"Joe" <Joe@discussions.microsoft.com> wrote in message 
news:93A47E88-8380-4E39-9AEB-87861030495A@microsoft.com...
> Hello Everyone,
>
> I have a lookup column in my database with two parts. One part lists the
> supplier's name with the supplier's ID number directly accross in the 
> other
> part. i enter data via a form. What I would like to do is select the 
> supplier
> field on the form but have the supplier's ID number auto populate in 
> another
> field on the form. Is this possible or am I going about this the wrong 
> way?
>
> Thanks. 


0
Jeanette
12/19/2007 4:31:37 AM
Thanks Beetle, 

 I shall try this. Now I have several way to accomplish this task.

 Joe

"Beetle" wrote:

> If by "lookup column" you mean a combo box, and if both the supplier name and 
> supplier ID appear in the combo box list, then you can use the column method 
> to accomplish what you want.
> 
> Add an unbound textbox to your form, then in the 
> AfterUdate event of your combo box put;
> 
> Me.NameOfYourUnboundTextbox = Me.NameOfYourComboBox.Column(x)
> 
> where x is the number of the column that contains the supplier ID. It's a 
> zero based numbering system so Column(0) is the first column, Column(1) is 
> the second column, etc.
> 
> HTH
> -- 
> _________
> 
> Sean Bailey
> 
> 
> "Joe" wrote:
> 
> > Hello Everyone, 
> > 
> > I have a lookup column in my database with two parts. One part lists the 
> > supplier's name with the supplier's ID number directly accross in the other 
> > part. i enter data via a form. What I would like to do is select the supplier 
> > field on the form but have the supplier's ID number auto populate in another 
> > field on the form. Is this possible or am I going about this the wrong way?
> > 
> > Thanks.
0
Utf
12/19/2007 3:14:05 PM
Thanks again, Jeanette. I appreciate the information and shall act on it. 
Hopefully, you won't see another post from me about the same subject.

Joe

"Jeanette Cunningham" wrote:

> Joe,
> You wouldn't usually need the SupplierID to populate another control on the 
> form.
> Usually the combo with the Supplier has 2 columns. One of the columns is 
> hidden from the user - the SupplierID.
> So the form already knows the SupplierID once a user selects a supplier.
> If you want to catch the SupplierID to feed it back to the table, the combo 
> needs to have its datasource set to the field SupplierID and the SupplierID 
> will automatically go into the table. However if the combo is just an 
> unbound lookup to set the form to the Supplier's details, then don't do 
> this.
> 
> Jeanette Cunningham
> 
> "Joe" <Joe@discussions.microsoft.com> wrote in message 
> news:93A47E88-8380-4E39-9AEB-87861030495A@microsoft.com...
> > Hello Everyone,
> >
> > I have a lookup column in my database with two parts. One part lists the
> > supplier's name with the supplier's ID number directly accross in the 
> > other
> > part. i enter data via a form. What I would like to do is select the 
> > supplier
> > field on the form but have the supplier's ID number auto populate in 
> > another
> > field on the form. Is this possible or am I going about this the wrong 
> > way?
> >
> > Thanks. 
> 
> 
> 
0
Utf
12/19/2007 3:15:00 PM
Reply:

Similar Artilces:

How do I make a range of cells in a column equal to another cell
Vague, and you should always elaborate on your query in the message area (never leave it blank) Anyway, one thought .. Assume A1 will contain the common value for the range in B1:B5 Put in B1: =IF(A$1="","",A$1) Copy down to B5 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jggsfl" <jggsfl@discussions.microsoft.com> wrote in message news:CDAECC8A-6402-4A13-9F38-89A3E9446C72@microsoft.com... > Re: How do I make a range of cells in a column equal to another cell ...

lookup help #5
Second try with this...I'll try to explain it better. I have a two-cell worksheet win workbook1. It is replaced every day, automatically with another two cell sheet in a new workbook with the same name. A1 is a date. B1 is number, eg. 1542. I want to automatically place the number into a cell in another workbook and sheet, by matching the date with dates in column A of the second sheet, and Column E (fillers)of the second sheet. I've been playing with match, offset and index, lookup etc. I've been able to get a number from DATE:E and have it show up in the first workbook.sheet, but...

Sort limitation; How many columns can be sorted?
I was trying to sort many columns. The item in the sort was an integer in column AA. My Excel did not give me an error message, but the sort failed. I did not realize that the sort failed. Is there a limit on how many columns can be sorted? would having more RAM memory help? Does Escel use virtual memory? Bill -- An old man would be better off never having been born. On Mon, 23 Aug 2010 17:32:03 -0700, Salmon Egg <SalmonEgg@sbcglobal.net> wrote: >I was trying to sort many columns. The item in the sort was an integer >in column AA. My Excel did not give me an error message...

VBA code to find first blank cell in a column and activate it
MS XP Pro, MS Office 2007. Can anyone assist with macro to find the first blank cell in a column and then to make it the active (selected) cell so that data can be pasted to it and the row in which it is placed. I cant find a stable and reliable solution with the Find Blank Cell procedure in the menu. When you say first blank cell, do you mean - From the top down, the first blank cell with possibly other non blank cells below that blank cell. or - From the bottom up, the cell below the first non-blank cell. Regards, Peter T "Wes_A" <WesA@discussions.microso...

Insert a column in GANTT view to have link to Workspace
Personally, I am not a fan of the Project Workspace list taking up so much space and cluttering up the default homepage for PWA users. I was wondering, if we could add a column in the GANTT view of the Project Center and have users be able to click on the correct row to access the workspace instead? Any tips much appreciated. Hi Kahuna, In the Home page of PWA you can remove the Project Workspaces WebPart, and create a new page dedicted to this Project Workspaces WebPart. On the other hand, when your users are in Project Pro, the Collaborate menu can lead them to the Issues, ...

How do I set up a default column format in all my email folders?
I want a specific date format in my sent and received columns, but have to set each folder individually. Is there a way to do this globally? -- Christine Ckaesche <Ckaesche@discussions.microsoft.com> wrote: > I want a specific date format in my sent and received columns, but > have to set each folder individually. Is there a way to do this > globally? The techniques described here may help: http://www.outlook-tips.net/howto/grouping.htm -- Brian Tillman ...

Lookup #10
I have a sales spreadsheet that is broken down by month (in columns). Each sale is listed individually (rows), so a salesperson may be listed several times. I would like to key in a month somewhere on the spreadsheet and have a formula that will check the month column headings, find the month indicated, and add up the sales for that month, broken down be each salesperson. Thanks in advance for your help! Hi, Ellen One easy way would be to sort your spreadsheet - month, salesperson. Then go to Data, Subtotal and select Salesperson when it asks for each change, SUM, Amount That will...

formula to compare columns?
Hello Does anyone know how to write a formula that compares two adjacent columns of names and lists those names that aren't exact matches in a blank column. The columns of names are in ascending order and the quantity of names in each column may be different Thanks btk Hi enter the following in C1 =IF(COUNTIF($A$1:$A$1000,B1)>0,B1,"") and copy down -- Regards Frank Kabel Frankfurt, Germany btk wrote: > Hello, > > Does anyone know how to write a formula that compares two adjacent > columns of names and lists those names that aren't exact matches in a >...

Data connections properties include column headings
Data connections properties "include/exclude column headings" option for importing data from Access to Excel is missing in the new 2007 version. I have many Excel spreadsheets that import data from Access and the 2003 version shows this option when creating a new connection, but the 2007 version does not have it. Existing 2003 spreadsheets show the option when opened in 2007, but as soon as a new connection is made to add columns, it disappears. Since column headings have been customized and adjusted from access, I need to keep the Excel headings by "Excluding&q...

How do I strip out some parts of a column of text data?
I have a column of text data, which happens to be 11 characters wide. I want to strip the right-most 6 characters out of the entire column, without having to do each cell one at a time. For example I want to to from this data: 2039 041175 to this data: 2039 for all 350 or so rows of data in the column on the Excel sheet. Use a helper column of formulas =right(a1,6) will return the value as text =--right(a1,6) will return the value as numeric Just copy that down the helper column. footballcmr2 wrote: > > I have a column of text data, which happens to be 11 characters wide. I ...

Column order when using 'Analyze it with Excel'
When exporting data from Access to Excel using the 'Analyze it with Microsoft Excel' facility, the columns on the spreadsheet appear in a different order to those on the report. The report is very simple in design with the fields in one row across the report. How can I force the fields to stay in the order that I require? ...

Automatic bar width in stacked column chart too thin
Hi - I tried to post this question on 10 Jan but can't seem to find it so suspect my posting was unsuccessful - apologies if I've missed it. I've created a stacked column chart, but the bars are automatically coming out as 1pt wide so I can't even see the colours - previously column widths in a standard bar chart have been fine. The best I can do is to alter the gap width in 'format data series-> options' but this only makes it marginally wider. I'm guessing it has something to do with the data - this is as follows: Column A = weeks of the year (31/12/07...

Multiply columns
I need to figure out how to make the whole column multiply by 1.6. Can anyone please help me? I have sent my price list to excel and Im trying to do a mark upof 1.6 and it is just not letting me format the whole column K. Sonnier Hi try the following: - enter 1.6 in an empty cell, select this cell and copy it (e.g. with CTRL+C) - now select your prices - goto 'Edit - Paste Special' and choose the action 'Multiply' Note: your original data is lost after this! -- Regards Frank Kabel Frankfurt, Germany "K Sonnier" <ksonnier@swbell.net> schrieb im Newsbeitrag n...

How can I reverse the order of a column of numbers . . .
I want to turn a long column of numbers upsidedown so that the last entries become the first vice versa. >-----Original Message----- >I want to turn a long column of numbers upsidedown so that the last entries >become the first vice versa. >. > If they are consecutive order just select them and do data sort select descending or ascending. If they are not in consecutive order in the column next to it put consecutive numbers (ex.1..99 ) and select both areas and data sort on the column with the consecutive numbers. A reply is always nice to know that it worked for you. ...

Comparing two columns #2
I have two columns (A & B) with data. I'm trying to come up with a third column consisting of cells that appear in column A and not in B. (Due to the way I have hidden some columns of strings this 3rd column will be G). I have used the formula =(COUNTIF($A:$A,B1)=0)*(B1<>"") to conditionally format everything in B that isn't in A. These cells missing from A have been shaded. I'm either trying to select and move just these formatted (shaded) cells into column G (and from there to another file) OR use some other process (not conditional formmatting) to simply out...

Cutting first 4 digits from a cell and pasting it into an adjacent column
Using 2007 on Vista I've got some data I want to import from excel into my crm software For type of business here in the UK we use 'SIC' codes However, the data I have purchased has the SIC code followed by it's description I want to seperate the first 4 digits which is for the SIC code and leave the description behind as per the following data 3320: Manufacture Of Instruments & Appliances For Measuring, Checking, Testing, Navigating & Other Which I want to seperate into SIC code 3320 SIC description Manufacture Of Instruments & Appliances For Measuring, Chec...

Lookup vertically
In cell C25 I would like to place a formula that finds the closest cell UP in Column A that contains the word "Arizona", and then returns the value in column B for that row. For example: if A19 contains "Arizona" and B19 contains 34.... and A14 contains "Arizona" and B14 contains 38, I would like the formula in cell C25 to return 34--because row 19 is closer to row 25 than row 14 is to row 25. (assume the other rows in column A contain names of different states). Thanks very much to whoever can help!!! ** Posted via: http://www.ozgrid.com Excel Templates, Trainin...

Calculate Filtered Columns
Excel 2007 How can I calculate columns when I use the filter? The calculated numbers do not always match what they should be. Look up the SUBTOTAL function in help "Canon" wrote: > Excel 2007 > How can I calculate columns when I use the filter? > The calculated numbers do not always match what they should be. SUBTOTAL Function http://www.ozgrid.com/Excel/excel-subtotal-function.htm -- Regards Dave Hawley www.ozgrid.com "Canon" <Canon@discussions.microsoft.com> wrote in message news:A3188127-CB8C-4DD8-BDCE-98916FB07411@microsoft.com....

Format Excel column to notify with a reminder?
Hello, I have a mailing log in an Excel sheet. There are particular ocassions that need second copies to be mailed. I would like to be able to format my "sites" column to notify me with a reminder whenever I enter one of the particular "sites" that need the additional copies to be mailed. Can this be done? Using Excel 2000 Thanks Linda ...

column heading label
The column label heading has change from an ABC format, to 123 format just like the row format of 123 rows. I am not sure if I changed this setting how I did it or how to change it back to column A, B, C, ect. from 1, 2, 3, ect. Thanks for the help, Ron EXCEL 2007 Office Button (top left hand corner) Excel Options (lower right hand corner) Formulas - make sure that there is no tick in the box called:- R1C1 reference style If my comments have helped please hit Yes. Thanks. "Ron" wrote: > The column label heading has change from an ABC fo...

formula for non-sequential columns
I've got data in columns separated by two other columns. I wish to create a formula in another column that will include the data from these two non-sequential columns. How do I do this? Ex: data are found in columns G(rows 1) and J(rows1) and would like to create formula which lists such data in column S in the following order: info from column G, row 1 then info from column J, row 1. Thank you for any assistance. I've pulled out most of what hair I've got left over this. in column "S" =G1 & J1 -- bgeier ------------------------------------------------------...

Deleting excess rows and columns
First time to use Excel, and I can't find the answer. I have already set up my sheet, and it has infinite rows and columns. How do I limit it to just what I need? Hi the 256 columns and 65536 rows are the basis of an excel worksheet and can not be added to or deleted. However, you can hide unused ones. Select the unused columns and choose format/ column / hide. Repeat for the rows. Cheers JulieD "abbyzmom" <abbyzmom@discussions.microsoft.com> wrote in message news:A9CAC128-7246-41EC-8194-3DA3A9915613@microsoft.com... > First time to use Excel, and I can'...

printing frozen column on each page
In excel 2007, when printing a spreadsheet that is to wide to fit on a landscape page, is there a way to make the frozen left column print on the subsequent pages to the right? Not essential but would be nice. Thanks, Jeff Under Page Setup>Sheet there are the functions "rows to repeat at top" and "columns to repeat at left" Gord Dibben Microsoft Excel MVP On Thu, 25 Aug 2011 12:30:53 -0400, "Jeff@nospam.invalid" <Jeff@nospam.invalid> wrote: >In excel 2007, when printing a spreadsheet that is to wide to fit on a >landscape page, is there...

Add Extra Column to List Box
I am trying to add an extra column to my "Distribute Invoice" List Box I would like "Distribute Invoice" to show tblInvoice_ItMdt.TotalAmount" like Holding Invoice does Thanks for any help with this................Regards Bob ***Holding Invoices**** lstModify.RowSource = "SELECT tblInvoice_ItMdt.IntermediateID," _ & " tblInvoice_ItMdt.HorseID," _ & " funGetHorse(0,tblInvoice_ItMdt.HorseID,false) AS Name," _ & " tblInvoice_ItMdt.TotalAmount" _ & " FROM tblInvoice_ItMdt INNER JOIN tblHorseInf...

Trying to create a page with three columns
I imported a list with 4300 members into excel and right now it is 103 pages to print out. Is there a way to set the spreadsheet up to have 3 columns without having to manually cut and paste to create the columns? Thanks, Kim I would import it into Word and print it in multiple column there. Much easier than trying to set it up in Excel. On Fri, 1 Apr 2005 21:39:02 -0800, "kimbers867@comcast.net" <kimbers867comcastnet@discussions.microsoft.com> wrote: >I imported a list with 4300 members into excel and right now it is 103 pages >to print out. Is there a way to s...