Help with loop (1 Viewer)

eacollie

Registered User.
Local time
Today, 08:51
Joined
May 14, 2011
Messages
159
I need help in creating a loop through a recordset.

I have data containing information about event meals. Each event has a start date, end date, first meal, first meal date, last meal and last meal date. I need to loop through the data and create a record in a table for each date that a meal should be prepared in the following format:

event#, meal date, # for breakfast, # for lunch, # for dinner

So, for example, event #1 starts on 1/1/2018, the first meal is lunch on 1/1/2018 and the last meal is dinner on 1/3/2018 and has 8 people attending.

The records created should be as follows:
1, 1/1/2018, 0, 8, 8
1, 1/2/2018, 8, 8, 8
1, 1/3/2018, 8, 8, 8

Thank you
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:51
Joined
Apr 27, 2015
Messages
6,281
Going to need a little more information. I assume the event information is in a table already and you want to add more information for each record in that table?

You may be able to accomplish what you want with a simple action query instead of a VBA loop. If you can provide a little more information, we will be in a better position to help you.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:51
Joined
Jul 9, 2003
Messages
16,244
I agree with John, you may be better off giving a more detailed explanation of what you want so we can set you off in the right direction!

You may also be interested in a Blog on my website here:-

Display Field Data Horizontally

Where I've started putting together a set of videos which cover a Recordset Loops...

On another blog page there's a different example which is more specific to creating a Checklist but you may glean some information about Recordset-loops from the videos and text therein:-

Recordset Loops How To
 

eacollie

Registered User.
Local time
Today, 08:51
Joined
May 14, 2011
Messages
159
Thank you. Yes, I have a table of events which contains the data. I need to display the data on a form as outlined above for each day.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:51
Joined
Apr 27, 2015
Messages
6,281
In that case a bound form with a Group on the Event and Date should do the trick.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:51
Joined
May 21, 2018
Messages
8,463
I think I get it. You have table 1 with EventID, StartDate, EndDate, StartMeal, EndMeal, NumberPeople.

From that you want to create the needed number of records for each day. The part that was missing from your post is where to get the number of people and the start and end meal. I assume that info is in the first table. If so this is doable with a loop and insert query.
 

eacollie

Registered User.
Local time
Today, 08:51
Joined
May 14, 2011
Messages
159
The number attending is not a difficulty. I'm more concerned about the process of how I get from a starting point (first meal date and last meal date) to filling in the information for each day. I don't know how to explain it any more clearly....sorry. If this can be done through an action query that would be fantastic. I can't figure out the logic though.
 

Cronk

Registered User.
Local time
Tomorrow, 02:51
Joined
Jul 4, 2013
Messages
2,770
I would add a record for the first date, test if Start Meal is B, L or D and insert the appropriate number of meals, then loop through the second to second last, adding a record with the 3 meals, then for for the last day, do the same as the first day but in reverse.


I would be first checking first for where the event was only 1 day (StartDate = EndDate)



I'd be inclined to use a recordset to add records unless the process was being done multiple times or there was a huge number of data items to process.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:51
Joined
May 21, 2018
Messages
8,463
Code:
Public Sub InsertMeals(EventID As Long, StartDate As Date, EndDate As Date, StartMeal As String, EndMeal As String, No_People As Integer)
  Dim strSql As String
  Dim TheDate As Date
  Dim No_Breakfeast As Integer
  Dim No_Lunch As Integer
  Dim No_Dinner As Integer
  TheDate = StartDate
  Do
    No_Breakfeast = No_People
    No_Lunch = No_People
    No_Dinner = No_People
    If TheDate = StartDate Then
      Select Case StartMeal
      Case "Lunch"
        No_Breakfeast = 0
      Case "Dinner"
        No_Breakfeast = 0
        No_Lunch = 0
      End Select
    ElseIf TheDate = EndDate Then
      Select Case EndMeal
      Case "Breakfeast"
        No_Lunch = 0
        No_Dinner = 0
      Case "Lunch"
        No_Dinner = 0
       End Select
    End If
    strSql = "INSERT INTO tblMeals(EventID, MealDate, No_Breakfeast, No_Lunch, No_Dinner) Values "
    strSql = strSql & "(" & EventID & ", " & SQL_Date(TheDate) & ", " & No_Breakfeast & ", " & No_Lunch & ", " & No_Dinner & ")"
    Debug.Print strSql
    CurrentDb.Execute strSql
    TheDate = TheDate + 1
    'If MsgBox(TheDate & " " & EndDate & " Exit?", vbYesNo) = vbYes Then Exit Do
  Loop Until TheDate > EndDate
  
End Sub

Public Function SQL_Date(varDate As Variant) As String
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQL_Date = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQL_Date = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    Else
      SQL_Date = "NULL"
    End If
End Function

Public Sub testInsert()
  InsertMeals 1, Date, Date + 5, "Lunch", "Lunch", 10
End Sub

