Updating records

[goldstar19821982]

Hello All.

I am having problems with updating stock levels.

What i have is a listbox which gets a new line each time a hired item is added to it. If i remove a hireline one by one it works fine, it deletes it and updates the stock in the item table.

But if i have added say 10 items to the hireorder, then the customer wants to cancel the whole order, i want to delete the whole list in the current order and update the stock levels for each item. At the moment i could delete all the items within the order, but is there a way that all the items within the list can have their stock updated at one time?

 

[dejones923]

I would recommend setting a reference to "Microsoft Scripting Runtime" and use the "Scripting.FileSystemObject" to do what you stated.

 

[goldstar19821982]

im getting a syntax problem when trying to resolve this issue. Please see below
Would you know what the syntax issue could be . Hireno - is set to number and is the primary key

Dim rsthire as recordset
Set rsthire = CurrentDatabase.OpenRecordset("SELECT * FROM tblhireline WHERE hireno = " & lsttransaction & ";")

 

[dejones923]

Take a look at the example below and use it to modify your code: Your 'where' clause seems to have a conflict. It seems like "hireno" is an integer, but your previous code seems to be treating it as it is a string. If "hireno" is indeed an integer then change the SQL to the following line of code:

mySQL = "SELECT * FROM tblhireline WHERE hireno = " & lsttransaction



Dim My_DB As DAO.Database, rst As DAO.Recordset, mySQL As String
Set My_DB = CurrentDb
mySQL = "SELECT * FROM tblhireline WHERE hireno = '" & lsttransaction & "'"
Set rst = My_DB.OpenRecordset(mySQL, dbOpenDynaset)
MsgBox "My recordcount is: " & rst.RecordCount

rst.Close
Set rst = Nothing

BTW, forget about my earlier response about the FileSystemObject; that was regarding another post. "SYNAPTIC MISFIRE ON MY PART"

Good Luck

 

[gemma-the-husky]

a) do you mean you cancel the order during entry. if this is os, i would be inclined to save the order in a temporarytable until it is accepted, and only then add it to the main table

b) you shouldnt have any stock issues - you ought not be keeping a running blaance of stock. stock is normally calculated by adding ins and outs each time you need to know the balance, so if you delete a few in or out lines, the stock calculation automatically adjusts.

Although thinking about this for the first time, i'm not sure what this means for stock valuation. is this done on thew fly also - you can do this easily for standard costs/fifo etc, but not so easy if your trying to value the stock on a moving average cost (avco) basis

 

[goldstar19821982]

Hi, I should have mentioned earlier, that issue was resolved, but how do i get it to match all the records and update the changes, At the moment i can get it to delete the first item of the lot but no more.

Im currently using findfirst, hence it only picks the first one. is there a way for it to match all the records.?