Auto Populate Fields

[todAnthony]

I need a dropdown choice to auto populate other fields. The choices in the dropdown come of course from another table.

-Main table is 'SurveyData'.
-The field 'surveyStatus' is the lookup column pulling from the table 'SurveyStatusChoices'.
-In that table the fields 'totalSurveyed', 'totalOccupied' and 'totalVacant' hold the "answers" i need populated according to the choice picked on the 'SurveyData' table.
-The report has the 3 fields (Total Surveyed, Occupied, and Vacant) ready to receive the "answer" from the pick off the lookup column.

I have tried MANY solutions I have found online but I got nothing. Closest thing i have come up with is to add the fields from the 'SurveyStatusChoices' table to the form. They DO auto populate and change with the dropdown but I need those choices to add to the record in the 'SurveyData' table.

Am I barking up the right tree? How do I make this work? Place to send copy of my project for review?

I have not worked with Access for many years and nothing as advanced as what I am trying to accomplish. Help me Obi-Won... you're my only hope.

 

[Beetle]

They DO auto populate and change with the dropdown but I need those choices to add to the record in the 'SurveyData' table.

Why? It's rarely a good idea to redundantly store the same data in more than one table. Just *display* the related data in a calculated control on a form or report (as it seems you're already doing).

 

[todAnthony]

I am not storing redundantly. In the 'SurveyStatusChoices' table the fields are: Choice, Surveyed, Occupied and Vacant. There are 5 'choices" displaying in the drop down of the form pulled from this field. In each of these choices there is a different combination of 1 or 0 in the three following fields. I call these the 'answers' to what is chosen.

Example:

Choice Surveyed Occupied Vacant
Choice A 1 1 0
Choice B 0 1 1
Choice C 1 0 1

No further data will be stored here

In the 'SurveyData' table I want these 1s and 0s to populate 3 fields according to which choice is picked in the drop down.

Now that being said, what I have found that looks like it should work is putting this VBA code in the After Update of the combo box:

Me.totalSurveyed = Me.SurveyStatus.Column(2)
Me.totalOccupied = Me.SurveyStatus.Column(3)
Me.totalVacant = Me.SurveyStatus.Column(4)

This should populate the fields in the 'SurveyData' table with 1s and 0s defined by the Choices table.

Unless I have it completely ass backwards because right now I put the code in and I got NOTHING.

Now you may be looking at this from the outside saying I am way off base and should do something else. Suggestions and tips will be much appreciated.

 

[Beetle]

The example code you posted should work. You're running this in the After Update event of a combo box on a form, and the Record Source of the form is the SurveyData table, is that correct?

 

[todAnthony]

I think I may have just learned a quirk of Access. Last thing I did yesterday was to enter that VBA without it working. It was populating the text box in the form but NOT adding the info to the field on the 'SurveyData' table.

Today, all is right with the world. I was racking my brain on why that code wasn't working but I am told by associates that they have seen where code would sometimes not work until after closing and restarting Access. Now my fields work properly.

Thank you Beetle for the assistance.