Data Conversions

[Ally]

I've got some code on a form, that posts fields to a subform, (therefore a table).

There is code (in a module) to trap whether any fields have been left null, but if there are, I'm getting a data conversion error for some of the numeric / currency fields. So rather than declaring them as currency and integer, I changed it to variant. Then tried to change it back as it posts to the table, but along the line I'm still getting an error.

Is there a simple way of doing this - ie, declaring it as CCur / CInt somehow? I had a go, but couldn't work it out.

Sorry - but my coding skills are appalling and get very confused! (And I don't take the credit for the good bits as Col wrote a lot of it and I just amended to suit my form)!

Here's the code. If you need any more info, please let me know. Errors occur in the lines emboldened.

Private Sub cmdSaveData_Click()
Dim bx1 As Variant, x As Integer, y As String, z As Variant, r As Recordset
Dim db As Database, a As String, b As Integer, c As Variant, d As Variant, ctl As Control, frm As Form
Dim e As String, f As Variant, g As Integer
Set db = CurrentDb
Set r = db.OpenRecordset("tblIssues")
Call CheckFields

Set frm = Screen.ActiveForm

For Each ctl In frm.Controls
    If ctl.ControlType = acTextBox And ctl.Tag = "xyz" And IsNull(ctl) Then
        DoCmd.GoToControl ctl.ControlName
        ctl.Tag = "req"
        msgbox ctl.Tag
                
        Exit Sub
    End If
    
    Next ctl


x = Me.OrderID.Value
y = Me.txtItemSelect.Value
[b]z = Me.txtQuantity.Value
c = Me.txtCost.Value[/b]
d = Me.txtNumberSent.Value
e = Me.txtBatchNo.Value
f = Me.txtExpDate.Value


If Me.txtNumberSent > Me.txtQuantity Then
    msgbox "Number sent is greater than number ordered! Please amend.", vbOKOnly, "Quantity Error"
    DoCmd.GoToControl "txtQuantity"
    Exit Sub
End If

If Me.txtQuantity > Me.txtNumberSent Then
    Me.PartOrder.Caption = "Part Order"
    Me.PartOrderTick = -1
Else
    Me.PartOrder.Caption = ""
    Me.PartOrderTick = 0
End If

a = Me.PartOrderTick.Value

'posts new issue into issues table
r.AddNew
r.Fields("OrderID") = x
r.Fields("ProductID") = y
[b]r.Fields("QuantityOrdered") = CInt(z)
r.Fields("UnitPrice") = CCur(c)[/b]
r.Fields("QuantityDelivered") = d
r.Fields("BatchNumber") = e
r.Fields("ExpiryDate") = f
r.Fields("PartOrder") = a
r.Update

DoCmd.Requery "Issues Subform"

bx1 = msgbox("Do you want to enter any more items" _
& " for this order?", vbYesNo + vbQuestion, "Note")
If bx1 = vbYes Then
    
    Me.txtItemSelect.Value = ""
    Me.txtQuantity.Value = ""
    Me.txtCost.Value = ""
    Me.txtNumberSent.Value = ""
    Me.txtBatchNo.Value = ""
    Me.txtExpDate.Value = ""
    
 DoCmd.OpenForm ("frmDrugSearch")
    
Else
    Me.txtItemSelect.Value = ""
    Me.txtQuantity.Value = ""
    Me.txtCost.Value = ""
    Me.txtNumberSent.Value = ""
    Me.txtBatchNo.Value = ""
    Me.txtExpDate.Value = ""

   
    Exit Sub
    End If
    
lexit:
    Exit Sub
lerror:
    msgbox Err.Description
    Resume lexit

End Sub

Module Code:

Public Function CheckFields()
Dim ctl As Control, frm As Form
Set frm = Screen.ActiveForm

For Each ctl In frm.Controls
        
        If ctl.ControlType = acTextBox And ctl.Tag = "req" And IsNull(ctl) Then
            msgbox ctl.ControlName & " has no value"
'            DoCmd.GoToControl ctl
            ctl.Tag = "xyz"
            msgbox ctl.Tag
            
            Exit Function
            
        Else
        
        GoTo 10
        
        
        End If
    
10: Next ctl

End Function

 

[Rich]

Have you tried removing .Value and just Me.txtQuantity ?

 

[Ally]

Have you tried removing .Value and just Me.txtQuantity ?

Thanks Rich. I hadn't, but just tried. No change unfortunately.

 

[Rich]

What is xyzabcd etc?

 

[Ally]

What is xyzabcd etc?

Variables for the fields in the table.

 

[Rich]

Yes but I assume you're updating the recordset somehow, where's the code for that?

 

[Ally]

Yes but I assume you're updating the recordset somehow, where's the code for that?

The fields on the form are unbound and OnClick of the Save Data button, the values in the fields are assigned to the x, y, z etc.

x = Me.OrderID.Value
y = Me.txtItemSelect.Value
z = Me.txtQuantity.Value
c = Me.txtCost.Value
d = Me.txtNumberSent.Value
e = Me.txtBatchNo.Value
f = Me.txtExpDate.Value

Then the values are posted to the table.

r.AddNew
r.Fields("OrderID") = x
r.Fields("ProductID") = y
r.Fields("QuantityOrdered") = CInt(z)
r.Fields("UnitPrice") = CCur(c)
r.Fields("QuantityDelivered") = d
r.Fields("BatchNumber") = e
r.Fields("ExpiryDate") = f
r.Fields("PartOrder") = a
r.Update

 

[Rich]

What happens if you take out all the Dim x As etc.etc ?

 

[Ally]

What happens if you take out all the Dim x As etc.etc ?

"Variable not defined" if I just remove the Dim x etc.

If I remove the x= etc, I then get "invalid use of property".

 

[Rich]

Something like

Dim rst As Recordset
rst.Edit

rst![OrderID] = Me.OrderID
rst![ProductID] = Me.txtItemSelect
rst! etc
rst.Update

etc?

 

[Ally]

That works generally but ... I'm still getting data-type conversion error when fields are left blank, on the line:

rst![QuantityOrdered] = Me.txtQuantity

 

[Rich]

That works generally but ... I'm still getting data-type conversion error when fields are left blank, on the line:

rst![QuantityOrdered] = Me.txtQuantity

rst![QuantityOrdered] = Nz(Me.txtQuantity) ?

 

[Ally]

The Nz code itself doesn't error, but the same error message on the same line comes up.

 

[Ally]

Just tried:

rst![UnitPrice] = CCur(Nz(Me.txtCost, 0))

but same error again.

 

[john471]

Ally,

Was trying to have a little look-see at yr prob.

Pls advise exactly what error you get on which line of code.

Do you have a sample DB* you cud post ?

Thx

*(AC97)

 

[john471]

You might like to try to isolate the problem.

Is the error caused by coercing the value, or putting the coerced value into the field. e.g.

dim intZ as Integer
dim curC as currency
intZ = CInt(z)
r.Fields("QuantityOrdered") = intZ
curC = CCur(c)
r.Fields("UnitPrice") = curC

That may shed some light. (once you've cracked it you could go back to doing it on the same line, if you wished)

Alternately, try using the NZ function when you get the data from the control, and specify a zero substitute for null values.

    z = Nz(Me.txtQuantity.Value, 0)
    c = Nz(Me.txtCost.Value, 0)

(snip snip --- cut to the chase scene)

    'posts new issue into issues table
    r.AddNew
    r.Fields("OrderID") = x
    r.Fields("ProductID") = y
    r.Fields("QuantityOrdered") = CInt(z)
    r.Fields("UnitPrice") = CCur(c)
    r.Fields("QuantityDelivered") = d
    r.Fields("BatchNumber") = e
    r.Fields("ExpiryDate") = f
    r.Fields("PartOrder") = a
    r.Update

 

[Ally]

Thanks for your reply John.

Phew - here's a very cut down version of the db - in Acc '97. Had to delete loads of stuff to get it to fit.

Go to frmCustomer and view Order from the subform.

The Save Data command button is where the code is. If any of the fields are null, I want it to say so and tell users to fill it in, and is working if there's entries in the Select Item box and Cost box. But if there's data in the others but not in the Select Item and Cost box, that's when it errors. I know that's not likely to happen but want to cover all eventualities.

Will have a look at the other stuff you mentioned in the meantime.

I have just realized (doh) that I could disable the Save Data button until all relevant fields are filled in, so may do that.

 

[john471]

ok.

Firstly; while I think of it, you've got

rst![UnitPrice] = CCur(Nz(Me.txtCost, 0))

in there twice

Your error is because Me.txtCost has a zero length string (""). You cannot coerce this into a currency (CCur("") ==> error <type Mismatch>)

Test for a zero length string, and :-

a) change it to an actual zero (string ("0") or number (0) doesn't matter in this instance)

ccur("0") and ccur(0) both yield 0​

if len(me.txtCost)= 0 then
'do something about it
endif

better still - use the trim function in case they put a space in there
if len(trim(me.txtCost))= 0 then
'do something about it
endif


or b) (preferred option) don't allow users to proceed without a number in there

if not isnumeric(me.txtCost) then
'Do something about it
endif

Same deal for the other field.

HTH

Regards

John.

 

[Ally]

Thanks John

Think I'll go for the "preferred" and ultimately easier option of not allowing them to get there in the first place! (The line of code being in there twice was from me trying to get my syntax right and forgot to delete it).