Append fields to another table in same database using a Query and

G

Guest

I have an APPEND query "qrtEmployeeCompany" that uses INSERT INTO table.

INSERT INTO Transferred ( QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity )
SELECT Question.QuestionID, Question.EmployeeID, Question.DepartmentID,
Question.CustomerID, Question.CallCode, Question.Status, Question.Severity
FROM Question
WHERE (((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));

How can I use the a command button "Transfer" ON CLICK event to execute:

1- this query and pass it the forms "QuestionID" field to use in the querys
where clause
and

2- clear two fields on the form same form "frmStartUp"
[Forms]![frmStartUp]![Employee Working Issue] and
[Forms]![frmStartUp]![Department Working Issue]
to ""

I have tried to do this with Macro but think it best to do this in VBA.
What is the code is my question.

Thanks for you reply
 
C

Chris2

Harrison said:
I have an APPEND query "qrtEmployeeCompany" that uses INSERT INTO table.

INSERT INTO Transferred ( QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity )
SELECT Question.QuestionID, Question.EmployeeID, Question.DepartmentID,
Question.CustomerID, Question.CallCode, Question.Status, Question.Severity
FROM Question
WHERE (((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));

How can I use the a command button "Transfer" ON CLICK event to
Click to expand...
execute:

Sample Tables:

Notes:

-- I used all LONG datatypes because I'm not sure about the datatypes
involved.
-- I did not add keys because I'm not sure about them, either (and it
looks like most of them could be keys).

CREATE TABLE Transferred
(QuestionID LONG
,EmployeeID LONG
,DepartmentID LONG
,CustomerID LONG
,CallCodeID LONG
,Status LONG
,Severity LONG
)

CREATE TABLE Question
(QuestionID LONG
,EmployeeID LONG
,DepartmentID LONG
,CustomerID LONG
,CallCodeID LONG
,Status LONG
,Severity LONG
)

Sample Data for Question
I added a 1 to each column for simplicity.


Assuming A2K or later:

1) Open a database.
2) Switch to Forms.
3) Double-click "Create form in Design View" (or create a new Form
in A97 or earlier).
4) Change the name of the Form to frmStartup.
5) Draw a Command Button on the Form.
6) Change the name to cmdTransfer
7) Open the Properties Sheet.
8) Switch to the Event Tab.
9) In the On Click row, put "[Event Procedure]".
10) Draw a Text Box on the Form.
11) Change the name of the Text Box to [QuestionID].
12) Right-click on the Form's background.
13) Go to the Visual Basic Editor (VBA IDE).
14) In the code window of the Form, pull down the left drop-down
box, and select "cmdTransfer". The default event,
"cmdTransfer_click", is automatically added to the code-window.
15) Add the following VBA code into the cmdTransfer_click event:

Note the change to the INSERT Query on the SELECT clause:
Question.CallCode is changed to Question.CallCodeID.
Note the change to the INSERT Query on the WHERE clause: [Form] is
changed to [Forms].

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID,
DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "SELECT Question.QuestionID,
Question.EmployeeID, Question.DepartmentID, "
strSQL = strSQL & "Question.CustomerID , Question.CallCodeID,
Question.Status, Question.Severity "
strSQL = strSQL & "FROM Question "
strSQL = strSQL & "WHERE (((Question.QuestionID) = " &
[Forms]![frmStartUp]![QuestionID] & "));"

db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing

16) Switch frmStartup to Form View.
17) Enter a 1 in the Text Box.
18) Click the cmdTransfer button.
19) Check the Transferred table to see if anything was inserted.


The above worked for me, and let me insert as many rows as I wanted
(there being no keys established). Let me know how it goes.

1- this query and pass it the forms "QuestionID" field to use in the querys
where clause
Click to expand...

It looks like the query alrady does this, although a slight change
is needed (see above).
"WHERE (((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));"
and

2- clear two fields on the form same form "frmStartUp"
[Forms]![frmStartUp]![Employee Working Issue] and
[Forms]![frmStartUp]![Department Working Issue]
to ""
Click to expand...

Assuming they are Text Boxes:
[Forms]![frmStartUp]![Employee Working Issue].value = ""
[Forms]![frmStartUp]![Department Working Issue].value = ""


