Query appends duplicate records, 2 times, 3 times, 4 times etc

R

Randy

This is a second posting. The first posting didn't solve my problem. When
I run this query the first record [BeginCertNolbl] (100 to 124)
[EndCertNolbl] is appended correctly. I get 24 records of 100, 101, 102,
all the way to 124. The next new record is enter (125 to 149) I get
appended to the table 125, 125, 126, 126,127, 127, 128, 128 etc.
duplicates. The next new record of 150 to 174, I get 150, 150, 150, 151,
151, 151, etc. Three times. All fields are numbers, and long intiger. I
did not create this append query, I got it off this newsgroup, I am a
beginner trying to learn. Help is appreciated..Thanks..

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]));
 
S

SteveS

Randy said:
This is a second posting. The first posting didn't solve my problem. When
I run this query the first record [BeginCertNolbl] (100 to 124)
[EndCertNolbl] is appended correctly. I get 24 records of 100, 101, 102,
all the way to 124. The next new record is enter (125 to 149) I get
appended to the table 125, 125, 126, 126,127, 127, 128, 128 etc.
duplicates. The next new record of 150 to 174, I get 150, 150, 150, 151,
151, 151, etc. Three times. All fields are numbers, and long intiger. I
did not create this append query, I got it off this newsgroup, I am a
beginner trying to learn. Help is appreciated..Thanks..

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,
CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]));
Click to expand...
Randy,

I read some of your previous posts, and I've been thinking about your
problem for a couple of days. Since no one else has replied, I'll take a run
at it.

You say that the query is working, so the problem is not the query. Now we
have to step back a little... HOW are you running the query? Are you running
code that has a Docmd.RunQuery () line, are you using a button on a form, or
......???

Somehow you have to loop 25 times (not 24... 100 thru 124 = 25) to insert
25 records. Which brings another question to mind: is
'CheckedOutCertificates' a query or a table? If it is a query, what is the
SQL for it?

You are using the 'Multiple-record append query' form of the Insert Into
statement. That means if the subquery (the Select part) returns 3 records,
then 3 new records will be appended to the table with the values specified;
if the Select returns 20 records, then 20 new records will be appended.
(Which is why I think that CheckedOutCertificates is a query - the number of
records keeps increasing.)

If you are using code to loop from [BeginCertNolbl] to [EndCertNolbl] and
you use controls on the form to limit records in the subquery, why can't you
use the controls on the form for the data and use a query that looks like
this:

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginningInitial )
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Cert,
[Forms]![frmCheckedOutCertificates]![cboInspector] AS Insp,
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] AS CertType,
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] AS DateOut,
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] AS BegInit;


Sorry to be so long winded.. But while typing this I, thought of a way might
work with your current query.

Try changing Select to Select Top 1....
It would look like this:

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT TOP 1 [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS
Expr1,CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,CheckedOutCertificates.BeginningInitial
FROM CheckedOutCertificates
WHERE
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cboInspector]) AND
((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cboTypeofCert]) AND
((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl]));


HTH

Steve
 
K

Kerman

