VBA - MS ACCESS 07 - Updating Two Seperate Recordsets

[Robert]

Hi Guys,

I'm having a bit of an issue... I need some code to loop through the records on two separate tables and update the records accordingly.

I basically need the code to loop through the testing table and check the product against the free stock in the free stock table. If there is sufficient free stock then i need the 'Qty Req New' column to be the value from the 'Qty Req' column and amend the include value to -1.

I then need the code to go back to the free stock table and update the quantity taking off what is in the Qty Req column for that design in the testing table.

I need it to loop through and do this for all records in the testing table.

Images of the table are as below, i wont post my code as frankly its a mess and this has taken me quite some time.



Thanks
Rob

 

[Jamie]

Transaction, Partial or All or nothing?

Basically From what I can see, if you don't have enough Product A, do you want to assign them all to -1 or after it runs out, first come first serve?


EDIT:
I ask this as it depends on your data entry order / sorting.

Also:

Customer 1 wants 20x ProductA
Customer 2 wants 5x ProductA

FreeStock = 10

should Customer 2 get his product or not?

I believe I could help you with your problem But It depends on how you want the "transaction" to occur, also Did you want it SQL with an UPDATE query or VB 6 Code?

---------- Post added at 06:18 PM ---------- Previous post was at 05:40 PM ----------

I can write the code out, but without ACCURATE syntax or language but in a readable logical way, so you can convert to VB6, atleast it should help. (If you need it in VB 6 I'' rewrite)

[COLOR="#0000FF"]Foreach [/COLOR](Record [COLOR="#0000FF"]in [/COLOR]Testing) [COLOR="#008000"]//For each Row in Testing Table[/COLOR]
{
   [COLOR="#0000FF"]If [/COLOR](Record.Include == [COLOR="#0000FF"]false [/COLOR])[COLOR="#008000"]//Record not yet Included so process
[/COLOR]   {
       [COLOR="#0000FF"]int [/COLOR]PCount = DFirst([COLOR="#A52A2A"]"[Free Stock]"[/COLOR],[COLOR="#A52A2A"]"[Free Stock]"[/COLOR],[COLOR="#A52A2A"]"[Product] = '"[/COLOR] & Record.Product & [COLOR="#A52A2A"]"'"[/COLOR]);[COLOR="#008000"]//Get Remaining Free Stock
[/COLOR]
        [COLOR="#0000FF"]if [/COLOR](Record.[Qty Req] > PCount)
            [COLOR="#0000FF"]continue[/COLOR]; [COLOR="#008000"]//Goto next record, nothing more can be done for this record.[/COLOR]
        DoCmd.SetWarnings False
        DoCmd.RunSQL [COLOR="#A52A2A"]"UPDATE [Testing] SET [Include] = -1, [New Qty Req] = [Qty Req] WHERE [Testing ID] = "[/COLOR] & Record.TestingID
        DoCmd.RunSQL [COLOR="#A52A2A"]"UPDATE [Free Stock] SET [Free Stock] = "[/COLOR] & PCount - Record.[Qty Req] & [COLOR="#A52A2A"]" WHERE [Product] = '"[/COLOR] & Record.Product & [COLOR="#A52A2A"]"'"[/COLOR]
        DoCmd.SetWarnings True
   }
}

I assume that is what your after if not, let me know I'll try help some more. Doing this with Queries would be far better though.

 

[Robert]

I'm not sure it's possible to do with queries? Because you can't ask a query to step through each line on a table updating the stock information as it went.

What you have said is correct it's first come first serve until, for example the free stock is 1 but the customer wants 2, it needs to allocate 1 to that customer and then allocate 0 to te rest, updating the include field to 0 as it went.

Also, is your above example ok to paste straight back into access and it will run fine?

 

[Jamie]

I'm not sure it's possible to do with queries? Because you can't ask a query to step through each line on a table updating the stock information as it went.

What you have said is correct it's first come first serve until, for example the free stock is 1 but the customer wants 2, it needs to allocate 1 to that customer and then allocate 0 to te rest, updating the include field to 0 as it went.

