Autopopulate Multiple Fields in Table Using a Form

I have a form based on a table where I want to store data. The data to store 
comes from combo boxes based on various lookup tables. The wrinkle is that 
there is one lookup table that contains 2 fields of data (questionnum and 
question) I want stored in the table once the questionnum is selected. The 
question field is formatted as 'memo'. When I create the combo box the 
question field is not an option for selection. I add it manually to the 
properties after completing the criteria for the combo box. I have been 
struggling with the code used to autopopulate more than one field but have 
been unsuccessful. Does the memo format of the question field have any impact 
on what I am trying to do?
1
Utf
4/23/2007 7:20:02 PM
access.forms 6864 articles. 1 followers. Follow

4 Replies
1306 Views

Similar Articles

[PageSpeed] 33

No, you don't!

If the data is already stored in your "lookup table", there's rarely a good 
reason to store it redundantly in another table.  While this approach may be 
needed if you were working with a spreadsheet, Access is a relational 
database, and there are several very good reasons NOT to store redundant 
data.

Save the ID of the record, not the entire record.  If you need to see the 
looked-up information, use a query to join the tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"lreecher" <lreecher@discussions.microsoft.com> wrote in message 
news:24A1628B-C15D-4589-A6A8-7EB1C9915630@microsoft.com...
>I have a form based on a table where I want to store data. The data to 
>store
> comes from combo boxes based on various lookup tables. The wrinkle is that
> there is one lookup table that contains 2 fields of data (questionnum and
> question) I want stored in the table once the questionnum is selected. The
> question field is formatted as 'memo'. When I create the combo box the
> question field is not an option for selection. I add it manually to the
> properties after completing the criteria for the combo box. I have been
> struggling with the code used to autopopulate more than one field but have
> been unsuccessful. Does the memo format of the question field have any 
> impact
> on what I am trying to do? 


0
Jeff
4/23/2007 8:05:35 PM
On Mon, 23 Apr 2007 12:20:02 -0700, lreecher
<lreecher@discussions.microsoft.com> wrote:

>I have a form based on a table where I want to store data. The data to store 
>comes from combo boxes based on various lookup tables. The wrinkle is that 
>there is one lookup table that contains 2 fields of data (questionnum and 
>question) I want stored in the table once the questionnum is selected. The 
>question field is formatted as 'memo'. When I create the combo box the 
>question field is not an option for selection. I add it manually to the 
>properties after completing the criteria for the combo box. I have been 
>struggling with the code used to autopopulate more than one field but have 
>been unsuccessful. Does the memo format of the question field have any impact 
>on what I am trying to do?

Yes. A Combo Box is limited in size - it cannot contain the up to two billion
bytes which could be put into a Memo field!

The first question is: WHY? Storing the question text redundantly in a second
table is neither necessary nor is it good design. Store the question number,
and use a Query to link to the questions table to pick up the question text
when needed!

             John W. Vinson [MVP]
0
John
4/23/2007 11:06:36 PM
You are correct that the data should not be stored several times, especially 
a memo formatted field. However, the lookup table is in the process of being 
built with questions. Eventually, I should be able to use the question num as 
the key. Right now it is easier for users to be able to enter new questions 
as well as use existing questions in the lookup table. 
Thank you for your input. From your comments, I was able to view the problem 
in a different way and devise a better solution.
"John W. Vinson" wrote:

