Passing value from Public Sub to query (1 Viewer)

[LjushaMisha]

I'd like to filter query with a value from my Sub named NewName:

.....
NameVePlusBuy = (Version) & " - varianta " & (Buyer)
......

where Version and Buyer are declared as strings andNameVePlusBuy is some kind of combination of both, which I would like to use it in Append query like INSERT INTO tblXyz (ColumnX) values NameVePlusBuy

Any help???:banghead:

 

[arnelgp]

Vhange your Sub To Function

 

[LjushaMisha]

I made I change as you suggested (from sub to function) and it looks like this:

Public Function NewName()

Dim Var As Variant
Dim N As Double
Dim Buyer As String
Dim Item As String
Dim E As String
Dim NameVePlusBuy As String
Dim skl As String


Buyer = Forms!form1!Combo3.Column(1)
Item = Forms!form1!Text15

Var = Forms!form1!Text26
N = InStr(1, Var, " -") - 1

If N = 0 Or N = -1 Then
E = Var
Else
E = Left(Var, N)
End If

NameVePlusBuy = (E) & " - varianta " & (Buyer)
Debug.Print ImeEL

End Function

typing ? NewName in Immediate window I get "debug.printed" correct "answer"

Now I must put skl like:
skl = "Insert INto tbl01Elements (ElementName) Values ???? WHAT: NameVePlusBuy, NewNAme;"

and then
DoCmd.RunSQL skl

 

[arnelgp]

you need to assign NameVePlusBuy to NewName so that
function NewName will return the new string.
See the code before
End Function:

Public Function NewName()

Dim Var As Variant
Dim N As Double
Dim Buyer As String
Dim Item As String
Dim E As String
Dim NameVePlusBuy As String
Dim skl As String


Buyer = Forms!form1!Combo3.Column(1)
Item = Forms!form1!Text15

Var = Forms!form1!Text26
N = InStr(1, Var, " -") - 1

If N = 0 Or N = -1 Then
E = Var
Else
E = Left(Var, N)
End If

NameVePlusBuy = (E) & " - varianta " & (Buyer)
Debug.Print ImeEL

NewName = NameVePlusBuy

End Function


'***

now you used it in query:

skl = Insert Into tbl01Elements (ElementName) SELECT '" NewName() & "';"

 

[LjushaMisha]

IT seems it work. Thanks, thanks, thanks, thanks, ...........

 

[arnelgp]

I can see wow happy you are...

 

[LjushaMisha]

Yes, indeed I'm. Especially because I've used some SUB's but never (till today) FUNCTIONS. And now I see what is one of good reasons to use them. Thanks again. Hope you'll be able to help me with my next problem (which will appear for shure)
Best regards
Zvone

 

[arnelgp]

That's for sure...