?
Solved

combo Value disappears

Posted on 2014-02-05
4
Medium Priority
?
449 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 40

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 40

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

615 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