Sorry Steve, I pasted the wrong query, another one I was playing with. I'll
paste the correct one. I changed the SELECT to SELECT TOP 1, didn't change
anything. I am running the query from my form [frmCheckedOutCertificates]
after update event. No cmd button. "CheckedOutCertificates" is a table as
well as "CheckedOutCertsAllNumbers".
What I am trying to do is enter a beginning and an ending number in my
[frmCheckedoutCertificates](Control Source "BeginCertNo" and "EndCertNo"
table [CheckedOutCertificates]. When I enter a new record, the after update
event of my form will run the following query which will append to table
[CheckedOutCertificatesallNumbers] all the cert numbers from lets say 100 to
124, (100, 101, 102, 103....124) in one field of [CertNo] I hope this
explains what I am trying to achieve....Thanks...Randy

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT TOP 1 [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS
Expr1, [CheckedOutCertificates].[Inspector],
[CheckedOutCertificates].[TypeOfCert],
[CheckedOutCertificates].[DateCheckedOut],
[CheckedOutCertificates].[BeginningInitial]
FROM Num, CheckedOutCertificates
WHERE
((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=[Forms]![frmCh
eckedOutCertificates]![EndCertNolbl]) And
(([CheckedOutCertificates].[Inspector])=[Forms]![frmCheckedOutCertificates]!
[cboInspector]) And
(([CheckedOutCertificates].[TypeOfCert])=[Forms]![frmCheckedOutCertificates]
![cboTypeofCert]) And
(([CheckedOutCertificates].[DateCheckedOut])=[Forms]![frmCheckedOutCertifica
tes]![DateCheckedOutlbl]) And
(([CheckedOutCertificates].[BeginningInitial])=[Forms]![frmCheckedOutCertifi
cates]![BeginningInitiallbl]))
ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];
SteveS said:
Randy said:
This is a second posting. The first posting didn't solve my problem. When
I run this query the first record [BeginCertNolbl] (100 to 124)
[EndCertNolbl] is appended correctly. I get 24 records of 100, 101, 102,
all the way to 124. The next new record is enter (125 to 149) I get
appended to the table 125, 125, 126, 126,127, 127, 128, 128 etc.
duplicates. The next new record of 150 to 174, I get 150, 150, 150, 151,
151, 151, etc. Three times. All fields are numbers, and long intiger. I
did not create this append query, I got it off this newsgroup, I am a
beginner trying to learn. Help is appreciated..Thanks..
Click to expand...
Randy,

I read some of your previous posts, and I've been thinking about your
problem for a couple of days. Since no one else has replied, I'll take a run
at it.

You say that the query is working, so the problem is not the query. Now we
have to step back a little... HOW are you running the query? Are you running
code that has a Docmd.RunQuery () line, are you using a button on a form, or
.....???

Somehow you have to loop 25 times (not 24... 100 thru 124 = 25) to insert
25 records. Which brings another question to mind: is
'CheckedOutCertificates' a query or a table? If it is a query, what is the
SQL for it?

You are using the 'Multiple-record append query' form of the Insert Into
statement. That means if the subquery (the Select part) returns 3 records,
then 3 new records will be appended to the table with the values specified;
if the Select returns 20 records, then 20 new records will be appended.
(Which is why I think that CheckedOutCertificates is a query - the number of
records keeps increasing.)

If you are using code to loop from [BeginCertNolbl] to [EndCertNolbl] and
you use controls on the form to limit records in the subquery, why can't you
use the controls on the form for the data and use a query that looks like
this:

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginningInitial )
SELECT [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Cert,
[Forms]![frmCheckedOutCertificates]![cboInspector] AS Insp,
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] AS CertType,
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] AS DateOut,
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] AS BegInit;


Sorry to be so long winded.. But while typing this I, thought of a way might
work with your current query.

