Auto Populating Data

[jammin140900]

I'm putting together a database that will keep track of my Websites marketing efforts. I have a list of business websites on different niches and want to submit articles to article directories. To do this, for each website, I will have to register for an article directory to be allowed to submit articles about each niche. There are about 30 article directories that I have to register for each niche articles I submit.

So, I have a website table, where I record the names of each of my websites. I also have a 'Site' table which lists those 30 article directory websites. Finally, I have a 'Registration' table where I have a 'RegID', 'WebsiteID','SiteID' and a few other fields to record the date registered and so on.

What I want to happen, is when I create a new entry in the Website table/form, I want the Registration table to create 30 entries, with the current new Website ID and the 30 article directory Site ID's next to it. That way, I open a form I created (frmRegistration) where I should see the listing of 30 sites against the website ID/name I selected from the combo box in the Registration form, be able to tick and add successful registration details for all the 30 sites.

I've been stuck on this for days. Can anyone please help? I'm not good with VBA! -Thanks a lot..

 

[taccoo]

Is there a way for you to upload your db here so I can take a look?

 

[Mavexe]

In the AfterUpdate event of the tables form put in code that goes like this:

Assuming RegId is an autonumber
If Me.Newrecord Then
Docmd.RunSQL "Insert into Registration (WebsiteID, SiteID) Select " & Me.WebsiteID & " SiteID a from site where not exists (Select WebsiteID, SiteID from sites as b where a.WebsiteID = b.WebsiteID and a.SiteID = b.SiteID)"
End If

Of course I don't know the exact column names, but what this will do is insert every siteID with the new website ID (Which it's reading from the form) after your new records run. The where not exists portion is simply there to prevent duplicates. in the event that ID's get reused.

 

[jammin140900]

Thanks guys. I've given it a go but I'm still not able to get it to work.

Taccoo- really appreciated if you can shed some light.

Just a few notes:

frmWebsite- This is the form where I'll be adding any new websites I develop. From this, I will have navigation to the form: ASRegistration.

ASRegistration form first contains general registration information with details required to register each of the 30 or so sites below in the subform (which we have been talking about). frmASRegistrationSub

I'm a little confused on the best way to make this subform work. In essence, when adding a website, I need this subform to display for that websiteID, all the sites I can record successful registration. The other issue I need to keep in mind is that if I add sites that I can register for, I need this to be on the subform the next time I open the Registration area.

Is this possible?

 

[Mavexe]

 DoCmd.RunSQL "Insert into TblASSiteRegistration (SiteID,WebsiteID) Select tblASSitesToSubmitTo.SiteID, tblWebsite.WebsiteID from tblASSitesToSubmitTo, tblWebsite where not exists " & _
"(select tblASSIteRegistration.SiteID, tblASSIteRegistration.WebsiteID from tblASSIteRegistration where tblASSIteRegistration.SiteID = tblASSitesToSubmitTo.SiteID and " & _
" tblASSIteRegistration.WebsiteID = tblWebsite.WebsiteID)"
Me.Refresh

You need to create a form to control the entry of information for the tblASSitesToSubmitToTable, when you've created that form place that code above into the after update event for. It will fire every time you change something there and add the records you need. but unless there is a new record, it won't insert any rows.

However, I got slightly confused after that...The WebSiteID that you need to populate the tblASSiteRegistration table is generated via the tblWebsite table, I'm not sure how you're making the association between records on tblAsGeneralWebsiteReg and records on tblWebsite, but whatever form that is on is where you'd to place this code in the AfterUpdate event to have it covered. If you don't have that form, you'd need to make one since you're essentially pre-populating a one to many relationship and need to make use of events since access doesn't support triggers. Let me know if you have any questions.

 

[jammin140900]

Thanks Mavexe for the time you took looking at this. I've been mulling over it and realised that my setup looks a little confusing to start with, hence the outcome is more difficult to achieve!

To summarise, what I really need is:
1. A form to maintain the Sites (as you stated)- No probs, I can do that.
2. A Registration form with a subform. The top part of the registration form contains general registration details required to register all the sites for a new website.

3. The subform to show all the sites that I can submit for registration for a website.

I believed that the registration form and subform needed to be refreshed every time I added a Website (Website ID) before coming into the registration area.

I noticed on your coding that it looks like I need Website ID on the tblASSitesToSubmitTo table? I'm a little confused!

What's the simplest table structure/setup you think to get this working? I feel my structure may not be correct? Thanks for your patience!