Sincerely,

Chris O.
 
G

Guest

Chris,
I am using a form that contains fields I would like to copy to a table named
transfer.
Already built is the form "frmStartUp" and a query "qryTransferred".

To do this my form contains the fields being transferred along with the
unique id field "quesitonsID" along with a command button.

The command button does two things. First it should take the current
"questionID" on the form and pass it to the querys parameter to execute.
Next on the form I am clearing out two fields. This is was done through a
macro with the action SetValue. This works by itself.

My question is how can I have this all work using VBA code to Execute the
Query, Execute the Macro.

Thanks again.

Chris2 said:
Harrison said:
I have an APPEND query "qrtEmployeeCompany" that uses INSERT INTO table.

INSERT INTO Transferred ( QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity )
SELECT Question.QuestionID, Question.EmployeeID, Question.DepartmentID,
Question.CustomerID, Question.CallCode, Question.Status, Question.Severity
FROM Question
WHERE (((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));

How can I use the a command button "Transfer" ON CLICK event to
Click to expand...
execute:

Sample Tables:

Notes:

-- I used all LONG datatypes because I'm not sure about the datatypes
involved.
-- I did not add keys because I'm not sure about them, either (and it
looks like most of them could be keys).

CREATE TABLE Transferred
(QuestionID LONG
,EmployeeID LONG
,DepartmentID LONG
,CustomerID LONG
,CallCodeID LONG
,Status LONG
,Severity LONG
)

CREATE TABLE Question
(QuestionID LONG
,EmployeeID LONG
,DepartmentID LONG
,CustomerID LONG
,CallCodeID LONG
,Status LONG
,Severity LONG
)

Sample Data for Question
I added a 1 to each column for simplicity.


Assuming A2K or later:

1) Open a database.
2) Switch to Forms.
3) Double-click "Create form in Design View" (or create a new Form
in A97 or earlier).
4) Change the name of the Form to frmStartup.
5) Draw a Command Button on the Form.
6) Change the name to cmdTransfer
7) Open the Properties Sheet.
8) Switch to the Event Tab.
9) In the On Click row, put "[Event Procedure]".
10) Draw a Text Box on the Form.
11) Change the name of the Text Box to [QuestionID].
12) Right-click on the Form's background.
13) Go to the Visual Basic Editor (VBA IDE).
14) In the code window of the Form, pull down the left drop-down
box, and select "cmdTransfer". The default event,
"cmdTransfer_click", is automatically added to the code-window.
15) Add the following VBA code into the cmdTransfer_click event:

Note the change to the INSERT Query on the SELECT clause:
Question.CallCode is changed to Question.CallCodeID.
Note the change to the INSERT Query on the WHERE clause: [Form] is
changed to [Forms].

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID,
DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "SELECT Question.QuestionID,
Question.EmployeeID, Question.DepartmentID, "
strSQL = strSQL & "Question.CustomerID , Question.CallCodeID,
Question.Status, Question.Severity "
strSQL = strSQL & "FROM Question "
strSQL = strSQL & "WHERE (((Question.QuestionID) = " &
[Forms]![frmStartUp]![QuestionID] & "));"

db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing

16) Switch frmStartup to Form View.
17) Enter a 1 in the Text Box.
18) Click the cmdTransfer button.
19) Check the Transferred table to see if anything was inserted.


The above worked for me, and let me insert as many rows as I wanted
(there being no keys established). Let me know how it goes.

1- this query and pass it the forms "QuestionID" field to use in the querys
where clause
Click to expand...

It looks like the query alrady does this, although a slight change
is needed (see above).
"WHERE (((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));"
and

2- clear two fields on the form same form "frmStartUp"
[Forms]![frmStartUp]![Employee Working Issue] and
[Forms]![frmStartUp]![Department Working Issue]
to ""
Click to expand...

Assuming they are Text Boxes:
[Forms]![frmStartUp]![Employee Working Issue].value = ""
[Forms]![frmStartUp]![Department Working Issue].value = ""


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Harrison said:
Chris,
I am using a form that contains fields I would like to copy to a table named
transfer.
Already built is the form "frmStartUp" and a query "qryTransferred".