Try changing Select to Select Top 1....
It would look like this:

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT TOP 1 [Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS
Expr1,CheckedOutCertificates.Inspector, CheckedOutCertificates.TypeOfCert,
CheckedOutCertificates.DateCheckedOut,CheckedOutCertificates.BeginningInitia
l
FROM CheckedOutCertificates
WHERE
Click to expand...
(((CheckedOutCertificates.Inspector)=[Forms]![frmCheckedOutCertificates]![cb
oInspector]) AND((CheckedOutCertificates.TypeOfCert)=[Forms]![frmCheckedOutCertificates]![cb
oTypeofCert]) AND((CheckedOutCertificates.DateCheckedOut)=[Forms]![frmCheckedOutCertificates]
![DateCheckedOutlbl]) AND
((CheckedOutCertificates.BeginningInitial)=[Forms]![frmCheckedOutCertificate
s]![BeginningInitiallbl]));


HTH

Steve
Click to expand...
 
S

SteveS

Randy,

Please, post the Form_AfterUpdate code.

With what you posted here, I think I understand what is happening. Here
goes...

What you want to do: Create a certain number of NEW records (usually 25 at
a time) in table "CheckedOutCertsAllNumbers", filling in 5 fields: CertNo,
Inspector, TypeOfCert, DateCheckedOut & BeginingCertInitial.

What you have: a form, "frmCheckedoutCertificates", bound to table
"CheckedOutCertificates" with (at least) 6 control on it: two controls,
"BeginCertNolbl" and "EndCertNolbl" are unbound and 4 controls
"cboInspector", "cboTypeofCert", "DateCheckedOutlbl" & "BeginningInitiallbl"
are bound to fields in the table.

(Is "DateCheckedOutlbl" really a combo box and the field is type long int?)


You have code in the form after update event that inserts records into table
"CheckedOutCertificates".

How am I doing so far? Now the good part.

It's 9AM. You open the form and create a new record: you enter the Beg & End
Cert numbers, then fill in the 4 controls. And let's say the
"DateCheckedOutlbl" is today's date.

When you change to a different record, what just created a new record in
table "CheckedOutCertificates". The form after update event fires off and
runs the query. The sub query finds a unique record (you just entered it) and
somehow (haven't seen the code, hint - hint ;) enters 25 new records into
table "CheckedOutCertificates".

Now it's 10:30AM. You used up the 25 Certs you created a 9AM. Time to create
more Certs.

You open the form and fill in the new Cert number range. The other 4
controls have the same info as at 9AM.

When you move off of the new record, the info is added to table
"CheckedOutCertificates". You now have *TWO* records that are identical. The
form after update event fires and runs the query. The sub-query now finds two
records, so, because you are using the "Multiple-record append query" form of
the 'Insert Into' statement, you now get *TWO* records with the same Cert
number and the other fields inserted into the table
"CheckedOutCertsAllNumbers".

At 1:30PM, well you can figure out what happens for the third set of Cert
numbers today.


Just for grins, I threw together a different way to make the new records.
(Watch for line wrap...)

'------Begin code----------------------------
Private Sub Form_AfterUpdate()

Dim strSQL As String
Dim NewCertNum As Long
Dim CertBeg As Long
Dim CertEnd As Long

'get the beginning and ending Cert numbers
CertBeg = Me.BeginCertNolbl
CertEnd = Me.EndCertNolbl

For NewCertNum = CertBeg To CertEnd
' create the SQL statement to insert the new records
strSQL = "INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector,
TypeOfCert, DateCheckedOut, BeginingCertInitial )"
strSQL = strSQL & " Values ( "
strSQL = strSQL & NewCertNum & ", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboInspector] &
", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboTypeofCert]
& ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] & ");"

' now do it
CurrentDb.Execute strSQL

Next
End Sub
'------End code----------------------------

Does this help any??

HTH

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


