Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81

    Unhappy Unanswered: VBCode Problem

    I have upgraded from 97 to 2000. I imported all of the information instead of converting because the person who originally made the database has left our company and we don’t know the admin password. This module was in the code. There is a button that runs it. The button properties showed

    OnClick
    =modImportDisks()

    This will not work in 2000. Can anyone help me with this? I tried changing this to a _CLICK to make it a SUB but it didn’t work. I am not a big coder; I am more into the access part and less into VB script. Please help.

    You will see that if someone does not enter a date, it errors out and if a file is not selected, it gives you a message, all of this works, its when it gets to the actual IMPORT area that it does NOTHING, no errors or anything.

    Function modImportDisks()
    On Error GoTo modImportDisks_Err
    'Define variable MyFile (used to retrieve A:\)
    Dim MyFile As String
    ' Verify Ship Date not blank.
    DoCmd.RunMacro "macVerifyShipDateNotNull", , ""
    ' Default filename is A:\Tsysnew3.msk. If more than one
    ' file exists, the first file found is returned.
    MyFile = Me!gfni ' This returns a value selected by the GetFilePath function.
    If MyFile = "" Then
    MsgBox ("No File Found! Select a file to import!")
    Forms!Start!GetFileName.SetFocus
    Else
    ' Import file *.msk to "tblImport".
    DoCmd.TransferText acImportDelim, "FedEx2", "tblImport", MyFile, False, ""
    ' Confirmation
    Beep
    DoCmd.RunMacro "macCountAndMoveRecords", , ""
    MsgBox "This file has been copied!@Please copy the next file by repeating step 1@or proceed to step 2.", vbInformation, "Data Transfer Status..."
    Requery
    DoCmd.GoToControl ("DiskImport")
    End If
    modImportDisks_Exit:
    Exit Function
    modImportDisks_Err:
    Resume modImportDisks_Exit
    End Function
    Last edited by tsyscps; 01-02-03 at 14:40.

  2. #2
    Join Date
    Feb 2002
    Posts
    29
    My guess is that, since you imported everything into a new database, you do not have the import specification that the transfertext command is looking for. You will have to create a new one for your database.

    You can do this by importing one of your text files using the import/export wizard, setting up all the specifications to import the file then, just before the import click on the "Advanced" button in the lower left of the import dialog box, then click SaveAs and name the import specification "FedEx2".

    Now, finish the import and check to see if it has imported properly. If so, try running the code again.

  3. #3
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    After researching more, I found out Access 2000 limits the extensions you can import into tables. I am currently importing .MSK using Acc97 but 2000 wont support it. If I rename the .MSK to .TXT, it works fine. thansk for your help, I guess we are stuck with just renaming the files everyday becuase the main computer exports data to ONLY .msk.

  4. #4
    Join Date
    Feb 2002
    Posts
    29
    Well, you could change the file extension in the code:

    Function modImportDisks()
    On Error GoTo modImportDisks_Err
    'Define variable MyFile (used to retrieve A:\)
    Dim fs
    Dim txtfile
    Dim txtName As String
    Dim MyFile As String
    ' Verify Ship Date not blank.
    DoCmd.RunMacro "macVerifyShipDateNotNull", , ""
    ' Default filename is A:\Tsysnew3.msk. If more than one
    ' file exists, the first file found is returned.
    MyFile = Me!gfni ' This returns a value selected by the GetFilePath function.
    If MyFile = "" Then
    MsgBox ("No File Found! Select a file to import!")
    Forms!Start!GetFileName.SetFocus
    Else
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set txtfile = fs.GetFile(MyFile)
    txtName = fs.GetFileName(MyFile)
    txtfile.NAME = Left(txtName, Len(txtName) - 4) & ".txt"
    ' Import file *.msk to "tblImport".
    DoCmd.TransferText acImportDelim, "FedEx2", "tblImport", MyFile, False, Set fs = Nothing
    Set txtfile = Nothing
    ""
    ' Confirmation
    Beep
    DoCmd.RunMacro "macCountAndMoveRecords", , ""
    MsgBox "This file has been copied!@Please copy the next file by repeating step 1@or proceed to step 2.", vbInformation, "Data Transfer Status..."
    Requery
    DoCmd.GoToControl ("DiskImport")
    End If
    modImportDisks_Exit:
    Exit Function
    modImportDisks_Err:
    Resume modImportDisks_Exit
    End Function

  5. #5
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    I get a COMPILE ERROR on this line

    DoCmd.TransferText acImportDelim, "FedEx2", "tblImport", MyFile, False, Set fs = Nothing

    I put [Set fs = Nothing] on its own line like the SetText below it and the REd goes away but the button doesnt do anything just like before. Uploading teh TXT doesnt work anymore either. I have temp gone back to old code but looknig for more suggestions. Thansk for your help so far Microsoft, Got to love it
    Last edited by tsyscps; 01-06-03 at 09:29.

  6. #6
    Join Date
    Feb 2002
    Posts
    29
    Sorry, I seem to have garbled it. Try this:

    Function modImportDisks()
    On Error GoTo modImportDisks_Err
    'Define variable MyFile (used to retrieve A:\)
    Dim fs
    Dim txtfile
    Dim txtName As String
    Dim MyFile As String
    ' Verify Ship Date not blank.
    DoCmd.RunMacro "macVerifyShipDateNotNull", , ""
    ' Default filename is A:\Tsysnew3.msk. If more than one
    ' file exists, the first file found is returned.
    MyFile = Me!gfni ' This returns a value selected by the GetFilePath function.
    If MyFile = "" Then
    MsgBox ("No File Found! Select a file to import!")
    Forms!Start!GetFileName.SetFocus
    Else
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set txtfile = fs.GetFile(MyFile)
    txtName = fs.GetFileName(MyFile)
    txtfile.NAME = Left(txtName, Len(txtName) - 4) & ".txt"
    ' Import file *.msk to "tblImport".
    MyFile = Left(MyFile, Len(MyFile) - 4) & ".txt"
    DoCmd.TransferText acImportDelim, "FedEx2", "tblImport", MyFile, False, ""
    Set fs = Nothing
    Set txtfile = Nothing
    ' Confirmation
    Beep
    DoCmd.RunMacro "macCountAndMoveRecords", , ""
    MsgBox "This file has been copied!@Please copy the next file by repeating step 1@or proceed to step 2.", vbInformation, "Data Transfer Status..."
    Requery
    DoCmd.GoToControl ("DiskImport")
    End If
    modImportDisks_Exit:
    Exit Function
    modImportDisks_Err:
    Resume modImportDisks_Exit
    End Function

  7. #7
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    That worked great. Thanks for all of your help. Would have never been able to do this without some help, thanks for stepping up the the challenge.

    Jesse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •