http://img26.imageshack.us/img26/3049/image0010620090309scree.jpg Is it possible (and how) to have a txt box autofill with information based on the selection from 2 drop down boxes?

do a little research and I'm pretty sure this wont work, but I'm wondering if I'm on the right track...

Private Sub TonsAvailable_AfterUpdate()
Dim stFuelType As String
Dim stFuelLoad As String
If stFuelType = "1" And stFuelLoad = "low" Then
Me!TonsAvailable = "3.0"
If stFuelType = "2" And stFuelLoad = "medium" Then
Me!TonsAvailable = "4.0"

End Sub

okay I found that I'm supposed to end the "if" statements, but still not working. I also changed the "fueltype" to an integer. I'm thinking that I'm missing something after the DIM statements.

Private Sub TonsAvailable_AfterUpdate()

Dim intFuelType As Integer
Dim stFuelLoad As String

If intFuelType = "1" And stFuelLoad = "low" Then
Me!TonsAvailable = "3.0"
End If

If intFuelType = "1" And stFuelLoad = "medium" Then
Me!TonsAvailable = "4.0"
End If

If intFuelType = "1" And stFuelLoad = "heavy" Then
Me!TonsAvailable = "4.4"
End If

End Sub

Private Sub (FuelTypeComboName)_AfterUpdate()

If (fueltypecomboname).value = "1" And (fuelloadcomboname).value = "low" Then
Me!TonsAvailable.value = "3.0"
Else
If (fueltypecomboname).value = "1" And (fuelloadcomboname).value = "medium" Then
Me!TonsAvailable.value = "4.0"
Else
If (fueltypecomboname).value = "1" And (fuelloadcomboname).value = "heavy" Then
Me!TonsAvailable.value = "4.4"
End If
End if
End if


I would try it like this. One problem you may have is that the combo for fuel load is probably not storing text. I would expect it to store the ID. I would put the same code in the after update event of (fuelloadcomboname)

Edit: Another problem is what if none of the three conditions in the code are met? Or Is the above only to get you started?

yes, this is just to get started, the "ftype" values will go from 1 through nine and the "fload" values are (low, med and heavy: 1 for each of the ftype values) so there is a total possible values of 27 values.
the tables that holds the "ftype" and "fload" values, I've taken out all the ID columns so that just the value columns remain.

I checked to make sure that the dropdowns are named correctly "ftype" and "fload" respectivly:


If ftype.Value = "1" And fload.Value = "low" Then
Me!TonsAvailable.Value = "3.0"
Else
If ftype.Value = "1" And fload.Value = "medium" Then
Me!TonsAvailable.Value = "4.0"
Else
If ftype.Value = "1" And fload.Value = "heavy" Then
Me!TonsAvailable.Value = "4.4"
End If
End If
End If


When I select values with the drops I'm still not seeing anything happen with the TonsAvailable field. Should I try putting this in the beforeupdate or somewhere else?

You might also try this:

If ftype.Value = 1 And fload.Value = "low" Then

but with that many options, I would have a table with 3 fields: type, load and the corresponding value. Then you can get the value very simply, and it will be more dynamic.

Here is what I came up with so far:


Private Sub TonsAvailable_AfterUpdate()
Dim intFuelType As Integer
Dim stFuelLoad As String
' Cached Values
intFuelType = cboFuelType.Value
stFuelLoad = cboFuelLoad.Value
' The bread and butter
If (intFuelType = "1") Then
If (stFuelLoad = "low") Then TonsAvailable = "3.0"
If (stFuelLoad = "medium") Then TonsAvailable = "4.0"
If (stFuelLoad = "heavy") Then TonsAvailable = "4.4"
If (intFuelType = "2") Then
If (stFuelLoad = "low") Then TonsAvailable = "2.6"
If (stFuelLoad = "medium") Then TonsAvailable = "3.8"
If (stFuelLoad = "heavy") Then TonsAvailable = "5.1"
If (intFuelType = "3") Then
If (stFuelLoad = "low") Then TonsAvailable = "6.4"
If (stFuelLoad = "medium") Then TonsAvailable = "6.8"
If (stFuelLoad = "heavy") Then TonsAvailable = "7.9"
If (intFuelType = "4") Then
If (stFuelLoad = "low") Then TonsAvailable = "4.4"
If (stFuelLoad = "medium") Then TonsAvailable = "7.6"
If (stFuelLoad = "heavy") Then TonsAvailable = "8.5"
If (intFuelType = "5") Then
If (stFuelLoad = "low") Then TonsAvailable = "0.8"
If (stFuelLoad = "medium") Then TonsAvailable = "1.5"
If (stFuelLoad = "heavy") Then TonsAvailable = "2.5"
If (intFuelType = "6") Then
If (stFuelLoad = "low") Then TonsAvailable = "2.0"
If (stFuelLoad = "medium") Then TonsAvailable = "3.0"
If (stFuelLoad = "heavy") Then TonsAvailable = "5.0"
If (intFuelType = "7") Then
If (stFuelLoad = "low") Then TonsAvailable = "4.0"
If (stFuelLoad = "medium") Then TonsAvailable = "6.0"
If (stFuelLoad = "heavy") Then TonsAvailable = "8.0"
If (intFuelType = "8") Then
If (stFuelLoad = "low") Then TonsAvailable = "5.0"
If (stFuelLoad = "medium") Then TonsAvailable = "7.5"
If (stFuelLoad = "heavy") Then TonsAvailable = "10.0"
If (intFuelType = "9") Then
If (stFuelLoad = "low") Then TonsAvailable = "1.5"
If (stFuelLoad = "medium") Then TonsAvailable = "3.8"
If (stFuelLoad = "heavy") Then TonsAvailable = 5.9
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub



