Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unhappy Unanswered: [B]How to Assign a Text Box Value to an SQL Select[/B]

    How to Assign a Text Box Value to an SQL Select Hello I'm new in this forum and in access as well.

    I just start designing a database for my office.

    I have a table "confirmation" with these fields "ConfNumber" and "ConfConsec"

    ConfNumber...............ConfConsec
    C4A1........................0001
    C4A2........................0060
    C4E12.......................0030

    For Instance everytime I have a fiield with C4A1 (I generate this automatically), I have to add (one) 1 to their ConfConsec.

    Example: I have a form and

    in TEXTBOX3 I have "C4A1"

    Then in the TEXBOX4 I have to display "C4A1002" and save this.

    If you see originally in the table I have C4A1 ..... 0001
    then because i have again C4A1 i just add (one) to 0001 => (0001+1) = "0002"

    Then I have to concatenate like this: C4A10002.

    and if for some reason i have again "C4A1" i have to add again (one) to their "ConfConsec" number

    It was C4A1......0002 now have to be C4A1......0003


    ===== I use this code to ad this number to the table confirmation =====

    DoCmd.SetWarnings False

    DoCmd.RunSQL "UPDATE confirmation " & _
    "SET ConfConsec = ConfConsec + 1 " & _
    "WHERE ConfNumber = Forms!Workorders![Text124];"



    DoCmd.SetWarnings True

    =============== End Code " and the code works " ========


    But now i have to get this "ConfNumber" and concatenated it with "ConfConsec" ==> . Then displayed in the TEXBOX4 and save it in the table services.

    == I'm triying to use this Code, to get this concatenation (C4A10002) from confirmation table ==

    mes = Chr(64 + Val(MonthLB))
    bteDay = Format(Me![Service_Pickup_Date], "d")
    bteYear = Format(Me![Service_Pickup_Date], "yy")
    ConfNum = "C" & Abs([bteYear]) & [mes] & [bteDay]

    ' This ConfNum have C4A1 as a value (It works fine)
    ' I just Display in TEXBOX3 the value of ConfNum = C4A1
    ' Then I try to get the "ConfConsec" to concatenate it with the TEXTBOX3 value

    DoCmd.RunSQL "SELECT confirmation.ConfConsec FROM confirmation WHERE (((confirmation.ConfNumber)='C4A1'));"


    ================= end code ======

    But because i'm new in this I DON'T KNOW hot to... Use the variable "ConfNum" or the TEXTBOX3 value as a part of my sql (avobe). instead of 'C4A1' at the end (that is to make it automatic, so no matter what i have in TXTBOX3 allways it going to select in confirmation the rithg one)??

    Please help me at with this or is any other form to just join the two sql in just one.


    Thank you. Att Helmer H
    Last edited by helmerh; 06-24-04 at 11:46.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    DoCmd.RunSQL "UPDATE confirmation " & _
    "SET ConfConsec = ConfConsec + 1 " & _
    "WHERE ConfNumber = Forms!Workorders![Text124];"


    DoCmd.SetWarnings True

    =============== End Code " and the code works " ========

    no it doesn't!



    and if you fix the above code you also have the fix to your question:

    if ConfNumber is...
    ...numeric use ConfNumber = " & Forms!Workorders![Text124] & ";"
    ...date use ConfNumber = #" & Forms!Workorders![Text124] & "#;"
    ...string use ConfNumber = '" & Forms!Workorders![Text124] & "';"

    handle form controls or variables exactly the same way.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2003
    Location
    Warren, PA
    Posts
    52
    DoCmd.RunSQL "UPDATE confirmation " & _
    "SET ConfConsec = ConfConsec + 1 " & _
    "WHERE ConfNumber =" & Forms!Workorders![Text124]

    DoCmd.SetWarnings True

    I think you would have better luck using
    CurrentDb.Execute "UPDATE confirmation " & _
    "SET ConfConsec = ConfConsec + 1 " & _
    "WHERE ConfNumber =" & Forms!Workorders![Text124]
    since you are using a textbox for the confconsec it may need to be like this using a single quote WHERE ConfNumber =' then followed by a double "
    then end the statement with a double quote single double like below

    CurrentDb.Execute "UPDATE confirmation " & _
    "SET ConfConsec = ConfConsec + 1 " & _
    "WHERE ConfNumber ='" & Forms!Workorders![Text124]"'"
    Brent
    Last edited by bhummel; 06-24-04 at 12:31.

Posting Permissions

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