Sub Form Issue

[jlathem]

I have an error in a sub form where I have made a change in the Data Type from Integer to Text due to the length constraint of 10 numbers.

Below is the BeforeUpdate code I am using in the sub form. I want the Disp_ICN_Number field (in the Disposition table) to have a unique value for the Parent form field called CR_CCN (Relational field found in the Disposition table and the Cash_Record table).

Example if I have a CR_CCN value (found in the Parent form referencing the Cash_Record table) of 100 and Disp_ICN_Number value (found in the sub form and that will be going into the Disposition table) of 2 there can not be another Disp_ICN_Number with the value of 2 for the CR_CCN.

But other CR_CCN records can have a Disp_ICN_Number with the value of 2.


Below is the code that worked until I changed the Data Type from integer to text.
--------------------------------------------------------------------------
Private Sub ICN_Number_BeforeUpdate(Cancel As Integer)

Dim SID As String

SID = Me.Disp_ICN_Number.Value

'Check Cash_Record table for duplicate CR_CCN
If DCount("*", "Disposition", "Disp_ICN_Number=" & SID & " AND CR_CCN = " & Me.Parent.CR_CCN) > 0 Then

'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning!! " _
& vbCr & vbCr & "A Disposition with ICN " & SID & " has been found!." _
& vbCr & "Please verify the ICN and try again", _
vbInformation, "Duplicate Information"

End If

End Sub
--------------------------------------------------------------------------



Here is the code I used in the BeforeUpdate in the Parent form for the CR_CCN

--------------------------------------------------------------------------
Private Sub CR_CCN_BeforeUpdate(Cancel As Integer)

Dim SID As String

SID = Me.CR_CCN.Value

'Check Cash_Record table for duplicate CR_CCN
If DCount("CR_CCN", "Cash_Record", "CR_CCN=" & Chr(34) & SID & Chr(34)) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning!! " _
& vbCr & vbCr & "CCN [ " & SID & " ] has already been used!." _
& vbCr & "Please verify the number and try again."
End If


End Sub
--------------------------------------------------------------------------

All suggestions are welcome!


Thank you,
James

 

[HiTechCoach]

Why not change the data type to Long Integer or some other numeric data type?

 

[jlathem]

If I understand right Long Integer will only allow 9 or 10 numbers long. I need a value that will be 13 numbers long.

I am still very new so I may not fully understand how this works.

James

 

[boblarson]

Single would probably be your next best bet if you aren't working with decimals.

 

[jlathem]

Would a Single allow a value of 9999999999999?

 

[boblarson]

A single can allow 1.401298E–45 to 3.402823E38 for positive numbers so yes, that is sufficient number of digits (31? if I count right)

 

[jlathem]

Thanks Bob, that should work then.

I'll give that a try!

James

 

[jlathem]

I changed the Data Type back to Number and set the Field Size to Single.

When I tested it by entering the value of 9999999999999 (Thirteen 9s) and viewed the record in the form I get a value of 1E+13 witch is the same thing in the table field when I view it.

How do I get the field in the table to display the 9999999999999 or get the form to display the 9999999999999?

Thanks,
James

 

[boblarson]

If on the form you need to make sure that the control is wide enough to display it.

 

[boblarson]

Oh, and to the format of the control on the form - put "0000000000000"

 

[jlathem]

Ah the form formatting changed it to the correct display BUT, I am getting 10000000000000 in place of the 9999999999999.

Should I do something with the Table Field’s Input Mask or Format?

 

[boblarson]

I tried it and it worked fine for me without setting anything. What happens if you delete that value and then re-enter it now that the format is set?

And what are you using for an input mask?

 

[jlathem]

I cleared out all records in the database and set the Form Field’s Format to 0000000000000.

There is no mask.

I enter the 9999999999999 and I still get 10000000000000.

 

[jlathem]

I do have Decimal Places set to 0, could that cause this issue?

 

[jlathem]

If this helps I just tried to enter 99999999999 (eleven 9s) and it rounded up to 100000000000. When I checked the database I see 1E+11 and the form shows it as 0100000000000.

 

[boblarson]

I don't know - I had mine set to auto, so maybe.

 

[Mr. Hero]

Try using in your table under the Number data type; then in the format use the drop down menu and click on Double, set the Decimal property from Auto to 0. Even though this is used for numbers with decimal values, it will prevent the roundup of the number.

 

[jlathem]

Thank you Mr. Hero.

JMS