Visual Basic code only works sometimes in access

[powelly]

This is my first time not sure whats needed this is XP office

this is the code i`m trying to run i have tried on close and command buttons
i don`t even get an error code just does not run

Dim dbsMedicalSupplies As Database
    Dim rstStock As Recordset
    Dim strToOrder As String
    Dim strExtraOrder As String
 
    Set dbsMedicalSupplies = OpenDatabase("x:\MedicalSupplies\MedicalSupplies.mdb")
    Set rstStock = _
        dbsMedicalSupplies.OpenRecordset("Stock", _
        dbOpenDynaset)
        With rstStock
 
    .MoveFirst
    Do While Not .EOF
        .Edit
        !ToOrder = !ToOrder + !ExtraOrder
        !ExtraOrder = 0
        .Update
        .MoveNext
    Loop
    End With
 
    rstStock.Close
    dbsMedicalSupplies.Close
 
End Sub

thank you for your time
cheers Powelly

[MMcCarthy]

Firstly there is no indication of the sub or function command line at the beginning of this code. For the moment I'll assume a command button called cmdOrderStock. I've also reset the Recordset variable type to DAO.Recordset because if the ADO library is also referenced this can cause confusion.

 Private Sub cmdOrderStock_Click()
Dim dbsMedicalSupplies As Database
    Dim rstStock As DAO.Recordset
    Dim strToOrder As String
    Dim strExtraOrder As String
 

Secondly if the database you are referencing is the same database you are working on then you don't need the full path. Change it as follows otherwise leave it as it is.

    Set dbsMedicalSupplies =CurrentDB
    Set rstStock = dbsMedicalSupplies.OpenRecordset("Stock",dbOpenDynaset)
 
        With rstStock
 
        .MoveFirst
   Do While Not .EOF
      .Edit
      !ToOrder = !ToOrder + !ExtraOrder
      !ExtraOrder = 0
              .Update
      .MoveNext
   Loop
    End With
 

Lastly you want to close the recordset but also set it to nothing and the same for the database. If the database is your current database then you don't want to close it.

    rstStock.Close
Set rstStock = Nothing
'dbsMedicalSupplies.Close
Set dbsMedicalSupplies = Nothing
 
End Sub
 

Mary

[powelly]

Firstly there is no indication of the sub or function command line at the beginning of this code. For the moment I'll assume a command button called cmdOrderStock. I've also reset the Recordset variable type to DAO.Recordset because if the ADO library is also referenced this can cause confusion.

 Private Sub cmdOrderStock_Click()
Dim dbsMedicalSupplies As Database
    Dim rstStock As DAO.Recordset
    Dim strToOrder As String
    Dim strExtraOrder As String
 

Secondly if the database you are referencing is the same database you are working on then you don't need the full path. Change it as follows otherwise leave it as it is.

    Set dbsMedicalSupplies =CurrentDB
    Set rstStock = dbsMedicalSupplies.OpenRecordset("Stock",dbOpenDynaset)
 
        With rstStock
 
        .MoveFirst
   Do While Not .EOF
      .Edit
      !ToOrder = !ToOrder + !ExtraOrder
      !ExtraOrder = 0
              .Update
      .MoveNext
   Loop
    End With
 

Lastly you want to close the recordset but also set it to nothing and the same for the database. If the database is your current database then you don't want to close it.

    rstStock.Close
Set rstStock = Nothing
'dbsMedicalSupplies.Close
Set dbsMedicalSupplies = Nothing
 
End Sub
 

Mary

Powelly[/quote]

Thank You mary it`s working like it should
These are he two thing that fixed
[code]
Dim rstStock As DAO.Recordset
&
[code]

rstStock.Close
Set rstStock = Nothing
'dbsMedicalSupplies.Close
Set dbsMedicalSupplies = Nothing

Thanks again

Cheers Powelly

[MMcCarthy]

You're Welcome

Mary