Not In List event procedure that creates a new record.

[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...

 

[rgbean]

This is a Visual FoxPro Forum, and this is clearly VBA code. Please repost this in the appropriate forum.

Rick