Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Unanswered: Using Variable from Input Box

    Hi All

    A simple request from a newbie. Been looking for help but it's driving me mad.

    I've created a function for importing a multisheet spreadsheet into an access table. Works well except where SQL Update tries to use the value entered from my InputBox. I get another Input Box looking for WeekNo.

    Can anyone offer help please?

    Function ImportIssuesWk()

    Dim filename As String
    Dim WeekNo As String

    On Error GoTo Resolved_Err

    WeekNo = InputBox("Enter the Week No of the file to import")
    filename = "C:\DATA\USERS\37533470\CCDHB Project\" & "Week_" & WeekNo & "_Open Issues" & ".xls"

    DoCmd.TransferSpreadsheet acImport, 8, "Open Issues", filename, True, "Resolved!A4:I50"
    DoCmd.TransferSpreadsheet acImport, 8, "Open Issues", filename, True, "Open Urgent!A4:H50"
    DoCmd.TransferSpreadsheet acImport, 8, "Open Issues", filename, True, "Open High!A4:H50"
    DoCmd.TransferSpreadsheet acImport, 8, "Open Issues", filename, True, "Open Medium!A4:H50"
    DoCmd.TransferSpreadsheet acImport, 8, "Open Issues", filename, True, "Open Low!A4:H50"
    DoCmd.OpenQuery "Delete Empty Fields", acNormal, acEdit
    DoCmd.RunSQL "UPDATE [Open Issues] SET [Open Issues].Week = WeekNo WHERE ((([Open Issues].Week)Is Null) AND (([Open Issues].[Case#]) Is Not Null));"

    Resolved_Exit:
    Exit Function

    Resolved_Err:
    MsgBox Error$
    Resume Resolved_Exit

    End Function

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think this may do it ?

    DoCmd.RunSQL "UPDATE [Open Issues] SET [Open Issues].Week = " & WeekNo & " WHERE ((([Open Issues].Week)Is Null) AND (([Open Issues].[Case#]) Is Not Null));"

    ??

    If WeekNo is suppose to be an integer/Long, I would try and validate that this is what the user has entered before using it !!

    MTB

  3. #3
    Join Date
    Oct 2007
    Posts
    2
    Thanks Mike

    It worked a treat. I had already tried to encapsulate it in speechmarks but what does the & indicate? I thought it was concatenating the text string?

    At the moment I am the only user, but yes I will look at validating the data entry or am thinking of using a "browse for file" method of choosing the spreadsheet to import.

    Thanks again

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    You are correct. The & does concatenate strings, but WeekNo is NOT a literal string it is a variable, so the VALUE of the variable (number or string) is concatenated.

    Without the two & just the literal “WeekNo” is seen by the complier, not the number it represents.


    Browsing for the file is my preferred method (minimises user error!).

    MTB

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look at this code bank post for an example of how to use the FileSystemObject to browse files.
    George
    Home | Blog

Posting Permissions

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