copy data for 2 week blocks (1 Viewer)

[kbrooks]

We have a very simple database showing the menu for a given date....both the sandwich and the hot plate (full meal) being served. Only 4 fields are involved:
ID (Autonumber)
MDate (Date field)
Sandwich
HotPlate


The Sandwich entries will have to be entered manually because they change depending on what kitchen staff feel like making, I guess.

The Hot Plate, though, is on a 2 week rotation and will very rarely change. Is there a simple way to copy forward that 2 weeks of meal information? Currently we enter everything, including the date, manually. So I guess in order to do that we'd have to have the date fields fill in automatically?

In a perfect world, I'd like to click a button and have it enter the next 28 days (or 30, or whatever) worth of records.....the ID, the Mdate, and the HotPlate. And then someone would have to come along and manually enter in the Sandwich at a later time.

Any chance of this?

 

[DCrake]

Try this:

Public Function PopulateTable(strHotPlate As String,nDays As Integer)

Dim Rs As DAO.Recordset
Dim dtmDate As Date
Dim x As Integer

Set Rs = CurrentDb.OpenRecordset("YourTableNameHere")

If Not Rs.EOF And Not Rs.BOF Then
   Rs.MoveLast
   dtmDate = DateAdd("d",1,Rs("MDate"))
Else
   dtmDate = Date()
End If

   For x  1 to nDays 
      Rs.AddNew
      Rs("MDate") = dtmDate
      Rs("HotPlate") = strHotPlate
      Rs.Update
      dtmDate = DateAdd("d",1,dtmDate)
   Next
Rs.Close
Set Rs = Nothing

Msgbox nDays & " record(s) added to the database",vbInformation,strHotplate

End Function

Then from your form get the user to select the hotplate and enter a value for the number of days into a text box then on a command button enter the following on the OnClick event

Call PopulateTable(Me.Hotplate, Me.TxtDays)

Psuedo code
The function opens you table
checks if there is any records in the table, if so it gets the last date and adds one day to it, if not it uses today's date.

Then it performs a loop based on the number of days that the user has selected (nDays)

Add a new record using the calculated date and the name of the hotplate (strHotPlate)

When it has finished it brings up a message box to let the user know that it has added them correctly.

Remember this is all aircode and has not been tested. Don't forget to use your table and field names. Not the ones I've used.

Let me know how you get on.

David

 

[kbrooks]

Wow, thank you for the time you spent on this, I appreciate it!

I got about halfway through (added the function) but when I got to the form I wasn't quite sure I'd explained myself right. If I'm reading this code right, it will have them select one hot plate, select a number of days, and it will copy that one hot plate on x # of days?

What I'm wanting to do is take a 2 week block of hot plates and copy and paste it to the next 2 weeks, then the next 2, etc.

So as an example, starting with today's date and only going 7 days:
01/22/2009 Pork chops
01/23/2009 Roast beef
01/24/2009 Pork roast
01/25/2009 Chicken
01/26/2009 Fish Sandwich
01/27/2009 Swiss steak
01/28/2009 Turkey


Now I would want some kind of function to automatically take that block of meals and move it forward the next 7 days:
01/29/2009 Pork chops
01/30/2009 Roast beef
01/31/2009 Pork roast
02/01/2009 Chicken
02/02/2009 Fish Sandwich
02/03/2009 Swiss steak
02/04/2009 Turkey


My apologies if I'm reading your code wrong!

 

[DCrake]

In that case what you need to do is to pass the number of records you want to copy to a function

Public Function CopyAndPaste(AnyValue As Integer) As Boolean

Dim Rs As DAO.Recordset
Dim Tomorrow As Date
Dim ArrayHotPlate(100)

Set Rs = CurrentDb.OpenRecordset("Select * From YourTableName Order By YourDate")

