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. 2 followers. Follow

4 Replies
2015 Views

Similar Articles

[PageSpeed] 35

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:

the criteria pane and table panes have disappered.
In my excel file I can't see the criteria and table panes and the functions will not reappear. ...

calculated field 12-30-09
i have a query with the following fields: year, month, customer, debit euro, credit euro and balance (debit-credit). the query returns multible entries for each customer. e.g. customer X in November 2009 has a balance of 500 euro and December 2009 has a balance of -100 euro. i want to build a field that returns 500 euro for November and 400 euro for December. can i achieve that in a query? i also want to create another field that returns a date. in the above mentioned example: if year is 2009 and month is 11 i want to add thrre months, that is the filed should return the d...

field name or schema name?
When i adjust excel column-names to match CRM 3.0 field names, preparing for bulk-import, should i use the (label) field names in CRM 3.0, or the schema-names for example, if i made a new country field, use Country 2 or New_Country2 thanks Kas use the schema-names -- -Nick "_Kas_" wrote: > When i adjust excel column-names to match CRM 3.0 field names, preparing for > bulk-import, should i use the (label) field names in CRM 3.0, or the > schema-names > > for example, if i made a new country field, use > > Country 2 > > or > > N...

Data Entry Form 03-20-08
Hi, I created a form to use for viewing currently records and entering new data. This time, the form doesn't allow me to enter new data. In fact, all text boxes didn't let me to enter new information. Please help Thanks Chi Several things to check: In form properties: Allow Additions = Yes Allow Edits = Yes If the form's record source is a query, be sure the query is updatable. -- Dave Hargis, Microsoft Access MVP "Chi" wrote: > Hi, > > I created a form to use for viewing currently records and entering new data. > > This time, the ...

Using Windows fax
I have used the Windows fax console for receiving faxes for a while. I had Photoshop installed when I started using the fax system in Windows. I could VIEW and it would open Photoshop automatically. I deleted Photoshop and now have Photoshop Essentials 8. I now cannot VIEW anything because I don't have software to view .tif . I know that Essentials opens it, but I can't find anywhere in the fax console to tell it to go to Essentials. Please help. Chip wrote: > I have used the Windows fax console for receiving faxes for a while. I had > Photoshop ...

Need help with Excel Form & ComboBox Tutorial
At http://www.excel-vba.com/v-forms-controls.htm I have followed instructions... my code on the form is below but it won't run... I've marked the error... Can anybody give me any help with this? thanks Code is below-------------- Private Sub cmdBtnSubmit_Click() shReport.Range("C4").Value = cbxCity.Value cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub cmdCityCancel_Click() cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub UserForm_Activate() shParameterst.Activate '<-----Run Time Error 424 - Object Required...

Merging tables
Hello, I do not have deep knowledge of Excel. That is why my proble might appear a bit strange: I have two tables, list1 and list2. In both tables there is a colum with unique ID numbers. The rest of the columns contains differen information in both tables. Example: list1 ----- ID name email 101 bob bob@none.com 102 rita rita@none.com ... 999 jim jim@none.com list2 ----- ID salary 102 2000 103 2500 ... 204 2100 What I would like to get as a result is a combined table, with selecte columns from both tables in it. I.e.: list3 ----- ID name salary 101 bob - 102 rita 2500 ... Anyone who can ...

Distribute an add-in using a URL
Hi! I'm trying to get an .xla file to load up in the Add-ins from a URL. I receive the following message when I try to add the URL link.."You cannot use an Internet address here. Enter a path that points to a location on your computer or on the network." ...

Removing attachments from multiple emails
Hi: I searched to get all emails with attachments that are from last year or older. Now I want to select them all and remove the attachments from all of them, but keep the messages. I can do this one email at a time by opening it up, right clicking and choosing "remove", but how do I do this for several emails at once? Is there something in Outlook that will allow me to do this or is there a third party tool? Thanks, Kayda There are many third-party tools to add this functionality to Outlook. = See http://www.slipstick.com/addins/housekeeping.htm=20 --=20 Sue Mosher, Outlook MVP...

I have the template i want to use, however I am not sure how to us
an e-mail was sent to me with a spreadsheet I'm to complete. A co -worker helped me to create a template of that spreadsheet that I need to fill in but I don't have experience using templates. How do I get started, and how do I create a next page or sheet? I need to fill it in and than start the next page with the blank template...help Have you looked at the Excel help for templates? If so, at which stage did you have a problem? -- David Biddulph "mouse" <mouse@discussions.microsoft.com> wrote in message news:C9209715-1DD7-4771-B23F-03BB8F916A10@microsoft.com...

Add Text Field Columns
PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) I have two fields both of which are text. When I try to add them as above, I get a concatenated result, not the sum. How does one add them? -- On Fri, 18 May 2007 17:38:49 -0600, bw wrote: > PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) > > I have two fields both of which are text. When I try to add them as above, I get a > concatenated result, not the sum. How does one add them? How does one add "This is text." + "this is also text&qu...

Smartlist current user id field
Hi all, Is there any table I can use that will indicate the user id of the user running the particular SmartList ? For one example, I want to build a report that only shows Orders that the current user who is running the report created. That way each person who wishes to run this report does not have to enter in the criteria their own ID each time the run it... Does that make sense ? Thanks, Chris B. If you are referring to the sales order out of the box, then this isn't possible. If this report comes from a SQL View that you've written then potentially you could. However t...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

