?
Solved

Use of variant in Do

Posted on 2012-09-19
2
Medium Priority
?
449 Views
Last Modified: 2012-09-19
I have the following code that seems to work sometimes but not all of the time. If I replace the variant "Cstock" with a number then the number stores down in the table. But the variant does not. Please help

Dim Cdb As DAO.Database, sd As DAO.Recordset, ps As DAO.Recordset, strMsg As String, iResponse As Integer, cstock As Variant

Set Cdb = CurrentDb
Set sd = Cdb.OpenRecordset("stockdetails", dbOpenDynaset)
Set ps = Cdb.OpenRecordset("purchasedstock", dbOpenDynaset)

sd.MoveFirst
Do While Not sd.EOF
    If sd!sid = Me.stockid Then
    sd.Edit
    cstock = (Me.quantity + sd!stocktotal)
    sd!stocktotal = cstock
   
   
    sd.Update
    Forms!stockdetailalterview.stocktotal = sd!stocktotal
    Forms!stockdetailalterview.availablestock = sd!stocktotal - sd!stockringfenced

    Forms!stockdetailalterview.Refresh
    'DoCmd.Close
    Exit Do
    Else
    sd.MoveNext
    End If
Loop
0
Comment
Question by:iainmacleod
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 38413119
Try declaring cstock as a double and use NZ to handle nulls:

Dim Cdb As DAO.Database, sd As DAO.Recordset, ps As DAO.Recordset, strMsg As String, iResponse As Integer, cstock As Double

Set Cdb = CurrentDb
Set sd = Cdb.OpenRecordset("stockdetails", dbOpenDynaset)
Set ps = Cdb.OpenRecordset("purchasedstock", dbOpenDynaset)

sd.MoveFirst
Do While Not sd.EOF
    If sd!sid = Me.stockid Then
    sd.Edit
    cstock = (NZ(Me.quantity, 0) + NZ(sd!stocktotal,0))
    sd!stocktotal = cstock
    
    
    sd.Update
    Forms!stockdetailalterview.stocktotal = sd!stocktotal
    Forms!stockdetailalterview.availablestock = sd!stocktotal - sd!stockringfenced

    Forms!stockdetailalterview.Refresh
    'DoCmd.Close
    Exit Do
    Else
    sd.MoveNext
    End If
Loop

Open in new window



Depending on your needs, you can also declare cStock as an integer.  Just try using a numeric type with handling for nulls instead of using a variant.
0
 

Author Closing Comment

by:iainmacleod
ID: 38413630
Thats fantastic, works a treat. Many thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question