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

    Unanswered: Passing a value from a prompt to a table

    Hi I am trying to update a field in an Access table through user interaction. Specifically i would like the user to enter the date into a prompt and then this populates a field in my table.

    I have this at the moment:

    Dim prompt As String
    Dim monthdate As String
    prompt = "please enter date"
    monthdate = InputBox(prompt)
    DoCmd****nSQL ("update tbl_holding set f3=monthdate;")

    unfortunately "monthdate" does not seem to be recognised as a variable and when I run it I get two prompts - the actual one and one prompting me for the value of "monthdate"! if I input a value into the second prompt the field "f3" is updated with that value. So this is kind of a work around but I don't understand why my original way of doing things does not work.....

    oh and how do I add another field to an existing table using sql in access?
    Last edited by Tiggerandpoo; 06-23-09 at 18:57.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I think it's b/c you're trying to assign a string value to a date field. Try:
    Code:
    DoCmd****nSQL("UPDATE tbl_holding SET f3 = #" &  monthdate & "#;")
    Have a look here for one of my rants on this

    To alter a table via sql have a look at the ALTER statement.

    Cheers
    Me.Geek = True

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's another method I use to update values from a form or prompt box (I use a function). It's a simple way to update a single or multiple values without constructing a complex SQL string (you can also get fancy with passing the actual values to the function and thus make it a more generic type function which can be called from anywhere):

    Function UpdateFormXValues(RID as variant) 'Note: RID equals the RecID (ie. autonumber) passed to this function
    if isnull(RID) then
    msgbox "No RecordID passed to function!"
    exit function
    end if
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myTable where RecID = " & RID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockOptimistic
    ' rs.addnew (if adding a new record)
    rs!FieldX = Forms!MyFormName!FieldXOnFormName
    rs!FieldY = Forms!MyFormName!FieldYOnFormName
    ....
    rs.update
    rs.close
    set rs = nothing
    End Function

    Then I simply call the function like this when I want to update the values (from the form: MyFormName)...

    call UpdateFormXValues(me!RecID)

    Generic function example passing the values to the function (so this function can be called from any form)...

    Function UpdateXValues(RID as variant, Val1 as variant, Val2 as variant) 'Note: RID equals the RecID (ie. autonumber) passed to this function
    if isnull(RID) then
    msgbox "No RecordID passed to function!"
    exit function
    end if
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myTable where RecID = " & RID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockOptimistic
    ' rs.addnew (if adding a new record)
    rs!FieldX = Val1
    rs!FieldY = Val2
    rs.update
    rs.close
    set rs = nothing
    End Function

    Then I simply call the function like this when I want to update the values...

    call UpdateXValues(me!RecID, me!Field1Name, me!Field2Name)


    Also note regarding your post and dates...

    You may want to check if the date entered via the prompt is actually a date value. You can do this using the IsDate function.
    ie...

    Dim prompt As String
    Dim monthdate As String
    EnterDateLabel:
    prompt = "please enter date"
    monthdate = InputBox(prompt)
    if IsDate(monthDate) = false then
    msgbox "Enter the date in a format such as 6/15/09"
    Goto EnterDateLabel
    end if

    Also, if the user clicks cancel (or clicks OK without entering a value (in the prompt box)), you may get an error. I like to throw in something to trap these so the MSAccess/debug error doesn't show (which is an ugly unclear error message.)
    ie...

    Dim prompt As String
    Dim monthdate As String
    on Error goto CancelAdding
    EnterDateLabel:
    prompt = "please enter date"
    monthdate = InputBox(prompt)
    if IsDate(monthDate) = false then
    msgbox "Enter the date in a format such as 6/15/09"
    Goto EnterDateLabel
    end if

    exit sub
    CancelAdding:
    msgbox "No value entered or user cancelled." (or just put nothing here so it just exits the sub.)
    End Sub

    Thus, if you wanted to use your routine and call a function (ie. UpdateMonth function), here's how you could do it.
    (create this function in a module...)

    Function UpdateMonth(RID as variant, MDate as variant)
    if isnull(RID) or isnull(MDate) then
    msgbox "No RecordID or MonthDate value passed to function!"
    exit function
    end if
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myTable where RecID = " & RID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockOptimistic
    rs!MyMonthDateField = MDate
    rs.update
    rs.close
    set rs = nothing
    End Function

    And then this in your code on the form...

    Dim prompt As String
    Dim monthdate As String
    on Error goto CancelAdding
    EnterDateLabel:
    prompt = "please enter date"
    monthdate = InputBox(prompt)
    if IsDate(monthDate) = false then
    msgbox "Enter the date in a format such as 6/15/09"
    Goto EnterDateLabel
    else
    Call UpdateMonth(me!RecID, monthdate)
    end if
    exit sub
    CancelAdding:
    End Sub

    I like to open a recordset to update the values versus the DoCmd****nSQL ("update tbl_holding set f3=monthdate;") as I've found it less problematic and easier than constructing a SQL statement which can sometimes become complex and prone to hours of troubleshooting the SQL statement.
    Last edited by pkstormy; 06-23-09 at 21:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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