To do this my form contains the fields being transferred along with the
unique id field "quesitonsID" along with a command button.

The command button does two things. First it should take the current
"questionID" on the form and pass it to the querys parameter to execute.
Next on the form I am clearing out two fields. This is was done through a
macro with the action SetValue. This works by itself.

My question is how can I have this all work using VBA code to Execute the
Query, Execute the Macro.

Thanks again.

"Chris2" wrote:
Click to expand...

In order to use Values from Controls on the Form, rearrange the
assembly of strSQL thusly:


strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & ", " & [Forms]![frmStartup]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![EmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![DepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![CallCodeID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![Severity].Value
strSQL = strSQL & ") "

I pasted the above over the previous section of the VBA code.

I added the six extra Text Box Controls, and named them all
appropriately.

I deleted the contents of the Transferred Table from previous testing.

I opened frmStartup in Form View, entered 1, 2, 3, 4, 5, 6, 7 in the
Text Boxes, and clicked the Transfer Command Button.

I opened the Transferred table, and the data was there.


Note, to clear the contents of the any of the Text Boxes that need
it:, just set it to .Value = "" right there in the VBA code. Don't
use the Macro.


Sincerely,

Chris O.
 
G

Guest

I replaced my code with your VBA.
Works as you expected.
I couldn't have done it without your help.
Excellent@
THANKS.

Chris2 said:
Harrison said:
Chris,
I am using a form that contains fields I would like to copy to a table named
transfer.
Already built is the form "frmStartUp" and a query "qryTransferred".

To do this my form contains the fields being transferred along with the
unique id field "quesitonsID" along with a command button.

The command button does two things. First it should take the current
"questionID" on the form and pass it to the querys parameter to execute.
Next on the form I am clearing out two fields. This is was done through a
macro with the action SetValue. This works by itself.

My question is how can I have this all work using VBA code to Execute the
Query, Execute the Macro.

Thanks again.

"Chris2" wrote:
Click to expand...

In order to use Values from Controls on the Form, rearrange the
assembly of strSQL thusly:


strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & ", " & [Forms]![frmStartup]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![EmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![DepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![CallCodeID].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartup]![Severity].Value
strSQL = strSQL & ") "

I pasted the above over the previous section of the VBA code.

I added the six extra Text Box Controls, and named them all
appropriately.

I deleted the contents of the Transferred Table from previous testing.

I opened frmStartup in Form View, entered 1, 2, 3, 4, 5, 6, 7 in the
Text Boxes, and clicked the Transfer Command Button.

I opened the Transferred table, and the data was there.


Note, to clear the contents of the any of the Text Boxes that need
it:, just set it to .Value = "" right there in the VBA code. Don't
use the Macro.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Harrison said:
I replaced my code with your VBA.
Works as you expected.
I couldn't have done it without your help.
Excellent@
THANKS.

:
Click to expand...


Harrison,

You are welcome. :)


Sincerely,

Chris O.
 
G

Guest

Chris,
One more piece for your expertise... I now need to adjust your code for two
OPTION fields where the user will select one or the other [ToEmployeeID] or
[ToDepartmentID] .

If one of the fields is Null (Empty) I get this error... 3134 on SQL Insert.

I can use the form and leave [ToEmployeeID] or [ToDepartmentID] Null (Empty)
and not an issues. Its something with SQL. My table data is long integers.
My Debugger window show this:
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1): " :
String

How can I get this code execute.

strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "

Again Thanks,
Harrison
 
C

Chris2

Harrison said:
Chris,
One more piece for your expertise... I now need to adjust your code for two
OPTION fields where the user will select one or the other [ToEmployeeID] or
[ToDepartmentID] .

If one of the fields is Null (Empty) I get this error... 3134 on SQL Insert.

I can use the form and leave [ToEmployeeID] or [ToDepartmentID] Null (Empty)
and not an issues. Its something with SQL. My table data is long integers.
My Debugger window show this:
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1): " :
String

How can I get this code execute.

strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "

Again Thanks,
Harrison


:
Click to expand...

Harrison,

In some cases, it can be instructive, for diagnostic purposes, to
simplify.

I took the existing form (unaltered from my last test), typed numbers
in, and left the first text box for QuestionID blank, and clicked
Transfer

I too received the 3134 error.

While in debug mode, in the Immediate Window, I typed "? strSQL" and
pressed Enter. I received:

INSERT INTO Transferred
(QuestionID
,EmployeeID
,DepartmentID
,CustomerID
,CallCodeID
,Status
,Severity )
VALUES ( , 3, 4, 5, 6, 7, 8)

As you can see from the structure above, there is a clear error. You
can just have "( ," in an SQL statement. It's a syntax error, or some
such.

My earlier examples were just that, examples. Validating data before
it goes into the assembly of the SQL statement is a separate process,
and of course must be done.

If I end the current execution, go back to the form, and type in NULL,
and click Transfer, I get a new row, where the first column is Null.

If I go back to the form, and type Mary, I get Run-time error '3061':
too few parameters. Expected 1.

If I do the Immediate Window trick again:


INSERT INTO Transferred
(QuestionID
,EmployeeID
,DepartmentID
,CustomerID
,CallCodeID
,Status
,Severity )
VALUES ( Mary, 3, 4, 5, 6, 7, 8)

Now it's really confused, because it's letters, but there are no "" to
denote a string. Oops. It worked when it was just Null, because JET
recognized the word Null.


What you need to do is make sure that if the user hasn't entered any
data for one of the columns in the Table, then you need to supply a
default so that the SQL statement can be correctly assembled.

Now, Option Box values are either -1 or 0, so I'm not precisely sure
what's going on in this specific case. The option box on the form
that isn't selected should be supplying a value of 0.


Go back to your altered table. Enter your data again, with option box
selections, just as you have been doing, hoping to get the 3134 error
again.

When the error appears, click debug.

In the Immediate Window, type "? strSQL" and press Enter. Your SQL
will be one long line. Copy and paste it your favorite text editor,
align it for readability, and see if anything looks out of place.

Paste back the contents of the strSQL here if nothing leaps out at
you.


Sincerely,

Chris O.
 
G

Guest

Chris,
I changed my code to change the value to a "0" and this displays the same
strSQL of
The Immediate window displays this....
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1)
?IsNull(ToEmployeeId)
False
?IsNull(ToDepartmentId)
True

