Adding an item Not in list combo box and calling a query

[bookenz]

This pertains to my "Customer Management" DB.

I have 2 tables: tblCustomer, and tblCompany.
and 2 forms: frmCustomer, and frmAddCompany

In frmCustomer I have a combo box called cboCompany. cboCompany requires the user to select a company (the company that the customer belongs to (row source: "SELECT tblCompany.Company_ID, tblCompany.Company_Name FROM tblCompany ORDER BY tblCompany.Company_Name;").

I have allowed the user to type in a company that is not in the list which invokes the 'cboCompany_Not_in_list' event procedure:

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

Dim strSQL As String
Dim i As Integer
Dim Msg As String
Dim CompanyName As String

'exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add a new company?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Company not found")
If i = vbYes Then
strSQL = "Insert Into tblCompany ([Company_Name]) values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

DoCmd.OpenForm "frmAddCompany"

Else
Response = acDataErrContinue
Exit Sub
End If
End Sub

As you can see the procedure adds the new company to tblCompany and then opens 'frmAddCompany'. Now 'frmAddCompany' is based on a query (qryCompany) with the following criteria under Company_ID: [Forms]![frmCustomerRequest]![cboCompany]. This is so that only the new company record is displayed (allowing the user to fill in other details relating to the new company).

However, this does not work. in fact frmAddCompany displays the previously entered company. It is as though cboCompany is not updated with the new record when VBA calls frmAddCompany.

I have a command button labelled "Add Company Details" on frmCustomer. If the user enteres a new company into cboCompany and then clicks the "Add Company Details" button then frmAddCompany displays the correct record (being the new company, or the last record).

How can I update or refresh cboCompany in VBA so that when the line:

DoCmd.OpenForm "frmAddCompany"

is called the correct (last record) record is displayed.

Thankyou in anticipation of this long winded problem...

 

[Remou]

Hi
Have a look at your other post!

 

[TheAceMan1]

How are ya bookenz . . . . .
[blue]

It is as though cboCompany is not updated with the new record when VBA calls frmAddCompany.[/blue]

Correct! [purple]acDataErrAdded[/purple] tells the event to perform its own save & requery when the event ends. You can't do your own requery under these conditions as the data has to be saved first! Since you providing the option not to add, you can't pre-save . . . . Bearing in mind the NotInList event has to somehow complete, try this:
[ol][li]Change:
[purple]DoCmd.OpenForm "frmAddCompany[/purple]
To:
[purple]DoCmd.OpenForm "frmAddCompany", , , , , , NewData[/purple][/li]
[li]In [blue]qryCompany[/blue] remove the criteria for [blue]Company_ID[/blue]. Then in the criteria for [purple]Company_Name[/purple] add the following:

[blue]Forms!frmAddCompany.OpenArgs[/blue]

[/li][/ol]
[purple]Give it a whirl & let me know . . . [/purple]

See Ya! . . . . . .

 

[bookenz]

Never in the field of human conflict was so much owed by so many to so few."

you guys are good.