Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: variable from prompt

    Hi - I am trying to have the user prompt me with the year but the code below comes back with a syntax error.

    the field in the table is a number field by the way and all I want from the user is a four digit number (year).

    Private Sub Command42_Click()
    'prompt for year
    Dim Year1 As String
    Dim promptforYear As String
    EnterYearLabel:
    promptforYear = "Please enter Year of TOC File in xxxx format."
    Year1 = InputBox(promptforYear)
    If Year1 = Null Then
    MsgBox "Please enter a valid year in the correct format (xxxx)"
    GoTo EnterYearLabel
    End If

    'update (SE)_tbl_Holding with Year
    Dim strCurrentYearTOC As String
    strCurrentYearTOC = "UPDATE [(SE)_tbl_Holding] " & _
    "SET [(SE)_tbl_Holding].[Year] ="# & Year1 & #""

    DoCmd****nSQL (strCurrentYearTOC)

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well first off a string isn't the smartest call for a number.. use integer instead, or if you must use a string make sure its a valid year.
    you only test if the year is not null, and you only do it the once
    don't know where you got the # symbols from.. mebbe its a misreading of how to set a datevalue.. but you aren't setting a date value you are setting a column called year... something the reserved words list tells you not to.. change it to something other than year.
    aside from that.....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    37
    I have made some changes to the code as suggested and it now reads:

    Private Sub Command42_Click()
    'prompt for year
    Dim Year1 As Integer
    Dim promptforYear As String
    EnterYearLabel:
    promptforYear = "Please enter Year of TOC File in xxxx format."
    Year1 = InputBox(promptforYear)
    If Year1 = Null Then
    MsgBox "Please enter a valid year in the correct format (xxxx)"
    GoTo EnterYearLabel
    End If

    'update (SE)_tbl_Holding with Year
    Dim strCurrentYearTOC As String

    strCurrentYearTOC = "UPDATE [(SE)_tbl_Holding]" & _
    "SET [(SE)_tbl_Holding].[Year] =(Year1); "

    DoCmd****nSQL (strCurrentYearTOC)

    End Sub

    I have tried single and double " around Year1 in the last but third line of the code but it just does not work. it does not seem to recognise it as a variable. WHen I run this code it prompts me for a parameter value for it....

    help....

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try putting a debug or message box immediately before you run the docmd macro and make sure the SQL you are sending to the engine is what you think it is

    I see you are still using the year reserved word... that can cause havoc...

    My guess is that you think you have told the SQL to use the value of the variable year1, whereas in reality you are saying where blah = (year1)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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