By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,586 Members | 992 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,586 IT Pros & Developers. It's quick & easy.

Visual Basic code only works sometimes in access

P: 18
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

Expand|Select|Wrap|Line Numbers
  1. Dim dbsMedicalSupplies As Database
  2.     Dim rstStock As Recordset
  3.     Dim strToOrder As String
  4.     Dim strExtraOrder As String
  5.  
  6.     Set dbsMedicalSupplies = OpenDatabase("x:\MedicalSupplies\MedicalSupplies.mdb")
  7.     Set rstStock = _
  8.         dbsMedicalSupplies.OpenRecordset("Stock", _
  9.         dbOpenDynaset)
  10.         With rstStock
  11.  
  12.     .MoveFirst
  13.     Do While Not .EOF
  14.         .Edit
  15.         !ToOrder = !ToOrder + !ExtraOrder
  16.         !ExtraOrder = 0
  17.         .Update
  18.         .MoveNext
  19.     Loop
  20.     End With
  21.  
  22.     rstStock.Close
  23.     dbsMedicalSupplies.Close
  24.  
  25. End Sub
thank you for your time
cheers Powelly
Dec 13 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdOrderStock_Click()
  2. Dim dbsMedicalSupplies As Database
  3.     Dim rstStock As DAO.Recordset
  4.     Dim strToOrder As String
  5.     Dim strExtraOrder As String
  6.  
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.

Expand|Select|Wrap|Line Numbers
  1.     Set dbsMedicalSupplies =CurrentDB
  2.     Set rstStock = dbsMedicalSupplies.OpenRecordset("Stock",dbOpenDynaset)
  3.  
  4.         With rstStock
  5.  
  6.         .MoveFirst
  7.    Do While Not .EOF
  8.       .Edit
  9.       !ToOrder = !ToOrder + !ExtraOrder
  10.       !ExtraOrder = 0
  11.               .Update
  12.       .MoveNext
  13.    Loop
  14.     End With
  15.  
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.

Expand|Select|Wrap|Line Numbers
  1.     rstStock.Close
  2. Set rstStock = Nothing
  3. 'dbsMedicalSupplies.Close
  4. Set dbsMedicalSupplies = Nothing
  5.  
  6. End Sub
  7.  
Mary
Dec 13 '06 #2

P: 18
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.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdOrderStock_Click()
  2. Dim dbsMedicalSupplies As Database
  3.     Dim rstStock As DAO.Recordset
  4.     Dim strToOrder As String
  5.     Dim strExtraOrder As String
  6.  
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.

Expand|Select|Wrap|Line Numbers
  1.     Set dbsMedicalSupplies =CurrentDB
  2.     Set rstStock = dbsMedicalSupplies.OpenRecordset("Stock",dbOpenDynaset)
  3.  
  4.         With rstStock
  5.  
  6.         .MoveFirst
  7.    Do While Not .EOF
  8.       .Edit
  9.       !ToOrder = !ToOrder + !ExtraOrder
  10.       !ExtraOrder = 0
  11.               .Update
  12.       .MoveNext
  13.    Loop
  14.     End With
  15.  
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.

Expand|Select|Wrap|Line Numbers
  1.     rstStock.Close
  2. Set rstStock = Nothing
  3. 'dbsMedicalSupplies.Close
  4. Set dbsMedicalSupplies = Nothing
  5.  
  6. End Sub
  7.  
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
Dec 20 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
You're Welcome

Mary
Dec 20 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.