Formula cannot retrieve value from Combobox

[frksdf]

Hi Experts;

I have an issue about VBA functions in Access. I am using a code to define function "Concatenate".
I am using this code with a textbox. If you use Concatenate function in textbox like this,

 =Concatenate("Select Supp_Series FROM Supplier")

the Code retrieves the Supp_Series from Supplier table and put them in textbox seperated with (-).

But İf you use a SQL statement like below, it gives error.

 
=Concatenate("Select Supp_Series FROM Supplier WHERE [Forms]![Form1]!Combo10")

What is the problem ? Why we cannot link a value in Form ? How we should amend the code.

And this is the Concatanate function code that lives in a module.

 
Function Concatenate(pstrSQL As String, Optional pstrDelim As String = "- ") As String
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

[RuralGuy]

Your WHERE clause is not complete.

[frksdf]

Hi Rural Guy,
I accidently write wrong sql statement on th forum.

I use ;

 
=Concatenate("Select Supp_Series FROM Supplier WHERE Supp_Company = [Forms]![Form1]!Combo10")

and it still gives error.

The error message is " Run-time Error.'-2147217904 (80040e10)' No value given for one or more required parameters."

Also, the line that gives error in the VBA code, is 4. line above.

Thank you very much Rural Guy.

[RuralGuy]

If your Supp_Company is a TEXT value then you need to surround the value with quotes.

[frksdf]

Hi Rural Guy,
I am very happy for your help and interest.

But it still doesn't work. Perhaps I can't be success and I am making mistake somewhere else.

I am sending you very simple example atttached. Could you please look it ?

You will see my question and problem on the form.

Thank you very much.

[joelmeaders]

I got it. The bottom text box should be:

=Concatenate("Select Cars.Brand FROM Cars Where Cars.Age = " & [Forms]![Form1]!Combo5)

This is because you have a number datatype and the double quotes convert it to a string which gives a mismatch

[frksdf]

I got it. The bottom text box should be:

=Concatenate("Select Cars.Brand FROM Cars Where Cars.Age = " & [Forms]![Form1]!Combo5)

This is because you have a number datatype and the double quotes convert it to a string which gives a mismatch

You are great joelmeaders; thank you.

Also thank to the RuralGuy

[RuralGuy]

Excellent! Thanks for posting back with your success and solution.