[MS_AccessPros] Re: Creating the right number of records - loop logic

 

Maybe this will get you started.

Here is a simple sub routine that will insert records into a table named tblMagEditions.

Public Sub FillEditions(intEditions As Integer, intStart As Integer, lngCampaign As Long)

    Dim db As DAO.Database

    Dim strSQL As String

    Dim strMonth As String

    Dim x As Integer

    Set db = CurrentDb

    For x = intStart To intStart + intEditions - 1

        strMonth = Format(DateSerial(Year(Date), x, 1), "mmm")

        strSQL = "INSERT INTO tblMagEditions(CampID, MagEdtn) " _

               & "VALUES(" & lngCampaign & ", '" & strMonth & "')"

        db.Execute strSQL, dbSeeChanges

    Next

    Set db = Nothing

End Sub

If you can upload a small sample database to our Files>_Assistance Needed folder we can give yu more direct help.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <rayfrew@gmail.com> wrote :

Dear Professionals

If you could give me a nudge in the right direction, it would be most appreciated.

I'm trying to create a database that reserves space in editions of magazines.

For example:

Aspen db Ltd wants a Summer campaign of adverts in 3 magazines to run for 4 editions starting with the June issue.

My database structure is:

Customer –< many Campaigns –< many Campaign / Magazine Editions

From the other end         Magazine --< Magazine Editions --< Campaign/Magazine Editions

A veritable many to many structure.

What I want to do is walk through the Campaign / Magazine Editions table and create the right number of Bookings.  If the data in Campaign / Magazine Editions was:

Rec1   Aspen db Ltd  Full Page ad in Astley Mag for 4 editions starting in June

Rec2   Aspen db Ltd  Full Page ad in Beaumont Mag for 4 editions starting in June

Rec3 Aspen db Ltd  Full Page ad in Sharples Mag for 2 editions starting in September

I want to hold Rec1 and create 4 bookings starting in June (June, July, Aug, Sept).  Then move to Rec2 and create 4 bookings starting in June (June, July, Aug, Sept).  Finally move to Rec3 and create 2 bookings starting in September (Sept, Oct).  I expect to create recordsets to walk through the data and have loops within loops to create the right number of bookings but I'm a bit flummoxed about the logic.  Any thoughts welcomed.

Many thanks

Ray

__._,_.___

---

Posted by: wrmosca@comcast.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___