Constants

[proe]

I need help; this is wha i have

Dim action as String
...
...
action = "ADDED"
...
...
DoCmd.RunSQL "INSERT INTO MASTER_DIE_TABLE (M_DIENAME,M_COLORS,M_LENGTH,M_QUANTITY,M_LOCATION,M_ACTION,M_DATE) VALUES (Forms![D_ENTRY_F]![DIENAME],Forms![D_ENTRY_F]![colors],Forms![D_ENTRY_F]![LENGTH],Forms![D_ENTRY_F]![QUANTITY],Forms![D_ENTRY_F]![LOCATION],action,DATE())", -1

When I run this application I get a little screen asking me to enter the action value.
what I am doing wrong..! I got it working once and some how I didn't save it now I can remember what I did.. HA please help.

 

[Chris RR]

Try building your SQL like this. I usually break up the long line into multiple lines for readability, but this is the basic idea:

Dim strSQL as String
.
.
.
strSQL = "INSERT INTO MASTER_DIE_TABLE (M_DIENAME,M_COLORS,M_LENGTH,M_QUANTITY,M_LOCATION,M_ACTION,M_DATE) VALUES (Forms![D_ENTRY_F]![DIENAME],Forms![D_ENTRY_F]![colors],Forms![D_ENTRY_F]![LENGTH],Forms![D_ENTRY_F]![QUANTITY],Forms![D_ENTRY_F]![LOCATION]," & action & ",DATE())"

DoCmd.RunSQL strSQL, -1

 

[proe]

Hi Chris,

What I am suppose to acomplish withi this? Is this will allow me to move hte filed that I call action with the value ADDED into my table?!

 

[Pat Hartman]

action is a variable you have defined with VBA. SQL does not have access to this variable so you need to construct the actual SQL string in such a way that VBA substitutes a value for the variable, action, before the SQL string is sent to Jet for processing. The net result of the statement as RR restructured it is to build the string in three pieces with the pieces being concatenated by ampersands so that they can be stored as a single string in strSQL.

If you set a breakpoint in your code at the statement following the "strSQL = blah, blah", and print the contents of strSQL in the debug window, you will see that as it is written, the word "action" appears in the string. Whereas, after you make the change, the actual value of the action variable will appear in the string.

 

[proe]

I still have a screen pop asking me to enter the action value..! once I do it it does put it on my table but I do not want the user have to enter the value everytime he adds a record.!

How could I just enter a the value on the SQL code. I think I am complicating myself

"INSERT INTO MASTER_DIE_TABLE (M_DIENAME,M_COLORS,M_LENGTH,M_QUANTITY,M_LOCATION,M_ACTION,M_DATE) VALUES (Forms![D_ENTRY_F]![DIENAME],Forms![D_ENTRY_F]![colors],Forms![D_ENTRY_F]![LENGTH],Forms![D_ENTRY_F]![QUANTITY],Forms![D_ENTRY_F]![LOCATION]," & action & ",DATE())"

I just need to move the word "ADDED" into M_ACTION.

Am I missing the point???

 

[Pat Hartman]

Since the value of action is text, you need to surround it with quotes. Single quotes will work here. In part:
[QUANTITY],Forms![D_ENTRY_F]![LOCATION], '" & action & "', DATE())"