Calling a sub??

[Access_guy49]

OK I thought i understood this, but apparently I don't.

Firstly, A function returns a value, a Sub just does an action.

I have created a subroutine that i pass some paramaters into. (A fileName, Starting value, Ending value.) They are all ByValue. There is nothing after the brackets.

when i call this sub from a button on a form it doesn't work. "Expected ="

So i put = true and then it doesn't like it because the function isn't defined. (obviously, because i didn't define it as anything because it's a sub.)

If i change it to a function, let the function be a string, and then use a variable at the start.

wtf = Function name (param,param,param)
Everything works... what am i doing wrong here!

 

[gemma-the-husky]

you need to post the code of the sub.

 

[Access_guy49]

Public Sub GetDataFromMyFile(ByVal MyfileName As String, ByVal StartNumber As Integer, ByVal EndNumber As Integer, Optional WriteToListbox As Boolean, Optional ListboxName As ListBox)

Dim strHold As String
Dim varSplit() As String
Dim i As Integer


Dim rst As DAO.Database
Set rst = CurrentDb.OpenRecordset("tblTEST", dbOpenDynaset)

Open MyfileName For Input As #1

Do Until EOF(1)
 Line Input #1, strHold
 
    If i >= StartNumber And i <= EndNumber Then
        strHold = Replace(strHold, "                    ", vbTab) '20 spaces
        strHold = Replace(strHold, "                   ", vbTab)
        strHold = Replace(strHold, "                  ", vbTab)
        strHold = Replace(strHold, "                 ", vbTab)
        strHold = Replace(strHold, "                ", vbTab)
        strHold = Replace(strHold, "               ", vbTab) '15 spaces
        strHold = Replace(strHold, "              ", vbTab)
        strHold = Replace(strHold, "             ", vbTab)
        strHold = Replace(strHold, "            ", vbTab)
        strHold = Replace(strHold, "           ", vbTab)
        strHold = Replace(strHold, "          ", vbTab) '10 spaces
        strHold = Replace(strHold, "         ", vbTab)
        strHold = Replace(strHold, "        ", vbTab)
        strHold = Replace(strHold, "       ", vbTab)
        strHold = Replace(strHold, "      ", vbTab)
        strHold = Replace(strHold, "     ", vbTab)
        strHold = Replace(strHold, "    ", vbTab)
        strHold = Replace(strHold, "   ", vbTab) ' 3 spaces
    
        varSplit = Split(strHold, vbTab)

'Test for write to listbox
If IsMissing(WriteToListbox) Then
    WriteToListbox = False
End If

If WriteToListbox = True Then
    ListboxName.RowSourceType = "Value List"
    ListboxName.AddItem (varSplit(0) & "      " & varSplit(1) & "      " & varSplit(2) & "      " & varSplit(3) & "      " & varSplit(4) & "      " & varSplit(5) & "      " & varSplit(6) & "      " & varSplit(7))
End If

With rst
   .AddNew
   .Fields("STATION_ID") = varSplit(o)
   .Fields("SAMPLE DATE") = varplit(1)
   .Update
End With
        
        End If
        i = i + 1
Loop

Close #1
rst.Close
Set rst = Nothing

End Function

GetDataFromMyFile(FileName, 14, (FileLines - 2), True, Me.List1)
should be able to call this sub just fine? shouldn't it?

 

[vbaInet]

That looks like a staircase

What you would end up with at that strHold variable is the last line:

        strHold = Replace(strHold, "   ", vbTab) ' 3 spaces

So I'm unsure what you're trying to achieve there.

IsMissing(WriteToListbox)

IsMissing() function works for Variants only.

 .Fields("STATION_ID") = varSplit([B][COLOR=Red]o[/COLOR][/B])

You missed the number 0.

Finally, you're not returning anything in your function.

If you want your function to return something then you should assign the variable within the function. E.g.

Private Function MyData() as String
dim myVar as string

[COLOR=Blue]MyData = myVar[/COLOR]
End Function

Notice the line in blue, that's what sets the value of your function.

Hope this helps.

 

[Access_guy49]

Sorry, i altered this post slightly from my actual code. In my project i have it as a function because that's the only way it works. I DON'T want it to be a function. I want it to be a SUB.

But when i reference it as a sub it wont let me. It said it expects an = ???? i don't know why it wants an =, but it does.


As for my staircase, The text file i deal with in this has spaces which vary, (wished they use tabs, but they don't) so that big thing just replaces sections of spaces with a single tab. that way i can then split it out using the methods provided in my other post your helping me with! "importing a file skip first 10 lines"

The ismissing function i was trying to use to get true/false option to work on my sub so that a user can specify if they want to also display the text file that it reads on a listbox. if the vale was flase i wanted it to do everything else but not display it on the text box. I didn't know how to set a default value of false if the argument is left blank by accident.

again, i would rather this be a sub because i don't want to return anything, i just want it to read the file and do some actions. no results.

 

[gemma-the-husky]

1. can you post the code that CALLS this sub - that may be the issue

2. Optional WriteToListbox As Boolean = false will set a false if the argument is not supplied

3. It isnt clear to me what your function actually does.
Can you explain!

 

[Access_guy49]

GetDataFromMyFile(FileName, 14, (FileLines - 2), True, Me.List1)

If what i use to call the Subroutine.

My subroutine takes a Filename, which is a text file, it skips the first 14 lines which is totally useless information for the purpose of my database, and then it reads in the text file. The subroutine will then place the data into a table.

I should also say, "FileLines - 2" is used because i have a different FUNCTION which i call first, it just goes through the text file to count how many lines of information there are. this argument is used to stop writing data to the recordset becuase the last 2 lines in the file are not data.

 

[vbaInet]

When you call a sub you don't use the brackets when you want to enter the paramter. So try this:

GetDataFromMyFile FileName, 14, (FileLines - 2), True, Me.List1

If you put the brackets it expects you to equate it to something on the left.

 

[Access_guy49]

YOU ARE AWESOME!
Gemma-the-husky. thank you for your help as well. I really appreciate it!

 

[vbaInet]

Glad to have made some input. You're welcome.

 

[SOS]

I will expand a little on what vbaInet said -

What happens when you call a procedure (sub or function) is that if you are not assigning it to something, or not using the CALL keyword, then you do not use parentheses.

MySub

MySub param1, param2

MyFunction

MyFunction param1, param2

But if you assign the value to something, or use the keyword CALL, then you DO use brackets:

Call MySub() ' although with no arguments it won't even keep them in this case

Call MySub(param1, param2)

Call MyFunction(param1, param2)

(by the way a function does NOT need to return a value and I tend to use functions exclusively instead of subs as there really is no problem doing that).

something = MyFunction() ' and again Access will likely drop the () if in VBA

Something = MyFunction(param1, param2)

hope that makes sense.

 

[Access_guy49]

yes that makes perfect sense! Thank you.

I actually did try removing the parentheses before i posted, but it came up with an error. I don't recall which one. So at the time i abandoned the idea that it had anything to do with brackets. I must have had a typo or something to that extent.

I'm Very greatful for everyone's help! Thank you ALL!!!!! <=== Pure Excitement