How do I get rid of a too few parameters error?

[shaneucc]

As it stands this is what I am trying to do. I'm trying to read a value from a combo box in a form and then choose the price corresponding to this value from a table so I can then do a calculation based on this price. This is my code,
Dim materialCost As Double
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "SELECT MaterialT.EuroCostPerWeight FROM MaterialT WHERE MaterialT.MaterialName =[Forms]![CustomComponentF]![C_MaterialCmb].[Value]"
Set rst = CurrentDb.OpenRecordset(strSQL)

materialCost = rst!EuroCostPerWeight
rst.Close
Set rst = Nothing

I preform the calculation on the materialCost variable. The SQL command works, I've checked it and I get the right result and if I manually write in the value in the form the above code works. But when I do it this way I get the error. Any ideas?

 

[pr2-eugin]

Hello shaneucc, Welcome to AWF..

You seem to have not concatenated the value, the code you currently have will look for the String not the value inside... Also you do not test if the recordset is empty, it is always safe to check.. So change it as follows..

Dim materialCost As Double
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "SELECT MaterialT.EuroCostPerWeight FROM MaterialT WHERE MaterialT.MaterialName =[B] '" & [Forms]![CustomComponentF]![C_MaterialCmb] & "'"[/B]
Set rst = CurrentDb.OpenRecordset(strSQL)
[B]If rst.Recordcount <> 0 Then[/B] materialCost = rst!EuroCostPerWeight
rst.Close
Set rst = Nothing

 

[shaneucc]

That worked perfectly, thank you.

 

[shaneucc]

I have one more question. Within the same subroutine I am trying to implement an if statement but I get the error "function call on left-hand side of assignment must return variant or object if statement".

This is my code,
If Me.C_ComponentCmb.Text = "Shell" Then
If Me.C_WeightTxt.Text = Null Then
MsgBox = "Please enter a weight."
Else
End If
ElseIf Me.C_ComponentCmb.Text = "Tower" Then
If Me.C_LengthTxt.Text = Null Then
MsgBox = "Please enter a length."
Else
End If
ElseIf Me.C_ComponentCmb.Text = "Tower - Tidal" Then
If Me.C_LengthTxt.Text = Null Then
MsgBox = "Please enter a length."
Else
End If
ElseIf Me.C_ComponentCmb.Text = "Nacelle" Then
If Me.C_VolumeTxt.Text = Null Then
MsgBox = "Please enter a volume."
Else
End If
End If

The purpose of this statement is to tell the user to enter a value in a specific case if a text box is left blank.

 

[pr2-eugin]

I would go for a better structure - Select rather than using Multiple If's.. The advantage is that it is more structured, and more easier to understand..

It is also to note that .Text is a method that is likely to throw the Code is some sort of Runtime error, .Text property requires the control to have focus.. If the ComboBox is multi column, i.e. first colum contains ID and the second contains the Text, then you could use Column property of the ComboBox.. Or use the ID..

Select Case Me.C_ComponentCmb
    Case "Shell"
        If Len(Me.C_WeightTxt & vbNullString) = 0 Then
            MsgBox "Please enter Weight"
        Else
           [COLOR=SeaGreen] 'do Something[/COLOR]
        End If
    Case "Tower"
        If Len(Me.C_LengthTxt & vbNullString) = 0 Then
            MsgBox "Please enter Length"
        Else
            [COLOR=SeaGreen] 'do Something[/COLOR]
        End If
    Case "Tower - Tidal"
        If Len(Me.C_LengthTxt & vbNullString) = 0 Then
            MsgBox "Please enter Length"
        Else
            [COLOR=SeaGreen] 'do Something[/COLOR]
        End If
    Case "Nacelle"
        If Len(Me.C_VolumeTxt & vbNullString) = 0 Then
            MsgBox "Please enter Volume"
        Else
            [COLOR=SeaGreen] 'do Something[/COLOR]
        End If
    Case Else
        MsgBox "Please select a valid option"
End Select

PS: Please use Code Tags when posting VBA Code

 

[shaneucc]

Thank you. That did the trick. I had a select statement too but my one had the same error. This fixed it though.

 

[pr2-eugin]

Glad that helped.. Good luck..