NotInList with Many to Many

[SAC]

I have two tables with a many to many relationship: tblOrg and tblPeople

So I also made an intermediate table: tblOrgPeo

tblPeo has fields: OrgKey and PeoKey which relate back to each of the tables.

On a form I first have a combo box for the tblOrg and the notinlist event works fine for it. I just add a record in the tblOrg. tblOrgPeo is not appended.

Then I have a combo box for tblPeople.
In its notinlist event, I add a record to the tblPeople, set a variable to the tblPeople.Key, and then add a record to the tblOrgPeo. That works.

But when the combo box is requeried, it still give me an error that the item is not in the list.

I can then see it in the combo box and select it.

The data source for the combo box is:

SELECT tblPeople.Key, tblOrgPeo.OrgKey, tblPeople.Last, tblPeople.First,

FROM tblOrgPeo INNER JOIN tblPeople ON tblOrgPeo.PeoKey=tblPeople.Key

WHERE tblOrgPeo.OrgKey=Forms!frmEstH!txtEstFor;

What can I do to correct this?

Thanks for your help.



I would like to have the notinlist event for the combo box for tblPeople to work correctly.

What I have d

[jzwp11]

The field OrgKey in tblPeople is unnecessary and is probably causing the error. You are handling the relationship between organizations and people with your junction table tblOrgPeo

[SAC]

I tried deleting the field from the tblPeo and it didn't change.

The odd thing is that the new data shows in the combo box after the error is cleared and if I select it it works.

Thanks for your help.

Any other ideas?

Thanks again.

[jzwp11]

Sounds like there still might be a problem in your code. Can you post the code for the not in list event and any related code?

[SAC]

I've attached a pdf showing the table layouts and the form.

Here's the code:

Private Sub txtRequestedBy_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue

Dim intAnswer As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset, rst1 As DAO.Recordset
Dim strOldValue As String
Dim strName As String
Dim varCost As Variant
Dim strSQL As String
Dim intPeoKey As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPeople", dbOpenDynaset)
Set rst1 = dbs.OpenRecordset("tblOrgPeo", dbOpenDynaset)

intAnswer = MsgBox("Add " & NewData & " to Organizations?", vbQuestion + vbYesNo) 'Caption of ComboBox

If intAnswer = vbYes Then

'tblOrgKey is already at Forms![frmEstH]![txtEstFor]

With rst 'Add the tblPeople record
.AddNew
!Last = NewData
intPeoKey = !Key
.Update
End With

With rst1 ' Add the tblOrgPeo record
.AddNew
!OrgKey = Forms![frmEstH]![txtEstFor]
!PeoKey = intPeoKey
.Update
End With

Response = acDataErrAdded ' Requery the combo box list.

Else
Me.Undo
Response = acDataErrContinue ' Require the user to select
' an existing item
End If

End Sub


Thanks a lot for your help!!!

[jzwp11]

I'm not sure whether it will make a difference, but since the key fields are long numbers and not just integers, I wonder if the variable intPeoKey should also be declared as a long number data type. Also, did you verify that Access actually sees what you expect it to from Forms![frmEstH]![txtEstFor]? You can always throw in a debug.print statement to display that value to confirm. Also, make sure to close the two recordsets and set the objects to nothing so your code is as clean as possible.

Private Sub txtRequestedBy_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue

Dim intAnswer As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset, rst1 As DAO.Recordset
Dim strOldValue As String
Dim strName As String
Dim varCost As Variant
Dim strSQL As String
Dim intPeoKey As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPeople", dbOpenDynaset)
Set rst1 = dbs.OpenRecordset("tblOrgPeo", dbOpenDynaset)

intAnswer = MsgBox("Add " & NewData & " to Organizations?", vbQuestion + vbYesNo) 'Caption of ComboBox

If intAnswer = vbYes Then

'tblOrgKey is already at Forms![frmEstH]![txtEstFor]