The highlighted part of the code above (intFuelType = cboFuelType.Value) is where I get an error that says (run-time error '424': Object Required)

so I'm guessing that the DIM statement is not cacheing the intFuelType value for some reason. so in looking at that combo box, when I created it, I used the wizard and did NOT point it to a seperate table, I typed in the values manually, and used 2 columns, the first column shows the numbered values (1-9) and the second column describes the values. So I thought that if I changed it to the description instead of the numbered values...

if (intFuelType = "Shortleaf Pine with Oak") then
if (stFuelLoad = "low") then TonsAvailable = "3.0"
etc...

but that did not work either.

Private Sub TonsAvailable_AfterUpdate()



Maybe thats why. You want this code to run in the after update events of the 2 combo fields. You shouldnt need any code in the tonsavailable field.

I took the code out of the "tonsavailable" afterupdate, and put it in both the Fueltype_afterupdate and Fuelload_afterupdate.

I still get the same error message.

Is this on a form module or on a standard module? If on a standard module then Access is not going to know what cboFuelType is - you would need the fully qualified form reference - Forms!YourFormName.cboFuelType

Oh, and the form would need to be open (not in design view) at the time you run this code.

Private Sub cbofueltype_AfterUpdate()
If (cbofueltype.value = "1") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "3.0"
If (cboFuelLoad.Value = "medium") Then me.TonsAvailable.value = "4.0"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = "4.4"
If (cbofueltype.value = "2") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "2.6"
If (cboFuelLoad.Value= "medium") Then me.TonsAvailable.value = "3.8"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = "5.1"
If (cbofueltype.value = "3") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "6.4"
If (cboFuelLoad.Value = "medium") Then me.TonsAvailable.value = "6.8"
If (cboFuelLoad.Value= "heavy") Then me.TonsAvailable.value = "7.9"
If (cbofueltype.value = "4") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "4.4"
If ( cboFuelLoad.Value= "medium") Then me.TonsAvailable.value = "7.6"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = "8.5"
If (cbofueltype.value = "5") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "0.8"
If (cboFuelLoad.Value= "medium") Then me.TonsAvailable.value = "1.5"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = "2.5"
If (cbofueltype.value = "6") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "2.0"
If (cboFuelLoad.Value = "medium") Then me.TonsAvailable.value = "3.0"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = "5.0"
If (cbofueltype.value = "7") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "4.0"
If (cboFuelLoad.Value = "medium") Then me.TonsAvailable.value = "6.0"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = "8.0"
If (cbofueltype.value = "8") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "5.0"
If (cboFuelLoad.Value = "medium") Then me.TonsAvailable.value = "7.5"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = "10.0"
If (cbofueltype.value = "9") Then
If (cboFuelLoad.Value = "low") Then me.TonsAvailable.value = "1.5"
If ( cboFuelLoad.Value= "medium") Then me.TonsAvailable.value = "3.8"
If (cboFuelLoad.Value = "heavy") Then me.TonsAvailable.value = 5.9
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub



I see no reason why this would work. Though you may need conditions if one of the combo fields is still null. Although, I think that wont cause a problem.

]...but with that many options, I would have a table with 3 fields: type, load and the corresponding value. Then you can get the value very simply, and it will be more dynamic.


If you still have a hard time, this looks like good advice to me.