This creates this
Code:
ID	EventID	MealDate	No_Breakfeast	No_Lunch	No_Dinner
1	1	7/16/2018	0	10	10
2	1	7/17/2018	10	10	10
3	1	7/18/2018	10	10	10
4	1	7/19/2018	10	10	10
5	1	7/20/2018	10	10	10
6	1	7/21/2018	10	10	0
 

eacollie

Registered User.
Local time
Today, 08:51
Joined
May 14, 2011
Messages
159
Thank you Cronk!
I think I've got something to work
(1) Checking if the first meal date is the same as the last and adding records for this date
(2) If they are different, set up the first meal date, then do a do while loop to create a record for each intermittent date
(3) Checking for the last meal date

It appears this is exactly what you suggested!! Thank you.
 

Mark_

Longboard on the internet
Local time
Today, 08:51
Joined
Sep 12, 2017
Messages
2,111
One difference I'd do from what MajP posted; If no meal is being served don't add a record. This avoids the issue when you have event 1 ENDING with breakfast on July 1st but even 2 STARTS with dinner on July 1st. This also avoids issues when you want to see if a given table is available for other reasons. If no record, it is available. That way if you have something happening on July 1st and your not sure what tables are available for lunch, you go through your tables table (TblTable? TableTable?) to see what isn't being used by doing a DLookup and seeing if you get a null back. Also allows you to intentionally add a "0" when you don't want a table used even though the group is only having breakfast and dinner.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:51
Joined
May 21, 2018
Messages
8,463
One difference I'd do from what MajP posted; If no meal is being served don't add a record. This avoids the issue when you have event 1 ENDING with breakfast on July 1st but even 2 STARTS with dinner on July 1st.
It already does that. You pass in an EventID and Start Date, End Date. It only makes records between those dates for a given event. So above you get two records records actually

7/1/2018 Event1 10 0 0
7/1/2018 Event2 0 0 15

If event 1 ended 2 days before event 2 you would not get any records from end of event1 to start of event2

Now this is the OPs table design, not mine. I think I would have designed my table different where there is a record for each meal instead of one for each day.

TblMeals
Event_ID_FK 'foreign key to the event id
Meal_type 'values of breakfeast, lunch, dinner
No_Persons 'number of persons for that meal

This way there is no record created if an event is not having a specific meal.
 

Mark_

Longboard on the internet
Local time
Today, 08:51
Joined
Sep 12, 2017
Messages
2,111
@MajP,

I do apologize, I misread the code and thought it was individual records (as it should be) instead of ONE record with breakfast/lunch/dinner.

@OP,
See MajP's last post. Better way to do what you are looking for. Also allows for other time blocks, if needed.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:51
Joined
May 21, 2018
Messages
8,463
If you would do it the other way, you will need a helper function to loop the meals.

Code:
Public Sub InsertMeals2(EventID As Long, StartDate As Date, EndDate As Date, StartMeal As String, EndMeal As String, No_Persons As Integer)
  Dim strSql As String
  Dim TheDate As Date
  Dim CurrentMeal As String
  
  TheDate = StartDate
  CurrentMeal = StartMeal
  Do
    Do
      strSql = "INSERT INTO tblMeals2(EventID, MealDate, MealType, No_Persons) Values "
      strSql = strSql & "(" & EventID & ", " & SQL_Date(TheDate) & ", '" & CurrentMeal & "', " & No_Persons & ")"
      'Debug.Print strSql
      CurrentDb.Execute strSql
      If TheDate = EndDate And CurrentMeal = EndMeal Then Exit Do
      CurrentMeal = GetNextMeal(CurrentMeal)
      'If MsgBox(TheDate & " " & EndDate & "  " & CurrentMeal & " Exit?", vbYesNo) = vbYes Then Exit Sub
    Loop Until CurrentMeal = "Breakfeast"
     
  '  CurrentDb.Execute strSql
    TheDate = TheDate + 1
  Loop Until TheDate > EndDate
  
End Sub

Public Function GetNextMeal(LastMeal As String) As String
  Select Case LastMeal
     Case "Breakfeast"
        GetNextMeal = "Lunch"
     Case "Lunch"
        GetNextMeal = "Dinner"
     Case "Dinner"
        GetNextMeal = "Breakfeast"
  End Select
End Function

Public Sub testInsert()
  InsertMeals2 1, Date, Date + 2, "Lunch", "Lunch", 10
  InsertMeals2 2, #7/30/2018#, #8/2/2018#, "Dinner", "Breakfeast", 25
End Sub

That gives me
Code:
EventID	MealDate	MealType	No_Persons
1	7/16/2018	Lunch	10
1	7/16/2018	Dinner	10
1	7/17/2018	Breakfeast	10
1	7/17/2018	Lunch	10
1	7/17/2018	Dinner	10
1	7/18/2018	Breakfeast	10
1	7/18/2018	Lunch	10
2	7/30/2018	Dinner	25
2	7/31/2018	Breakfeast	25
2	7/31/2018	Lunch	25
2	7/31/2018	Dinner	25
2	8/1/2018	Breakfeast	25
2	8/1/2018	Lunch	25
2	8/1/2018	Dinner	25
2	8/2/2018	Breakfeast	25
 

Users who are viewing this thread

Top Bottom