Kerman said:
Sorry Steve, I pasted the wrong query, another one I was playing with. I'll
paste the correct one. I changed the SELECT to SELECT TOP 1, didn't change
anything. I am running the query from my form [frmCheckedOutCertificates]
after update event. No cmd button. "CheckedOutCertificates" is a table as
well as "CheckedOutCertsAllNumbers".
What I am trying to do is enter a beginning and an ending number in my
[frmCheckedoutCertificates](Control Source "BeginCertNo" and "EndCertNo"
table [CheckedOutCertificates]. When I enter a new record, the after update
event of my form will run the following query which will append to table
[CheckedOutCertificatesallNumbers] all the cert numbers from lets say 100 to
124, (100, 101, 102, 103....124) in one field of [CertNo] I hope this
explains what I am trying to achieve....Thanks...Randy

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT TOP 1 [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS
Expr1, [CheckedOutCertificates].[Inspector],
[CheckedOutCertificates].[TypeOfCert],
[CheckedOutCertificates].[DateCheckedOut],
[CheckedOutCertificates].[BeginningInitial]
FROM Num, CheckedOutCertificates
WHERE
((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=[Forms]![frmCh
eckedOutCertificates]![EndCertNolbl]) And
(([CheckedOutCertificates].[Inspector])=[Forms]![frmCheckedOutCertificates]!
[cboInspector]) And
(([CheckedOutCertificates].[TypeOfCert])=[Forms]![frmCheckedOutCertificates]
![cboTypeofCert]) And
(([CheckedOutCertificates].[DateCheckedOut])=[Forms]![frmCheckedOutCertifica
tes]![DateCheckedOutlbl]) And
(([CheckedOutCertificates].[BeginningInitial])=[Forms]![frmCheckedOutCertifi
cates]![BeginningInitiallbl]))
ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];
Click to expand...
 
S

SteveS

Sorry, working nights.

This paragraph should read

When you change to a different record, a new record is inserted into
table "CheckedOutCertificates". The form after update event fires off and
runs the query. The sub query finds a unique record (you just entered it) and
somehow (haven't seen the code, hint - hint ;) enters 25 new records into
table "CheckedOutCertsAllNumbers".

(was wrong table name ^^)

Steve
 
K

Kerman

I tried your new code in after update of my frmCheckedoutCertificates. I
get an error of: "Runtime error 3075 Syntax error (Missing operator) in
query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected from
"cmbotypeofcert" from table "TypeofCertificates". I don't know why this is
showing up..Thanks a lot for your help...Randy
 
S

SteveS

I don't know why you got the error.....

I'll ask one more time...

PLEASE post your the Form_AfterUpdate event code


My code is based on the SQL you posted on 1/31; I tested it and it worked.
If the control names on your form are not the same as the control names in my
example, you have to change the control names in my example to match the
names of the controls on your form.


In you post on 1/29, you said:

"All fields are numbers, and long intiger. "

Now you say:
query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected from
"cmbotypeofcert" from table "TypeofCertificates".
Click to expand...


It looks like the the control name has changed and the data type is actually
String.

If the data type is String (text), then it needs to be enclosed by quotes.

For my example code it would look like this:

'----snip
.... ![cboInspector] & ", '"
'-----snip

that is: ampersand/space/double quote/comma/space/single quote/double quote

'---snip
... ![cboTypeofCert] & "', "
'---snip

that is: ampersand/space/double quote/single quote/comma/space/double quote


Please post your code or delete the data in your database, compact it, zip
it and sent it to me.

Good luck

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



Kerman said:
I tried your new code in after update of my frmCheckedoutCertificates. I
get an error of: "Runtime error 3075 Syntax error (Missing operator) in
query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected from
"cmbotypeofcert" from table "TypeofCertificates". I don't know why this is
showing up..Thanks a lot for your help...Randy
Click to expand...
 
K

Kerman

Here is the code you asked for, I tried e-mailing the db to you but it was
not accepted by the server. I Tried the reply to, not reply to the
group,,Thanks..Randy

Private Sub Form_AfterUpdate()
Dim strSQL As String
Dim NewCertNum As Long
Dim CertBeg As Long
Dim CertEnd As Long

'get the beginning and ending Cert numbers
CertBeg = Me.BeginCertNolbl
CertEnd = Me.EndCertNolbl

For NewCertNum = CertBeg To CertEnd
' create the SQL statement to insert the new records
strSQL = "INSERT INTO CheckedOutCertsAllNumbers (
CertNo,Inspector,TypeOfCert,DateCheckedOut,BeginingCertInitial )"
strSQL = strSQL & " Values ( "
strSQL = strSQL & NewCertNum & ", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboInspector] &
", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboTypeofCert]
& ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] & "); "

' now do it
CurrentDb.Execute strSQL

Next
End Sub

SteveS said:
I don't know why you got the error.....

I'll ask one more time...

PLEASE post your the Form_AfterUpdate event code


My code is based on the SQL you posted on 1/31; I tested it and it worked.
If the control names on your form are not the same as the control names in my
example, you have to change the control names in my example to match the
names of the controls on your form.


In you post on 1/29, you said:

"All fields are numbers, and long intiger. "

Now you say:
query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected from
"cmbotypeofcert" from table "TypeofCertificates".
Click to expand...


It looks like the the control name has changed and the data type is actually
String.

If the data type is String (text), then it needs to be enclosed by quotes.

For my example code it would look like this:

