Hi Jasper,
on the Click event for the command button, before you do other things:
'~~~~~~~~~~~~~~~~~~
if cLng(nz(controlname.column(4),"")) = 0 then
msgbox "Your message",,"Your title"
exit sub
end if
'~~~~~~~~~~~~~~~~~~
WHERE
controlname is the Name propert of the combobox
Make sure the ColumnCount property of the combobox is set to --> 5
You can use the ColumnWidths property to assign column widths --
separate with semi-colon ;
Access Basics has more information about this
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Jasper Recto wrote:
Crystal,
The SQL query for the qryPartRev is not as hairy as the other:
SELECT PUB_PartRev.Company,
PUB_PartRev.PartNum,
PUB_PartRev.RevisionNum,
PUB_PartRev.EffectiveDate,
PUB_PartPlant.MfgLotSize
FROM PUB_PartRev INNER JOIN PUB_PartPlant ON (PUB_PartRev.Company =
PUB_PartPlant.Company) AND (PUB_PartRev.PartNum = PUB_PartPlant.PartNum)
WHERE (((PUB_PartRev.Company)="Loc") AND
((PUB_PartRev.PartNum)=[Forms]![frmBOM]![PartNumber]))
ORDER BY PUB_PartRev.EffectiveDate DESC;
The mfgLotsize column is the 5th one over so it's number 4 (I think)
Thanks!
Jasper
Hi Jasper,
What is the SQL for qryPartRev ? Is that the monstrous thing that you
posted? If so, you should use something much simpler for a RowSource!
Get in the habit of basing queries on tables -- only use a query in the
source of another query if you need to
need column number for MfgLotSize ...column indexes start at 0 (zero)
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Jasper Recto wrote:
The RowSource Type for the combo Table/Query
The RowSource is qryPartRev and the bound column is 1.
I have not read your Access Basics but I definitely will!!
Thanks!
Hi Jasper,
you're welcome
"error say that's its so complex and to simplify it"
you have a query based on other queries -- try going directly to your
tables...
"On the 'On Exit' option of the combo box"
what is the RowSource for the combo? Need to know to get the column
number for MfgLotSize
anyway, if you do not want to allow the user to pick a certain value
in a combo, you would use the combo BeforeUpdate event -- and CANCEL
it if the user needs to make another choice
"If its possible, maybe even to disable the button that runs the
whole query for the form until a new revision is chosen that does not
have a mfglotsize of zero."
on the Click event for the command button, you can test to make sure
the data is valid before it executes
Have you read Access Basics in my siggy?
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Jasper Recto wrote:
Crystal,
The formula works great on its own but because the query is so
complex, I get an error say that's its so complex and to simplify
it.
I have another idea on how I might avoid this.
The form this is on is related to the BomCost qry.
There is a part number field that a user enters in a number.
Next to that is a combo box that has a PartRev query as a
datasource. That query grabs the part number entered on the form and
list out the available revision numbers. That query also has the
MfgLotSize.
For the Part Revision combo box, I have an event procedure on the
'On Enter' option that says this:
Me.RevOption.Requery.
This reruns the PartRev query and displays the results in the combo
box.
On the 'On Exit' option of the combo box, I would like a simple
formula that checks to see if the mfglotsize is equal to 0.
If it is, than a message box would appear saying that its equal to
zero and not to click the button to run the form.
If its possible, maybe even to disable the button that runs the
whole query for the form until a new revision is chosen that does
not have a mfglotsize of zero.
Any suggestions on this?
Thanks for all your help. As you can tell I'm fairly new to all of
this and just trying to work my way through it.
THANKS!!
Jasper
hi Jasper,
woah! that is some query!
In this query, you are dividing by:
qryBOMData!MfgLotSize
... and then, you also have qryBOMData and qryBOMCostsSingleLevel
that could have equations in them.
Like Klatuu said (in another post), you need to first test to make
sure that what you are dividing by is defined and also a non-zero
number...
here is your SQL formatted so it is easier to read:
SELECT qryBOMData.PartNum
, qryBOMData.OprSeq
, qryBOMData.OpCode
, qryBOMData.ProdStandard
, qryBOMData.StdFormat
, qryBOMData.EstProdHours
, qryBOMData.ProdCrewSize
, qryBOMData.ProdLRate
, IIf(qryBOMData!StdFormat="HR",
(qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdLRate)
/qryBOMData!MfgLotSize
,qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdLRate)
AS ProdLaborCost
, qryBOMData.EstSetHours
, qryBOMData.SetUpCrewSize
, qryBOMData.SetupLRate
,
qryBOMData!EstSetHours*qryBOMData!SetUpCrewSize*qryBOMData!SetupLRate
/qryBOMData!MfgLotSize AS SetupLaborCost
, qryBOMData.WCCode
, qryBOMData.ProdBurRate
,
IIf(qryBOMData!StdFormat="HR",(qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdBurRate)
/qryBOMData!MfgLotSize
,qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdBurRate)
AS ProdBurCost
, qryBOMData.SetupBurRate
,
qryBOMData!EstSetHours*qryBOMData!SetUpCrewSize*qryBOMData!SetupBurRate
/qryBOMData!MfgLotSize AS SetupBurCost
, qryBOMData.EstUnitCost
, qryBOMData.StdMaterialCost
, qryBOMData.RevisionNum
, Format(DSum(" [ProdLaborCost]","qryBOMCosts"),".0000") AS
ProdLaborTTL
, Format(DSum(" [SetupLaborCost] ","qryBOMCosts"),".0000") AS
SetupLaborTTL
, Format(DSum(" [ProdBurCost] ","qryBOMCosts"),".0000") AS
ProdBurTTL
, Format(DSum(" [SetupBurCost] ","qryBOMCosts"),".0000") AS
SetupBurTTL
, qryBOMData.StdMtlBurCost
, qryBOMData.PartDescription
, qryBOMData.MfgLotSize
, qryBOMCostsSingleLevel.MtlCost
, qryBOMCostsSingleLevel.MtlBurCost
, Format(DSum("[EstUnitCost]","qryBOMCosts"),".00") AS OVSSum
, qryBOMCostsSingleLevel.PartCost
FROM qryBOMData
INNER JOIN qryBOMCostsSingleLevel
ON qryBOMData.PartNum = qryBOMCostsSingleLevel.PartNum;
~~~
for instance, instead of doing this:
qryBOMData!EstSetHours*qryBOMData!SetUpCrewSize*qryBOMData!SetupBurRate
/qryBOMData!MfgLotSize AS SetupBurCost
you should do this:
IIF(nz(qryBOMData!MfgLotSize,0) <> 0,
,qryBOMData!EstSetHours*qryBOMData!SetUpCrewSize*qryBOMData!SetupBurRate
/qryBOMData!MfgLotSize
, 0) AS SetupBurCost
~~~
NZ is a function to return zero or a user-specified value if the
expression is null
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Jasper Recto wrote:
Here is the SQL query. The qryBOMData!MfgLotSize is the problem
field.
Thanks!
SELECT qryBOMData.PartNum, qryBOMData.OprSeq, qryBOMData.OpCode,
qryBOMData.ProdStandard, qryBOMData.StdFormat,
qryBOMData.EstProdHours, qryBOMData.ProdCrewSize,
qryBOMData.ProdLRate,
IIf(qryBOMData!StdFormat="HR",(qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdLRate)/qryBOMData!MfgLotSize,qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdLRate)
AS ProdLaborCost, qryBOMData.EstSetHours,
qryBOMData.SetUpCrewSize, qryBOMData.SetupLRate,
qryBOMData!EstSetHours*qryBOMData!SetUpCrewSize*qryBOMData!SetupLRate/qryBOMData!MfgLotSize
AS SetupLaborCost, qryBOMData.WCCode, qryBOMData.ProdBurRate,
IIf(qryBOMData!StdFormat="HR",(qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdBurRate)/qryBOMData!MfgLotSize,qryBOMData!EstProdHours*qryBOMData!ProdCrewSize*qryBOMData!ProdBurRate)
AS ProdBurCost, qryBOMData.SetupBurRate,
qryBOMData!EstSetHours*qryBOMData!SetUpCrewSize*qryBOMData!SetupBurRate/qryBOMData!MfgLotSize
AS SetupBurCost, qryBOMData.EstUnitCost,
qryBOMData.StdMaterialCost, qryBOMData.RevisionNum, Format(DSum("
[ProdLaborCost]","qryBOMCosts"),".0000") AS ProdLaborTTL,
Format(DSum(" [SetupLaborCost] ","qryBOMCosts"),".0000") AS
SetupLaborTTL, Format(DSum(" [ProdBurCost]
","qryBOMCosts"),".0000") AS ProdBurTTL, Format(DSum("
[SetupBurCost] ","qryBOMCosts"),".0000") AS SetupBurTTL,
qryBOMData.StdMtlBurCost, qryBOMData.PartDescription,
qryBOMData.MfgLotSize, qryBOMCostsSingleLevel.MtlCost,
qryBOMCostsSingleLevel.MtlBurCost,
Format(DSum("[EstUnitCost]","qryBOMCosts"),".00") AS OVSSum,
qryBOMCostsSingleLevel.PartCost
FROM qryBOMData INNER JOIN qryBOMCostsSingleLevel ON
qryBOMData.PartNum = qryBOMCostsSingleLevel.PartNum;
hi Jasper,
what is the SQL for your query?
from the query design --> View, SQL
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Jasper Recto wrote:
I have a form that ask for a part number and a button the runs a
query based off that partnumber.
If a certain criteria is met, I can get a division by zero error
as the query is running.
How can I get the query to stop and pop up a message after it
encounters a value of zero for a certain field?
The way it is right now is if I get that error, it just keeps
popping up and we have to Ctrl-Alt-Del and shut down the program
to get out of it.
Any ideas?
Thanks,
Jasper
happy to help