diable command button based on field value
I have a command button which I would like to disable based on the following rules: If A = 1 then button is disabled If B = 2 then button is enabled Where you put the code depends on how the form works. If this needs to be checked for each record, use the form current event: If A = 1 Then Me.Button.Enabled = False ElseIf B = 2 Then Me.Button.Enabled = True End If But, what if A is not 1 and B is not 2? -- Dave Hargis, Microsoft Access MVP "barrynichols@gmail.com" wrote: > I have a command button which I would like to disable based on the >...

Forward multiple emails
I am trying to pull together several emails from multiple sources and forward them out with a new email cover letter. I am trying to avoid forwarding each email separately. I want the emails to arrive together with the new email giving a summary of each. Select all the messages and press Forward. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Hendrix" <anonymous@discussions.microsoft.com> wrote in message news:3bbf01c48f9d$4aabd870$a301280a...

Pivot Table Refresh Problems
Hi All: Hoping that someone can help. I am trying to refresh a pivot table using the following code which was inserted on the Daily Production Output Sheet(both sheets in the same workbook). I am using Excel 2003 Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Daily Production Output").PivotTables("PivotTable3").RefreshTable End Sub The Calculation has been set to Automatic. However when I try to run the above code I am getting the following error" Run Time error 1044, Application defined or Object defin...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

Creating a new document from a multiple paged Excel document
Can I save only one page of an existing Excel multiple page document? If so, how? Thanks to anyone that can take the time to answer/instruct. Right-click the sheet tab that you want, and hit Move or Copy. Choose "Create a copy" and then, from the dropdown, choose "new book". **** Hope it helps! **** ~Dreamboat Excel VBA Certification Coming Soon! www.VBAExpress.com/training/ ******************************** "Lisa" <Lisa@discussions.microsoft.com> wrote in message news:CBBDECC6-2432-41D4-99F2-C1EF10B1EC1F@microsoft.com... > Can I save only one page of...

how do i create a quiz using excel?
hey guys...i badly need to make a quiz using excel,,,i got no clue how to do it. the quiz will be a simple one, no drop downs and should display the scores after the quiz is taken.... please help! Andruu, What type of quiz are you looking to create? What format are the answers? Numbers? Text? One suggestion..... 1. Add questions *What is 2 x 2?* 2. Assign cells for the user to insert their answers. (format cells as text/numbers etc) *Cell: B2* 3. Either in hidden cells, or on a seperate sheet, put the correct answers 4. Assign a point value for each correct answer (can be different for ...

Trouble using resolution for Outlook error
I am recieving the error in Outlook mentioned on the following page: http://support.microsoft.com/default.aspx?scid=kb;en-us;822503 The instructions say to resolve the issue for an existing installation, specify the *.pst file by using the Mail icon in Control Panel. First of all, I do not know what *.pst file they are referring to. The following directory on my machine: C:\Documents and Settings\Nathan Sokalski\Local Settings\Application Data\Microsoft\Outlook Contains the following *.pst files: archive.pst njsokalski@hotmail.comHotmail-00000002.pst Outlook1.pst Outlook.pst I am ver...

pivot tables #19
I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? -- tiki Hi tikitai, Look at my Excel Database Tutorial at http://edferrero.m6.net/DataTutor1.html You should be able to work it out form there. Ed Ferrero > I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? &...

expand and Collapse form
I am trying to make a form that will serve as a sort of navigation pane. I want the form to expand and collapse when I click on a subject this will display other forms I am wanting in that catagory. any idea on how to do this? Not sure exactly what you have in mind, but have you thought of hiding and showing the form. That is easy to do. There are some switchboard examples around if you search. One way is to use a form with a listbox down the left side. Clicking on an item in the listbox, opens a form inside the subform on the right side of the main form. Your code just change...

Multiple e-mail account in MS Outlook 2003
I have 4 e-mail accounts set up (all from the same server) and can send from all accounts but I only receive mail for the default account. Any suggestions? Does each account use the same account properties? (e.g. same mailbox name, userid, and password.) "Crazy Squaw" <Crazy Squaw@discussions.microsoft.com> wrote in message news:78F4A600-8A92-4011-8E4C-6094A77DEF4A@microsoft.com... >I have 4 e-mail accounts set up (all from the same server) and can send >from > all accounts but I only receive mail for the default account. Any > suggestions? ...

Modify Access 97 tables in Access 2003
How do I modify an Access 97 table using Access 2003 without converting the database? Is there any tool available? Rick This is only one person's experience... There is only one tool I'm familiar with that would let you do that, and it's called ... Access '97<g>! You've described HOW you want to do something. Now, if you'll describe a bit more about WHY you need this done, the folks here in the newsgroup may be able to offer more specific suggestions. Regards Jeff Boyce Microsoft Office/Access MVP "Rick" <Rick@discussions.microsoft.com...

Change Default When Selecting New Message From Form
Whenever I select New -> Choose Form, it defaults to "Organizational Forms Library" (which is empty). How can I make it default to "User Templates in File System"? Thanks. I've been looking for a way to do that for years, but have never found = one. FYI, there is a newsgroup specifically for Outlook forms issues = "down the hall" at microsoft.public.outlook.program_forms or, via web = interface, at = http://www.microsoft.com/office/community/en-us/default.mspx?dg=3Dmicroso= ft.public.outlook.program_forms --=20 Sue Mosher, Outlook MVP Author of Con...