Item not on the list.............yes it is!

  • Thread starter Thread starter jeff klein
  • Start date Start date
J

jeff klein

On my "Orders" Form I have a command button that brings up a "Add Customer"
form that is sourced to "Customers" Table. After typing the name and
pressing the "Add" command button on this form the form closes and the new
costomer name is saved to a string (strNewName) that I have defined in
Module 1. The string I want to wright to the "Customer Name" combobox on
the order form when the Add Customer form has closed. This is the code in
the Add command button on the Add Customer form.

strNewName = ClientLastName + ", " + ClientFirstName
doCmd.Close
Forms!Orders!ClientFullName.SetFocus
Forms!Orders!ClientFullName.Text = strNewName

When the focus is returned to the Customer Name combo box on the orders form
I get an error message that the name is not on the list. Although when I
scroll down the list there it is and I can select it! I tried a Me.refresh
in the onfocus of the Customer Name combo box but this still does not work.
Maybe my problem lies with the combobox having the control source to
ClientID and the Row Source is :
SELECT DISTINCTROW Clients.ClientID, [ClientLastName] & ", " &
[ClientFirstName] AS ClientLastandFirst FROM Clients ORDER BY
[ClientLastName] & ", " & [ClientFirstName];

I know my explanation is lengthly although I think that the solution is
probably simple. Can anyone help me with this??
 
It sounds as though you've got the 1st column of the combobox as the bound
one.
 
Douglass, thanks for the reply,

Yes...The first column is the ClientID (bound) and the second is:
ClientLastandFirst: [ClientLastName] & ", " & [ClientFirstName].
The Add form does add the client name to its table although I am not sure
how to code it to the combo box since the name actually is in the second
column. Any Ideas??

PS sorry for the delay in reply



Douglas J. Steele said:
It sounds as though you've got the 1st column of the combobox as the bound
one.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



jeff klein said:
On my "Orders" Form I have a command button that brings up a "Add Customer"
form that is sourced to "Customers" Table. After typing the name and
pressing the "Add" command button on this form the form closes and the new
costomer name is saved to a string (strNewName) that I have defined in
Module 1. The string I want to wright to the "Customer Name" combobox on
the order form when the Add Customer form has closed. This is the code in
the Add command button on the Add Customer form.

strNewName = ClientLastName + ", " + ClientFirstName
doCmd.Close
Forms!Orders!ClientFullName.SetFocus
Forms!Orders!ClientFullName.Text = strNewName

When the focus is returned to the Customer Name combo box on the orders form
I get an error message that the name is not on the list. Although when I
scroll down the list there it is and I can select it! I tried a Me.refresh
in the onfocus of the Customer Name combo box but this still does not work.
Maybe my problem lies with the combobox having the control source to
ClientID and the Row Source is :
SELECT DISTINCTROW Clients.ClientID, [ClientLastName] & ", " &
[ClientFirstName] AS ClientLastandFirst FROM Clients ORDER BY
[ClientLastName] & ", " & [ClientFirstName];

I know my explanation is lengthly although I think that the solution is
probably simple. Can anyone help me with this??
 
If the bound field is the ID, then

Forms!Orders!ClientFullName.Text = strNewName

needs to be replaced with

Forms!Orders!ClientFullName = Id

Another alternative would be to loop through all rows in the combobox until
you find the one which matches, using something like the following untested
aircode:

Dim lngLoop As Long

For lngLoop = 0 to (Forms!Orders!ClientFullName.ListCount - 1)
If Forms!Orders!ClientFullName.Column(1, lngLoop) = strNewName Then
Forms!Orders!ClientFullName =
Forms!Orders!ClientFullName.Column(0, lngLoop)
Exit For
End If
Next lngLoop


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Jeff said:
Douglass, thanks for the reply,

Yes...The first column is the ClientID (bound) and the second is:
ClientLastandFirst: [ClientLastName] & ", " & [ClientFirstName].
The Add form does add the client name to its table although I am not sure
how to code it to the combo box since the name actually is in the second
column. Any Ideas??

PS sorry for the delay in reply



Douglas J. Steele said:
It sounds as though you've got the 1st column of the combobox as the bound
one.
code
in orders
form
when
I
scroll down the list there it is and I can select it! I tried a Me.refresh
in the onfocus of the Customer Name combo box but this still does not work.
Maybe my problem lies with the combobox having the control source to
ClientID and the Row Source is :
SELECT DISTINCTROW Clients.ClientID, [ClientLastName] & ", " &
[ClientFirstName] AS ClientLastandFirst FROM Clients ORDER BY
[ClientLastName] & ", " & [ClientFirstName];

I know my explanation is lengthly although I think that the solution is
probably simple. Can anyone help me with this??
 
Back
Top