On Not in List - Many to Many Help

[vapid2323]

So I have some code I use to add values to a combo box when they are not allready there.

Private Sub cboCOI_NotInList(NewData As String, Response As Integer)
Response = fNotInList(NewData, "tblQSCOI", "QSCOI")
End Sub

Function fNotInList(NewValue, strRecordSource As String, strFieldName As String)
10 If MsgBox("The value '" & NewValue & "' does not exist, do you want to add it?", vbQuestion + vbYesNo) = vbYes Then
   'proceed
15   DoCmd.SetWarnings False
20   DoCmd.RunSQL "INSERT INTO [" & strRecordSource & "] ([" & strFieldName & "]) SELECT '" & NewValue & "'"
30   fNotInList = acDataErrAdded
Else
40   fNotInList = acDataErrContinue
End If

But I cant seem to get it to work when its a Many to Many setup, I want to add a new COI, it will ask if I want to add it and the code will run. But after its done it errors with, value not in list.

I think it has to do with refreshing the data but I cant seem to wrap my head around it.

 

[jzwp22]

There a 2 versions of an append query. The first has the following syntax:


This version appends 1 record with 3 field values
INSERT INTO tablename (textfieldname, numericfieldname, datefieldname)
VALUES ("myvalue", 2, #07/27/2011#);

The following syntax is used when you want to append multiple records from one source into the destination table.

INSERT INTO tbl1 ( txtFname, txtLName )
SELECT tbl2.myfirstname, tbl2.myLastname
FROM tbl2;


If you are only adding 1 record at a time, then you need the first syntax described above. I assume that you are only populating the one field. Basically you have to change SELECT to VALUES (). Something along these lines (not tested):

DoCmd.RunSQL "INSERT INTO [" & strRecordSource & "] ([" & strFieldName & "]) VALUES ( '" & NewValue & "')"

 

[vapid2323]

I changed SELECT to VALUES and still works for all other other fields except for this one darn Many to Many.

I cant think of why I am not allowed to add a record to the table, I mean in my mind it should act just like the other tables regardless of the many to many setup.

 

[jzwp22]

When you have a many-to-many relationship (junction table), the junction table is used as the record source for a subform. For example, if you have people who can be associated with many companies and a company has many people your tables would look like this:

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblPeopleCompany (junction table)
-pkPeopleCompID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkCompanyID foreign key to tblCompany

Let's say that you want to see with what companies a person is associated, so you would base your main form on tblPeople and the subform on the junction table. You would use a combo box to supply the fkCompanyID. You would use code similar to what you describe to add new companies to the company table.

I assume your table structure is similar to the above. Can you provide more details regarding your table structure and how you have your form/subform set up?

 

[vapid2323]

You got it:

Record Source for the Subform:

SELECT tblQSCQAQSCOIJoin.*, tblQSCOI.ProductReturned, tblQSCOI.DateofManufacture, tblQSCOI.Cancelled, tblQSCOI.Terminated, tblQSCOI.MFGSiteOwner, tblQSCOI.Sterility, tblQSCOI.Released, tblQSCOI.Approved, tblQSCOI.Infused, tblQSCOI.Reported, tblQSCOI.fk_QSInfusionSiteID, tblQSCOI.COIComments
FROM tblQSCOI INNER JOIN tblQSCQAQSCOIJoin ON tblQSCOI.QSCOIID = tblQSCQAQSCOIJoin.fk_QSCOIID;

Record Source for the Combo box I am running the not in list code on:

SELECT tblQSCOI.QSCOIID, tblQSCOI.QSCOI
FROM tblQSCOI;

I have added a .zip that has two screenshots, one of the relationship and also one of the form.

Hope this helps you get a better understanding.

 

[jzwp22]

You have an extra join between tblQSCOI.QSCOIID and tblQSCQA.fk_QSCOIID that should not be there.

 

[vapid2323]

You have an extra join between tblQSCOI.QSCOIID and tblQSCQA.fk_QSCOIID that should not be there.

You might be correct but let me point out why its there.

I need to let my users select all the related COI's, like you see in the subform, but also they need to choose one thats the "primary" I will need to report on this etc.

If you have a better solution I am all ears! I love learning better ways to set things up.

PS: you can see this in the screenshot, you have the "Reported COI" up top, and then the normal many to many in hte subform.

 

[jzwp22]

With the join as you have it, you will never be able to add the record via your code. The better approach would be to add a field in the junction table that can be used to flag the primary COI.

 

[vapid2323]

With the join as you have it, you will never be able to add the record via your code. The better approach would be to add a field in the junction table that can be used to flag the primary COI.

Ohhh, I never thought about that! I was only thinking about the two main tables.

I will work on the change and post back.

 

[vapid2323]

So I have made all the requested changes but I am still running into the same issues. Code will run asking if I want to add in the record but as soon as its done it tells me that the record selected is not in the list and nothing gets added to the tables.

Anyone?

 

[jzwp22]

Can you post the database and identify from where you are executing the code?

 

[vapid2323]

Can you post the database and identify from where you are executing the code?

The code runs from the NotInList event.

I will see what I can do about posting the database, I need to modify it a bit before I can post.