Autopopulate Multiple Fields in Table Using a Form

[Guest]

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?

 

[Jeff Boyce]

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

 

[John W. Vinson]

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]

 

[Guest]

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.

 

[Jeff Boyce]

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