Lookup value from two tables, then incriment to next highest of the 2? 1

[jmbcreative]

I need to use strMax to look up and compare the values in two different tables and then incriment the higher of the two tables.

Basically I have a table "HardwareList" that has a field "ControlID". I also have a table "HardwareAdd" that has a field "ControlID". I need to look up the controlID in both tables and which ever one contains the highest number, I need to incriment that number to the next highest and insert it into a field on my form "frmHardwareAdd". Make sense?

 

[BoxHead]

DMax("[ControlID]","HardwareList") should return the maximum ControlId in that table.

DMax("[ControlID]","HardwareAdd") should return the maximum ControlID in that table.

Use the IIf statement to determine the higher of the two and add 1.

Iif(DMax("[ControlID]","HardwareList") > DMax("[ControlID]","HardwareAdd"), 1+ DMax("[ControlID]","HardwareList"), 1+ DMax("[ControlID]","HardwareAdd"))

HTH

John





When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[jmbcreative]

Excellent! Thanks. I believe that will work. I already have an if statement within this event. I believe that I can create a module for this and have the "then" statement reference this, but how do I do that?

 

[BoxHead]

You confused me. The IIf statement contains

IIf( Condition to check, Value if Condition is True, Value if Condition is False)

So the statement from the last post basically translates to, "If the largest ControlId in the HardwareList is greater than the largest ControlID in the HardwareAdd table, add 1 to the largest ControlId number in the HardwareList table. If the largest ControlID in the HardwareList table is NOT larger than the largest ControlID in the HardwareAdd table, then add 1 to the largest ControlID number in the HardwareAdd table."

If you have a form where people are adding new items, I'd have a 'Save New Item' button and assign the ControlID as part of that bottun's click event.

You can use the wizard to create the Save New Item button if you don't already have one and then add the following code.

If isNull([txtControlID]) Then
ControlID = Iif(DMax("[ControlID]","HardwareList") > DMax("[ControlID]","HardwareAdd"), 1+ DMax("[ControlID]","HardwareList"), 1+ DMax("[ControlID]","HardwareAdd"))
End If
[COLOR=green]'Continue the save action[/color]

HTH

John

 

[PHV]

Instead of calling 4 times the pretty slow DMax function you may consider another way:
Create a query named, say, qryGetMaxControlID with the following SQL code:
SELECT Max(ControlID) AS LastID FROM HardwareList
UNION SELECT Max(ControlID) FROM HardwareAdd;

And now your expression:
1 + Nz(DMax("LastID", "qryGetMaxControlID"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886