does antone see whats rong with this?

S

Stu Dongel

hello all,

i have the folowing snippet that s giving me problems, i think it is with my
sql string.

Private Sub Command14_Click()
Dim strSQL As String

strSQL = "INSERT INTO tblTransactions ( [Brother ID], [Date], Type, Amount,
Designation, Description )" & _
"SELECT tblBrotherInfo.ID, [forms]![frmGlobalCharge]![txtDate] AS Expr1,
[forms]![frmGlobalCharge]![cboType] AS Expr2,
[forms]![frmGlobalCharge]![txtAmount] AS Expr3,
[forms]![frmGlobalCharge]![cboDesignation] AS Expr4,
[forms]![frmGlobalCharge]![txtDescription] AS Expr5 " & _
"FROM tblBrotherInfo LEFT JOIN tblTransactions ON tblBrotherInfo.ID =
tblTransactions.[Brother ID]; "

MsgBox stSQL
DoCmd.RunSQL strSQL




End Sub



Im trying to get this query to creat a record in tbltransaction for every
brother in tblbrother info. It acts quirky. there are 19 record in
tblbrotherinfo. I run this code, and it creates 19 records the first and
second time. after that the number of records it create doubles everytime i
run the code. wierd. anyone know what is wrong with this?


thanks so much in advance.
stu
 
M

Marshall Barton

Stu said:
i have the folowing snippet that s giving me problems, i think it is with my
sql string.

Private Sub Command14_Click()
Dim strSQL As String

strSQL = "INSERT INTO tblTransactions ( [Brother ID], [Date], Type, Amount,
Designation, Description )" & _
"SELECT tblBrotherInfo.ID, [forms]![frmGlobalCharge]![txtDate] AS Expr1,
[forms]![frmGlobalCharge]![cboType] AS Expr2,
[forms]![frmGlobalCharge]![txtAmount] AS Expr3,
[forms]![frmGlobalCharge]![cboDesignation] AS Expr4,
[forms]![frmGlobalCharge]![txtDescription] AS Expr5 " & _
"FROM tblBrotherInfo LEFT JOIN tblTransactions ON tblBrotherInfo.ID =
tblTransactions.[Brother ID]; "

MsgBox stSQL
DoCmd.RunSQL strSQL

End Sub


Im trying to get this query to creat a record in tbltransaction for every
brother in tblbrother info. It acts quirky. there are 19 record in
tblbrotherinfo. I run this code, and it creates 19 records the first and
second time. after that the number of records it create doubles everytime i
run the code. wierd. anyone know what is wrong with this?
Click to expand...


The Join causes the select query to return a record for
every record in tblTransactions, so naturally it doubles.

It seems rather odd to run this more than once, are you sure
this is what you want to do??

If so, maybe you want to use DISTINCT in the select part.
 
S

SteveS

Also, the SQL string should have the references to the controls on the form
outside of the quotes:

(watch for line wrap)

strSQL = "INSERT INTO tblTransactions ( [Brother ID], [Date], Type, Amount,
Designation, Description )" & _
" SELECT tblBrotherInfo.ID, " & [Forms]![frmGlobalCharge]![txtDate] & " AS
Expr1, " & _
[Forms]![frmGlobalCharge]![cboType] & " AS Expr2, " & _
[Forms]![frmGlobalCharge]![txtAmount] & " AS Expr3, " & _
[Forms]![frmGlobalCharge]![cboDesignation] & " AS Expr4, " & _
[Forms]![frmGlobalCharge]![txtDescription] & " AS Expr5 " & _
"FROM tblBrotherInfo LEFT JOIN tblTransactions ON tblBrotherInfo.ID =
tblTransactions.[Brother ID]; "



And there is an R missing from the msgbox line

MsgBox stSQL should be: MsgBox strSQL
^^

HTH

--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)




Marshall said:
Stu said:
i have the folowing snippet that s giving me problems, i think it is with my
sql string.

Private Sub Command14_Click()
Dim strSQL As String

