combobox should get updated without selecting or clicking

[rashu123]

In my application i have 2 comboboxes displaying name and number.If i select name from 1 st combobox the second one displays the corresponding number.But the issue is that for the first time when i select name from 1st combobox, the value in the 2nd combobox will be displayed only after I click the combobox.In the subsequent checks the value is readily displayed in the 2nd combobox just after we select the name from the 1st one.Please let me know how to display the value readily for the first time itself.

 

[MajP]

can you provide
1) If the controls are bound or unbound
2) The rowsource for each combo
3) the current code you have to synch the 2nd combo
4) Is there a number for every name
5) does the name have a single or multiple numbers
6) Do you want it synched both directions. Pick a name show the corresponding number, pick a number show the corresponding name.

 

[rashu123]

hi
thanks for replying

The controls are bounded.There are 2 queries used as rowsource for both the boxes along with the rowsource for displaying all the names and numbers for the customer to select from.The queries are passed using code depending upon whether the user selects name or number(name for specified number/number for specified row)

I have just used setfocus to acheive synchronisation after assigning rowsource.

all names have corresponding numbers and also some names may have more than one number in which case i want the 1st number to be shown in the box.

and yes i need the boxes to be synchronised in both directions..
reply at the earliest.

 

[MajP]

There are 2 queries used as rowsource for both the boxes along with the rowsource for displaying all the names and numbers for the customer to select from.The queries are passed using code depending upon whether the user selects name or number(name for specified number/number for specified row)

Please provide the requested information, that is just a general description.
1)Show the actual rowsources. ie. "Select x, y from tblz order by y"
2)show the actual code. ie public sub cmbo1_afterUpdate()...

 

[rashu123]

Private Sub CustomerID_AfterUpdate()

If (Me.CustomerID.RowSource = "qryCustomerName") Then

Me.CustomerAccountID.SetFocus

Dim strSQL As String
strSQL = "SELECT DISTINCT tblCustomerAccount.CustomerAccountID, tblCustomerAccount.CustomerAccount"
strSQL = strSQL & " FROM tblCustomerAccount WHERE CustomerID = " & Me.CustomerID


Me.CustomerAccountID.ColumnCount = "2"
Me.CustomerAccountID.ColumnWidths = "0"
Me.CustomerAccountID.BoundColumn = "1"
Me.CustomerAccountID.SetFocus
Me.CustomerAccountID.RowSource = strSQL

'Me.CustomerAccountID = Me.CustomerAccountID.ItemData(Abs(Me.CustomerAccountID.ColumnHeads))


Me.CustomerAccountID = Me.CustomerAccountID.ItemData(0)

Else
Me.CustomerAccountID.RowSource = "qryCustomerAccount"
Me.CustomerAccountID.SetFocus

End If

End Sub

Private Sub CustomerAccountID_AfterUpdate()

If (Me.CustomerAccountID.RowSource = "qryCustomerAccount") Then

Me.CustomerAccountID.SetFocus
Dim strSQL1 As String

strSQL1 = "SELECT DISTINCT tblCustomer.Customer,tblCustomer.CustomerID"
strSQL1 = strSQL1 & " FROM tblCustomer WHERE CustomerID = " & Me.CustomerAccountID
Me.CustomerID.SetFocus
Me.CustomerID.RowSource = strSQL1
Me.CustomerID.ColumnCount = "1"
Me.CustomerID.ColumnWidths = "1"
Me.CustomerID.BoundColumn = "0"

'Me.CustomerID.Requery
Me.CustomerID.SetFocus


Else

Me.CustomerID.RowSource = "qryCustomerName"
Me.CustomerAccountID.SetFocus


End If
End sub



the rowsources are there in the code itself...Hope my doubt is clear now.

qryCustomerName: gives list of all names
qryCustomerAccount: gives list of all numbers.

these rowsources are assigned in the double click event of both the comboboxes

the problem is that the corresponding customer name has to be explicitly selected by the user by clicking the combobox whenever he enters the account number.
This happens whenever the account number field loses focus..ie when u click some other field in the form and come back to account number again.
Hope the problem is clear now.Please suggest a work around for this.

 

[MajP]

This was hard to read and had a lot of repetitive code. I tried to clean it up to make it more manageable.

I put this in stand alone procedures so if you have to you can call it from multiple events. For example after I update the customer ID I can call the UpdateCustomerAccount, but if necessary can call it from the customerAccountID got focus event. I rarely have any code in event procedures besides calling stand alone procedures. Just add a lot of flexibility.

On the "first instance" problem you likely need a do events which I added.

The other problem I believe comes from the bound column of 0 on the customerID combo. I cannot understand how that works. If this combo is bound, not sure how saving a list index in the table works. Can you bind it to 2, the customerID?

You do not have to set focus to a control to change it properties except for a few (i.e. text, visible).

Not sure if this solves the problem. It is hard to debug timing/event problems without seeing how the events come together.

Private Sub UpdateCustomerAccount()
   Dim strSQL As String
   If (Me.CustomerID.RowSource = "qryCustomerName") Then
      strSQL = "SELECT DISTINCT tblCustomerAccount.CustomerAccountID, tblCustomerAccount.CustomerAccount"
      strSQL = strSQL & " FROM tblCustomerAccount WHERE CustomerID = " & Me.CustomerID
      With Me.CustomerAccountID
         .ColumnCount = "2"
         .ColumnWidths = "0"
         .BoundColumn = "1"
         .RowSource = strSQL
         .Value = Me.CustomerAccountID.ItemData(0)
     End With
   Else
      Me.CustomerAccountID.RowSource = "qryCustomerAccount"
   End If
   DoEvents
   Me.CustomerAccountID.SetFocus
End Sub

Private Sub updateCustomer()
    Dim strSQL1 As String
   If (Me.CustomerAccountID.RowSource = "qryCustomerAccount") Then
     strSQL1 = "SELECT DISTINCT tblCustomer.Customer,tblCustomer.CustomerID"
     strSQL1 = strSQL1 & " FROM tblCustomer WHERE CustomerID = " & Me.CustomerAccountID
     With Me.CustomerID
       .RowSource = strSQL1
       .ColumnCount = "1"
       .ColumnWidths = "1"
       .BoundColumn = "0"
       .setfocus 
    End With
  Else
    Me.CustomerID.RowSource = "qryCustomerName"
    Me.CustomerAccountID.SetFocus
  End If
End Sub

 

[rashu123]

hey thanks a lot...I made some small changes and it worked!!.I added the .value to customerID update also.
Thanks again