By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Microsoft Access 2010, insert multiple records based on specific days in date range

P: 3
I have an Access 2010 database that our staff use to keep track of appointments and activities. Some events occur weekly, and I would like the staff to be able to create those entries by selecting the day of the week and the start and end dates of the event. I am relatively new to VBA coding. I have found some examples that appear to replicate the entry daily across a series of dates, but I do not know how to tell Access to put in the entry for, say, every Wednesday, between those dates.

The staff are entering the information in a form bound to the main table. xtxBeginningDate and txtEndingDate are the fields for the dates. cmbWeekday allows users to select the day of the week. The table is tblEventsRecords and the form is fmEventsRecords. I have a CalendarDates table.
4 Weeks Ago #1

✓ answered by Demeter

I have solved this problem. For anyone who is seeing this and interested in the answer, here it is.

This site gives the way to add an autonumber to a query using a user defined function.

https://access-excel.tips/add-auto-n...-access-query/

Applying the Dmax function to the field name:

Auto: wAutoNumber([ID])+DMax("ID","Event Records")

returns a sequential number starting one number higher than the last autnonumber in the table.

Turn this query into an append query and run.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,419
This will require code as this is a job you're requesting.

If you want help with it then please come back when you've started and are struggling with a particular aspect. We're not here simply to do your jobs for you.
4 Weeks Ago #2

P: 3
I have started. This database has been actively in use by the staff for over a year without issues. I have done extensive searches on how to do this, as I said in my original post, and have come across code that works on situations like mine, but it will insert a record for every day in that date range, not just for one particular weekday in that date range. A record for every day is not appropriate in this situation.

In my attempt to do this myself I have created a CalendarDates table, a query that will return all the weekdays in the date range, and and query that will duplicate the current record for every day selected by the weekday query, which gives me the exact records that I want. However, they can't be appended, updated, or saved to the original table because they don't generate the required automunber.

I am not asking anyone to do my job for me, I am asking for help with one specific part of it. If you don't want to help, that's fine, please just move on. I am not here to be scolded.
3 Weeks Ago #3

P: 3
I have solved this problem. For anyone who is seeing this and interested in the answer, here it is.

This site gives the way to add an autonumber to a query using a user defined function.

https://access-excel.tips/add-auto-n...-access-query/

Applying the Dmax function to the field name:

Auto: wAutoNumber([ID])+DMax("ID","Event Records")

returns a sequential number starting one number higher than the last autnonumber in the table.

Turn this query into an append query and run.
3 Weeks Ago #4

Post your reply

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