HI,

I originally posted this in the wrong forum, so I moved it here.

I am pretty new to Access and am struggling with something.

I have three tables I am working with.

tblCommuter
tblLocation
tblJunctionCommuterLocation

I have a form based on tblCommuter and a subform based on tblJunctionCommuterLocation. I want to write a new record into tblJunctionCommuterLocation and currently it won't let me add the new data.

I am currently trying to add a record into the field LocationId, which is a lookup field based on a query.The lookup field allows me to make new write in the form of an address while generating new autonumbers for the LocationId. Is this clear?

Eventually I want to also have the code write the same data to tblLocation by generating an AutoId to use as the Primary Key for that table, but I first just need to figure how to get the code to allow me to insert into tblJunctionCommuterLocation.

The code I am trying to use is below. Any help is appreciated thank you.

Paul



Private Sub LocationID_NotInList(NewData As String, Response As Integer)

    Dim strsql As String, x As Integer
    Dim LinkCriteria As String

    x = MsgBox("Do you want to add this value to the list?", vbYesNo)

If x = vbYes Then
        strsql = "Insert Into tblJunctionCommuterLocation ([LocationID]) values (" & NewData & ")"

        Response = acDataErrAdded
    Else
        Response = acDataErrContinue

    End If

End Sub

If this is an unbound combo box, it is pretty easy to add the item to the list. The issue is when it is bound like this. Normally what I do is open the Location Form in Modal, and have the user fill it out to add the new location (in your case) using the proper form. Then requery the combo box on the On Close event of the Add New Location Form. How exactly you do that part is up to you.

Simply inserting the new location like this may get these results...

User types in "samp" lokking for the location "Sample", then hits return.

Your message box pops up, then user selects Yes, and "samp" is entered as a new location.

You still haven't updated your combo box.

Did any of that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

That may also be a non-updatable query issue as well. How did you create the form / sub-form? If you used the auto form wizard, then the code I said should work without me seeing more. You just need to use the regular Add New Location form that you normally use (in Modal to prevent strays) and then refresh your current form. Also, your primary key field should be autonumber, so you wouldn't insert that, is that correct? Just insert the Location, and the LocationID is generated for you. You can't insert an autonumber. Your query results should also not display the ID, just meaningful data.



mmcdonal

Look it up at: http://wrox.books24x7.com