'----snip
... ![cboInspector] & ", '"
'-----snip

that is: ampersand/space/double quote/comma/space/single quote/double quote

'---snip
... ![cboTypeofCert] & "', "
'---snip

that is: ampersand/space/double quote/single quote/comma/space/double quote


Please post your code or delete the data in your database, compact it, zip
it and sent it to me.

Good luck

Steve
Click to expand...
 
S

SteveS

OK, that is the code I wrote.

What is the code you had in the form_afterupdate event before you
changed it to the code I wrote?

My email is: sanfu_NO@SPAM_techie.com

Remove the _NO and SPAM_

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


Here is the code you asked for, I tried e-mailing the db to you but it was
not accepted by the server. I Tried the reply to, not reply to the
group,,Thanks..Randy

Private Sub Form_AfterUpdate()
Dim strSQL As String
Dim NewCertNum As Long
Dim CertBeg As Long
Dim CertEnd As Long

'get the beginning and ending Cert numbers
CertBeg = Me.BeginCertNolbl
CertEnd = Me.EndCertNolbl

For NewCertNum = CertBeg To CertEnd
' create the SQL statement to insert the new records
strSQL = "INSERT INTO CheckedOutCertsAllNumbers (
CertNo,Inspector,TypeOfCert,DateCheckedOut,BeginingCertInitial )"
strSQL = strSQL & " Values ( "
strSQL = strSQL & NewCertNum & ", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboInspector] &
", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboTypeofCert]
& ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] & "); "

' now do it
CurrentDb.Execute strSQL

Next
End Sub

I don't know why you got the error.....

I'll ask one more time...

PLEASE post your the Form_AfterUpdate event code


My code is based on the SQL you posted on 1/31; I tested it and it worked.
If the control names on your form are not the same as the control names in
my

example, you have to change the control names in my example to match the
names of the controls on your form.


In you post on 1/29, you said:

"All fields are numbers, and long intiger. "

Now you say:

query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected
Click to expand...
from
"cmbotypeofcert" from table "TypeofCertificates".
Click to expand...


It looks like the the control name has changed and the data type is
actually

String.

If the data type is String (text), then it needs to be enclosed by quotes.

For my example code it would look like this:

'----snip
... ![cboInspector] & ", '"
'-----snip

that is: ampersand/space/double quote/comma/space/single quote/double
quote

'---snip
... ![cboTypeofCert] & "', "
'---snip

that is: ampersand/space/double quote/single quote/comma/space/double
quote


Please post your code or delete the data in your database, compact it, zip
it and sent it to me.

Good luck

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



:

I tried your new code in after update of my frmCheckedoutCertificates.
Click to expand...
I
get an error of: "Runtime error 3075 Syntax error (Missing operator) in
query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected
Click to expand...
from
"cmbotypeofcert" from table "TypeofCertificates". I don't know why this
Click to expand...
is
showing up..Thanks a lot for your help...Randy
"SteveS" <sanfu at techie dot com> wrote in message

Sorry, working nights.

This paragraph should read

When you change to a different record, a new record is inserted into
table "CheckedOutCertificates". The form after update event fires off
Click to expand...
and
runs the query. The sub query finds a unique record (you just entered
Click to expand...
it)
and

somehow (haven't seen the code, hint - hint ;) enters 25 new records
Click to expand...
into
table "CheckedOutCertsAllNumbers".

(was wrong table name ^^)

Steve
Click to expand...
Click to expand...
Click to expand...
 
K

Kerman

Here it is,,thanks..

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
[CheckedOutCertificates].[Inspector], [CheckedOutCertificates].[TypeOfCert],
[CheckedOutCertificates].[DateCheckedOut],
[CheckedOutCertificates].[BeginningInitial]
FROM Num, CheckedOutCertificates
WHERE
((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=[Forms]![frmCh
eckedOutCertificates]![EndCertNolbl])
And
(([CheckedOutCertificates].[Inspector])=[Forms]![frmCheckedOutCertificates]!
[cboInspector])
And
(([CheckedOutCertificates].[TypeOfCert])=[Forms]![frmCheckedOutCertificates]
![cboTypeofCert])
And
(([CheckedOutCertificates].[DateCheckedOut])=[Forms]![frmCheckedOutCertifica
tes]![DateCheckedOutlbl])
And
(([CheckedOutCertificates].[BeginningInitial])=[Forms]![frmCheckedOutCertifi
cates]![BeginningInitiallbl]))
ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];

