Solved Data Entry Error (1 Viewer)

[Emma35]

Hi All,
I've got a database for entering maintenance information about various machinery. On the data entry form, i have a combo box which has several columns and i've used some code in the OnChange event of the combo box to populate other text boxes on the form when i make a selection to save time. Most of these text boxes populate fine except for one which keeps giving me the error attached. (Column 4)
The field for the one giving problems is set to 'Number' in the table and the info i'm trying to enter is a 12 digit number with no decimal places. I've tried changing the table field to Short Text but it still won't accept this 12 digit number. Anyone any idea why this is happening. The OnChange code is below

Private Sub cboMachine_Change()
Me.Desc = Me.cboMachine.Column(2)
Me.SKU = Me.cboMachine.Column(3)
Me.INumber = Me.cboMachine.Column(4)
Me.Cost = Me.cboMachine.Column(5)
Me.BioInfo = Me.cboMachine.Column(6)
End Sub

 

[moke123]

first use the afterupdate event of the combo.

if the table field is numeric, try specifically changing the datatype to a long.

Me.INumber = Clng(Me.cboMachine.Column(4))

 

[arnelgp]

change the field to Double or Currency or Decimal.
Long integer can only accommodate 8 digit number (not 12)

 

[moke123]

change the field to Double or Currency or Decimal.
Long integer can only accommodate 8 digit number (not 12)

good point, didn't think of that.

https://support.microsoft.com/en-us...unctions-8ebb0e94-2d43-4975-bb13-87ac8d1a2202

 

[Emma35]

Thanks lads. I changed the table field to Double and reset the code into the AfterUpdate event and all's well again.
Appreciate the help as always
Em x

 

[gemma-the-husky]

That's not strictly correct, I think. A long can go to just over +/- 2 billion, so certainly any number up to 1 billion, hence 9 zeroes, but not a 12 figure number anyway.

A longlong in 64bit Access could manage a 12 bit integer.

As your serial numbers are more in the nature of text values, you could have just stored the12 digit "number" as text. Unless you need to do arithmetic operations on them, which is unlikely, text would work OK, and then you could also show leading zeroes if necessary. (like a telephone "number")

 

[Pat Hartman]

Just to clarify WHY you needed to move the code -
The Change event runs once for each character typed into the control. Clearly, you won't have valid values until you get the entire value selected. Plus, data is typed into the .Text buffer and not even moved to the .Value buffer until after focus leaves the control.
The AfterUpdate runs ONCE after the focus leaves the control and after the entered value is moved to the .Value buffer

The Change event has one specific use - you want to validate each character as it is typed. If you need the entire value, then this is the wrong event. If you are validating the entered value, you would use the control's BeforeUpdate event occasionally or better still, use the form's BeforeUpdate event.

Since this is not validation code, you have several options for events that run once focus leaves the control:
AfterUpdate
LostFocus
Exit