The Local windows displays this....
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, 0, 8, 1, 1, 1) " :
String

My code is this...
strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
If IsNull([Forms]![frmTransfer]![ToEmployeeID].Value) Then
[Forms]![frmTransfer]![ToEmployeeID].Value = 0
End If
If IsNull([Forms]![frmTransfer]![ToDepartmentID].Value) Then
[Forms]![frmTransfer]![ToDepartmentID].Value = 0
End If
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "
'strSQL = strSQL & "WHERE
(((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));"

Now the record is added. New issues. The record is added in two parts.
There is a record for EmployeeID or DepartmentID which ever was choosen then
a new record for the rest of the fields. Don't know why its seperating this
statement into two records instead of one ?

Again thanks

Harrison

Chris2 said:
Harrison said:
Chris,
One more piece for your expertise... I now need to adjust your code for two
OPTION fields where the user will select one or the other [ToEmployeeID] or
[ToDepartmentID] .

If one of the fields is Null (Empty) I get this error... 3134 on SQL Insert.

I can use the form and leave [ToEmployeeID] or [ToDepartmentID] Null (Empty)
and not an issues. Its something with SQL. My table data is long integers.
My Debugger window show this:
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1): " :
String

How can I get this code execute.

strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "

Again Thanks,
Harrison


:
Click to expand...

Harrison,

In some cases, it can be instructive, for diagnostic purposes, to
simplify.

I took the existing form (unaltered from my last test), typed numbers
in, and left the first text box for QuestionID blank, and clicked
Transfer

I too received the 3134 error.

While in debug mode, in the Immediate Window, I typed "? strSQL" and
pressed Enter. I received:

INSERT INTO Transferred
(QuestionID
,EmployeeID
,DepartmentID
,CustomerID
,CallCodeID
,Status
,Severity )
VALUES ( , 3, 4, 5, 6, 7, 8)

As you can see from the structure above, there is a clear error. You
can just have "( ," in an SQL statement. It's a syntax error, or some
such.

