Do.. Loop Conundrum

[DubCap01]

Hi all,



It's late Friday, I am mentally exhausted, and cannot function correctly and work this one out. It's an inherited database, so not responsible for how things are currently set out, just trying to fix. Simple complicated scenario below:

TblTmpBarSales has one field (StockID) and only ever one entry at any one time
TblStockLink has three fields (StockID,StockLinkID, StockLink)
TblStockFix has two fields, StockID, Qty (default = 1)

Problem is this, when a sale is made in the bar of Jack Daniels and Coke, it affects the stock levels of two items - the JD and the Coke supply. The further problem is that the StockID in TblTmpBarSales does NOT relate to the StockID in TblStock, hence the intermediary TblStockLink.

Here's what I am trying to achieve:
TblTmpBarSales.StockID ("JD_COKE") = TblStockLink.StockLink

TblStockLink has two corresponding entries:
StockID StockLinkID Stocklink
JD.............. 1........ JD_COKE
COKE.......... 2........ JD_COKE

I'd like if someone could help me with a Do...Loop to Take the StockID from TblTmpBarSales, look for the corresponding StockLink in TblStockLink, then append the TblStockLink.StockID to TblStockFix - and do it for every occasion of Stocklink in TblStockLink which in this case is two (one for JD, one for Coke)

I can then rework the correct TblStock free stock level from the information written in TblStockFix.

Is that clear? After all it is beer o'clock and things are starting to get fuzzy. Sorry if it sounds clunky, but feel sure someone will work out what I need by way of VBA code

cheers
Pete

[ranman256]

I dont think you want DO LOOP. Most data changes can be handled with queries. VB code is for extreme complicated events.
Tho, I'm still not sure what you want.
If you can tell me what you currently have....in a form, or table or what, then tell what you want to end up, then I can give better advice.
I think you want an UPDATE query to change

Make a Query from TblTmpBarSales
add table TblStockLink
join together on Stocklink field.
then update the field you need.

[DubCap01]

Hi RanMan256

I can't update two stock items (with different StockID's) in TblStock using one StockID from TblTmpBarSales, where two separate stock items are used to make the end product.
So an update query is wasted, and I don't mind the VBA side of things, I find it cleaner than queries

[ranman256]

If you must do it the hard way, put the code in a module and run from immediate window

  'run this code from a module

private sub ScanRecs()
Dim sSql As String, sMyFld As String
Dim rst 'As Recordset

sSql = "select * from Tbl"
Set rst = CurrentDb.OpenRecordset(sSql)


With rst
  while not .eof
      .edit
       .Fields("Name").Value  = "new value"
      .update

     .MoveNext
  wend
End With
            
 
rst.Close
Set rst = Nothing
End Function

[DubCap01]

Thanks RanMan, with a little modification I got it working okay!

Here's the full result which works fine:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT TblStockLink.* from TblStockLink;")

With rst
While Not .EOF
DoCmd.RunSQL "INSERT INTO TblTmpStockFix ( StockID ) SELECT TblStockLink.StockID FROM TblTmpBarSales INNER JOIN TblStockLink ON TblTmpBarSales.StockID = TblStockLink.StockLink WHERE (((TblTmpBarSales.StockID)=[TblStockLink].[StockLink]));"
rst.MoveNext
Wend
End With
rst.Close
Set rst = Nothing

DoCmd.RunSQL "UPDATE TblStock INNER JOIN TblTmpStockFix ON TblStock.StockID = TblTmpStockFix.StockID SET TblStock.FreeStock = [TblStock].[FreeStock]-[TblTmpStockFix].[Qty];"