A loop on recordset.field value (1 Viewer)

Mous1

New member
Local time
Today, 00:34
Joined
Dec 11, 2019
Messages
5
hi everybody,
I'm a VBA beginner and I need Please your help to create loop that will generate a date for each operation order based on a forecast by month.

In one hand I've forecast table

Site Operation_ID Month Quantity

And in second hand detail table

City Operation_ID Operation_Order Year date

the final result is a date for each operation order based on the forecast

I put on attachement a example of the expected result
 

Attachments

  • Result_Example.xlsx
    16.4 KB · Views: 97

vba_php

Forum Troll
Local time
Today, 02:34
Joined
Oct 6, 2019
Messages
2,884
the final result is a date for each operation order based on the forecast
i don't think that makes sense dude. you want a DATE for each OPERATION ORDER based on the ID from the forecast table?

so i'm guessing what you want is code to calculate the date based on how many months long are listed in the "forcast table"? that's the only thing that makes sense. it doesn't look like a query will handle this. i'm guessing code is needed. it also doesn't look like the set up is even right. are the tables even related? being so early in the morning though, i could be wrong...
 
Last edited:

Mous1

New member
Local time
Today, 00:34
Joined
Dec 11, 2019
Messages
5
thank you Adam for your quick answer.
in fact, what I want is not you want a DATE for each OPERATION ORDER based on the ID from the forecast table
but you want a DATE for each OPERATION ORDER based on the forecast from the forecast table.

Yes, we can link the both table letft join on
city
operation_ID

Thanks
 

vba_php

Forum Troll
Local time
Today, 02:34
Joined
Oct 6, 2019
Messages
2,884
but you want a DATE for each OPERATION ORDER based on the forecast from the forecast table.
that's what i said, i think. it still doesn't make much sense. you have data duplicated in both tables my friend.

what's the purpose of "quantity"? SITE = paris and CITY = paris makes no sense. and YEAR = 2020 for each OPERATION_ORDER when listing every month of that year for the same OPERATION _ID in the forecast table also makes no sense.

tell me what you mean by "I want a date"? the date of what? the date of completion for an order? if so, are you trying to figure out how many months an OPERATION ORDER will take to get completed? again, if so, is QUANTITY related to this? or is "quantity" related to how many "somethings" are part of an operation order?

if I don't make sense, maybe someone else is reading this and they can understand this in a diff way.
 

Mous1

New member
Local time
Today, 00:34
Joined
Dec 11, 2019
Messages
5
The Purpose of "quantity"? is the quantity of order forecasted for ID XXX in the City ABC

SITE = CITY we can change the name
and YEAR = 2020 for each OPERATION_ORDER : 2020 is the year where the order should be done. And the forecast table with bring us in witch specific month based on the qty by month
"I want a date"? the date of what? the date of completion for an order? Yes
Let say if the forecast table said that:
FOR ID: XXX
City: ABC
Month: March
Qty:5
The expected result will be that
5 Operation orders will have a date of 2020-01-03
I put an attachment in my first post. I thank it will illustrate the logic expected result
Hope I did better to explain my needs
Regards
 

vba_php

Forum Troll
Local time
Today, 02:34
Joined
Oct 6, 2019
Messages
2,884
Let say if the forecast table said that:
FOR ID: XXX
City: ABC
Month: March
Qty:5
The expected result will be that
5 Operation orders will have a date of 2020-01-03
ha ha! I get it. you really shouldn't be using access this for man! this is an excel-oriented set up you have!

you already have all of your operation orders in order and you are just counting the number of orders, line by line, that correspond to your quantity field, and you're throwing the related date into those operation order records. you still have things set up incorrectly. but to fix your problem, you currently need this in a VBA module:
Code:
dim db as database
dim rs_details as recordset
dim rs_forecast as recordset
dim counter as long
set db = currentdb
set rs_details = db.openrecordset("detail_table")
set rs_forecast = db.openrecordset("forecat table") 'this is spelled wrong on your excel sheet

rs_details.movelast
rs_details.movefirst
rs_forecast.movelast
rs_forecast.movefirst

with rs_forecast
  do until .eof
    for counter = 1 to .fields("quantity")
      [COLOR="DarkRed"][COLOR="Red"][B]rs_details.edit[/B][/COLOR][/COLOR]
      rs_details.fields("date_due") = cdate("28-" & cstr(.fields("month")) & "-" & "2020")
      [COLOR="Red"][B]rs_details.update[/B][/COLOR]
      rs_details.movenext
    next counter
      .movenext
  loop
end with

rs_details.close
rs_forecast.close
set rs_details = nothing
set rs_forecast = nothing
set db = nothing
that assumes you put a field in your table DETAIL_TABLE named "date_due". there's no need for a 3rd table, like you've shown in your excel attachment. that CDATE() function i wrote probably won't work. I'm no date expert. but let us know if it errors out and it should be a quick fix. dates have to be enclosed in "#" symbols when written literally (hard coded). I hope I got that right!
 