SteveS said:
OK, that is the code I wrote.

What is the code you had in the form_afterupdate event before you
changed it to the code I wrote?

My email is: sanfu_NO@SPAM_techie.com

Remove the _NO and SPAM_

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


Here is the code you asked for, I tried e-mailing the db to you but it was
not accepted by the server. I Tried the reply to, not reply to the
group,,Thanks..Randy

Private Sub Form_AfterUpdate()
Dim strSQL As String
Dim NewCertNum As Long
Dim CertBeg As Long
Dim CertEnd As Long

'get the beginning and ending Cert numbers
CertBeg = Me.BeginCertNolbl
CertEnd = Me.EndCertNolbl

For NewCertNum = CertBeg To CertEnd
' create the SQL statement to insert the new records
strSQL = "INSERT INTO CheckedOutCertsAllNumbers (
CertNo,Inspector,TypeOfCert,DateCheckedOut,BeginingCertInitial )"
strSQL = strSQL & " Values ( "
strSQL = strSQL & NewCertNum & ", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboInspector] &
", "
strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![cboTypeofCert]
& ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] & "); "

' now do it
CurrentDb.Execute strSQL

Next
End Sub

I don't know why you got the error.....

I'll ask one more time...

PLEASE post your the Form_AfterUpdate event code


My code is based on the SQL you posted on 1/31; I tested it and it worked.
If the control names on your form are not the same as the control names
Click to expand...
in

my
example, you have to change the control names in my example to match the
names of the controls on your form.


In you post on 1/29, you said:

"All fields are numbers, and long intiger. "

Now you say:


query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected
from

"cmbotypeofcert" from table "TypeofCertificates".


It looks like the the control name has changed and the data type is
actually

String.

If the data type is String (text), then it needs to be enclosed by quotes.

For my example code it would look like this:

'----snip
... ![cboInspector] & ", '"
'-----snip

that is: ampersand/space/double quote/comma/space/single quote/double
quote

'---snip
... ![cboTypeofCert] & "', "
'---snip

that is: ampersand/space/double quote/single quote/comma/space/double
quote


Please post your code or delete the data in your database, compact it, zip
it and sent it to me.

Good luck

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



:


I tried your new code in after update of my frmCheckedoutCertificates.
I

get an error of: "Runtime error 3075 Syntax error (Missing operator) in
query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected
from

"cmbotypeofcert" from table "TypeofCertificates". I don't know why
Click to expand...
this

is
showing up..Thanks a lot for your help...Randy
"SteveS" <sanfu at techie dot com> wrote in message

Sorry, working nights.

This paragraph should read

When you change to a different record, a new record is inserted into
table "CheckedOutCertificates". The form after update event fires off
and

runs the query. The sub query finds a unique record (you just entered
it)

and

