Code Illiterate seeks help with automating a form (1 Viewer)

[MeisterGirl]

This is probably so easy for someone else but not me.. I have a form in which I need 50 fields to populate automatically after I enter a value in a specific field. Each record will contain different values so I can't use the "default" setting because that carries through to other records. Although I want each field to automatically populate I need to be able to go into individual ones occasionally and change just that one field without changing the value in other the other 49 fields. Is this going to require writing code behind the form? If so what is the command line I need to use? Any help is appreciated. Thanks!

 

[Rich]

Fifty fields? sounds rather suspect, perhaps you could supply some more info regarding youe table structure etc.

 

[MeisterGirl]

Here's more detail on the Form:
I have one field where I enter a rate. Then I have 50 fields - one for each US state. Our Data Warehouse requires each state to have it's own field for querying purposes. 9 times out of 10 the rate is the same across the board - occasionally one or two states will have a different rate. I want to remove as much room for error and increase efficiency by not having the data entry team entering the rates manually in all 51 fields. I want the fields to automatically populate but allow for an override on an indicidual field by field basis. DOes this help......

 

[Jack Cowley]

A number of ways to do this. Here is one -
Create a query and drag all the 50 fields to the QBE Grid. Drag your Primary field as well. Select the Query Type Icon and select Update Query. A row will be added to the QBE grid called Update To:. Select a State that is going to be the one that the others will be based on. Lets select Texas... Assuming your form is Named Form1 and your field name for Texas is TX then put this code in the Update To: field
[Forms]![Form1]![TX]

In the Primary key column put this in the Criteria Field
[Forms]![Form1]![FieldNameOfPrimaryFieldOnForm]

Save the query and give it a name.

Go to your form and in the After Update event of the TX field select [Event Procedure] and the put code like this in the subroutine:

Me.Refresh
DoCmd.SetWarnings False
DoCmd.OpenQuery "YourUpdateQueryName"
DoCmd.SetWarnings True


I hope this will work for you...



[This message has been edited by Jack Cowley (edited 08-08-2001).]

 

[MeisterGirl]

The code worked and I am able to start entering data now! Thank you so much!!