Solved

Combo Problem

Posted on 2013-06-28
7
254 Views
Last Modified: 2013-07-03
I have a simple combo that searches the form's underlying table for contact name and moves to the record selected. For some strange reason occasionally the combo will not show all records. It will leave out "John Smith" but show "Jane Smith" and  "Jonny Smith" any reason for this? it's very disconcerting!
0
Comment
Question by:DatabaseDek
[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
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39283707
We need to see the sql of the  rowsource for the query.
If the rowsource is a named/saved  query we need the sql for that.
0
 

Author Comment

by:DatabaseDek
ID: 39283858
SELECT Project.AccountCode, Project.CustomerName, Project.ProjectRef, Project.Ref, Project.OrderNumber, Project.CustomerOrderNumber, Project.DateCreatedProject AS DateCreated, Project.AccountCode FROM Project ORDER BY Project.CustomerName, Project.AccountCode;

The code that runs for th combo box is:
Please note that the OnError part is in the wrong place could this be the problem? I also just noticed that I am re-querying the very combobox that I am clicking on. I do this so that if a new record is created it immediately appears in the combo. It is an early DB I did 10 years ago. That's my excuse.
Private Sub Combo29_AfterUpdate()
If Me.NewRecord = True Then
    DoCmd.Echo False
        Combo29.Requery
        Combo32.Requery
        Combo34.Requery
        Combo36.Requery
        Combo38.Requery
    DoCmd.Echo True
End If
On Error GoTo Err_Combo29_Click
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AccountCode] = " & Str(Nz(Me![Combo29], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_Combo29_Click:
    Exit Sub
Err_Combo29_Click:
    MsgBox "The selected Project has been Deleted. Click OK and select another Project"
    Resume Exit_Combo29_Click
End Sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39283897
To answer your first question, how many records do you have in this recordset you are using as the rowsource for Combo29?  If you have too many records, Access might still be trying to return the entire recordset.

I'm not certain why you would requery the combos immediately after selecting a record in the combo.  This could significantly slow down your application.

If you requery combo29 before doing the "findFirst", then there will be no value for the combo and the find first will not be able to locate the record.  Try disabling or removing the requery code.

Private Sub Combo29_AfterUpdate()

'If Me.NewRecord = True Then
'    DoCmd.Echo False
'        Combo29.Requery
'        Combo32.Requery
'        Combo34.Requery
'        Combo36.Requery
'        Combo38.Requery
'    DoCmd.Echo True
'End If
On Error GoTo Err_Combo29_Click
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AccountCode] = " & Str(Nz(Me![Combo29], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_Combo29_Click:
    Exit Sub
Err_Combo29_Click:
    MsgBox "The selected Project has been Deleted. Click OK and select another Project"
    Resume Exit_Combo29_Click
End Sub
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:DatabaseDek
ID: 39284872
Thank you

There is about 400 records in the underlying table/query.

I will remove the re-query code. The difficulty here is that this problem is sporadic and not easy to repeat. So I won't know if it has worked until it comes up again. Closing the database and reopening it sometimes clears the problem.

I asked the following question years ago but had a mixed response. What is the best way to ensure that combo boxes are up to date or updated immediately after a new record is entered without forcing a user to do something like press an F Key or click a save button. Or should this be a separate post?
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 39285065
First, give your combo boxes meaningful names, to make them easier to work with in code.  Next, you might want to filter your row source to exclude records with blank names (if any).  Finally, you no longer need to use the Bookmark property.  Here is the standard code I use for a record selector combo box:

Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Comment

by:DatabaseDek
ID: 39288009
That's great, thank you

When you say that the bookmark is no longer needed does that apply to with Access 2002?

Can you answer the last part of my last post if possible.

Many thanks

Derek
0
 

Author Closing Comment

by:DatabaseDek
ID: 39297409
Thank you.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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