How to Assign a Text Box Value to an SQL Select

helmerh
24 de Junio del 2004
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 =====

[COLOR=Red]DoCmd.SetWarnings False

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


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