combo box on dbl click open form linked to combo box

[angie]

Thanks for all your help I have everything working great and all problems solved. The only thing I could not get to work is when I add a new company using not in the list and the form pops up to enter new company information it all works but does not update the field on the main form (main form name "customer" field combo box "compID) If any one has any suggestions. thanks Angie

[angie]

Hi June7. I have read both links you gave me but so far I can't figure it out. I need the customer form to Requrey with the new compid information that is added when not in list. Also to update if any changes are made in the selected compid combo box. The dbl click opens the form and I can make the changes but it does not update the form with the combo box I have to choose the name again and them it will update. It will add a new record on not on list but again I have to use the comb box to select the new record before it will show the information. I would like it to updat with out choosing it again in the combo box. Thanks Angie.

[June7]

Need something to analyse.

Post the relevant code or provide db.

[angie]

DB FOR ACCESS.zip

Hi June7 I have attached a compressed file of my database I have tired to just include the tables and forms you will need to cut the size down. I hope it is what you need. thank you Angie

[June7]

Those two links weren't the ones I usually reference for this issue. Here is the one I usually go to http://www.blueclaw-db.com/access_no...ed_example.htm. I am sure there are many more if you do a search. The trick with your situation is need to pass the new CompID created in the autonumber field back to the customer form. Most examples don't seem to deal with that.

Your code can be simple as:

Private Sub CompID_NotInList(NewData As String, Response As Integer)
    Response = acDataErrContinue
    Me.CompID = Null
    DoCmd.OpenForm "company owner NAME details", acNormal, , , acFormAdd, acDialog, NewData
    Me.CompID.Requery
End Sub

Code behind the company details form:

Private Sub Form_Current()
If Not IsNull(Me.OpenArgs) Then
    Me![Company Or Owner] = Me.OpenArgs
    DoCmd.RunCommand acCmdSaveRecord
    Form_Customer.CompID.Requery
    Form_Customer.CompID = Me.ID
End If
End Sub

Can have additional code for a MsgBox in the NotInList event to ask user if they really want to add new company record.

[angie]

Hi June7, This worked great thank you so much. I also did the following code in the detail form, on after update
Private Sub Form_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
Form_Customer.CompID.Requery
Form_Customer.CompID = Me.ID
End Sub

this updates the information for the customer record but it does not update for all customer with the same compID, should I make a query to update all records using the same compid if any changes are made. are is there a better way. I have never done this before but if I add something to the code above to update all records using the compid maybe activating a query ? not sure how to do it. Angie

[June7]

I don't understand why that code was needed.

What do you mean by 'update all records using the same compid'? What needs to be updated? This has just created a new company record, how could there be 'other' records to update?

The ID's of company and contractor should be saved as foreign keys in Customers but the associated company and contractor detail info should not also be saved.

[angie]

I think I had a brain melt down. Sorry after I thought about it I realized i was not thinking right... Everything is working great. Thank you so much for your help. Have a great day. Angie.