My earlier examples were just that, examples. Validating data before
it goes into the assembly of the SQL statement is a separate process,
and of course must be done.

If I end the current execution, go back to the form, and type in NULL,
and click Transfer, I get a new row, where the first column is Null.

If I go back to the form, and type Mary, I get Run-time error '3061':
too few parameters. Expected 1.

If I do the Immediate Window trick again:


INSERT INTO Transferred
(QuestionID
,EmployeeID
,DepartmentID
,CustomerID
,CallCodeID
,Status
,Severity )
VALUES ( Mary, 3, 4, 5, 6, 7, 8)

Now it's really confused, because it's letters, but there are no "" to
denote a string. Oops. It worked when it was just Null, because JET
recognized the word Null.


What you need to do is make sure that if the user hasn't entered any
data for one of the columns in the Table, then you need to supply a
default so that the SQL statement can be correctly assembled.

Now, Option Box values are either -1 or 0, so I'm not precisely sure
what's going on in this specific case. The option box on the form
that isn't selected should be supplying a value of 0.


Go back to your altered table. Enter your data again, with option box
selections, just as you have been doing, hoping to get the 3134 error
again.

When the error appears, click debug.

In the Immediate Window, type "? strSQL" and press Enter. Your SQL
will be one long line. Copy and paste it your favorite text editor,
align it for readability, and see if anything looks out of place.

Paste back the contents of the strSQL here if nothing leaps out at
you.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Harrison said:
Chris,
I changed my code to change the value to a "0" and this displays the same
strSQL of
The Immediate window displays this....
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1)
?IsNull(ToEmployeeId)
False
?IsNull(ToDepartmentId)
True
Click to expand...

Ok, now I'm not sure what's going on. If these are option boxes,
their values should be 0 or -1.

To make sure, I set up two options boxes (on the same form), and name
them optTop and optBottom. I then set up a text box name txtOptions.
In the AfterUpdate event of each option box, I have some code that
will display the "values" of *both* options boxes in the text box.

Example:


Private Sub optBottom_Click()

Forms!frmstartup!txtOptions.Value = Forms!frmstartup!optBottom.Value
& " :Bottom" & " :: " & _
Forms!frmstartup!optTop.Value & " :Top"


End Sub

Private Sub optTop_Click()

Forms!frmstartup!txtOptions.Value = Forms!frmstartup!optTop.Value &
" :Top" & " :: " & _
Forms!frmstartup!optBottom.Value & " :Bottom"

End Sub

I run the forms, and:

Sure enough, in the text box, it shows 0 or -1 as the values of the
option boxes.

So I'm definitely confused.


However, in any event, let's take a closer look at what is going on in
the assembly of the string:


When we examine strSQL, we are examining the contents of a String
variable in VBA.

When we examine ToEmployeeId via the IsNull function, you are
examining the contents of a Control (which itself has a variable
inside of it, storing the value.

strSQL is a series of characters, that, once assembled, is passed to
JET for syntax checking, query optimization, complilation, and
execution.

When you "assemble" a String, and use an *actual* Null value from a
variable in VBA or a Control, the Null value *does not appear as the
word Null*. It appears as *nothing*. Which is why you get a blank
spot in the assembled string.

In order to pass an *actual* Null value to JET, we must write the
*actual word* "Null" directly in the *string* (without the "" marks).

Not Valid:
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1)
---------------------------

Valid:
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, Null, 8, 1, 1, 1)
---------------------------

Not Valid (string value going into data type LONG, unescaped " marks
break the string assembly):
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, "Null", 8, 1, 1, 1)
---------------------------

Not Valid (string value going into data type LONG, note: assembling
the double " marks into the string isn't as simple as it looks, but
that's another story):
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, ""Null"", 8, 1, 1, 1)
---------------------------


