Help!!! Cmbo Box / Notinlist

[mousemat]

Help.

I have search the forum for some considerable time and tried most ofthe code that I have found. However, nothing seems to work on my Databse.

The scenario is simple. From the Enter Invoice Form, there is a combobox, if the required name /company isnt listed I want to be able to double click or press enter and for access to bring my Customers form up so that I can input the new customer details.
Once the new data has been put into the customers table I want to be able to select it or have it put automatically in the combo box itself.

Everything I have tried has failed so far.

I am using Access 2002 and have included my database in that format.

The combo box was made via the combobox wizard and the address details is fed from this via dlookup.

Please help if you can.

 

[Mile-O]

I'm using Access 97 so can't download it (Access 2002 has a conversion option, it's always best to use it to increase the scope of responses to your question).

What it sounds like you want to do is:

1. Set the combobox's Limit To List proprty to Yes.

2. Make use of the NotInList() event of the combobox.

i.e.

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

    Response = MsgBox(Me.cboYourCombo & " is not recognised in this database." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unrecognised Data")

    If Response = vbYes Then
        DoCmd.OpenForm "frmCustomers", acNormal, , , acFormAdd, acDialog, NewData
        Me.cboYourCombo.Requery
        Response = acDataErrAdded
    Else
        Me.cboYourCombo.Undo
        Response = acDataErrContinue
    End If

End Sub

3. On the frmCustomers Load() event, something like this:

Private Sub Form_Load()

    If Me.NewRecord Then Me.txtCustomerName = Me.OpenArgs

End Sub

 

[mousemat]

I have added my Database in Access 97 format.

 

[Mile-O]

FIxed your problem there but there seems to be something wrong with your relationship - I don't have the time, sorry, to investigate that but I've fixed your initial request.

 

[Mile-O]

As an aside, you might want to consider ensuring Option Explicit is set on all your modules.

 

[mousemat]

Mile

Thank you so much. I have spent ages on this trying to work it out.

Many thnks.

Im not sure what you mean about the relationship problem though, but i will look into it.

Many thanks once again