Field Restrictions

R

Reeza

Hi,

I'm fairly new to Access, as you'll see by my questions :).

The project is to design a form that will record precipitation from
volunteers across the state. 4 fields are to be entered, IDNum/Day/
Precipitation/Unit.

There is basically 2 things I do not know how to do:

1) For precipitation, set restrictions based on limits. ie. if pcp
is measured in 'millimeters' then the amount has no decimals whereas
if in 'inches' then the amount will have two decimals.

2) Retain the previously entered idnum, so the student hired to enter
the data does not have to enter it in multiple times.

Any Help is Appreciated!!!

Thanks,
Reeza
 
A

Arvin Meyer [MVP]

Data wiz you are asking for 2 different data types to be stored in 1 field,
that cannot be done. You can use a double datatype like:

25.00 mm
1.01 inches

IOW, you can force anything in mm to round up or down, and inches to be left
alone, but the format must always be 2 decimals in order for either 1 to be.

You have a second problem. You cannot do this in a continuous form or
datasheet, because any running code will affect all records in the dataset.

Lastly ID's are normally unique, unless they are used as a foreign key.
Like:

tblCompany
CompanyID - Unique index

tblPeople
PersonID - Unique index
CompanyID - Foreign key
 
J

John W. Vinson

Hi,

I'm fairly new to Access, as you'll see by my questions :).

The project is to design a form that will record precipitation from
volunteers across the state. 4 fields are to be entered, IDNum/Day/
Precipitation/Unit.

There is basically 2 things I do not know how to do:

1) For precipitation, set restrictions based on limits. ie. if pcp
is measured in 'millimeters' then the amount has no decimals whereas
if in 'inches' then the amount will have two decimals.
Click to expand...

I'd - STRONGLY! - suggest actually storing the data in a consistent
unit (millimeters say), and eliminating the Unit field. You can
provide two textboxes on the form, one for millimeters, bound to the
Precipitation field; the other unbound for entering inches. In its
AfterUpdate event you can put code like:

Private Sub txtInches_AfterUpdate()
If IsNull(Me!txtPcp) Then
Me!txtPcp = CLng(Me.txtInches*25.4)
Else
MsgBox "Enter inches or millimeters, not both!", vbOKOnly
End If
End Sub
2) Retain the previously entered idnum, so the student hired to enter
the data does not have to enter it in multiple times.
Click to expand...

Is the user entering an externally generated IDNum? If so, simply make
that field the Primary Key of the table. Duplicates will be rejected.

John W. Vinson [MVP]
 
Top