Reference number of records

  • Thread starter Thread starter Mike Revis
  • Start date Start date
M

Mike Revis

Hi Group
Access 2000 WinXPPro

I have a form/subform
frmPackage/sfrmPart
Parts go in packages.

I have a command button to preview a report. cmdPreviewrptAir.
There is a text box. Qvalue. Qvalue is Single data type. Default value is
blank.

Qvalue *must* have a value input if there is more than one part in the
package and *must not* have a value if there is only one part in the
package.

I can't figure out the code to check if there is more than one record in
frmPart.

As always any thoughts, comments or suggestions are welcome.

Regards
Mike
 
Hi Group
Access 2000 WinXPPro

I have a form/subform
frmPackage/sfrmPart
Parts go in packages.

I have a command button to preview a report. cmdPreviewrptAir.
There is a text box. Qvalue. Qvalue is Single data type. Default value is
blank.

Qvalue *must* have a value input if there is more than one part in the
package and *must not* have a value if there is only one part in the
package.

I can't figure out the code to check if there is more than one record in
frmPart.

As always any thoughts, comments or suggestions are welcome.

Regards
Mike
Click to expand...

Since data is not stored in forms, there's no way I can think of to do so as
stated.

I presume that sfrmPart is based on a Parts table, and that there is a
PackageID as a link between the Packages table and the Parts table? If so, you
need not - indeed should not - store QValue in the Packages table at all. You
can calculate it dynamically using an expression in a Query:

QValue: IIF(DCount("*", "[Parts]", "[PackageID] = " & PackageID) > 1,
DCount("*", "[Parts]", "[PackageID] = " & PackageID), Null)
 
Thanks John,
You are correct that data is not stored in forms. Just me being sloppy.

tblPackageData with PackageID as the PK
tblPartData with PackageID as the FK and PartID as the PK

QValue is not a calculated field. Well, the value is calculated but not
within the scope of this project.
Although someday I would like to bring that into the project.
Currently it is calculated independently by the user and the value is input.

What I am trying to accomplish now is that if there is more than one part in
a package and the QValue is null the user is prompted to input a value.

Your example of using DCount was of great help to get me moving in this
direction.

If (DCount("[partid]", "[tblPartData]", "[PackageID] = " & PackageID)) > 1
And IsNull(QValue) Then
MsgBox "Enter Q Value"
Me.QValue.SetFocus
Else
DoCmd.OpenReport stDocName, acPreview
End If

The only part that still bothers me is that the QValue can be not null but
out of range. The only allowable values are 0.1 to 1. The value has to be
rounded up to the nearest 10th.

I welcome any further comments or suggestions you might have.

As always your input is greatly appreciated.

Regards
Mike

John W. Vinson said:
Hi Group
Access 2000 WinXPPro

I have a form/subform
frmPackage/sfrmPart
Parts go in packages.

I have a command button to preview a report. cmdPreviewrptAir.
There is a text box. Qvalue. Qvalue is Single data type. Default value is
blank.

Qvalue *must* have a value input if there is more than one part in the
package and *must not* have a value if there is only one part in the
package.

I can't figure out the code to check if there is more than one record in
frmPart.

As always any thoughts, comments or suggestions are welcome.

Regards
Mike
Click to expand...

Since data is not stored in forms, there's no way I can think of to do so
as
stated.

I presume that sfrmPart is based on a Parts table, and that there is a
PackageID as a link between the Packages table and the Parts table? If so,
you
need not - indeed should not - store QValue in the Packages table at all.
You
can calculate it dynamically using an expression in a Query:

QValue: IIF(DCount("*", "[Parts]", "[PackageID] = " & PackageID) > 1,
DCount("*", "[Parts]", "[PackageID] = " & PackageID), Null)
Click to expand...
 
If (DCount("[partid]", "[tblPartData]", "[PackageID] = " & PackageID)) > 1
And IsNull(QValue) Then
MsgBox "Enter Q Value"
Me.QValue.SetFocus
Else
DoCmd.OpenReport stDocName, acPreview
End If

The only part that still bothers me is that the QValue can be not null but
out of range. The only allowable values are 0.1 to 1. The value has to be
rounded up to the nearest 10th.
Click to expand...

Well, then... add additional code to check that.

If QValue < 0.1 Then
MsgBox "QValue must be greater than or equal to 0.1", vbOKOnly
Me.QValue.SetFocus
Cancel = True
End If
If QValue > 1 Then
MsgBox "QValue must be between 0.1 and 1.0", vbOKOnly
Me.QValue.SetFocus
Cancel = True
End If

The "Cancel" lines assume that this code is in the form's BeforeUpdate event
and will cause the update to be aborted - if the code is elsewhere, adjust as
needed.
 
Of course.
Thanks John

Mike

John W. Vinson said:
If (DCount("[partid]", "[tblPartData]", "[PackageID] = " & PackageID)) > 1
And IsNull(QValue) Then
MsgBox "Enter Q Value"
Me.QValue.SetFocus
Else
DoCmd.OpenReport stDocName, acPreview
End If

The only part that still bothers me is that the QValue can be not null but
out of range. The only allowable values are 0.1 to 1. The value has to be
rounded up to the nearest 10th.
Click to expand...

Well, then... add additional code to check that.

If QValue < 0.1 Then
MsgBox "QValue must be greater than or equal to 0.1", vbOKOnly
Me.QValue.SetFocus
Cancel = True
End If
If QValue > 1 Then
MsgBox "QValue must be between 0.1 and 1.0", vbOKOnly
Me.QValue.SetFocus
Cancel = True
End If

The "Cancel" lines assume that this code is in the form's BeforeUpdate
event
and will cause the update to be aborted - if the code is elsewhere, adjust
as
needed.
Click to expand...
 
Back
Top