Last edited:

Mous1

New member
Local time
Today, 00:34
Joined
Dec 11, 2019
Messages
5
thanks a lot for your spontaneous answers Adam, still have this error message when the code reach the date row

"update or cancelupdate without addnew or edit"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:34
Joined
Aug 30, 2003
Messages
36,118
Typically I'd expect these lines:

rs_details.Edit
rs_details.fields("date_due") = cdate("28-" & cstr(.fields("month")) & "-" & "2020")
rs_details.Update
 

vba_php

Forum Troll
Local time
Today, 02:34
Joined
Oct 6, 2019
Messages
2,884
Typically I'd expect these lines:

rs_details.Edit
rs_details.fields("date_due") = cdate("28-" & cstr(.fields("month")) & "-" & "2020")
rs_details.Update
thank you paul! I forgot those! :rolleyes: Mous, I highlighted what I missed in RED. sorry!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:34
Joined
May 21, 2018
Messages
8,463
A little different take but does the assignments

I would assume that the forecast table would have a year field so I added a field TheYear. I assumed that the final table is the same as the detail table so I simply added a field DateAssigned. With that the following code will do the assignments into tblDetail

Code:
Public Sub LoopOpID()
  'Change as needed
  Const tableName = "tblForecast"
  Dim strSql As String
  Dim ID As String 'I made the ID a string
  Dim theYear As Long
  Dim rs As DAO.Recordset
  'I added a year field to the forecast table, I assumed this is needed
  Set rs = CurrentDb.OpenRecordset("Select Distinct Operation_ID, TheYear from " & tableName)
  'loop the op ID
  Do While Not rs.EOF
    ID = rs!operation_ID
    theYear = rs!theYear
    'Debug.Print ID
    CreateFinal ID, theYear
    rs.MoveNext
  Loop
End Sub

Public Sub CreateFinal(ID As String, theYear As Long)
  Const tableForecast = "tblForecast"
  Const tableDetail = "tblDetail"
  Dim rsForecast As DAO.Recordset
  Dim rsAssignment
  Dim strSql As String
  Dim theMonth As Integer
  Dim theAmount As Integer

  'Your names do not match up
  strSql = "select * from " & tableForecast & " where Operation_ID = '" & ID & "' AND TheYear = " & theYear
  Set rsForecast = CurrentDb.OpenRecordset(strSql)
  'This assumes your forecast does not exceed the amount available
  'This assumes that the final table and the detail table are the same table.
  Do While Not rsForecast.EOF
    theMonth = rsForecast!Month
    theAmount = rsForecast!quantity
    Debug.Print theMonth & " " & theAmount
    If theAmount > 0 Then
      strSql = "Select TOP " & theAmount & " * FROM " & tableDetail & " WHERE Operation_ID = '" & ID & "' AND DateAssigned IS NULL"
      Set rsAssignment = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
        Do While Not rsAssignment.EOF
           rsAssignment.Edit
           'Added as Date Assigned field to the detail table
        
           rsAssignment!DateAssigned = Format(DateSerial(theYear, theMonth, 1), "mm/dd/yyyy")
           rsAssignment.Update
           rsAssignment.MoveNext
        Loop
    End If
    rsForecast.MoveNext
  Loop
  
End Sub

A reserved word is a word that has a SQL or VBA meaning. So words like Table, Field, Count, Month, Year etc do not make good fields names. They have to then get enclosed in [] when used in any code. So instead of year or month I would use TheYear or TheMonth or TheDate
 

Mous1

New member
Local time
Today, 00:34
Joined
Dec 11, 2019
Messages
5
Thanks buddy, I can see now a result, but it s not the expected one , because

1-we need to count the number of orders, line by line by city by operation ID, 2-that correspond to the quantity field by ID by City

3-throwing the related date into those operation order records.
 

vba_php

Forum Troll
Local time
Today, 02:34
Joined
Oct 6, 2019
Messages
2,884
I'm "out of the office" as far as this forum goes, for a little while now. but I'll check back with you in a bit to see if someone else has help you in this last step. if not, I"ll look at it.
 

vba_php

Forum Troll
Local time
Today, 02:34
Joined
Oct 6, 2019
Messages
2,884
1-we need to count the number of orders, line by line by city by operation ID, 2-that correspond to the quantity field by ID by City
this is EXACTLY what I was saying earlier. if that's what you want, you would write a query in access to get it, however your data is not structured to do that. you want results referred to as "aggregate" results. thus, you need to change the structure. especially if your going to manage this same data down the road with your company.

as far as what you're now saying, all of the data you want to see should be able to be viewed on a form, but putting functions in the control sources of textboxes. is that fine? I don't think the code anybody wrote here would need to be modified. but in case you want the code modified, I mirrored you xl sheet and put it into an access file so it can be changed with little trouble.
 

Attachments

  • example.zip
    28.7 KB · Views: 98

Users who are viewing this thread

Top Bottom