somehow (haven't seen the code, hint - hint ;) enters 25 new records
into

table "CheckedOutCertsAllNumbers".

(was wrong table name ^^)

Steve
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

That is the SQL of your query. It, by itself, can't "run".

Open the form "frmCheckedOutCertificates" in design mode.
Open properties.
Click on the EVENTS tab.
Click on AFTERUPDATE.
Click on the ellipsis (...) on the right.

This is the Form_AfterUpdate code.

Copy everything between:

Private Sub Form_AfterUpdate()

and

End Sub



It would be easier if you sent me the database. It must be less than 1
megabyte, so if it is larger, please zip it. I an using Access 2000.



My email is: sanfu_NO@SPAM_techie.com
Remove the _NO and SPAM_


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

Here it is,,thanks..

INSERT INTO CheckedOutCertsAllNumbers ( CertNo, Inspector, TypeOfCert,
DateCheckedOut, BeginingCertInitial )
SELECT [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl] AS Expr1,
[CheckedOutCertificates].[Inspector], [CheckedOutCertificates].[TypeOfCert],
[CheckedOutCertificates].[DateCheckedOut],
[CheckedOutCertificates].[BeginningInitial]
FROM Num, CheckedOutCertificates
WHERE
((([N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl])<=[Forms]![frmCh
eckedOutCertificates]![EndCertNolbl])
And
(([CheckedOutCertificates].[Inspector])=[Forms]![frmCheckedOutCertificates]!
[cboInspector])
And
(([CheckedOutCertificates].[TypeOfCert])=[Forms]![frmCheckedOutCertificates]
![cboTypeofCert])
And
(([CheckedOutCertificates].[DateCheckedOut])=[Forms]![frmCheckedOutCertifica
tes]![DateCheckedOutlbl])
And
(([CheckedOutCertificates].[BeginningInitial])=[Forms]![frmCheckedOutCertifi
cates]![BeginningInitiallbl]))
ORDER BY [N]+[Forms]![frmCheckedOutCertificates]![BeginCertNolbl];

OK, that is the code I wrote.

What is the code you had in the form_afterupdate event before you
changed it to the code I wrote?

My email is: sanfu_NO@SPAM_techie.com

Remove the _NO and SPAM_

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



was
Click to expand...

[Forms]![frmCheckedOutCertificates]![cboInspector] &
", "
strSQL = strSQL &
Click to expand...
[Forms]![frmCheckedOutCertificates]![cboTypeofCert]
& ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] & "); "

' now do it
CurrentDb.Execute strSQL

Next
End Sub

"SteveS" <sanfu at techie dot com> wrote in message


I don't know why you got the error.....

I'll ask one more time...

PLEASE post your the Form_AfterUpdate event code


My code is based on the SQL you posted on 1/31; I tested it and it
Click to expand...
worked.
If the control names on your form are not the same as the control names
Click to expand...
in
my


example, you have to change the control names in my example to match the
names of the controls on your form.


In you post on 1/29, you said:

"All fields are numbers, and long intiger. "

Now you say:



query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected

from


"cmbotypeofcert" from table "TypeofCertificates".


It looks like the the control name has changed and the data type is

actually


String.

If the data type is String (text), then it needs to be enclosed by
Click to expand...
quotes.
For my example code it would look like this:

'----snip
... ![cboInspector] & ", '"
'-----snip

that is: ampersand/space/double quote/comma/space/single quote/double

quote


'---snip
... ![cboTypeofCert] & "', "
'---snip

that is: ampersand/space/double quote/single quote/comma/space/double

quote


Please post your code or delete the data in your database, compact it,
Click to expand...
zip
it and sent it to me.

Good luck

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



:



I tried your new code in after update of my frmCheckedoutCertificates.

I


get an error of: "Runtime error 3075 Syntax error (Missing operator)
Click to expand...
in
query expression "Fv 205"" Fv 205 is a "typeofcert" that is selected

from


"cmbotypeofcert" from table "TypeofCertificates". I don't know why
Click to expand...
this
is


showing up..Thanks a lot for your help...Randy
"SteveS" <sanfu at techie dot com> wrote in message


Sorry, working nights.

This paragraph should read

When you change to a different record, a new record is inserted into
table "CheckedOutCertificates". The form after update event fires off

and


runs the query. The sub query finds a unique record (you just entered

it)


and


somehow (haven't seen the code, hint - hint ;) enters 25 new records

into


table "CheckedOutCertsAllNumbers".

(was wrong table name ^^)

Steve
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

Randy,

I received your database. The problem is there were integer, text and
date fields. When you use SQL in code, text needs to be enclosed with
quotes (single or double) and dates need to be enclosed with pound (#)
signs.

I modified the code and sent the DB back to you. It should work now..

HTH
 

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

Append query appends twice. Why? 7
Whats wrong with this SQL query? 4
Iif statement in SQL append query? 1
Need help with form coding (Date Problems) 1

Top