> On Mon, 23 Apr 2007 12:20:02 -0700, lreecher
> <lreecher@discussions.microsoft.com> wrote:
> 
> >I have a form based on a table where I want to store data. The data to store 
> >comes from combo boxes based on various lookup tables. The wrinkle is that 
> >there is one lookup table that contains 2 fields of data (questionnum and 
> >question) I want stored in the table once the questionnum is selected. The 
> >question field is formatted as 'memo'. When I create the combo box the 
> >question field is not an option for selection. I add it manually to the 
> >properties after completing the criteria for the combo box. I have been 
> >struggling with the code used to autopopulate more than one field but have 
> >been unsuccessful. Does the memo format of the question field have any impact 
> >on what I am trying to do?
> 
> Yes. A Combo Box is limited in size - it cannot contain the up to two billion
> bytes which could be put into a Memo field!
> 
> The first question is: WHY? Storing the question text redundantly in a second
> table is neither necessary nor is it good design. Store the question number,
> and use a Query to link to the questions table to pick up the question text
> when needed!
> 
>              John W. Vinson [MVP]
> 
0
Utf
4/24/2007 12:28:00 PM
If you are working on something like a survey ("... questions..."), consider 
taking a look at a sample db created by a fellow MVP, Duane Hookom:

 http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

"lreecher" <lreecher@discussions.microsoft.com> wrote in message 
news:81A6473B-B870-452E-BD50-66F72C78CCCE@microsoft.com...
> You are correct that the data should not be stored several times, 
> especially
> a memo formatted field. However, the lookup table is in the process of 
> being
> built with questions. Eventually, I should be able to use the question num 
> as
> the key. Right now it is easier for users to be able to enter new 
> questions
> as well as use existing questions in the lookup table.
> Thank you for your input. From your comments, I was able to view the 
> problem
> in a different way and devise a better solution.
> "John W. Vinson" wrote:
>
>> On Mon, 23 Apr 2007 12:20:02 -0700, lreecher
>> <lreecher@discussions.microsoft.com> wrote:
>>
>> >I have a form based on a table where I want to store data. The data to 
>> >store
>> >comes from combo boxes based on various lookup tables. The wrinkle is 
>> >that
>> >there is one lookup table that contains 2 fields of data (questionnum 
>> >and
>> >question) I want stored in the table once the questionnum is selected. 
>> >The
>> >question field is formatted as 'memo'. When I create the combo box the
>> >question field is not an option for selection. I add it manually to the
>> >properties after completing the criteria for the combo box. I have been
>> >struggling with the code used to autopopulate more than one field but 
>> >have
>> >been unsuccessful. Does the memo format of the question field have any 
>> >impact
>> >on what I am trying to do?
>>
>> Yes. A Combo Box is limited in size - it cannot contain the up to two 
>> billion
>> bytes which could be put into a Memo field!
>>
>> The first question is: WHY? Storing the question text redundantly in a 
>> second
>> table is neither necessary nor is it good design. Store the question 
>> number,
>> and use a Query to link to the questions table to pick up the question 
>> text
>> when needed!
>>
>>              John W. Vinson [MVP]
>> 


0
Jeff
4/24/2007 3:02:07 PM
Reply:

Similar Artilces:

Multiple Charts #9
I have a spreadsheet that lists 575 departments with monthly totals for each department. I need to create individual charts for each department that show the monthly totals. I was able to create a dropdown list where I can pick the department but I can't figure out how to correlate the correct row of data to that department when I change it. Example: Dept Jan Feb Mar Apr May, etc. 500 x x x x x 501 x x x x x 502 x x x x x 503 x x x x x 504 ...

Re: combine multiple excel file in to one excel file and multiple worksheet
I am wanting to use the following code to combine worksheets from multiple files. However I would like to be able to select folder which contains files in a more automated way that having to change the code every time, and also copy all worksheets with links and formulas removed. Any help on this is greatly appreciated as I have limited code knowledge. Option Explicit > Sub Copy_them() > > Dim TargetWkbk As Workbook > Dim mrgWkbk As Workbook > > Dim i As Long > Dim Wks As Worksheet > Dim fName As String > > Application.ScreenUpdating = F...

