Going around the query to add data to the table

[hysonmb]

Hello,
I have a NotInList event which runs fine alone, but, when linked to a query that is intended to pull only those offices within a component (two separate tables) the form will go back to the query, in turn going back to the form, and not post the NewData to the table.
Is there a way to get the query to recognize new data being entered and allow it to post after the users clicks yes on the question box?

This is the query that is causing the field to go back to the form:

SELECT tblOffice.OffID, tblOffice.Office, tblOffice.DirID
FROM tblOffice
WHERE (((tblOffice.DirID)=[Forms]![frmPersoPLAY]![cboDir]));

and this is the NotInList Event that works if I take out the WHERE criteria in my query:

Private Sub cboOffice_NotInList(NewData As String, Response As Integer)
Dim strSOL As String
If MsgBox(NewData & " is not currently listed as an office within this Component. Would you like to add this office to the database?", _
vbYesNo + vbQuestion) = vbYes Then
strSQL = "insert into tblOffice (Office) values ('" & NewData & "')"
CurrentDb.Execute strSQL
Response = acDataErrAdded

Else: MsgBox "" & NewData & " Not added"
End If
End Sub

Please, if you know how to work around this issue, let me know. Thanks in advance.

 

[Quehay]

Can you explain the form context a bit more (what you want the form to do in user terms not programmatically)?


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[hysonmb]

I want the user to enter the component and and office based on combo boxes on the form. When they select a component, rather than having a long and repetitive list of offices in the second combo box, it will adjust based on the first entry and show only those which are related. I need this because I have over 15 components who each have an administration office and a few other similar offices.

 

[Quehay]

So you've got components and offices (one office many components and one component has potentially more than one corresponding office correct?)

So there's an intersection table (OFFICES_COMPONENTS) for the M > M relationship, correct?

You're allowing an insert into the office list but you're not updating the component FKEY field that corresponds to the office in the intersection table. For each component to have a meaningful relationship with a list of offices that can be updated on the fly through the form you'll have to attend to the intersection table (if I understand your context correctly...)


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[hysonmb]

You've got it on the nose. If I do this without the query to limit the list of the office combo box to those within the correct component, there is no issue, it works as you just described. Once I put in the query, the query works, it only gives those offices, but, you can't add to it on the fly.