Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

combo Value disappears

Posted on 2014-02-05
4
Medium Priority
?
447 Views
Last Modified: 2014-02-06
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
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39836589
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
 

Author Comment

by:Karen Schaefer
ID: 39836893
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 39837144
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
 

Author Closing Comment

by:Karen Schaefer
ID: 39840053
thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question