INDIRECT not working as expected
Hi all I have some dynamic ranges that are subsets of a master dynamic ranges. The criteria for the start/end of each subset range are in A1:A4 dynMaster = OFFSET($A$40, 0, 0, NumRows) dynSub1 = INDEX(dynMaster, MATCH($A$1, dynMaster, 0)):INDEX(dynMaster, MATCH($A$2, dynMaster, 0)-1) dynSub2 = INDEX(dynMaster, MATCH($A$2, dynMaster, 0)):INDEX(dynMaster, MATCH($A$3, dynMaster, 0)-1) etc These work fine. But now I want to be able to use a formula that refers to the different subset data sources according to which column the formula is used in. DataSource = INDIRECT("d...

summing data in multiple columns
I'm looking for a formula to lookup criteria in column A and add the results in columns B, C, D, E, etc. For example I would like to know the sum of how many "CATS" have been sold in a given year and I will be adding addtional months as time progresses and do not want to have to adjust my range or formula each time a new months worth of data is added. For example. column A column B column C column D column E DOGS 100 200 150 50 CATS 200 300 100 0 FISH 50 1...

Multiple owners
Is it possible to assign a account to owners An account must be owned by one person however that user or another user with the appropriate rights can share the account with other users or teams. In this way you can give others the permissions to read or update the account. "Alper Can" <alperc@datasistem.com.tr> wrote in message news:ONKBddjIEHA.2928@TK2MSFTNGP10.phx.gbl... > Is it possible to assign a account to owners > > ...

Autopopulate? is it possible
Tough to explain, but here goes. On Worksheet 1 I have columns with date, customer name, make, model.. and a column with a dropdown menu of different steps in the sale process. (looking, test drive, sold, dead...) I would like for any row that has column J selected as "Sold" t automatically copy columns B, D, I & L to Worksheet 2 corresponding t B ---> A, D-->B, I-->D, and L -->F So Worksheet 1 B = date, D= customer, I=salesman, L=manager But on Worksheet 2 A=date, B=customer, C=salesman and D=manager Worksheet 1 is my customer and deal input sheet, and on worksh...

Open File Dialog to fileter multiple file types at once...?
Hello, Here is another quick one for you all. The solution is probably simple, but I cannot find the answer anywhere. I have the following code: CFileDialog OpenDlg(TRUE, NULL, NULL, OFN_ALLOWMULTISELECT | OFN_HIDEREADONLY, ""); // Later must set this to retrieve a value from the registry OpenDlg.m_ofn.lpstrInitialDir = "c:\\"; OpenDlg.m_ofn.lpstrFilter = "txt Files\0*.txt\0Doc Files\0*.doc\0"; OpenDlg.DoModal(); I am trying to get the box to filter out everything but the *.doc and the *.txt files ... I have seen some boxes that have something that...

Multiple Scheduling
I have a pocket pc with outlook that I use to follow my school schedule. The only problem is there are 4 different types of schedule each in the same order but the class lengths are different. In addition, another problem is that there is no order to when the occur. Is there a way where I can toggle between two or more different recurring schedule? Thank you for your help Jonathan ...

multiple digits in a combobox
hello, i have an mfc CCombobox, which is populated with numeric data. For example: 2 3 4 5 6 8 10 14 18 i would like to user to be able to type 14 and have 14 selected. Currently when the user types 1 the box selects 10 and then when they type the 4, the 4 is selected. I currently have the object type set to Drop List. If I change to Drop Down, then 14 can be selected as described, but I dont want the user to be able to enter any numbers that arent in the list. For example, if they were to try to type 13, i want it to stay on 10. any ideas? are there any custom comboboxes that already...

Cell extraction from Multiple worksheets
Hi group, I am new to this newsgroup and am hoping that you can help me out. I took a software/hardware inventory of all the computers in my work's network and imported them all into excel, in their own worksheet. So I have one file, with 128 different worksheets, all containing similar information. What I would like to do is create a new sheet, a summary page, where I can pull the value of the same cell from each sheet and list them. I need to have a list of each computer, with computer name, cpu speed, video card, serial number, ect. If the values that I am searching for are in the same...

Didtheyreadit spyware
At work we're running Outlook 2000 SR-1. I've done some reasearch on the didtheyreadit spyware and how to turn that capability off. I don't have access to the registry and don't really want to do anything too drastic. At this point I'd settle for just being able to tell if a message I've received contains the embedded invisible .bmp file (or whatever it is) that links back to the didtheyreadit servers. Is there a way to tell that either before or after the message is read? Thanks There is a free program called NoSpyMail that does this. It is available at http://...

How to have multiple mailbox in an outlook client
I am wondering how to configure multiple mailboxes in an outlook 2003 client, so I can monitor the others' email use in the office. Thanks! Assign yourself full mailbox access permissions to other mailbox and open it as additional mailbox in Outlook by going to More Settings (in Outlook properties) | Advanced | Open these additional mailboxes: -> | Add. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Rick" <Rick@discussions.microsoft.com> wrote in message news:C4CDF75C-5C71-4BCB-A068-C852EC4F06DF@...

Replying with Multiple Accounts
Hi, Recently migrated a user from Outlook Express to Outlook XP. There is lots of pop accounts in his setup. When he received an email in OE, replying would go from the account it was sent from, and there was a drop down box that would allow you to choose an account to send mail from... What does Outlook offer for multiple accounts as he needs this feature... Thanks Marek It offers the same. You can use the Accounts dropdown to choose who you send from. Chris "Marek" <nospam@spam.spam> wrote in message news:uYZGaVaLFHA.2748@TK2MSFTNGP09.phx.gbl... > Hi, > > Re...

Pivot Table
Is there any way to suppress the display of blank cells within the data area of a pivot table? Debra Dalgleish has posted code to do this: http://groups.google.co.uk/group/microsoft.public.excel.misc/browse_frm/thread/3fbd903e92adc270/2cb8900b96680905?lnk=st&q=hiding+0+pivottable+group:*excel*+author:dalgleish&rnum=4&hl=en#2cb8900b96680905 or http://tinyurl.com/axk75 Kirk P. wrote: > > Is there any way to suppress the display of blank cells within the data area > of a pivot table? -- Dave Peterson ...

How use name refs for Source Data?
In Source Data > Series > Y Values, I want to use name references (e.g. 'Sheet 1'!Foo:Bar or simply Foo:Bar). Excel 2003 does not seem to permit that. I also tried using a named range (e.g. 'Sheet 1'!FooBar or simply FooBar), to no avail. Is there some way that I can avoid absolute references in the chart Source Data? Hi, You can use named ranges to define chart data. This example uses a workbook and worksheet level names. =SERIES(,Sheet1!ChtLabels,Book1.xls!ChtData,1) Here is a collection of dynamic charting examples, http://peltiertech.com/Excel/Charts/Dynamics....

Multiple Companies Simultaneous Updates
Is it possible to update multiple companies simultaneously on GP? For example I want to change an account on the CoA on several companies. Can I do this without having to login to each individual company? We are using GP v8. Thanks, Nawaz Nawaz: Email mbsprofessionalservices@microsoft.com - I believe they have some triggers for sale that allow you to do this with accounts, vendors, and customers. I am not sure of the price - but I believe those triggers will do what you want them too and update multiple databases. Shoot them an email as I am sure they can explain in better detail an...

Customers receiving multiple workflow emails
We have a workflow in place that automatically emails customers upon order creation. Customers are reporting multiple emails for the same order. I confirmed this through Exchange message tracking. This doesn't occur with all order confirmations...but it does occur with more than one customer and more than one domain. Has anyone else seen anything like this? How can I troubleshoot the Exchange router? This workflow has been in place for over a year and has just started doing this. Any ideas will be appreciated. Thank you. use the workflow monitor to try and track the instance...

Using Excel to analyze HTML
I can't get Excel 2000 or Word 2000 to let me paste HTML code without displaying the code as a webpage. Is there a setting I can turn off somewhere to get Excel and Word to be less presumptuous and let me work with HTML without displaying it, in weak attempts at being WYSIWYG HTML editors? Gregg Unfortunately HTML became a 'native' format in XL2000, so no is the simple answer -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "GreggRoberts" <GreggRoberts@discussions.microsoft.com> wrote in message news:A9...

Command button to replace current record's field
I have a continuous form with a command button in detailed band. On click event, I want to replace 'checkin' field of current record with =now(). Click the command button the 2nd time, replace same field with blank. (toggle). Thanks. To assign the current time to the checkin field, use this code: Me.checkin = Now() (or use the SetValue action in a macro if you prefer.) It would be easier to use another button to blank the field. Code: Me.checkin = Null Otherwise you'll have to keep track of when the button was clicked first (module level variable in the f...

Multiple recipents, multiple emails
We run Exchange 2003 in our organisation and are having problems with mail is sent to more than one recipient from external sources. If mail is sent to 4 users, each user receives 4 copies of the message, 3 goes to 3, 2 to 2 etc. Has anyone else encountered this problem and is there a fix? BigMan <BigMan@discussions.microsoft.com> wrote: >We run Exchange 2003 in our organisation and are having problems with mail is >sent to more than one recipient from external sources. If mail is sent to 4 >users, each user receives 4 copies of the message, 3 goes to 3, 2 to 2 etc. >Ha...

how can I save a multiple page file as multiple JPEG's?
I created a 26 page doc in Pub2003, and want to save each page as a separate JPEG to upload to a site to create a photo book- They do not work with PDF's. How can I save each page separately? I know I can save the entire thing as a JPEG, and but I don't know how to save a page alone. Each page is many-layered and I can't seem to group all the multiple objects... how can you group a large number of objects on a page? I think if I could do that I could right-click and 'save as'. Thanks! -Joanna ...

close multiple Excel workbooks simultaneously
I have a situation where I finish up with several separate workbooks open simultaneously. At the moment, I close them all one by one, manually. Is there a quicker way? Thanks, folks Hi Paul, With the Shift key depressed, select Close All on the File Menu. This option is only available in conjunction with the shift key. --- Regards, Norman "PaulRetired" <PaulRetired@discussions.microsoft.com> wrote in message news:823C11FA-4FB3-47AD-AAD5-5EEA951490AA@microsoft.com... >I have a situation where I finish up with several separate workbooks open > simultaneously. At ...

Conditional Formating Same Color as Form Background
Hi, I got the conditional formatting on a continuous form to work. My only problem is getting the color of the text box (the one under the conditional format) to change to match the 'default' color of the background. From my computer, it appears that the 'default' color is a shade of gray, but I can not find the exact match. Is there a way to match the color of the text box (from current white) to the default of the form? Any pointers are very much appreciated. Thank you. With conditional formatting, you are limited to the colors available in the drop-down color-pick...

EXCEL, VSTO: Fastest way to access multiple cells
Hi all, Can anybody help clarifying my issues? In one part of my Excel/VSTO application, I have to compare values of cells and assign a background color according to the result of the comparison. The code looks something like this: .... rng = wkSheet.Range("A1:Z1000") vals = Array(1000 * 26 ) ' same size as 'rng' Do ( for the whole range ) If vals(i, j) > XXX Then rng(i, j).Interior.ColorIndex = 1 Else If vals(i, j) = XXX Then rng(i, j).Interior.ColorIndex = 2 Else ' vals(i, j) < XXX Then rng(i, j).Interior.ColorIndex = 3 End ...

Best way to persist alter table changes in my local DB to web host
Hello. Using SQL Server 2008. I'm developing on my local PC with a copy of the DB that is contained on a web host. Is there a good way when I do an alter table on the local DB to export that change to the web host DB and have "semi-automatically" apply the change to its copy of the table? dontspammenow@yahoo.com (dontspammenow@yahoo.com) writes: > Using SQL Server 2008. I'm developing on my local PC with a copy of > the DB that is contained on a web host. Is there a good way when I do > an alter table on the local DB to export that change to the w...