Using MS Access/Duplicating values

Advertisement


Question
QUESTION: Hi Scott,

I have a requirement to fill out the same value in one column for all the records in a table. Say a table has 3 columns and this table is a control source for a form. When data is entered in the form it creates 100 records. So 2 columns are filled with unique 100 values but the 3rd column is only filled out with only one record. Please do not ask why and how it can be. It comes from crosstab that has to be normalized and so on. I do not want to waste your time on explanations. The point is I have to fill out other 99 values in the column 3 with the same value as the record one has.

Example:
This is what I have
Column1     Column2      Column3
Value1A     Value2A      Value3A
Value1B     Value2B       
Value1C     Value2C       
...          ...

This is what I need:
Column1     Column2      Column3
Value1A     Value2A      Value3A
Value1B     Value2B      Value3A  
Value1C     Value2C      Value3A     
...          ...          ...

Thank you.

ANSWER: It would help if you explain. I might be able to provide a better alternative is I understand the situation. With the little info you provide the only thing I can suggest is to set the default value in the After Update event.

So you enter Value3A . The After Update event for that control has the following line of code:

Me.Controlname.DefaultValue = Me.ControlName.Value

From then on, each new record would automatically be filled in with Value3A when you enter data in the other two controls.



---------- FOLLOW-UP ----------

QUESTION: Sorry but this does not work. This is what it does:

 Column1     Column2      Column3
 Value1A     Value2A      Value3A
 Value1B     Value2B       
 Value1C     Value2C       
 Value1D     Value2D        
*          Value3A

Answer
What I suggested will not work on existing records. It was my understanding that you were entering all new records. And the result shows that any new records you enter will default to Value3A for the third column.

If you need to back fill existing records, then you need to use an UPDATE query to update the existinf records.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.