Solved

combo Value disappears

Posted on 2014-02-05
4
430 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

12 Experts available now in Live!

Get 1:1 Help Now