passing 2 arguments to a sub

  • Thread starter Thread starter Southern at Heart
  • Start date Start date
S

Southern at Heart

I made this sub() so that anytime I need to I can remove all the trailing
returns from any given field in a table. I need to pass 2 strings to it, the
name of the table & field. But it doesn't work. Can you not pass 2 things
to a sub?
Is there a way to make this work?
thanks.

Sub Remove_Trailing_Returns(Table As String, Field As String)
strSql = "UPDATE " & Table & " SET " & Table & "." & Field & " = Left(" & _
Field & ",Len(" & Field & ")-2) WHERE (((" & Table & "." & _
Field & ") Like ""*"" & Chr(13) & Chr(10)));"
Debug.Print strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
End Sub
 
I made this sub() so that anytime I need to I can remove all the trailing
returns from any given field in a table. I need to pass 2 strings to it, the
name of the table & field. But it doesn't work. Can you not pass 2 things
to a sub?
Is there a way to make this work?
thanks.

Sub Remove_Trailing_Returns(Table As String, Field As String)
strSql = "UPDATE " & Table & " SET " & Table & "." & Field & " = Left(" & _
Field & ",Len(" & Field & ")-2) WHERE (((" & Table & "." & _
Field & ") Like ""*"" & Chr(13) & Chr(10)));"
Click to expand...

Step through the code in debug mode. What value is actually being passed to
strSQL? What values are you passing as Table and Field?

One possible problem is that Table and Field are both meaningful words - try
renaming these to strTable and strField.
Debug.Print strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
DoCmd.RunSQL strSql
End Sub
Click to expand...

I presume you want to remove up to seven trailing crlf pairs?
 
This sub is to remove any/all trailing returns. I can't pass arguments to it
though.
....Below is the line in my other sub I thought would pass on the table &
field I wanted, but VBA doesn't like this line and turns it red after I type
it in.

Remove_Trailing_Returns("Name","Phone")


....and this is the finished sub. (It does work, I've tried it by Dimming
strTable, strField and asigning them a string, instead of having them in the
Sub() line

Sub Remove_Trailing_Returns(strTable As String, strField As String)
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
strSQL = "UPDATE " & strTable & " SET " & strTable & "." & strField & " =
Left(" & _
strField & ",Len(" & strField & ")-2) WHERE (((" & strTable & "." & _
strField & ") Like ""*"" & Chr(13) & Chr(10)));"
Set qd = db.CreateQueryDef("", strSQL) ' create an unnamed, unsaved query
StartOver:
qd.Execute
Debug.Print qd.RecordsAffected
If qd.RecordsAffected <> 0 Then GoTo StartOver
End Sub
 
When I type in the line
Remove_Trailing_Returns("Name","Phone")
I get a compile error that says "expected ="
what is wrong with my code???
 
Southern said:
When I type in the line
Remove_Trailing_Returns("Name","Phone")
I get a compile error that says "expected ="
Click to expand...


When you call a Sub procedure, you must use either of these
syntax:

Remove_Trailing_Returns "Name","Phone"
or
Call Remove_Trailing_Returns("Name","Phone")

The way you wrote it is only valid for calling a Function in
an expression, e.g. x=f(a,b) If you are not using a
function's returned value, you can call a function the same
as a Sub.
 
Back
Top