[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Inserting table entries upon clicking a button- MS Access 2003

Hi,

I've got some code behind a button that seems to sometimes work and then it also does some random things which sometimes a 'Compact and Repair' will fix it for a short period and then it will start happening again.

I want to insert Website ID, Site ID from tblASWebsiteSite and ArticleID from tblASArticleDetails into a table: tblASSiteSubmitted.

Site ID at the moment contains about 24 entries. A website can have many articles which need to be posted onto each of the 24 sites.

So, if I had say 20 articles from website '1', then if the code worked correctly, it would append:
24 Sites, for each of the 20 article id's (and also corresponding WebsiteID) or 480 records in total.

The last line of code ..."And Not EXISTS..." should be checking that if these records already exist in tblASSiteSubmitted, then it shouldn't append the records and duplicate it again.

I thought it was all working, but when I check tblASSiteSubmitted, I have incorrect entries where I might have ArticleID 3, next to WebsiteID 5 AND WebsiteID 9. Each ArticleID should be a unique entry with a maximum of 24 records (one for each site)

Looking at the code below, is there something I've got wrong?
J
Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmASSubmission"
    
    
       Me.Refresh

 Dim strInsert As String
 
    DoCmd.SetWarnings False
    
  
    strInsert = "Insert into tblASSiteSubmitted (WebsiteID, SiteID, ArticleID)"
    strInsert = strInsert & " select tblASWebsiteSite.WebsiteID as WebsiteID, tblASWebsiteSite.SiteID as SiteID, tblASArticleDetails.ArticleID as ArticleID"
    strInsert = strInsert & " from tblASWebsiteSite, tblASArticleDetails"
    strInsert = strInsert & " where tblASWebsiteSite.WebsiteID = " & Me!WebsiteID
    strInsert = strInsert & " AND NOT EXISTS (SELECT NULL AS NotNeeded FROM tblASSiteSubmitted WHERE tblASWebsiteSite.SiteID = tblASSiteSubmitted.SiteID And tblASWebsiteSite.WebsiteID = tblASSiteSubmitted.WebsiteID And tblASArticleDetails.ArticleID = tblASSiteSubmitted.ArticleID)"
    
    DoCmd.RunSQL strInsert

    stLinkCriteria = "[ArticleID]=" & Me![ArticleID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command32_Click:
    Exit Sub

Err_Command32_Click:
    MsgBox Err.Description
    Resume Exit_Command32_Click
    
End Sub

Open in new window

0
jammin140900
Asked:
jammin140900
  • 21
  • 20
  • 3
1 Solution
 
hnasrCommented:
attach a sample database, and explain what to do to get the expected result.
0
 
jammin140900Author Commented:
Ok, I've attached a smaller version of the database.

To get to the area I'm talking about:
Open Dbase
Under the "Select Website" combo box, perhaps select "Willthiswork.com"
Click on the red button (Article Submitter Registration)
At the bottom, click on red button (Go To article Details)

Ok, there is a button in red "Submit Articles". The code mentioned above is behind this button.




 Sample-Database.mdb
0
 
hnasrCommented:
Sorry: I expect a reduced database, with enough objects to reproduce the issue.
And instructions explaining what to do and to expect what. For example, open form, press, enter, check table, and so on.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jammin140900Author Commented:
This is a reduced database, so I'm not sure what you mean.

What I omitted, was once you navigate and run the "Submit Articles" buttons (instructions how to get there are above) , if you went back to the "frmWebsite", selected "www.learntosing.com" from the combo and go through the same routine as above to get to the "Submit Articles" and click it.

Once the Submit Articles routine has run, if you go into the table "TSArticle Details", you will see that Article 41, should have a WebsiteID of 13. If you go into the table "TSASSiteSubmitted", you will find that for Article41, you have 10 entries for WebsiteID= 13 (which is correct), but then it also added another 10 entries for Website4 (which is incorrect). That's the problem. Adding another lot of website information has the system creating the correct but also incorrectly matching WebsiteID's to ArticleIDs. in TSASSitSubmitted. (The Submit Articles button runs this process.)



 
0
 
aikimarkCommented:
1. is this a split (FE/BE) database configuration?
2. does each user have their own FE copy?
3. the SQL does not appear to have a condition that will join the tblASWebsiteSite and tblASArticleDetails tables.
0
 
jammin140900Author Commented:
No, there isn't a FE/BE, just one database. At the moment it will be only used by myself.

Ah ok. I agree it has to be something wrong with the condition in the SQL, but I'm a novice to code. Is it clear what I'm setting out to achieve after running this code?
0
 
aikimarkCommented:
The best way to prevent duplicates is to create a unique index on the table.  The index may be comprised of multiple columns.
0
 
jammin140900Author Commented:
How would you do that? You mean change the properties on the field to "no duplicates"?

If so, I would run into problems as the SiteID has 25 records and requires a repeat of WebsiteID and ArticleID next to it. Everytime I add a new article, I need that ArticleID, the 25 SiteID's and the same corresponding WebsiteID (for that article) in the three columns of the table.
0
 
aikimarkCommented:
I assume that there is some combination of columns that constitute uniqueness.  You would create a "unique" index comprised of these columns.
0
 
jammin140900Author Commented:
Could you please explain in detail what that means? I have no idea what you mean by that.
0
 
aikimarkCommented:
Is there some additional columns, in addition to SiteID and WebsiteID, that should constitute a unique value?
0
 
jammin140900Author Commented:
ArticleID needs to be included.

Perhaps I'll attach an example of the table entries I would expect from this routine. In the attachment, three articles are added to the table details. In addition to adding these entries, I need the routine to check that if say WebsiteID = 2 And SiteID = 1 And ArticleID =1, do not add this same information into the table again.

 
 tblSiteSubmittedID.xls
0
 
aikimarkCommented:
create a unique index with those three columns.
0
 
jammin140900Author Commented:
How do I do that specifically?
0
 
aikimarkCommented:
open up the table design dialog window
open the index dialog window
type a name for your index in the first column
in the second column select the first of these three columns
in the next two lines, select the other two of these three columns.

click on the first of these three lines (the one with the index name)
below the grid, you should see a place to specify the nature of the index (duplicates allowed or unique), select Unique.

Save your index definition
0
 
jammin140900Author Commented:
Ok, tried to do as per your instructions. It still doesn't work. Perhaps I haven't done it correctly?

Attached are the screenshots. I've run the code after that and it still allows duplicate and incorrect entries into the table.

 Multiple-Index-010511.doc
0
 
aikimarkCommented:
* ONE index name on the first of three lines -- you have defined three indexes

Remove the index names on the second and third lines
0
 
jammin140900Author Commented:

You mean like this? (See last example on attachment)

(If not, could you please explain using the actual fields themselves to get this sorted faster? - Thanks)


 Multiple-Index-010511.doc
0
 
aikimarkCommented:
you are defining a four column index

Since you did not highlight the first row (containing the index name), I can't see its properties in the lower part of the dialog.
0
 
jammin140900Author Commented:
Attached and at the bottom of the document is a picture of the properties.

Is that right?  Multiple-Index-010511.doc
0
 
aikimarkCommented:
That is correct, although your unique index does not have to be a Primary Key (PK) index.
0
 
jammin140900Author Commented:
That still doesn't work and the code seems wrong. Are you able to try it on the sample database attached above and show me please?
0
 
aikimarkCommented:
you don't need the "And Not Exists ()" clause since the unique index will prevent duplicates.
0
 
jammin140900Author Commented:
it doesn't work. Did you actually try it in the sample database or just look at the code above?
0
 
aikimarkCommented:
I looked at the code you posted, since we were working on the alteration of the data structure(s) by creating a unique index.  Please upload the database in its current form.
0
 
jammin140900Author Commented:
Here you go. Thanks.

 Management-System-V1.260411.mdb
0
 
aikimarkCommented:
You included SiteSubmittedID in the multi-column unique index.  Since this is an autonumber field, every inserted/appended row will be unique.  You need to empty the tblASSiteSubmitted table and change the index to not include the autonumber field.  I suspect you wanted the SiteID field, but I can't say for sure since this isn't my data.
0
 
hnasrCommented:
I lost track of this thread.
But a little suggestion:
Try to run the insert sql as a select sql and check for the desired records.
0
 
aikimarkCommented:
I changed the index definitions to:
Index Name	Field Name	Sort Order
==========	============	==========
SiteSubmittedID	SiteSubmittedID	Ascending
UniqueIX	ArticleID	Ascending
        	SiteID  	Ascending
        	WebsiteID	Ascending

Open in new window

0
 
jammin140900Author Commented:
I think we have two problems here and perhaps its best to solve one at a time.

First, perhaps we'll look at the code not working correctly and after that, we'll look at the index problem.

When I am on frmASArticleDetails (having being navigated through frmWebsite and selecting "Learntosing.com", the form shows that I am currently on Website = 13 and articleid = 4. Remembering that there are 10 sites to submit each article to(tblSitesToSubmitTo), I would expect when you run the code behind the "Submit Articles" button that it would create 20 table entries (no worrying about duplicating or indexing for the moment).

For each article, you would expect sites 1 - 10 listed in SiteID, next to the same WebsiteID = 13 along with the respective articleID. In this example, since there are two articles for this particular WebsiteID, you would expect an additional 10 entries for the second article.

If you go into the "tblASSiteSubmitted" table, you can see 90 entries instead. It seems to have put the articleID 41 and 44 (which should belong only to WebsiteID= 13) next to EVERY websiteID that exists in tblASArticleDetails table. This off course is incorrect. I'm not sure why, but it also creates a batch of table entries for articleID= 0.

Why is the code behind "Sumbit Articles" not adding entries specifically for any websiteID (and respective articles of that websiteID) that form "frmASArticleDetails" is currently filtered to? Why is the batch of articleid= 0 batch being created too? Management-System-V1.260411.mdb
0
 
aikimarkCommented:
When I followed your steps I see articleID 41, not 4.

This is the SQL being executed in your Command32_Click() event:
Insert into tblASSiteSubmitted (WebsiteID, SiteID, ArticleID) 
select tblASWebsiteSite.WebsiteID as WebsiteID, tblASWebsiteSite.SiteID as SiteID, tblASArticleDetails.ArticleID as ArticleID 
from tblASWebsiteSite, tblASArticleDetails 
where tblASArticleDetails.WebsiteID = 13

Open in new window


What is missing is a limitation on the tblASArticleDetails rows.  These two tables are not joined.  When I added a limitation on the articleID, the SQL looks like this:
INSERT INTO tblASSiteSubmitted ( WebsiteID, SiteID, ArticleID )
SELECT tblASWebsiteSite.WebsiteID AS WebsiteID, tblASWebsiteSite.SiteID AS SiteID, tblASArticleDetails.ArticleID AS ArticleID
FROM tblASWebsiteSite, tblASArticleDetails
WHERE (((tblASArticleDetails.ArticleID)=41) AND ((tblASArticleDetails.WebsiteID)=13));

Open in new window

and 40 rows are inserted.

The tblASSiteSubmitted multi-column index was NOT unique.  I corrected the unique index definition and copied the unique data into the table.  This will prevent duplicates.  If you click on the UniqueIX row in the Index dialog window, you will need to change it to a unique index.  You must do this.

This is the corrected code that constructs the Insert Statement.
    strInsert = "Insert into tblASSiteSubmitted (WebsiteID, SiteID, ArticleID)"
    strInsert = strInsert & " select tblASWebsiteSite.WebsiteID as WebsiteID, tblASWebsiteSite.SiteID as SiteID, tblASArticleDetails.ArticleID as ArticleID"
    strInsert = strInsert & " from tblASWebsiteSite, tblASArticleDetails"
    strInsert = strInsert & " where tblASArticleDetails.WebsiteID = " & Me!WebsiteID
    strInsert = strInsert & " and tblASArticleDetails.ArticleID = " & Me.ArticleID

Open in new window

0
 
jammin140900Author Commented:
Thanks.

I've changed the index definition as per the attachment and changed the code to include the last line of code so it looks at articleID also. Like you, I now get 40 records. It's creating a record using articleid= 41 (sorry- my mistype earlier when I said 4) for every single websiteid, not just where websiteid= 13.

So I haven't implemented something correctly? I didn't quite understand how you 'copied the unique data into the table to prevent duplicates'. Is that the step I haven't applied?

 Multiple-Index-050511.doc
0
 
aikimarkCommented:
Yes.

1. Copy the current table structure to a new table (NO DATA).
2. Change the multi-column index on the new table to be unique.
3. Save the table.
4. Create a query to append the rows from the current table to the new table
5. When you run the query, you will see that not all the rows are appended because of the unique index preventing duplicate keys.
6. Delete the current table
7. Rename the new table with the original table name.
0
 
jammin140900Author Commented:

I copied the table structure into a new table. Went into the new table and checked the multi column index. This index was exactly the same as the per the screen shots I sent earlier. When you say in step 2 to change to be unique, does something need to be changed here?

When I appended the data in step 5, it brought through all 40 records so I'm not doing something right. My multi index must not be set up correctly?
0
 
aikimarkCommented:
what do you see in the lower part of the index dialog window when you click on the row that starts the multi-column index?
0
 
jammin140900Author Commented:
Unique is set to Yes.

Please see attached..  Multiple-Index-050511b.doc
0
 
aikimarkCommented:
I don't understand which rows you want to copy and which ones you don't.  The two (source) tables aren't joined, which might be acceptable, but I think it time to reconsider that assumption on my part.
0
 
jammin140900Author Commented:
Well, an ArticleID should only have 1 WebsiteID, but many SiteID's (In this case the 10 sites)
0
 
aikimarkCommented:
Then you need to change the event code statements to join the two tables:

strInsert = "Insert into tblASSiteSubmitted (WebsiteID, SiteID, ArticleID)"
    strInsert = strInsert & " select tblASWebsiteSite.WebsiteID as WebsiteID, tblASWebsiteSite.SiteID as SiteID, tblASArticleDetails.ArticleID as ArticleID"
    strInsert = strInsert & " FROM tblASWebsiteSite INNER JOIN tblASArticleDetails ON tblASWebsiteSite.WebsiteID = tblASArticleDetails.WebsiteID"
    strInsert = strInsert & " where tblASArticleDetails.WebsiteID = " & Me!WebsiteID
    strInsert = strInsert & " and tblASArticleDetails.ArticleID = " & Me.ArticleID

Open in new window

0
 
jammin140900Author Commented:

Very close! I was on a filtered form with Website = 13 and Article ID = 41. I should have had 10 entries. I ended up with 20. (See attached). I get these entries where Article ID = 0?  SiteSubmittedTable.xls
0
 
aikimarkCommented:
Is it possible that these entries already existed in the table before you started?

Delete the ArticleID=0 entries from the target table and repeat your test.  Let me know if the zero entries reappear.
0
 
jammin140900Author Commented:
Ok. Cleared the table and re-ran the process.

Put's in the same 20 entries- 10 with ArticleID = 0
0
 
aikimarkCommented:
for some reason, you are inserting rows, with the original SQL, in the Command20_Click() event on the initial form (frmWebsite).  I think this is the source of your zero ArticleID rows.
0
 
jammin140900Author Commented:
My apologies. That's my fault. I originally had planed to run it through the Website form when I started and forgot about the code there.

That works PERFECTLY. No duplicates and everything inserts just as it should. Thank you for your patience and persistence with this.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 21
  • 20
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now