Query based on Multiple Forms

[JC3]

Hi there,

I have a query to update data to a table which takes the account ID from the form and updates. This works fine as is.

The problem is that the update can be made on several different forms and was wondering whether there was a way to conditionally select the ID in the query based on what form is open. I have tried some combinations of IIF statements but every time I get a parameter prompt for the Forms that are not open.

Is there any way around this or do I just need a separate query for each form. Any advice would be appreciated.

JC

 

[RuralGuy]

You might post the SQL for the query so that we can get a better idea what you are doing.

 

[JC3]

Hi Rural Guy

I am not a SQL user, normally try to stick to Queries and simple VBA. As an example I attach a note of the two SQL queries. The only difference between them is which Form the OverheadNo is taken from. When I combine them in the third query then it always asks for the Form which is not open.

Update Engineer for New Accounts

UPDATE Overheads INNER JOIN Engineer ON Overheads.EngineerCode = Engineer.EngineerCode SET Overheads.Engineer = Engineer!Engineer, Overheads.Region = Engineer!Region
WHERE (((Overheads.OverheadNo)=[Forms]![OverheadMainForm]![OverheadNo]));

Update Engineer for Existing Accounts

UPDATE Overheads INNER JOIN Engineer ON Overheads.EngineerCode = Engineer.EngineerCode SET Overheads.Engineer = Engineer!Engineer, Overheads.Region = Engineer!Region
WHERE (((Overheads.OverheadNo)=[Forms]![OverHeadViewForm]![OverheadNo]));

Combined

UPDATE Overheads INNER JOIN Engineer ON Overheads.EngineerCode = Engineer.EngineerCode SET Overheads.Engineer = Engineer!Engineer, Overheads.Region = Engineer!Region
WHERE (((Overheads.OverheadNo)=[Forms]![OverHeadViewForm]![OverheadNo])) OR (((Overheads.OverheadNo)=[Forms]![OverHeadMainForm]![OverheadNo]));

Any ideas would be appreciated.

Thanks

JC3

 

[RuralGuy]

As much as I dislike them, the only thing I can think of right now is a Global Public Variable in a Standard module. You set it to:

MyGlobalVariable = Me.OverheadNo from whatever form you are using and then run the query:

UPDATE Overheads
INNER JOIN Engineer ON Overheads.EngineerCode = Engineer.EngineerCode
SET Overheads.Engineer = Engineer!Engineer, Overheads.Region = Engineer!Region
WHERE ((Overheads.OverheadNo)=MyGlobalVariable);

 

[JC3]

Why don't you like them?

JC3

 

[RuralGuy]

I prefer tightly modular code. It is just a personal preference. I simply try to avoid them whenever possible but there are times when nothing else will do.

 

[JC3]

Thanks Rural Guy.

JC3

 

[RuralGuy]

You're welcome.