It is up to you, the programmer, to stop *actual* Null values from
hitting the string assembly process. If you need a 0 or "" (zero
length string), you use 0 or "" (although you'd have to escape that
via """", becasue the " mark is a special character). If you need to
pass a Null into Jet, you pass Null (without "" around it, because in
this case it's a *reserved word* recognized by Jet, not a string).


The Local windows displays this....
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, 0, 8, 1, 1, 1) " :
String

My code is this...
strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
If IsNull([Forms]![frmTransfer]![ToEmployeeID].Value) Then
[Forms]![frmTransfer]![ToEmployeeID].Value = 0
End If
If IsNull([Forms]![frmTransfer]![ToDepartmentID].Value) Then
[Forms]![frmTransfer]![ToDepartmentID].Value = 0
End If
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "
'strSQL = strSQL & "WHERE
(((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));"

Now the record is added. New issues. The record is added in two parts.
There is a record for EmployeeID or DepartmentID which ever was choosen then
a new record for the rest of the fields. Don't know why its seperating this
statement into two records instead of one ?
Click to expand...

I have no idea. I think you're somehow back to the original
double-row addition problem, which I never really was able to figure
out, because I couldn't repro it.

Given the code you're using, *that* should *not* be happening.



For simplicity over the IF statements, you might try the Nz function.

Nz(<expression>, <value>)

<expression>: If <expression> Is Null, then returns <value>, otherwise
returns <expression>

Nz(([Forms]![frmTransfer]![ToDepartmentID].Value, 0)



What version of OS, Access, and JET (and Service Packs for each) are
you running?

I'm running Win2k SP-4, Access 2k SP-3, and JET SP-8.


Sincerely,

Chris O.
 
G

Guest

Chris,

I too am running Win2k SP-4, Access 2k SP-3
How can I see what version is Jet ?

I will change me code for Nz once we can solve the duplicate code.

I am wondering... I use a form that has a RecordSource of "tblTransferred".
[I went back and prefed my tables with "tbl" for clarity.]

When I even click "Cancel" button it too adds the record.
Code for "Cancel"
DoCmd.Close acForm, "frmTransfer", acSaveNo
This adds one record.

The "Save" button adds two records. One is from the strSQL and the other is
somehow adding when the form is closed.

Again thanks for your input.

Harrison

Chris2 said:
Harrison said:
Chris,
I changed my code to change the value to a "0" and this displays the same
strSQL of
The Immediate window displays this....
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1)
?IsNull(ToEmployeeId)
False
?IsNull(ToDepartmentId)
True
Click to expand...

Ok, now I'm not sure what's going on. If these are option boxes,
their values should be 0 or -1.

To make sure, I set up two options boxes (on the same form), and name
them optTop and optBottom. I then set up a text box name txtOptions.
In the AfterUpdate event of each option box, I have some code that
will display the "values" of *both* options boxes in the text box.

Example:


Private Sub optBottom_Click()

Forms!frmstartup!txtOptions.Value = Forms!frmstartup!optBottom.Value
& " :Bottom" & " :: " & _
Forms!frmstartup!optTop.Value & " :Top"


End Sub

Private Sub optTop_Click()

Forms!frmstartup!txtOptions.Value = Forms!frmstartup!optTop.Value &
" :Top" & " :: " & _
Forms!frmstartup!optBottom.Value & " :Bottom"

End Sub

I run the forms, and:

Sure enough, in the text box, it shows 0 or -1 as the values of the
option boxes.

So I'm definitely confused.


However, in any event, let's take a closer look at what is going on in
the assembly of the string:


When we examine strSQL, we are examining the contents of a String
variable in VBA.

When we examine ToEmployeeId via the IsNull function, you are
examining the contents of a Control (which itself has a variable
inside of it, storing the value.

strSQL is a series of characters, that, once assembled, is passed to
JET for syntax checking, query optimization, complilation, and
execution.

When you "assemble" a String, and use an *actual* Null value from a
variable in VBA or a Control, the Null value *does not appear as the
word Null*. It appears as *nothing*. Which is why you get a blank
spot in the assembled string.

In order to pass an *actual* Null value to JET, we must write the
*actual word* "Null" directly in the *string* (without the "" marks).

Not Valid:
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1)
---------------------------

Valid:
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, Null, 8, 1, 1, 1)
---------------------------

Not Valid (string value going into data type LONG, unescaped " marks
break the string assembly):
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, "Null", 8, 1, 1, 1)
---------------------------

Not Valid (string value going into data type LONG, note: assembling
the double " marks into the string isn't as simple as it looks, but
that's another story):
---------------------------
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, ""Null"", 8, 1, 1, 1)
---------------------------


It is up to you, the programmer, to stop *actual* Null values from
hitting the string assembly process. If you need a 0 or "" (zero
length string), you use 0 or "" (although you'd have to escape that
via """", becasue the " mark is a special character). If you need to
pass a Null into Jet, you pass Null (without "" around it, because in
this case it's a *reserved word* recognized by Jet, not a string).


The Local windows displays this....
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, 0, 8, 1, 1, 1) " :
String

