473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Using VBA to perform action on every field in a table

Hi,
I am an ICT Student and I am designing a work related project which is using MS Access 2007 to create a database to monitor leave for fire brigade staff.

I have studied relational database with SQL but am new enough to Access.

The shift patternns follow a 28 day cycle. I have a table with fields containing dates

My question is: how might I, using VBA, or otherwise add 28 days to this table to create a new table of dates, without having to do it manually.

Thanks in advance.

Alan
Apr 3 '09 #1
23 2425
NeoPa
32,556 Expert Mod 16PB
@irishalanm
Hi Alan. Welcome to Bytes!

I'm not clear what you mean by adding 28 days to a table. I'm sure we can help you (VBA has the power), but we need a clearer understanding first of precisely what you're trying to achieve.
Apr 4 '09 #2
Ok Sorry about being less than clear!

What I want to do is advance each date in a table of dates by exactly 28 days. I suppose I would have to set up a loop and for each field value use the AddDate() function to add 28 to each and either append the new values in the existing table or create a new table, leaving the original dates unaffected. Does that explain it any better?

Thanks again

Alan
Apr 7 '09 #3
NeoPa
32,556 Expert Mod 16PB
I would do it in SQL, but would need an idea of what the field to be changed is called (when the new record is added), as well as a way of determining which records to copy after there are a number of duplicates in the table.

How about posting the table structure to look at, and explaining, in that scenario, how you would want to identify the records that need to be copied.
Apr 7 '09 #4
Hi NeoPa,
Thank you for your time and apologies for the delay in reply. I think I need to give you some background to my project so you can see what I am trying to achieve. I am designing a database to monitor leave (days off) for a company that has different groups of staff on different shifts. Because the employees work more hours than the standard week they are entitled to different leave throughout the year. These are rostered to every employee at different times of the year but they can exchange or swap dates of leave for different dates provided the shift patterns are the same or similar. My main problem is to devise a method to auto-populate tables with dates so that the dates are correct, as mistakes can be made manually, and also because inputting the dates would be too time consuming.

For each of the groups of workers, their shift pattern repeats ad infinitum EVERY 28 DAYS. So if I can find a way that a table could be populated based on one 28 day block by continually adding 28 days thast might solve my problem.

Also I need to refer to a calender so that I can cross reference what day of the week a particular date is.

I have been trying to find out if I can get 'under the bonnet' of the calender control tool in MS Access 2007 but so far all I have been able to find is a way to add a calender date picker to a form or use the date picker to pick individual dates on a table.

I have seen comments on importing outlook calender from outlook using VBA?

Any other ideas?

Regards

Alan
Apr 15 '09 #5
NeoPa
32,556 Expert Mod 16PB
I'm starting to get a picture, but see various potential problems. I need a better understanding I expect.

What sort of data are you storing for a 28-day block? Some example data would help greatly.

Clearly, advancing by 28 days avoids any weekday issues, but holidays may be lost from one to the next. Possibly a template would be preferable. I still need a clearer understanding to advance any.
Apr 15 '09 #6
NeoPa
32,556 Expert Mod 16PB
Consider also, any block of 28 days will be interesting within that block, as well as for a certain period before and after it. Having only one set of the data in the table at any one time is likely to be too limited.
Apr 15 '09 #7
I am storing several tables with nothing but dates in them and I need to have a calender in a table as well. I plan to cross reference dates. I have seen previous thread where ADezii posted an example database from Philly Fire Dept a while back but the link has been removed so i couldnt see it but it sounds like it could be similar to what I want to achieve.

Regards

Alan
Apr 16 '09 #8
Dan2kx
365 100+
Could you store this shift information as a day number, eg

Expand|Select|Wrap|Line Numbers
  1. StafffID     DayNo    Shift (hrs?)
  2. 1    1    7.5
  3. 1    2    7.5
  4. 1    3    8.5
  5. 1    4    9
  6. …        
  7. 2    1    7.5
  8. 2    2    7
  9.  
then use a Mod 28 function on said dates to pick out patterns on the fly, storing leave dates elsewhere if needed?

Dan
Apr 16 '09 #9
NeoPa
32,556 Expert Mod 16PB
How about a set of template data. This could be stored in a separate table.

If the results you're after is simply a set of dates (no other fields as indicated in post #8), then the template could be a set of records storing date offsets only. That way, on being passed a base date, you could easily create a set of dated records which match the offsets stored in the template table, beyond the base date given.

This could be called whenever a new block of dates is required.

Does this sound like a viable solution?
Apr 16 '09 #10
The problem is I don't know how to do it. Do I use VBA with the DateAdd() function? Does anyone know how to access the calendar control function in Access 2007 or is this possible?
Thanks

Alan
Apr 16 '09 #11
Hi Dan
The tables contain dates and an important part of the project is to cross reference these dates. I was thinking about using DateAdd() function but I don't know how I would access the table in VBA or otherwise.

Thanks

Alan
Apr 16 '09 #12
Dan2kx
365 100+
If i understand the problem correctly then you dont need to store and update dates,you can use the template to store shift patterns and then refer to that as a cycle of 28 days. you can then in another table if you need to store specific dates for someone to be "off". and with regard to ADezii's calendar you easily adapt it to show information from this template table.
Apr 16 '09 #13
NeoPa
32,556 Expert Mod 16PB
@irishalanm
If the template held offset values as suggested, you could create some SQL to use DateAdd() with the offset field value as the offset and a reference to the date on your form somewhere as the base date to which the offset is applied.