If Not Rs.EOF And Not Rs.BOF Then
   Rs.MoveLast
   Tomorrow = DateAdd("d",1,Rs("YourDateField"))

   If Rs.RecordCount < AnyValue Then
      'Not enough records to copy forward
      CopyAndPaste = False
      Exit Function
   Else
      'Retreat up the table the required number of steps
      For i = 1 To AnyValue
          Rs.MovePrevious
      Next
     
     For i = 0 To AnyValue -1
         'Copy the hotplates into an array
         ArrayHotplate(i) = Rs("Hotplate")        
     Next
     'Now we have got the hotplates in an arry we can use this list to  
     'add them to the table in correct order.

     For i = 0 To AnyValue -1 
        Rs.AddNew
        Rs("YourDateField") = Tomorrow
        Rs("Hotplate") = ArrayHotplate(i)
        Rs.Update
        Tomorrow = DateAdd("d",1,Tomorrow)
     Next
   Else
     'No records in database (nothing to copy)
     CopyAndPaste = False
   End If
   Rs.Close
   Set Rs = Nothing
   CopyAndPaste = True

End Function

Hope this helps

David

 

[stopher]

Here's what I'd do. Rather than look back as previous menus...

Create a new "StandardMenu" table:
Day____HotPlate
1______Pork chops
2______Roast beef
3______Pork roast
4______Chicken
5______Fish Sandwich
6______Swiss steak
7______Turkey

Then the VBA function only has to loop through the next x days and lookup the hotplate required from this table (via a recordset/array) according to the day number of the date being added.

The benefit is you have an easy way to maintain your StandardMenu and it won't be affected by any changes may to the previous menu e.g. pancake day (yum, yum). The principle is, you don't care what happened in the past, all you need to know is that tomorrow is Tuesday and on Tuesday we have Pork Roast. I think the code will be easier too.

David's code could easily be adjusted to do this.

hth
Chris

PS
David, looking at the logic in your code, what happens if AnyValue say 3. Does your code not take the last three days and copy them forward as the next three days? This would result in Pork, Chicken, Fish then Pork Chicken, Fish again. Although I suspect I've misunderstood your code. If not then the StandardMenu would alleviate this issue.

Chris

 

[DCrake]

Chris
According to the spec port chix fish, pork chix fish was what was ordered. I agree with your concept, having said that I don't like either as it becomes a bit monotenous. What happens if you want to introduce a new flavour? Using a table you would have to change the sort order to insert curry at day 4.

 

[stopher]

Chris
According to the spec port chix fish, pork chix fish was what was ordered. I agree with your concept, having said that I don't like either as it becomes a bit monotenous. What happens if you want to introduce a new flavour? Using a table you would have to change the sort order to insert curry at day 4.

Good point. I'd (perhaps incorrectly) assumed that the requirement was to replicate the menu for a fixed seven day week. As you say it could just as easily be a rolling 4 day menu. In which case I would still favour a separate table but with a sequence number as the key. Again not ideal for the reason you mentioned.

Chris

 

[kbrooks]

Thank you (both) again for your help.

For the 2nd bit of code you gave me, would you call that with a command button on the form? I put a button on and tried to put the code in the On Click event, but it puts a Private Sub line on first that apparantly doesn't play nice with the Public Function you gave me, as I get errors. Am I understanding incorrectly?

I do like the idea of a separate table with a list of the rolling 14 day menu. A lot, actually, in case they change it down the road. So I did set that up.
Table StandardMenu, fields MealID and Meal
The original table is called Menu, fields are MDate and HotPlate

I apologize, but I have absolutely no clue how to change the code to accommodate this new table. If you'd have a chance to look at it, it would be greatly appreciated.

 

[stopher]

Here's some code (based on David's). The inputs are the date where you want the standard menu to start from, and an integer for how many times you want the standard menu added.

The code will deal with a Standard menu of any number of days.

Public Function AddStandardMenu(StartDate As Date, intRepeat As Integer)
Dim rsSM As DAO.Recordset 'record set for standard menu
Dim rsLM As DAO.Recordset 'record set for live menu
Dim ArrayHotPlate(100)
Dim i As Integer
Dim r As Integer
Dim intMenuSize As Integer

