Solved: Text Field Binding

[Imdabaum]

Okay here is a fun one. Just got a request to fix a guys Access Application. He has several text fields that are unbound, because each field has information from the same field just related to a input into the AssignedBldg1 text field. I can update the rest of the fields piece by piece, by doing this:

Private Sub AssignedBldg1_AfterUpdate()
On Error Resume Next
Dim test As String
If Nz(AssignedBldg1) = "" Then
GoTo fieldcleared
Else
test = DLookup("[BuildingID]", "Building", "[BuildingID] = AssignedBldg1")
If AssignedBldg1 = test Then
Me.AsBldgName1 = DLookup("[Title]", "Building", "[BuildingID] = AssignedBldg1")
Me.O6unit1 = DLookup("[06 Units Reserved]", "Building", "[BuildingID] = AssignedBldg1")
Me.O7units1 = DLookup("[07 Units Reserved]", "Building", "[BuildingID] = AssignedBldg1")
Me.O8units1 = DLookup("[08 Units Reserved]", "Building", "[BuildingID] = AssignedBldg1")
Me.BldgCap1 = DLookup("[BuildingCapacity]", "Building", "[BuildingID] = AssignedBldg1")
Me.bldgutil1 = DLookup("[NumWardsAssigned]", "Building", "[BuildingID] = AssignedBldg1")

Else
Me.AsBldgName1 = "" 'These are all textboxes.
Me.O6vacant1 = 0
Me.O6unit1 = 0
Me.O7vacant1 = 0
Me.O7units1 = 0
Me.O8vacant1 = 0
Me.O8units1 = 0
Me.BldgCap1 = 0
Me.bldgutil1 = 0
GoTo Err_PROPERTY_NBR
End If
End If
Exit_AssignedBldg1_AfterUpdate:
Exit Sub
fieldcleared:
Me.AsBldgName1 = "" 'Text fields are cleared if the box is deleted
Me.O6vacant1 = 0
Me.O6unit1 = 0
Me.O7vacant1 = 0
Me.O7units1 = 0
Me.O8vacant1 = 0
Me.O8units1 = 0
Me.BldgCap1 = 0
Me.bldgutil1 = 0
Exit Sub
Err_PROPERTY_NBR:
MsgBox AssignedBldg1 & " was not found.", vbCritical, "Error: Property Number is invalid"
AssignedBldg1 = ""
Me.AssignedBldg1.SetFocus
End Sub

The problem is... he wants 9 more text fields that do the same thing all fields storing information from the same table field just related to different IDs. Is there an easier way to do this than repeating this code 10 times? Can you bind text fields to table fields defining criteria?

[Norie]

I think the real problem here is the data structure.

You should not have 9/10 fields containing the same type of data.

Just looking at the field/control names in that code is giving me nightmares.:)

[Imdabaum]

So do you have any suggestions? I'm not sure how to go about fixing it for him because I myself wouldn't have done that either.

The idea is there are buildings with max occupancy. Then the managers make projections for the upcoming year for how many vacancies are going to be filled through the next 3 years. There is a Region where the buildings are assigned. He wants to use this database to help him determine where he will build new buildings and where he needs to buy them. The AsBuildingName1 is a text field that would show the names of the buildings in the region. His plan was to make Name2-Name9, but it is a headache, I mean it's cut and paste for the coding, but there is usually a better way around it if you can cut and paste code.

[GaryB]

If I am understanding this correctly, you have several fields that need to be filled by the same field from a table. example: fields 1-7 all fill from field 8. If that is the case would it be easier to go into desigin mode and and change the control source of the fields 1-7 to field 8? This would automatically draw from the same field and you would only have to set the code once.

Gary

[Imdabaum]

http://www.geocities.com/imdabaum2002/bldAnalysis.JPG
Yes, I tried binding them that way but there is a text box to the side of each text box that allows input. So the textboxes 1-10 are bound to the table field, but need to be filled with the data that is related to the text field on the side of those fields if that makes sense. Here is what he gave me. If I bind them the way you mentioned then each time the textboxes 1-10 are updated then all greyed out fields are updated. I'm trying to figure out a better way to do it. Possibly through listboxes or something like that.

[GaryB]

Are the building usage boxes a total of the text boxes to the right of them in each row? ie... 2006 units 2006 vacancy etc?

Gary

[Imdabaum]

Are the building usage boxes a total of the text boxes to the right of them in each row? ie... 2006 units 2006 vacancy etc?

Gary

The Current Vacancy is the difference between the Capacity and the Utilization. Each of the other fields are simply information stored in the table for Projected usage in the next few years.

The bottom-most text boxes hold the sum of the corresponding columns. These text boxes have been populated with the Get Summary button.:help

[GaryB]

ok, I get the summary of each column but, what does the data each line of the building usage represent. Is it the sum for each different building entered on each line and the bottom of the each column gives you a summary all the buildings listed?

Gary

[Imdabaum]

Yes sir. That's the way he wants it to work.

[GaryB]

I have similar set up in a voc tracking database I wrote. What I did was in the control source of each text box I built a formula. Since I don't know what each box is named I'll give you and example of what I am talking about.
In the Bulding usage text box 1. =[name] - [name]. This is simplified but it gives you the idea of what I'm trying to say. I hope! If you have to isolate parts of the forumula you can us () so =([name]+[name])-([name]*2) etc... You would have to do this in all 10 boxes, but it would give you the yield for each row with out having to update. It would calculate automatically. Hope it helps.

Gary

[Imdabaum]

Thank you GaryB. That will do it. That will save a lot of unnecessary coding.