strSQL = "INSERT INTO tblTransactions ( [Brother ID], [Date], Type, Amount,
Designation, Description )" & _
"SELECT tblBrotherInfo.ID, [forms]![frmGlobalCharge]![txtDate] AS Expr1,
[forms]![frmGlobalCharge]![cboType] AS Expr2,
[forms]![frmGlobalCharge]![txtAmount] AS Expr3,
[forms]![frmGlobalCharge]![cboDesignation] AS Expr4,
[forms]![frmGlobalCharge]![txtDescription] AS Expr5 " & _
"FROM tblBrotherInfo LEFT JOIN tblTransactions ON tblBrotherInfo.ID =
tblTransactions.[Brother ID]; "

MsgBox stSQL
DoCmd.RunSQL strSQL

End Sub


Im trying to get this query to creat a record in tbltransaction for every
brother in tblbrother info. It acts quirky. there are 19 record in
tblbrotherinfo. I run this code, and it creates 19 records the first and
second time. after that the number of records it create doubles everytime i
run the code. wierd. anyone know what is wrong with this?
Click to expand...



The Join causes the select query to return a record for
every record in tblTransactions, so naturally it doubles.

It seems rather odd to run this more than once, are you sure
this is what you want to do??

If so, maybe you want to use DISTINCT in the select part.
Click to expand...
 
S

Stu Dongel

i guess some background would help... the query is part of a book keeping
database... and some times i will need to throw a credit or a debit to
every member of the database, that is why ill be needing to run it more than
once. thanks for all the help guys, lets se if this all works :)

stu

Also, the SQL string should have the references to the controls on the
form outside of the quotes:

(watch for line wrap)

strSQL = "INSERT INTO tblTransactions ( [Brother ID], [Date], Type,
Amount, Designation, Description )" & _
" SELECT tblBrotherInfo.ID, " & [Forms]![frmGlobalCharge]![txtDate] &
" AS Expr1, " & _
[Forms]![frmGlobalCharge]![cboType] & " AS Expr2, " & _
[Forms]![frmGlobalCharge]![txtAmount] & " AS Expr3, " & _
[Forms]![frmGlobalCharge]![cboDesignation] & " AS Expr4, " & _
[Forms]![frmGlobalCharge]![txtDescription] & " AS Expr5 " & _
"FROM tblBrotherInfo LEFT JOIN tblTransactions ON tblBrotherInfo.ID =
tblTransactions.[Brother ID]; "



And there is an R missing from the msgbox line

MsgBox stSQL should be: MsgBox strSQL
^^

HTH

--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)




Marshall said:
Stu said:
i have the folowing snippet that s giving me problems, i think it is with
my sql string.

Private Sub Command14_Click()
Dim strSQL As String

strSQL = "INSERT INTO tblTransactions ( [Brother ID], [Date], Type,
Amount, Designation, Description )" & _
"SELECT tblBrotherInfo.ID, [forms]![frmGlobalCharge]![txtDate] AS
Expr1, [forms]![frmGlobalCharge]![cboType] AS Expr2,
[forms]![frmGlobalCharge]![txtAmount] AS Expr3,
[forms]![frmGlobalCharge]![cboDesignation] AS Expr4,
[forms]![frmGlobalCharge]![txtDescription] AS Expr5 " & _
"FROM tblBrotherInfo LEFT JOIN tblTransactions ON tblBrotherInfo.ID =
tblTransactions.[Brother ID]; "

MsgBox stSQL
DoCmd.RunSQL strSQL

End Sub


Im trying to get this query to creat a record in tbltransaction for every
brother in tblbrother info. It acts quirky. there are 19 record in
tblbrotherinfo. I run this code, and it creates 19 records the first and
second time. after that the number of records it create doubles
everytime i run the code. wierd. anyone know what is wrong with this?
Click to expand...



The Join causes the select query to return a record for
every record in tblTransactions, so naturally it doubles.

It seems rather odd to run this more than once, are you sure
this is what you want to do??

If so, maybe you want to use DISTINCT in the select part.
Click to expand...
Click to expand...
 
Top