Unbound Combo Box

[Ivor Williams]

I've a form with an unbound combo box Combo44. The combo box Data Source is
the following:

SELECT tblCustomers.Cust, qryCustomerBuildings.CustBuildID,
qryCustomerBuildings.BuildName FROM qryCustomerBuildings INNER JOIN
tblCustomers ON qryCustomerBuildings.Cust=tblCustomers.CustID ORDER BY
tblCustomers.Cust, qryCustomerBuildings.BuildName;
The Bound Column is set to column 2, Column Count is 3, Column Widths are
1";0";1".

In the Not in List event of the combo box I've added the following code:

Private Sub Combo44_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "frmNewCustomer", , , , , acDialog
Response = acDataErrAdded
End Sub

The problem I'm having is that I have to close and re-open the form in order
to get it to populate the record with the proper data. Not sure what's wrong
here.

Ivor

 

[Klatuu]

Just requery the form:

Private Sub Combo44_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "frmNewCustomer", , , , , acDialog
Response = acDataErrAdded
Me.Requery
End Sub

 

[Ivor Williams]

The Not in List event, when triggered, opens a pop up form called
frmNewCustomer in which all the ne data is entered. Adding Me.Requery causes
the frmNewCustomer to be requeried, not the original form. I think what I
need is to requery the original form, and I've not sorted out how to do it.

Thanks,
Ivor

 

[Klatuu]

Post back with your code the way you wrote it. As I wrote it, since your
frmNewCustomer is opened in dialog mode, the code suspends until the form is
closed. Even if frmNewCustomer were open, it would not respond to Me. not in
its own code module.

 

[Ivor Williams]

Another thing which happens since adding Me.Query is the frmNewCustomer form
will not close. I get a dialog which says it is still waiting for user
input, but of course as soon as data is input, the form goes to a new record
and the cycle starts all over.

Ivor

 

[Klatuu]

The requey does not belong in frmNewCustomer.

 

[Ivor Williams]

This is the code in the NotInList event of Combo74 in the main form
(frmCustomerBuildings). Combo74 is used to choose a customer building from a
list. If I start typing a new customer, the pop up form frmNewCustomer pops
up as it should, data gets entered, then I expect to be able to close the
frmNewCustomer form and have the appropriate fields in the
frmCustomerBuildings form poplulated with the information that was just
entered on the frmNewCustomer form. What is happening instead is data gets
entered, and when I try to close the frmNewCustomer form, it just goes to a
new record and will not close. There is code in the AfterUpdate event of the
same combo box which I've included as well. I don't think this should be
causing the problem, but at this point I'm not sure. I've used this code
before and never had the issue, nor did I ever have to add the line
Me.Requery, so I'm very confused.

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

DoCmd.OpenForm "frmNewCustomer", , , , , acDialog
Response = acDataErrAdded
Me.Requery
End Sub


Private Sub Combo74_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustBuildID] = " & Str(Nz(Me![Combo74], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks, Ivor

 

[Klatuu]

Assuming both of the controls mentioned in your code are in the main form
(frmCustomerBuildings), the code should do what you want. As to what is
happening with your frmNewCustomer, I don't know. None of your posted code
could possibly cause that problem. If you frmNewCustomer is going to a new
record, try setting the form's cycle property to Current Record. I would
also check everything in frmNewCustomer to see why it is not closing.
--
Dave Hargis, Microsoft Access MVP

This is the code in the NotInList event of Combo74 in the main form
(frmCustomerBuildings). Combo74 is used to choose a customer building from a
list. If I start typing a new customer, the pop up form frmNewCustomer pops
up as it should, data gets entered, then I expect to be able to close the
frmNewCustomer form and have the appropriate fields in the
frmCustomerBuildings form poplulated with the information that was just
entered on the frmNewCustomer form. What is happening instead is data gets
entered, and when I try to close the frmNewCustomer form, it just goes to a
new record and will not close. There is code in the AfterUpdate event of the
same combo box which I've included as well. I don't think this should be
causing the problem, but at this point I'm not sure. I've used this code
before and never had the issue, nor did I ever have to add the line
Me.Requery, so I'm very confused.

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

DoCmd.OpenForm "frmNewCustomer", , , , , acDialog
Response = acDataErrAdded
Me.Requery
End Sub


Private Sub Combo74_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustBuildID] = " & Str(Nz(Me![Combo74], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks, Ivor

 

[Jeanette Cunningham]

Hi Ivor,
don't know if this will help, but I had a similar problem yesterday.
change this line
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
to
if not rs.NoMatch then
Me.Bookmark = rs.Bookmark
end if

when I stepped through the code, access indicated that NoMatch was true
but the next thing it did was to automatically find the record and set the
bookmark.

Jeanette Cunningham

This is the code in the NotInList event of Combo74 in the main form
(frmCustomerBuildings). Combo74 is used to choose a customer building from
a list. If I start typing a new customer, the pop up form frmNewCustomer
pops up as it should, data gets entered, then I expect to be able to close
the frmNewCustomer form and have the appropriate fields in the
frmCustomerBuildings form poplulated with the information that was just
entered on the frmNewCustomer form. What is happening instead is data gets
entered, and when I try to close the frmNewCustomer form, it just goes to
a new record and will not close. There is code in the AfterUpdate event of
the same combo box which I've included as well. I don't think this should
be causing the problem, but at this point I'm not sure. I've used this
code before and never had the issue, nor did I ever have to add the line
Me.Requery, so I'm very confused.

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

DoCmd.OpenForm "frmNewCustomer", , , , , acDialog
Response = acDataErrAdded
Me.Requery
End Sub


Private Sub Combo74_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustBuildID] = " & Str(Nz(Me![Combo74], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks, Ivor