query runs on entire table not just current record

[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

 

[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]

 

[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

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]

 

[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]

 

[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.

 

[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]

 

[steve2jh]

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