query runs on entire table not just current record

S

steve2jh

i am using the following code on the save command of a form & wish the
queries only to run for the current record on the form but they seem to run
for every record in the table, this takes about five minutes on the current
4000 odd records any suggestions

Option Compare Database

'------------------------------------------------------------
' save_exit_form1
'
'------------------------------------------------------------
Function save_exit_form1()
On Error GoTo save_exit_form1_Err

DoCmd.SAVE , ""
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Currency", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Currency1", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Net Calculator", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Net Calculator1", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Total Calculator", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Total Calculator1", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Vat Calculator", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Vat Calculator1", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "EuroConversion", acViewNormal, acEdit
End If
If (Forms!editorderdetails![Our Ref]) Then
DoCmd.OpenQuery "Rate Card Bonus", acViewNormal, acEdit
End If
DoCmd.Close acForm, "editorderdetails"


save_exit_form1_Exit:
Exit Function

save_exit_form1_Err:
MsgBox Error$
Resume save_exit_form1_Exit

End Function
 
J

John W. Vinson

i am using the following code on the save command of a form & wish the
queries only to run for the current record on the form but they seem to run
for every record in the table, this takes about five minutes on the current
4000 odd records any suggestions

The SAVE command *does not save the record*.

It saves any design changes *to the structure of the form*!

Use

DoCmd.RunCommand acCmdSaveRecord

to save the current record to disk.

As for your queries - the fact that you're running them from a Form does not
automagically tell them which record in the table should be used. To do so,
you would need a criterion such as

=[Forms]![YourFormName]![SomeControlName]

as a criteiron on a field which uniquely identifies the record, in the query.

What are these queries *doing*??? Could you post the SQL view of a
representative query? It seems *very* strange to need to run ten queries every
time a record is saved to disk!

John W. Vinson [MVP]
 
S

steve2jh

the sql view of one of the queries is as follows
UPDATE [Advert Code Table] AS [Advert Code Table_1], [Advert Code Table],
[Order Table] SET [Order Table].[Rate Card Bonus] = [Advert Code Table]![Rate
Card Bonus]
WHERE ((([Order Table].[Cost])=[Advert Code Table]![Cost]) AND (([Order
Table].[Advert Code])=[Advert Code Table]![Advert Code])) OR ((([Order
Table].[Cost])>[Advert Code Table]![Cost]));


this and all of the other queries run correctly other than the fact they run
against every record in the table not just against the current record on the
active form as I thought i had written in my previously posted code
John W. Vinson said:
i am using the following code on the save command of a form & wish the
queries only to run for the current record on the form but they seem to run
for every record in the table, this takes about five minutes on the current
4000 odd records any suggestions

The SAVE command *does not save the record*.

It saves any design changes *to the structure of the form*!

Use

DoCmd.RunCommand acCmdSaveRecord

to save the current record to disk.

As for your queries - the fact that you're running them from a Form does not
automagically tell them which record in the table should be used. To do so,
you would need a criterion such as

=[Forms]![YourFormName]![SomeControlName]

as a criteiron on a field which uniquely identifies the record, in the query.

What are these queries *doing*??? Could you post the SQL view of a
representative query? It seems *very* strange to need to run ten queries every
time a record is saved to disk!

John W. Vinson [MVP]
 
J

John W. Vinson

WHERE ((([Order Table].[Cost])=[Advert Code Table]![Cost]) AND (([Order
Table].[Advert Code])=[Advert Code Table]![Advert Code])) OR ((([Order
Table].[Cost])>[Advert Code Table]![Cost]));

This WHERE clause will run the query IF the Advert Codes are the same in the
two tables and the costs are also the same...

OR if there is ANY record anywhere in the Order Table with a Cost greater than
the Advert Code Table cost.

UPDATE [Advert Code Table] AS [Advert Code Table_1], [Advert Code Table],
[Order Table] SET [Order Table].[Rate Card Bonus] = [Advert Code Table]![Rate
Card Bonus]
WHERE ((([Order Table].[Cost])=[Advert Code Table]![Cost]) AND (([Order
Table].[Advert Code])=[Advert Code Table]![Advert Code])) OR ((([Order
Table].[Cost])>[Advert Code Table]![Cost]));

I do not understand why you're including the Advert Code Table twice. You're
not using the second instance anywhere in the query, for criteria or for
updating. You're also not joining the Order Table to the Advert Code Table_1
instance.

could you describe in words what this query is intended to accomplish?


John W. Vinson [MVP]
 
S

steve2jh

Thanks again john for your reply, I think I may be complicating my question
let me try putting it another way. I have a form with a unique field "Our
Ref" I wish to run a query on this record only I have set the criteria in the
query as follows [Forms]![editorderdetails]![Our Ref] I assumed this would
then only run the query against the record displayed on the active form and
not against all the records in the table, but it seems this is not the case
any help or comments would be appreciated.
 
J

John W. Vinson

Thanks again john for your reply, I think I may be complicating my question
let me try putting it another way. I have a form with a unique field "Our
Ref" I wish to run a query on this record only I have set the criteria in the
query as follows [Forms]![editorderdetails]![Our Ref] I assumed this would
then only run the query against the record displayed on the active form and
not against all the records in the table, but it seems this is not the case
any help or comments would be appreciated.

It will do so if the query is constructed correctly.

Your query is not constructed correctly.

If you care to answer the questions asked, we'll be glad to help you construct
it correctly.

John W. Vinson [MVP]
 
S

steve2jh

Thanks John, I have spotted the error of my ways & re-written the query based
on your advice all is now working fine
 

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

Top