IE. Records with offset values of 1, 2 & 5 and a base date (on your form) of 20 April 2009 would produce date records in your destination table of :
21 April 2009, 22 April 2009 & 26 April 2009 respectively.

Does that make the concept easier to follow?
Apr 17 '09 #14
Yes sounds good-could someone attach ADezii's calendar? I'm afraid the attachment is no longer there from thread way back.

Thanks

Alan


@Dan2kx
Apr 17 '09 #15
ADezii
8,834 Expert 8TB
@irishalanm
irishalanm, If you could provide me with some sample Table Data, I'll see if I can integrate it into the Calendar for you. You can provide Date Ranges and the code will convert it to linear Date Values but remember to keep the Display Fields limited and short, since space is of the essence in the Calendar Display.
Apr 17 '09 #16
Hi ADezii,

I am doing a project for college which is work-related and I want to cross reference dates in tables which are particular to a group of people with a calendar as well as various rosters they have been assiged holidays to. Part of my project relates to researching how a similar problem might have been solved by someone else. I appreciate your offer and understand if you don't want to show your work. But I need to solve the problem myself and need some expert guidance!!! So i don't think there is any point giving you data as I don't even know what your calendar does?

I can put all the dates in my fields manually and I can painstakingly enter the dates and days into a table to form a calendar but I am trying to find out if there is a way to autopopulate a field with calender dates. See word doc attached.

regards

alan

@ADezii
Attached Files
File Type: zip alan.zip (91.6 KB, 118 views)
Apr 18 '09 #17
FishVal
2,653 Expert 2GB
Hi, Alan.

I think you may find some tricks described in this thread useful in building your application.

Regards,
Fish.
Apr 18 '09 #18
ADezii
8,834 Expert 8TB
@irishalanm
Hello Alan, I've modified the Code in the Calendar Database in order to give you some idea of the functionality which it can expose. tblEvent contains the Primary Data, and the Calendar will provide a visual indication as to Events, their Owners, Event IDs, and the specific Ranges for those Events. Should you need any additional help, just let me know. FYI, Date Ranges specified in tblEvent are converted to linear Values in tblEventTemp which then supplies the required Data for the Calendar display. See the Atachment for the DB.
Apr 18 '09 #19
Thanks a million ADezii,

The Calendar application looks amazing-I haven't had a chance to experiment with it yet.
Thanks for the link as well Fish-I haven't had a chance to look at it yet.

Regards

Alan
Apr 18 '09 #20
Hi everyone (who might be interested;))
I have found a way to append dates to the table -it is not sophisticated but it works.

First I create the table which will store the dates I need.
Table [Watchdates-A/B]-contains a single Date/time field [A/B WatchDate] which is a PK
I add dates that are worked in a single 28 day period for a certain shift -This amounts to 7 dates.

I then create an Append table query which will move each date forward by 28 days.
I declare the following field and set the query to append new dates to the existing table:

New Dates: DateAdd("d",28,[Watchdates-A/B].[A/B WatchDate])

I run the query over and over..click tap tap tap(After about a minute I have a table with 5-600 fields enough for the next few years).

Because no duplicates are allowed as it is a primary key no duplicates are appended into the table.

I'm sure there is a more clever and sophisticated way to do this, but it works.

Regards

Alan
Apr 25 '09 #21
@ADezii
Hi Adezii,
I'm not sure what your calendar project does or how it works-I can only see the form-it looks good though!!

regards

Alan
Apr 25 '09 #22
@FishVal
Hi Fish,
I amended your flat calender query to add the day of the week. It is brilliant. i can't believe how easy it is (when you know how!). I guess the moral of the story is it is all about using the in-built functions and SQL. I used the Weekday() function to convert the date to the day of the week.

Thanks very much

Regards

alan
Attached Files
File Type: zip Calendar-Leave database.zip (30.6 KB, 122 views)
Apr 25 '09 #23
NeoPa
32,556 Expert Mod 16PB
That's good to hear Alan.

However it works, working is good :)
Apr 25 '09 #24

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Bret Shortall | last post by:
Using the SENDOBJECT action command in a Macro works fine if you hard code in their email address. SENDOBJECT TO bret@emailaddress.com SUBJECT This is the Subject line...
3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
15
by: Tony Williams | last post by:
I have two forms, frma and frmb, both are for inputting new records and are based on a table, tblmonth, both have two controls, txtmonth and txtmonthlabela which are based on the fields in the...
1
by: Daveyk0 | last post by:
Hello there, I have a front end database that I have recently made very many changes to to allow off-line use. I keep copies of the databases on my hard drive and link to them rather than the...
3
by: anthonybrough | last post by:
I have an asp page that has a form to collect user data in a form. when the user clicks submit the input is validated. If any fields are not acceptable the user clicks on a button to go back to...
4
by: Lauren Quantrell | last post by:
I have just put together a vb.net app and now need to provide it to users. This application needs to run the code in a sub every 60 seconds from a Windows Service application. I have the...
1
by: whizkid | last post by:
Hi... Looking for a solution to a problem that I am facing... Basically I have a table schema defined as follows. Table X( br char(2), Dealno char(8) SeqNo char(4)
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
3
by: hutch75 | last post by:
Question could also be asked, how to compare data between two arrays and perform an action (print cmd) everytime there is a match? The problem I'm having with the code below is that the comparison...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.