create a look up
in [General]
|
From: JMD.Park on 19 Mar 2010 09:07 Lets try this again. I am trying to use DLookup to bring up several fields from a table into a form. Can someone please let me know if this is possible and tell me how to do this? I am new to expression builder so please be patient with me and specific as well as where to build. I would like this "CustomerName" that is a number field in Form called "Distribution" to bring up these fields from "Historical" table that is linked by the way. The following fields are for me to verify that I have the correct customer number. The fields are all text and are fields of there own in the form. They are as follows: BusinessName ,MailingAddress1, Mailing Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this: =DLookUp("[Distribution]","Historical","[CustomerNumber] = " &Me.[CustomerNumber]) I receive an error. I have also tried the wand. It does not bring up the correct customer with number and only brings up several fields that are not placed in the forms table. "fredg" wrote: > On Fri, 12 Mar 2010 08:51:02 -0800, JMD.Park wrote: > > > I would like to create a look up for a field in a form. I would like to put > > in a customer number that will bring up their name and address info. How can > > I do this? Thanks in advance for your assistance. > > It depends. > If The [CustomerNumber] field's datatype is a number, then: > =DLookUp("[Address]","TableName","[CustomerNumber] = " & > Me.[CustomerNumber]) > > However, if the [CustomerNumber] field is a Text datatype, then: > > =DLookUp("[Address]","TableName","[CustomerNumber] = '" & > Me.[CustomerNumber] & "'") > > Do the same for the Customer's Name field. > > Change CustomerNumber to whatever the actual field name is. > -- > Fred > Please respond only to this newsgroup. > I do not reply to personal e-mail > . >
From: John W. Vinson on 19 Mar 2010 11:55 On Fri, 19 Mar 2010 06:05:02 -0700, JMD.Park <JMDPark(a)discussions.microsoft.com> wrote: >Lets try this again. I am trying to use DLookup to bring up several fields >from a table into a form. Can someone please let me know if this is possible >and tell me how to do this? I am new to expression builder so please be >patient with me and specific as well as where to build. >I would like this "CustomerName" that is a number field in Form called >"Distribution" to bring up these fields from "Historical" table that is >linked by the way. The following fields are for me to verify that I have the >correct customer >number. The fields are all text and are fields of there own in the form. >They are as follows: BusinessName ,MailingAddress1, Mailing >Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this: >=DLookUp("[Distribution]","Historical","[CustomerNumber] = " >&Me.[CustomerNumber]) Let's try this again. 1. What is the Recordsource of the Form? What table? Could you post the SQL? 2. What is the Rowsource of the combo box? Could you post its SQL? 3, and most important: are you trying to *COPY* the CustomerName and other fields from the combo box's table into the form's (different) table, or are you just trying to *DISPLAY* them? The latter would be correct, so I'll give instructions: Include all the fields that you want to see in the Combo Box's Rowsource. Guessing in the dark, I'll say that the row source might be SELECT CustomerNumber, BusinessName, MailingAddress1, MailingAddress2, MailingCity, MailingState, MailingZip, Phone FROM Historical ORDER BY BusinessName; You can set the ColumnWidths property of the combo so that columns you don't want to see have zero width; only the first nonzero column will be visible when the combo isn't dropped down. So you might have a ColumnWidths property like 0";1.25";0";0";1";0.25";0" so the user can see the BusinessName, City and State when the combo is dropped, and only the BusinessName when it's not. To display the other fields' data on the form, have a textbox for (say) MailingAddress1. Set its Control Source property to =comboboxname.Column(2) This will display the third column of the combo (it's zero based) - the mailingaddress1 field. If you already have a MailingAddress1 control on the form, bound to a MailingAddress1 field in your table, and this is a different table than the one upon which the combo box is based... *you're making a bad mistake* or else need to explain why you would want to violate relational design principles by storing the same information redundantly in two different table! -- John W. Vinson [MVP]
From: JMD.Park on 19 Mar 2010 16:57 Where do I find the record source and sql? "John W. Vinson" wrote: > On Fri, 19 Mar 2010 06:05:02 -0700, JMD.Park > <JMDPark(a)discussions.microsoft.com> wrote: > > >Lets try this again. I am trying to use DLookup to bring up several fields > >from a table into a form. Can someone please let me know if this is possible > >and tell me how to do this? I am new to expression builder so please be > >patient with me and specific as well as where to build. > >I would like this "CustomerName" that is a number field in Form called > >"Distribution" to bring up these fields from "Historical" table that is > >linked by the way. The following fields are for me to verify that I have the > >correct customer > >number. The fields are all text and are fields of there own in the form. > >They are as follows: BusinessName ,MailingAddress1, Mailing > >Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this: > >=DLookUp("[Distribution]","Historical","[CustomerNumber] = " > >&Me.[CustomerNumber]) > > Let's try this again. > > 1. What is the Recordsource of the Form? What table? Could you post the SQL? > 2. What is the Rowsource of the combo box? Could you post its SQL? > 3, and most important: are you trying to *COPY* the CustomerName and other > fields from the combo box's table into the form's (different) table, or are > you just trying to *DISPLAY* them? The latter would be correct, so I'll give > instructions: > > Include all the fields that you want to see in the Combo Box's Rowsource. > Guessing in the dark, I'll say that the row source might be > > SELECT CustomerNumber, BusinessName, MailingAddress1, MailingAddress2, > MailingCity, MailingState, MailingZip, Phone FROM Historical ORDER BY > BusinessName; > > You can set the ColumnWidths property of the combo so that columns you don't > want to see have zero width; only the first nonzero column will be visible > when the combo isn't dropped down. So you might have a ColumnWidths property > like > > 0";1.25";0";0";1";0.25";0" > > so the user can see the BusinessName, City and State when the combo is > dropped, and only the BusinessName when it's not. > > To display the other fields' data on the form, have a textbox for (say) > MailingAddress1. Set its Control Source property to > > =comboboxname.Column(2) > > This will display the third column of the combo (it's zero based) - the > mailingaddress1 field. > > If you already have a MailingAddress1 control on the form, bound to a > MailingAddress1 field in your table, and this is a different table than the > one upon which the combo box is based... *you're making a bad mistake* or else > need to explain why you would want to violate relational design principles by > storing the same information redundantly in two different table! > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 19 Mar 2010 17:14 On Fri, 19 Mar 2010 13:57:02 -0700, JMD.Park <JMDPark(a)discussions.microsoft.com> wrote: >Where do I find the record source and sql? Open the form in design view. View its Properties (rightclick the little square at the upper left intersection of the rulers and choose Properties, or use the View menu option). The Recordsource is the first row on the Data tab. Click the ... icon by it; if Access asks if you want to open a query, do so. Select View... SQL from the menu, or choose SQL from the leftmost dropdown in the toolbar. Copy and paste the SQL to a message here. Did you try my suggestion? (you certainly didn't answer my questions). -- John W. Vinson [MVP]
From: JMD.Park on 24 Mar 2010 10:34 SELECT * FROM [TRIP TICKETS ISSUED TO]; No Combo Box "John W. Vinson" wrote: > On Fri, 19 Mar 2010 13:57:02 -0700, JMD.Park > <JMDPark(a)discussions.microsoft.com> wrote: > > >Where do I find the record source and sql? > > Open the form in design view. > View its Properties (rightclick the little square at the upper left > intersection of the rulers and choose Properties, or use the View menu > option). > The Recordsource is the first row on the Data tab. > Click the ... icon by it; if Access asks if you want to open a query, do so. > Select View... SQL from the menu, or choose SQL from the leftmost dropdown in > the toolbar. > Copy and paste the SQL to a message here. > > Did you try my suggestion? (you certainly didn't answer my questions). > -- > > John W. Vinson [MVP] > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Subdatasheet records not updating totals Next: Crystal Reports 2008 |