Solved

combo Value disappears

Posted on 2014-02-05
4
432 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
  • 2
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 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 34

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now