Saving Data in Unbound Forms

[theSizz]

I have a form that has unbound text box controls. The user enters numeric data in each of these text box controls. I want to be able to store the values that the user enters for future use. I am using the following code which was provided on Richard Killey’s web site.

The problem is that the values that the code stores in the unbound text boxes is populated back as text values rather than numeric values.
Here is the code that saves the values.

Private Sub cmdSave_Click()

    '--- only process Save if there is data in scheme name
    If IsNull(txtScheme) Then
        MsgBox "Please Enter A Scheme Name"
        txtScheme.SetFocus
        Exit Sub
    End If
    
    Dim rst As Recordset
    '--- use the primary key (which is always an autonumber field) to find the record
    '--- if it is a new record, this will find no records, as txtID will be 0
    Set rst = CurrentDb.OpenRecordset("select * from tblschemes where ID=" & txtID)
    If chkNew = True Then   '--- do we add a new record and save an existing one
        rst.AddNew
    Else
        rst.Edit
    End If
    '--- transfer data from text boxes to table fields
    rst!MVol = txtMVol
    rst!TVol = txtTVol
    rst!WVol = txtWVol
    rst!ThVol = txtThVol
    rst!FVol = txtFVol
    rst!SVol = txtSVol
    rst!SuVol = txtSuVol
    rst!Scheme = txtScheme
    rst.Update  '--- save the record
    rst.Close   '--- close the recordset
    Set rst = Nothing   '--- reclaim the memory the recordset was using
    chkNew = False  '--- reset the new flag
    '--- enable the list box and the Add New button and the Close button
    '--- must be done before moving focus to the list box
    lstData.Enabled = True
    cmdAddNew.Enabled = True
   
    '--- make sure the newest data is in the list box
    lstData.Requery
    '--- set the focus to the list box
    lstData.SetFocus
    lstData = lstData.ItemData(0)
    Call lstData_AfterUpdate
    '--- disable the text boxes and the Save button, and make Edit button enabled
    txtMVol.Enabled = False
    txtTVol.Enabled = False
    txtWVol.Enabled = False
    txtThVol.Enabled = False
    txtFVol.Enabled = False
    txtSVol.Enabled = False
    txtSuVol.Enabled = False
    txtScheme.Enabled = False
    cmdSave.Enabled = False
    cmdEdit.Enabled = True
    
End Sub

And here is the code that populates the values back into the unbound text boxes after an update.

Private Sub lstData_AfterUpdate()

    '--- whenever a new item is chosen in the list box, display the data in text boxes
    txtScheme = lstData.Column(1)
    txtMVol = lstData.Column(2)
    txtTVol = lstData.Column(3)
    txtWVol = lstData.Column(4)
    txtThVol = lstData.Column(5)
    txtFVol = lstData.Column(6)
    txtSVol = lstData.Column(7)
    txtSuVol = lstData.Column(8)
    txtID = lstData.Column(0)   '--- primary key is in the first column (always)
    
End Sub

How do I change the code so it stores the values as numeric rather than text ?
Thank you for any help that can be offered.

 

[JoeAtWork]

Whatever you put in a textbox is text. If you need to manipulate the value in the textbox as a number, you will need to convert it to a numeric variable.

Since the controls are unbound, Access has no idea what type of data should be in them. It's up to your code to do any necessary conversions or validation.

 

[theSizz]

Thanks Joe that's the solution.
I modified the code using a Type Conversion Function.
Here is the corrected code:

Private Sub lstData_AfterUpdate()

    '--- whenever a new item is chosen in the list box, display the data in text boxes
    txtScheme = lstData.Column(1)
    txtMVol = CLng(lstData.Column(2))
    txtTVol = CLng(lstData.Column(3))
    txtWVol = CLng(lstData.Column(4))
    txtThVol = CLng(lstData.Column(5))
    txtFVol = CLng(lstData.Column(6))
    txtSVol = CLng(lstData.Column(7))
    txtSuVol = CLng(lstData.Column(8))
    txtID = lstData.Column(0)   '--- primary key is in the first column (always)
    
End Sub

Thanks for the help.
theSizz