Using multiple combo boxes to pull record

[Mishanya]

tblRootCost contains RootID, FromID, ToID, TransportTypeID, VehicleTypeID,
Cost fields.
There are tblFromTo, tblTransportType, tblVehicleType containing lists of
the values wich ID numbers are used in the tblRootCost.
In my form I want to select 4 unbound combo-boxes cboFrom, cboTo,
cboTransportType, cboVehicleType (based on the relative table-lists) and get
the cost of the root from the tblRootCost .
In other words, the control RootCost in my form should pull the value from
the tblRootCost using the unique combination of FromID, ToID,
TransportTypeID, VehicleTypeID as selected in the 4 relative unbound combos.
How can I work it out?
Thanks.

 

[Doctor]

Set the record source for each combo to include whatever cost is associated
with it in one of the columns of the query builder. For instance in your
cboFrom, the columns of the recordsource might be ID, FromPlace, Cost. Then
in the column widths property, you would set the widths to something like
0;1;0.

Do that for each combo box. Then set the ControlSource for your RootCost
control to =cboFrom.column(2) + cboTo.column(2) + cboTransportType.column(2)
+ cboVehicalType.column(2)

Just remeber that when refereing to the columns of a combo box that the
first column would be 0.

 

[Mishanya]

Doc
Thanks for your try, but I'm looking for the expert's answer.
I think yours is confused and still wait for the right one.

 

[Doctor]

no problem.

Doc
Thanks for your try, but I'm looking for the expert's answer.
I think yours is confused and still wait for the right one.

 

[Doctor]

Your right, I did mis read. I didn't realize that tblRootCost contained the
cost. Got cut off on my screen.

Put this VBA Code in the form's module in the AfterUpdate Events for your
four unbound combo boxes. Then when all four combos have data in them, it
should calculate your cost. Then also if you change any one of the four, it
will update the cost.

If not IsNothing(me.cboFrom) AND Not IsNothing(me.cboTo) AND Not
IsNothing(Me.cboTrasportType) AND Not IsNothing(Me.cboVehicleType) Then
Me.RootCost = DLookup("Cost","tblRootCost","FromID= " & Me.cboFrom & "
AND ToID = " & Me.cboTo & " AND TransportTypeID= " & Me.cboTransportType & "
AND VehicleTypeID=" & Me.cboVehicleType)
End If

Then paste the isnothing Function code into any module.
'********Code Start***********
Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
' Null = nothing
' Empty = nothing
' Number = 0 is nothing
' String = "" is nothing
' Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
' Created By: JLV 01/31/95
' Last Revised: JLV 01/31/95
'-----------------------------------------------------------
Dim intSuccess As Integer

On Error GoTo IsNothing_Err
IsNothing = True

Select Case VarType(varValueToTest)
Case 0 ' Empty
GoTo IsNothing_Exit
Case 1 ' Null
GoTo IsNothing_Exit
Case 2, 3, 4, 5, 6 ' Integer, Long, Single, Double, Currency
If varValueToTest <> 0 Then IsNothing = False
Case 7 ' Date / Time
IsNothing = False
Case 8 ' String
If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then
IsNothing = False
End Select


IsNothing_Exit:
On Error GoTo 0
Exit Function

IsNothing_Err:
IsNothing = True
Resume IsNothing_Exit

End Function
'********Code End***********

 

[Mishanya]

Hi
Thank you very much.
I'm sorry for undrestimating your advices.
As for this one - too complicated for me. I "rounded" the solution to less
elegant, but simplier, defining control source fields for every combo in the
table wich will hold the results chosen in the form, so I can pull them from
there and not "on the fly".

Thanks again!

 

[Doctor]

No worries. I've been wrong a lot in my life, too. Still learning this thing
as well...I'm not even close to the expertise and professionalism of the big
guys.