Also, is your above example ok to paste straight back into access and it will run fine?

I'm almost certain it is possible, I could try write an SQL Query if you would like, or would you perfer it in VB 6, the example wasn't for direct Copy and paste, some of the syntax were correct such as DoCmd, DLookup etc, but Loop / format isn't correct.

Let me know which you would prefer and I will do my best to provide the solution for you.

 

[Robert]

Whatever will work in access 2007 would be fine. Anything at least that I can basterdise do work in that format!

Thanks for your help : )

 

[Jamie]

Whatever will work in access 2007 would be fine. Anything at least that I can basterdise do work in that format!

Thanks for your help : )

if the database is small enough do you think you could upload it?

The chances of me creating code which can be dropped in a work is very slim, from what I can tell you may need to add a PK to your testing Table

 

[Robert]

Yeah I can do that Monday although essentially it's just those two tables as they are in a test database.

 

[fannybatter]

Did you manage to get this sorted? Jamie is completely spot on in advising this is done via a query, calculated fields really don't belong in databases. You should always try to avoid storing a field that is dependant on another field, it goes against the basic principal of normalisation.

That said, I think Access 2010 has put a new data type in for calculated fields (there are some exceptional circumstances you may want to store calculated data so long as it is purely a luxury on the existing data and not critical to integrity). But I can't imagine they would work cross-tables, even with relationships in place. Which kind of just reinforces the statement that it should be a query, I guess.

 

[Robert]

This can't be done with a query as you need it to go through the dataset line by line as the next line could
Potentially depend on the line above.

I have managed a work around, storing the stock in the same table and having it loop.

Will post my code when I get to work!

---------- Post added at 07:57 AM ---------- Previous post was at 07:05 AM ----------

Code below:

 Dim db As DAO.Database
Dim rstTesting As DAO.Recordset
Dim count As Long
Dim records As Long


On Error GoTo ErrorHandler


Set db = CurrentDb()
Set rstTesting = db.OpenRecordset("Testing")




db.Execute "UPDATE TESTING SET [QTY NEW]=0"




Do Until rstTesting.EOF '***Outer Loop Start***

Do Until rstTesting![Processed] = 0 '***Check if record has been processed, if so move to the next record***

rstTesting.MoveNext

Loop '***Once first record has been found where Processed = 0, fall back to outer loop***

'Allocate the product based on the free stock.

If rstTesting![Free Stock] = 0 Then
rstTesting.Edit
rstTesting![Qty New] = 0
rstTesting![Include] = 0
rstTesting![Processed] = -1
rstTesting.Update
Else
If rstTesting![Free Stock] - rstTesting![Qty] < 0 Then
rstTesting.Edit
rstTesting![Qty New] = rstTesting![Free Stock]
rstTesting![Include] = -1
rstTesting![Processed] = -1
rstTesting.Update
Else
rstTesting.Edit
rstTesting![Qty New] = rstTesting![Qty]
rstTesting![Include] = -1
rstTesting![Processed] = -1
rstTesting.Update
End If
End If
  
Do Until rstTesting.EOF '***Update Free Stock based on what has been allocated***
 
'Check if the Free Stock is less than or equal to 0

If rstTesting![Processed] = -1 Then
rstTesting.MoveNext

Else

If rstTesting![Free Stock] <= 0 Then
rstTesting.Edit
rstTesting![Free Stock] = 0
rstTesting.Update
rstTesting.MoveNext


Else
  
rstTesting.Edit
rstTesting![Free Stock] = rstTesting![Free Stock] - Nz(DSum("[Qty New]", "Testing", "[Product] = '" & rstTesting![Product] & "'"), 0)
rstTesting.Update
rstTesting.MoveNext


End If
End If

Loop




rstTesting.MoveFirst

Loop





   rstTesting.Close
   db.Close


   Set rstEmployee = Nothing
   Set dbsNorthwind = Nothing
  
MsgBox "Process Complete"


   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description