With rst 'Add the tblPeople record
.AddNew
!Last = NewData
intPeoKey = !Key
.Update
End With

With rst1 ' Add the tblOrgPeo record
.AddNew
!OrgKey = Forms![frmEstH]![txtEstFor]
!PeoKey = intPeoKey
.Update
End With

rst.close
rst1.close
 
Set rst=nothing
set rst1=nothing

Response = acDataErrAdded ' Requery the combo box list.

Else
Me.Undo
Response = acDataErrContinue ' Require the user to select
' an existing item
End If


End Sub

[SAC]

Thanks for the info.

The data actually gets written to the tblPeople and the tblOrgPeo.

I can see it in the drop down box after I get the error mjessage that it's not in the list. I can then select it and everything is fine.

So I'm not sure why I'm getting the messsage. For some reason when the combox is requeried it doesn't see it. Maybe it's a timing thing becasue after the error everything is fine.

Any ideas on timing?

[jzwp11]

Nothing jumps out at me. You have a couple of combo boxes on the form. What do the row sources look like for those combo boxes?

[SAC]

Row Sourse for first one:
SELECT tblOrganization.Key, tblOrganization.NAME FROM tblOrganization ORDER BY tblOrganization.NAME;

Row source for second one:
SELECT tblPeople.Key, tblOrgPeo.OrgKey, tblPeople.Last, tblPeople.First, FROM tblOrgPeo INNER JOIN tblPeople ON tblOrgPeo.PeoKey=tblPeople.Key WHERE tblOrgPeo.OrgKey=Forms!frmEstH!txtEstFor ORDER BY tblPeople.Last, tblPeople.First;

I have just set and index for OrgPeo so that OrgKey and PeoKey are unique.

It's still allowing a duplicate record so my row source must be allowing it.

When I attempt to add a duplicate record directly into the table it won't let me.

This makes no sense...becasue it shouldn't be allowing me to add a duplicate record with the dropdown box either.

This might be the problem, but I'm not sure how to correct it.

Thanks for checking it out.

[SAC]

From the dropdown it's adding a new record into the tblPeople. That's the root of the problem.

That's for making me get more detailed.

Why won't it just lookup a record from tblPeople when I enter the name?

Thanks again.

[jzwp11]

I think we're at a point, where I need to see the database. Would you be able to post a copy with any sensitive data removed?

[SAC]

It's attached.

I've got it really messed up now.

Thanks so much.

[SAC]

I didn't see the attachment so I zipped it and am trying again.

Thanks for your help.

[jzwp11]

We have to take a step back from the form. I see some issues with your table structure that need to be resolved before you can work on forms.

First, I have a couple of general recommendations. It is best not to have spaces or special characters (+,!, # etc.) in your table and field names. There are certain reserved words in Access such as Date and Name, so these should not be used as field or table names.

Now back to your table structure...

You have various contact methods for a person (phone, work phone, cell, e-mail etc.). This would be considered a one-to-many relationship which should be handled with a separate but related table.

In your org table, I see several fields related to what look likes activities (calls and follow ups to calls) related to the organization or if my guess is correct, people of the organizations. These activities would represent the many side of a one-to-many relationship and should be in a related table.

Also, in your people table I noticed that you had what looked like reference to a spouse. If a person is related to another person, the best way to handle it is to put all the people in the people table and then use a junction table to relate them to each other.

I also wonder whether your tblEstH table can actually be considered an activity and thus in the activity table. You'll have to explain more about that.

I have restructured your database based on my best guess in an attempt to illustrate some of what I explained above (DB attached). You will want to look at the tables and the relationship window to see how things fit together. This stills more work before forms can be considered. We'll also need to get a better understanding about what you are trying to accomplish with the database. From what I can tell, it looks like a prospecting database.

[SAC]

Thanks for your help.

If you don't mind, I would appreciate just know how to do the notinlist event and then I can work on your other suggestions later.

Thanks