Update Text box from criteria selected from Combo Boxes

[ms1746]

Hello all!

I hope you can help me with this one. I am a SQL DBA so Access is familiar but still sometimes like a different language to me!

Here is the scenario:

I have one form = FormPCM
3 combo boxes = Platform
Stage
Component
I have one text box = Cost

Query = PCMQuery

I want the user to select what they want from each of the combo boxes and according to their selections autoupdate the text box with the associated cost from the Query it is all based on.

This is what I have so far:

Private Sub Cost_GotFocus()

Dim xCost as Currency

xCost = DLookup("[PCMCost]", "PCMQuery", PCMQuery.[PCMPlatformID] = Forms!FormPCM![Platform]& [PCMStageID] = Forms!FormPCM![Stage] & [PCMComponentID] = Forms!FormPCM![Component])

ME.Cost.Requery

End Sub

This works when I recreate it in a regular query with paramters however when I run it in the form I get the

Runtime 424
Object required

I can scroll over the form values in the Sub above and see they are populated properly but the xCost is still zero when this error occurs.

Once I get the appropriate cost I would like to have a command button that will populate the choices on a datsheet viewable on the form, this way the user can add several line items.

Any thoughts or ideas or an easier way to do this would be greatly appreciated!

Thanks in advance!

 

[jksmi]

Hi,

I think it may just be the syntax of your dlookup - I'm assuming that PCMQuery doesnt normally use any parameters...

xCost  = DLookup("[PCMCost]", "PCMQuery", [highlight]"[/highlight][PCMPlatformID] = [highlight][b]'[/b]" & me!Platform.value[/highlight] & [highlight]"[b]'[/b] AND [/highlight][PCMStageID] = [highlight][b]'[/b]" & me!stage.value[/highlight] & [highlight]"[b]'[/b] AND [/highlight][PCMComponentID] = [highlight][b]'[/b]" & me!Component.value & "[b]'[/b]"[/highlight])

if the values are numbers then loose the ' (in bold) or if they're dates replace ' with #

If you think of the last argument of the dlookup as a WHERE clause without the WHERE, i.e.
WHERE PLATFORMID = 'stringA' AND PCMSTAGEID = 'stringB' AND PCMCOMPONENTID = 'stringC'
and stringA comes from the value of the combobox called Platform on the the form where the code is written, i.e. me!Platform.Value, etc...

HTH, Jamie
FAQ219-2884

 

[jksmi]

Hi,

also, couple of things I just noticed...

if you create this as a seperate routine in the form code module you can call it from the on change event of each combo box, i.e. it will update whenever you change any of the parameters

Private Sub UpdateCost()
    Dim strWhere as string    [COLOR=green]' just makes it easier to read and correct[/color]

    strWhere = "[PCMPlatformID] = '" & me!Platform.value _
        & "' AND [PCMStageID] = '" & me!stage.value _
        & "' AND [PCMComponentID] = '" & me!Component.value _
    & "'")
    Debug.Print strWhere

    me!Cost.Value = DLookup("PCMCost", "PCMQuery", strWhere)
End Sub

and colled on the change event, i.e.

Private Sub Platform_Change()
    call UpdateCost
End Sub

HTH, Jamie
FAQ219-2884