Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

combo Value disappears

I have a combo on a Mainform that sets the value of the combo on the subform.

I also have the  onCurrent Event of the subform, setting the value of subform.combo  based on the MainForm contractID.  However, after entering the values in the remaining fields for the current record, when the user creates a new record and selects another value in the subform.combo, the values is empty.

What can be causing this problem?

Private Sub Form_Current()
    Dim strSQL As String
    
    If gUser = "user" And Nz(Me.RecordLock, 0) = -1 Then
        Me.AllowEdits = False
    Else
        Me.AllowEdits = True
    End If
   strSQL = "Select VendorName, AgencyID, AgencyPID, ContractNumber from tblinvoice" & _
            " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & "" & _
            " GROUP BY VendorName, AgencyID, AgencyPID, ContractNumber" & _
            " ORDER BY VendorName"
    Me.cboVendorName.RowSource = strSQL
End Sub

Open in new window

Private Sub cboVendorName_AfterUpdate()
    Me.AgencyID = Me.cboVendorName.Column(1)
    Me.AgencyPID = Me.cboVendorName.Column(2)
    Me.InvoiceDate.SetFocus
End Sub

Open in new window

0
Karen Schaefer
Asked:
Karen Schaefer
  • 2
  • 2
1 Solution
 
PatHartmanCommented:
The combobox displays a value from the RowSource.  If you changed the RowSource, the value that cooresponds to the bound field may no longer be part of the list.  That is why it is blank.  This isn't usually an issue on single record forms since all you can see is the current record.  However on continuous or datasheet forms, you can see multiple records so this can be disconcerting.  This is because Access maintains only one set of properties per form and that means that it can only represent one RowSource at a time.  This doesn't impact the bound column.  It is still in the table.  You just can't see it.

I haven't found a good way to solve this problem for datasheet forms but I have attached an example that explains the problem and shows how to resolve it for continuous forms.
FixCascadingCombos130128.zip
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the input, One last question.

If I am adding new value to list view the List Item Edit Form.

Is it possible to capture the value that was just typed in the combo, even though it has not been added to the list yet, so that it may be used in the edit form - preventing the need to type the value twice.

K
0
 
PatHartmanCommented:
Yes.  You would need to add it to the RowSource table and requery the combo.  I rarely allow this though since I think it makes the users sloppy and they frequently add misspellings to the list.  Of course in some cases you don't have any choice.  Use the not in list event to capture the event and put your code there.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now