My code is this...
strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
If IsNull([Forms]![frmTransfer]![ToEmployeeID].Value) Then
[Forms]![frmTransfer]![ToEmployeeID].Value = 0
End If
If IsNull([Forms]![frmTransfer]![ToDepartmentID].Value) Then
[Forms]![frmTransfer]![ToDepartmentID].Value = 0
End If
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "
'strSQL = strSQL & "WHERE
(((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));"

Now the record is added. New issues. The record is added in two parts.
There is a record for EmployeeID or DepartmentID which ever was choosen then
a new record for the rest of the fields. Don't know why its seperating this
statement into two records instead of one ?
Click to expand...

I have no idea. I think you're somehow back to the original
double-row addition problem, which I never really was able to figure
out, because I couldn't repro it.

Given the code you're using, *that* should *not* be happening.



For simplicity over the IF statements, you might try the Nz function.

Nz(<expression>, <value>)

<expression>: If <expression> Is Null, then returns <value>, otherwise
returns <expression>

Nz(([Forms]![frmTransfer]![ToDepartmentID].Value, 0)



What version of OS, Access, and JET (and Service Packs for each) are
you running?

I'm running Win2k SP-4, Access 2k SP-3, and JET SP-8.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Harrison said:
Chris,

I too am running Win2k SP-4, Access 2k SP-3
How can I see what version is Jet ?

I will change me code for Nz once we can solve the duplicate code.

I am wondering... I use a form that has a RecordSource of "tblTransferred".
[I went back and prefed my tables with "tbl" for clarity.]

When I even click "Cancel" button it too adds the record.
Code for "Cancel"
DoCmd.Close acForm, "frmTransfer", acSaveNo
This adds one record.

The "Save" button adds two records. One is from the strSQL and the other is
somehow adding when the form is closed.

Again thanks for your input.

Harrison

:
Click to expand...

Harrison,

The form I am working with does not have any RecordSource, and the
Controls definitely have no Control Source

As I mentioned before, for the text boxes and cmd button you are
using, you need to *go through* the properties for *each and every*
Control used *in this operation* itself, and get rid of anything in
their ControlSources.

Try that, and tell me what happens.


Sincerely,

Chris O.
 
C

Chris2

Harrison said:
Chris,

I too am running Win2k SP-4, Access 2k SP-3
How can I see what version is Jet ?
Click to expand...

Harrison,

I forgot to answer this one:

To just get the version, in the immediate window, type:

? currentdb().Version

It should say 4.0 (hopefully).

To find out which service pack of Jet you are currently running:

"How to obtain the latest service pack for the Microsoft Jet 4.0
Database Engine
http://support.microsoft.com/default.aspx?scid=kb;en-us;239114"

There is a chart that shows the version numbers of the files, and
which file to access. Open the file's properties to see what the
version number in your file is, and you'll know what service pack you
are on.
 
G

Guest

Chris,

My version is also 4.0

Bound Controls...
You are correct. The form and fields were bound to the tblTransferred. I
removed the Control Source and presto... it worked just like yours. :)
No more duplictes.

All is well.

THANK YOU AGAIN!!!
Harrison
 
C

Chris2

Harrison said:
Chris,

My version is also 4.0

Bound Controls...
You are correct. The form and fields were bound to the tblTransferred. I
removed the Control Source and presto... it worked just like yours. :)
No more duplictes.

All is well.

THANK YOU AGAIN!!!
Harrison

:
Click to expand...

Harrison,

Huzzah!


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

3184 on SQL Insert 4
Run an append query from a form control event procedure 1
VBA to Append Form Fields Into Table? 1
append query not working 3
Using multiple form fields to populate the WHERE clause in a query 2
Append Fields In Subform To Another Table 2
Using check boxes in a form to be criteria in a query 1
How to select an entire row And append it? 1

Top