Set rsSM = CurrentDb.OpenRecordset("Select * From StandardMenu Order By MealID")
Set rsLM = CurrentDb.OpenRecordset("Menu")

'Get the standard menu into the array
i = 0
Do While Not rsSM.EOF And Not rsSM.BOF
    i = i + 1
    ArrayHotPlate(i) = rsSM("Meal")
    rsSM.MoveNext
Loop
'get the size of the menu i.e. how many days
intMenuSize = i

'repeatedly put the menu from the array into the Menu table
'note that the date offset can be calculated from (r * intMenuSize) + i - 1
'with the first date offset being zero (when r=0 and i=1)
For r = 0 To intRepeat - 1
    For i = 1 To intMenuSize
        rsLM.AddNew
        rsLM("MDate") = StartDate + (r * intMenuSize) + i - 1
        rsLM("Hotplate") = ArrayHotPlate(i)
        rsLM.Update
    Next i
Next r
    

rsLM.Close
Set rsLM = Nothing

rsSM.Close
Set rsSM = Nothing

End Function

There is pretty much nothing in the way of error checking. So if you decide to enter a Tuesday date instead of a Sunday date, it will not care but of course you could add some code to check for this.

Also, the code does not do anything to manage errors caused by record conflicts e.g. trying to add a date that already exists. Again you can (and should) add this.

hth
Chris

 

[kbrooks]

This is great stuff, thank you!

How do I call this function, though? I thought a button's OnClick event, but can't get it to work. Or is there a better way to do this than a command button? I want one click of the button to copy ahead all 14 days in the Standard Menu.

 

[stopher]

Yes, create an Onclick event.

Then put the following line in (for example):

CALL AddStandardMenu("1/2/9",1)

You can change the inputs to refence fields on your form or you could prompt the user for input.

hth
Chris

 

[kbrooks]

Ah....I was close.....I just had CALL GenerateMenu and it wasn't liking that.

The date you have for an input, I assume that would be a starting point, like the first date you want added?
What does the last 1 indicate, though?

 

[stopher]

yes to the date input.

the integer input is the number of times you want to add the standardmenu

chris

 

[kbrooks]

I'm sorry....I'm annoying myself so I'm sure I'm driving you absolutely buggy. But I'm apparantly doing something wrong because I'm getting an error.

I saved the code (in post #9) as a module and saved it as GenerateMenu.

On my form, I put a command button on the top. In the On Click event, I entered:
CALL GenerateMenu("1/2/9",1)

When I click the command button, I get:
Microsoft Access cannot find the macro 'CALL GenerateMenu("1/2/9",1)'.

Why is it looking for a macro?

Also, I created a field on the form and called it StartDate. I'd want the function to start generating menu entries starting with the date entered here. So would I put that in the On Click event instead of a hard coded date? (ex: CALL GenerateMenu("StartDate",1))

 

[stopher]

Don't worry. The penny will drop soon. Stick with it.

You have a button and you obviously know where the OnClick line is. However, it sounds like you have just typed on that line. Instead, if you click (once) on that line you will see a dropdown symbol and also a "three dots" symbol on the far right of the line. Click on the "three dots". This will give you a choice and you need to select "Code Builder".

This takes you to the code screen and you will see the empty OnClick code has been created. This is where you write the code for what you want that button to do. In this case you want to call the function you have created earlier. So add the call line and it should end up looking something like this:

Private Sub CreateMenuButton_Click()
Call AddStandardMenu("1/2/9", 1)
End Sub

If you have copied the code from #9 exactly then the function is called AddStandardMenu (see the first line of the code). The name you give to the module is irrelevant (at least for the purposes of this exercise!).

If you're still stuck I'll do an example.

Chris

 

[kbrooks]

Hallelulah, it works! You're right, I was typing the CALL command right on the event line instead of in the code. Got that fixed (and also changed it to pull the date from my StartDate field) and it works beautifully!

Thanks for your help (and patience!)

 

[stopher]

Nice one. Credit to dcrake as well of course (since I pinched his code )