Create duplicate records with incrementing dates (1 Viewer)

[guinness]

Hi Guys

I have almost no experience of VBA outside of working a little with codes generated for me like docmd etc. If I can explain in English what I am trying to do can someone point me in the right direction to start creating the code.

I am trying to create a database for resourcing staff.

• I have a form where the user inputs a start date and an end date which updates a table.
• I have a query that works out the number of working days between those dates.
• I have a table with all the working dates between now and 2016
• I want to put a duplicate record button on the form (I can do that bit)
• The tricky part is I want it to create a new record for each date in the dates table between the start and end dates.

Example 23/06/2014-18/07/2014 = 20 working days. I want to end up with 20 records with start dates as below and the same end date.

Can anyone help me get started?

23/06/2014, 24/06/2014,25/06/2014,26/06/2014,27/06/2014,30/06/2014,01/07/2014,02/07/2014,03/07/2014,04/06/2014,07/07/2014 etc up to 18/07

 

[gerry@docshop.ie]

Why do you need to have separate daily records?
Could you have a record for each date range?

 

[guinness]

Hi Gerry

That was my first thought but there were a couple of issues. The big problem is that things change. I might book someone to a project for ten days and then need to take them out for half a day on day three. If I work with a date range I would need to change the date range, add in the thing for day three then create a new date range for the remainder. If there were a record for each day I could just change that record. Also I'm a tring to display this on a crosstab with dates accross the top, names down the side and bookings as values.

I hope that makes sense

Thanks for having a look

Guinness

 

[gerry@docshop.ie]

That sounds ok to get your cross tab. Can you zip up a copy of your tables with some sample data, It should be a simple Append Query or a short VBA Routine to achieve what you need. I do something similar with Nursing Homes where I get a range of rooms, I enter 1 to start and 25 as the end and the button creates all the intermediate rooms for me. Your requirement is the same but using dates.

 

[guinness]

Thanks Gerry

I'd really appreciate it. I did think of an append query but was advised VBA would be required.

It will take me half an hour or so to get you a sample of the database.

Hope that's ok

 

[gerry@docshop.ie]

No rush I will be alerted when you get to post and I will have a look then as soon as I can.

 

[guinness]

Hi Gerry

Sample of the database attached.

Entries are made using the Resourcing form
Query 1 calculates the amount of working days between start and end date
Query 2 is the crosstab that I want to populate.

Any questions just give me a shout

Thanks again

Guinness

 

[gerry@docshop.ie]

Hi Guinness

I have modified your form and added a new button which runs a VBA Routine. I changed the names of the Combo Box fields to [cmb_Field Name] to make them more identifable. I would also suggest that in the dates table you change the field name to 'Working_Date' as 'Date' is a VBA keyword. Hope this helps

Gerry

 

[guinness]

That looks fantastic thanks Gerry

I'll need to adapt it slightly to ignore any combination of the first four fields being empty as not all fields will be filled in for things like holiday.

This will also help me with another enquiry I had on here as when 'all day' is selected I want to create an AM/PM record. You've really set me of I the right direction.

Thanks again

Guinness

 

[gerry@docshop.ie]

You are very welcome,.

Gerry