compile errors Expected expression

[JEFFREY MUSGRAY]

I have received alot of help from this site and I am grateful. My problem is that I was able to get the first part of code to work. I tested it via the immediate window. I received the compile error "expected expression" when I tested for function InputFile.

?InputFile(Opti onal strPath As String)
"expected expression"

?InputFile()
"expected function or variable"
Below is the code that I was given-with slight modification

Public Function readFile(Optional strPath As String) As String
    'Create a filesystemobject
        Dim fso As Object
        Set fso = CreateObject("scripting.filesystemobject")
 
    'Open file for reading
        strPath = "C:\Users\jeffrey.musgray\Desktop\TcossDocument.txt"
        readFile = fso.OpenTextFile(strPath, 1).ReadAll
 
    'Cleanup
        Set fso = Nothing
 
End Function
 
Public Sub InputFile(Optional strPath As String)
     Dim strContent As String
     strContent = readFile(strPath)
 
     Dim rsDao As DAO.Recordset
     Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM tblTSR", dbOpenDynaset)
     rsDao.AddNew
     With rsDao
        ![TSR Number] = getValue(strContent, 101, 103)
        ![Type Action] = getValue(strContent, 103, 104)
        ![TYPE OF SERVICE] = getValue(strContent, 104, 105)
        ![Requesting Activity's Requirement Number] = getValue(strContent, 514)
     End With
     rsDao.Update
     Set rsDao = Nothing
 
End Sub

[gershwyn]

InputFile is not a function, it is a subroutine. Because it does not return a value, it is not legal to print it (the ? is a shortcut for the print command.)

Also, the subroutine takes an optional parameter, which is declared as a string. You don't have to pass anything, but if you do it needs to be a string. Your first attempt is just repeating the sub declaration, which makes no sense in the immediate window.

If you want to test your code from the immediate window, the syntax to call it would be either of the following (with or without a parameter):

InputFile
InputFile "c:\path\to\files\"

[JEFFREY MUSGRAY]

Thank you sir for your reply-I am fairly new at the VBA side of access. I tried what you said and I still get compile errors. I am just trying to import a text file-the complete code is attached. Can you tell me what is wrong with this code (I know I need to correct the heart)Thanks in advance

Option Compare Database
Option Explicit
Public Function readFile(Optional strPath As String) As String
    'Create a filesystemobject
        Dim fso As Object
        Set fso = CreateObject("scripting.filesystemobject")
 
    'Open file for reading
        strPath = "C:\Users\jeffrey.musgray\Desktop\TcossDocument.txt"
        readFile = fso.OpenTextFile(strPath, 1).ReadAll
 
    'Cleanup
        Set fso = Nothing
 
End Function
 
Public Sub InputFile(Optional strPath As String)
     Dim strContent As String
     strContent = readFile(strPath)
 
     Dim rsDao As DAO.Recordset
     Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM tblTSR", dbOpenDynaset)
     rsDao.AddNew
     With rsDao
        ![TSR NUMBER] = getValue(strContent, 101, 103)
        ![TYPE OF ACTION] = getValue(strContent, 103, 104)
        ![TYPE OF SERVICE] = getValue(strContent, 104, 105)
        ![NETWORK REQUIREMENT] = getValue(strContent, 105, 1061)
        ![OPERATIONAL SVC DATE] = getValue(strContent, 1061, 1062)
        ![REQUESTED SVC DATE] = getValue(strContent, 1062, 107)
        ![CCSD TRUNK ID] = getValue(strContent, 104, 105)
        ![PURPOSE USE CODE] = getValue(strContent, 104, 105)
        ![TYPE OPERATION] = getValue(strContent, 104, 105)
        ![MODULATION BANDWIDTH] = getValue(strContent, 104, 105)
        ![SVC AVAILABILITY] = getValue(strContent, 104, 105)
        ![SIGNALING MODE] = getValue(strContent, 104, 105)
        ![COMM SVC AUTHORIZATION] = getValue(strContent, 104, 105)
        ![PROG DESIGNATOR CODE] = getValue(strContent, 104, 105)
        ![OTIME EXPEDITING CHARGES] = getValue(strContent, 104, 105)
        ![TRANSMISSION MEDIA AV] = getValue(strContent, 104, 105)
        ![TERMINAL NODE LOCATION] = getValue(strContent, 104, 105)
        ![STATE COUNTRY CODE] = getValue(strContent, 104, 105)
        ![AREA CODE] = getValue(strContent, 104, 105)
        ![FACILITY CODE] = getValue(strContent, 104, 105)
        ![ADDRESS SITE] = getValue(strContent, 104, 105)
        ![ROOM NUMBER] = getValue(strContent, 104, 105)
        ![TYPE CRYPTO EQUIPMENT] = getValue(strContent, 104, 105)
        ![INTERFACE] = getValue(strContent, 104, 105)
        ![USER TECHNICAL POC] = getValue(strContent, 104, 105)
        ![MAIL ADDRESS] = getValue(strContent, 104, 105)
        ![UNIT IDENTIFICATION] = getValue(strContent, 104, 105)
        ![TERMINAL NODE LOCATION 2] = getValue(strContent, 104, 105)
        ![STATE COUNTRY CODE 2] = getValue(strContent, 104, 105)
        ![AREA CODE 2] = getValue(strContent, 104, 105)
        ![FACILITY CODE 2] = getValue(strContent, 104, 105)
        ![ADDRESS SITE 2] = getValue(strContent, 104, 105)
        ![ROOM NUMBER 2] = getValue(strContent, 104, 105)
        ![TERMINAL EQUIPMENT 2] = getValue(strContent, 104, 105)
        ![TYPE CRYPTO EQUIPMENT 2] = getValue(strContent, 104, 105)
        ![INTERFACE 2] = getValue(strContent, 104, 105)
        ![USER TECHNICAL POC 2] = getValue(strContent, 104, 105)
        ![MAIL ADDRESS 2] = getValue(strContent, 104, 105)
        ![UNIT IDENTIFICATION 2] = getValue(strContent, 104, 105)
        ![NARRATIVE INFORMATION] = getValue(strContent, 104, 105)
        ![TSR CONTACT] = getValue(strContent, 104, 105)
        ![NATIONAL SEC SYSTEM] = getValue(strContent, 104, 105)
        ![CMO ACCEPT SERVICE] = getValue(strContent, 104, 105)
        ![SECURITY REQUIREMENTS] = getValue(strContent, 104, 105)
        ![SHIPPING INFORMATION] = getValue(strContent, 104, 105)
        ![EXERCISE PROJECT NAME] = getValue(strContent, 104, 105)
        ![COST THRESHOLD] = getValue(strContent, 104, 105)
        ![REMARKS] = getValue(strContent, 104, 105)
        ![ESTIMATED SVC LIFE] = getValue(strContent, 104, 105)
        ![CPIWI] = getValue(strContent, 104, 105)
        ![CPIWM] = getValue(strContent, 104, 105)
        ![FUNDING TCO APPROVAL] = getValue(strContent, 104, 105)
        ![REQ ACTIVITY REQ NUMBER] = getValue(strContent, 514)
     End With
     rsDao.Update
     Set rsDao = Nothing
 End Sub
 
Public Function getValue(strInput As String, lngStart As Long, Optional lngEnd As Long = 0)
    'Get starting location
        Dim lngPosStart As Long
        Dim strStart As String
        strStart = vbCrLf & lngStart & "."
        lngPosStart = InStr(1, strInput, strStart) + Len(strStart)
    'Get end location (if any)
        Dim lngPosEnd As Long
        If lngEnd <> 0 Then
            lngPosEnd = InStr(1, strInput, vbCrLf & lngEnd & ".")
        Else
            lngPosEnd = Len(strInput)
        End If
 
    getValue = Mid(strInput, lngPosStart, lngPosEnd - lngPosStart)
 
End Function

[NeoPa]

Hi Jeffry.

You seem to be trying your best to work with us so I will just gently point you towards next steps.

When correcting code that doesn't compile you have to take each error in turn and fix it. The clue for each is in the error message so, I'm afraid, just posting a whole module here and asking us to find & fix the problems isn't a good way forward. Even if the code were mine I'd be looking to see what the compiler told me are the problems. To do it blind simply doesn't make sense.

So, that means you need to take one error at a time; read the message; attempt to determine what is wrong from that; if you can't work it out then feel free to post the question with the compiler error message. It may seem laborious but even the experts have to go through this when dealing with a bunch of code they didn't create themselves. It's actually very common for those of us that work as consultants to have to deal with databases that simply don't work.

So, step-by-step, find & fix all the problems. We'll be happy to help where required if provided with the appropriate information.

Gershwyn's post is actually a very good response to your original question so I will leave it in place as Best Answer regardless of where this goes from here.

[ADezii]

The Code is a little confusing to me, are you trying to extract Sub-Strings, given specific Start and End Values, from a larger String? If this is the case, it is a relatively simple matter to resolve.

[isladogs]

Jeffrey last replied in 2011. I believe this thread was resurrected by someone else whose post has since been (re)moved.

[ADezii]

Thanks isladogs, getting senile!

[NeoPa]

You & me both ADezii. I failed to spot that too (blush)!

It may well be that Jeffry won't be responding to this any more :-D

[isladogs]

I've done the same thing myself on numerous occasions. The question is, what happened to the post that resurrected this thread?

[NeoPa]

That post was deleted by me. I didn't spam-ban them as it had no link and I just saw no sense to it at all. It was positioned before post #4. Do you not see that? It may be that such info is only visible to Moderators. Can you confirm for me.

What I see is :

[isladogs]

Just to confirm, its only visible to mods