Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Combobox selection displays first record in table

Posted on 2008-10-09
11
Medium Priority
?
730 Views
Last Modified: 2013-11-27
The goal is for users to have one form to edit existing and to add new records, and to have some safeguards that force them to save their changes appropriately.

I've got one form with two combo boxes, one to select faculty to edit (these faculty are in the table) and one to select faculty to add (these faculty are not in the table yet).  The edit combobox always displays the first record in the table, regardless of the record I choose.  When I select a second (or third or fourth) time, the correct record is displayed.  Something is not happening in correct sequence and I am stuck on what to try next.  Code for this form is below.


Option Compare Database
Private mblnSave As Boolean
 
Private Sub Form_Open(Cancel As Integer)
    Me.Dirty = False
    
    Me.cboSelFacultyEdit.Visible = False
    Me.cboSelFacultyEdit.Enabled = False
    
    Me.cboSelFacultyAdd.Enabled = False
    Me.cboSelFacultyAdd.Visible = False
    
    Me.txtFacultyName.Visible = False
    Me.txtFacultyID.Visible = False
    
    Me.Detail.Visible = False
End Sub
 
Private Sub Form_Current()
    mblnSave = False
End Sub
 
 
Private Sub cmdRosterAdd_Click()
 
    DoCmd.GoToRecord , , acNewRec
    
    Me.cboSelFacultyAdd.Enabled = True
    Me.cboSelFacultyAdd.Visible = True
    
    Me.cmdRosterEdit.Enabled = False
    
    Me.cboSelFacultyEdit.Visible = False
    Me.cboSelFacultyEdit.Enabled = False
 
    'Me.Detail.Visible = True
 
    Me.cboSelFacultyAdd.SetFocus
 
End Sub
Private Sub cmdRosterEdit_Click()
 
    
    Me.cboSelFacultyAdd.Enabled = False
    Me.cboSelFacultyAdd.Visible = False
    
    Me.cmdRosterAdd.Enabled = False
    
    Me.cboSelFacultyEdit.Visible = True
    Me.cboSelFacultyEdit.Enabled = True
 
    'Me.Detail.Visible = True
 
    Me.cboSelFacultyEdit.SetFocus
 
End Sub
 
Private Sub cboSelFacultyAdd_Change()
    
    'this cbo is bound to FacultyID
    
    Me.txtFacultyName.Value = Me.cboSelFacultyAdd.Column(1)
    Me.txtFacultyID.Value = Me.cboSelFacultyAdd.Column(0)
 
    Me.txtFacultyName.Visible = True
    Me.txtFacultyID.Visible = True
    Me.Detail.Visible = True
 
End Sub
 
Private Sub cboSelFacultyEdit_Change()
 
    'this cbo is unbound; FacultyRosterID is the primary key in the table
    
    Me.txtFacultyName.Value = Me.cboSelFacultyEdit.Column(2)
    Me.txtFacultyID.Value = Me.cboSelFacultyEdit.Column(1)
    
    Dim rs As Object
    Set rs = Me.RecordsetClone
    rs.FindFirst "[FacultyRosterID]= " & Str(Me![cboSelFacultyEdit])
    'rs.FindFirst "[FacultyRosterID]= " & Str(Nz(Screen.ActiveControl, 0))
    Me.Bookmark = rs.Bookmark
    Me.cboSelFacultyEdit.Requery
    
    
    Me.txtFacultyName.Visible = True
    Me.txtFacultyID.Visible = True
    Me.Detail.Visible = True
      
      
End Sub
Private Sub cboSelFacultyEdit_AfterUpdate()
    
'    Dim rs As Object
'    Set rs = Me.RecordsetClone
'    'rs.FindFirst "[FacultyRosterID]= " & Str(Me![cboSelFacultyEdit])
'    rs.FindFirst "[FacultyRosterID]= " & Str(Nz(Screen.ActiveControl, 0))
'    Me.Bookmark = rs.Bookmark
'    Me.cboSelFacultyEdit.Requery
    
End Sub
Private Sub FacultyOrgID_Change()
    
    Me.RosterReportsToID.Value = Me.FacultyOrgID.Column(2)
    'Me.txtReportsTo = Me.FacultyOrgID.Column(3)
    
    Me.txtFacultyID.Value = Me.cboSelFacultyEdit.Column(0)
    
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
'    If mblnSave = False Then
'        proceed = MsgBox("Your changes have not been saved.", vbExclamation, "Unsaved Changes!")
'        Cancel = True
'        Me.Undo
'        'Me.cboSelProspect = ""
'    End If
 
    If mblnSave = False Then
        proceed = MsgBox("Do you want to save your changes?", vbYesNo)
            If proceed = vbYes Then
                ModifiedDate.Value = Now()
                mblnSave = True
                'DoCmd.RunCommand acCmdSaveRecord
                'Me.Dirty = False
 
       ' Me.cboSelProspect = ""
                    Else
                    Me.Undo
            End If
    End If
 
End Sub
 
Private Sub cmdSaveRoster_Click()
    If Me.Dirty = True Then
        mblnSave = True
            ModifiedDate.Value = Now()
            DoCmd.RunCommand acCmdSaveRecord
            MsgBox "Record Saved"
            Me.Requery
            
            Me.cmdRosterEdit.Enabled = True
            Me.cmdRosterAdd.Enabled = True
            
            Me.cboSelFacultyEdit.Visible = False
            Me.cboSelFacultyEdit.Enabled = False
    
            Me.cboSelFacultyAdd.Enabled = False
            Me.cboSelFacultyAdd.Visible = False
    
            Me.txtFacultyName.Visible = False
            Me.txtFacultyID.Visible = False
            
            Me.Detail.Visible = False
            
        Me.Dirty = False
    End If
    
Exit_cmdSaveRoster_Click:
    Exit Sub
 
Err_cmdSaveRoster_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRoster_Click
    
End Sub
 
Private Sub cmdCloseRoster_Click()
On Error GoTo Err_cmdCloseRoster_Click
 
    If Me.Dirty Then
        proceed = MsgBox("Do you want to save your changes?", vbYesNo)
            If proceed = vbYes Then
                mblnSave = True
                ModifiedDate.Value = Now()
                DoCmd.RunCommand acCmdSaveRecord
                Me.Dirty = False
                    Else
                    Me.Undo
            End If
    End If
        
    'Me.Dirty = False
    DoCmd.Close
 
Exit_cmdCloseRoster_Click:
    Exit Sub
 
Err_cmdCloseRoster_Click:
    MsgBox Err.Description
    'MsgBox "here"
    Resume Exit_cmdCloseRoster_Click
    
End Sub

Open in new window

0
Comment
Question by:ginafred
[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
  • 6
  • 5
11 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 22680380
To highlight a specific row in a combobox

Me.cboSelFacultyEdit.SetFocus
Me.cboSelFacultyEdit.ListIndex = n  -  where n=0 (first) to n-1 records
0
 

Author Comment

by:ginafred
ID: 22680626
Thanks, GRayL, but this does not fit what I need.  The way we want the form to work is this:
1. Click Edit Faculty
2. Select the Faculty person from cboFacultyEdit [pulls list from tblFacultyRoster]
3. The Faculty person's record appears for editing
4. Edit, save and close
or
1. Click Add Faculty
2. Select the Faculty person from cboFacultyAdd [pulls list from tblFaculty]
3. Add new record
4. Save and close

What happens, however, is that I select a person using cboSelFacultyEdit and get the first record entry in the table.  If a select the person a second time, then I get the correct record.  We're reworking this form because of user confusion, which is why we do not have the fields even appear until the selection is made.  If the wrong record appears, they will melt down.  Thanks!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22681077
How do you use the combobox selection to load the form with the record(s) for the faculty person with that ID?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:ginafred
ID: 22681752
One combobox is bound and one is unbound:

The form's record source is tblFacultyRoster.

For cboSelFacultyAdd:  
Control Source is FacultyID
Row Source = SELECT dbo_tblFaculty.FacultyID, [dbo_tblFaculty].[LName] & ", " & [dbo_tblFaculty].[FName] AS FacName FROM dbo_tblFaculty LEFT JOIN tblFacultyRoster ON dbo_tblFaculty.FacultyID=tblFacultyRoster.FacultyID WHERE (((dbo_tblFaculty.FacultyAcademicRankID) Is Not Null And Not (dbo_tblFaculty.FacultyAcademicRankID)=0) AND ((tblFacultyRoster.FacultyRosterID) Is Null)) GROUP BY dbo_tblFaculty.FacultyID

For cboSelFacultyEdit:
Control is unbound
Row Sources = SELECT tblFacultyRoster.FacultyRosterID, dbo_tblFaculty.FacultyID, [dbo_tblFaculty].[LName] & ", " & [dbo_tblFaculty].[FName] AS FacName, tblFacultyOrg.FacultyOrgName, tblFacultyOrg.FacultyOrgID
FROM (((dbo_tblFaculty RIGHT JOIN tblFacultyRoster ON dbo_tblFaculty.FacultyID = tblFacultyRoster.FacultyID) LEFT JOIN dbo_tlkpFacultyAppointmentType ON dbo_tblFaculty.ApptTypeID = dbo_tlkpFacultyAppointmentType.FacultyApptTypeID) LEFT JOIN tblFacultyOrg ON tblFacultyRoster.FacultyOrgID = tblFacultyOrg.FacultyOrgID) LEFT JOIN dbo_tblFaculty AS dbo_tblFaculty_1 ON tblFacultyOrg.FacultyOrgOwnerID = dbo_tblFaculty_1.FacultyID
GROUP BY tblFacultyRoster.FacultyRosterID, dbo_tblFaculty.FacultyID, [dbo_tblFaculty].[LName] & ", " & [dbo_tblFaculty].[FName], tblFacultyOrg.FacultyOrgName, tblFacultyOrg.FacultyOrgID, tblFacultyRoster.IsActive
HAVING (((tblFacultyRoster.IsActive)<>0))
ORDER BY [dbo_tblFaculty].[LName] & ", " & [dbo_tblFaculty].[FName];

 
0
 

Author Comment

by:ginafred
ID: 22686827
I went back to the beginning and made a new combo box.  I put everything I would have coded into the new combo box's embedded macro.  Although my preference is to have everything in code, rather than depend on macros, this approach seems to be working.  When I select a record, the fields and detail appear, get populated from the combo box correctly, and the detail displays the corresponding record.  

I am sure this was just a matter of putting things in the correct order in the code, but I was having no luck there.  

I'll consider this closed and accept my own solution by the end of the day, unless you have a suggestion that lets me get everything back into code and working in the proper sequence.

Thank you very much for your help.

GinaFred
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22688231
I find it strange that you set both the control source and the row source for cboSelFacultyAdd.  Normally, it is one or the other.  
0
 

Author Comment

by:ginafred
ID: 22688445
It's because only a subset of faculty are eligible to be added.  The query gives us this subset.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 1500 total points
ID: 22689487
I've just finished playing around with a table bound to a form containing a combobox bound to a fiend (control source) containing a query as the record source further restricting the bound field.  I did not have the locks set properly and messed up several postal codes in my addresses.  Lucky I had a backup.

In your edit query, you have eight fields in the combobox!  You approach is rather unusual.  Perhaps a read of something like:

http://www.eggheadcafe.com/software/aspnet/31880497/how-to-set-up-a-combo-box.aspx

might help
0
 

Author Comment

by:ginafred
ID: 22689675
Thanks for the article, GRayL.  It certainly does help.  Especially weeding through this "inherited" database and its problems.  I ended up converting the macro to VBA and sticking it in the After_Update event.  It's all working now, so I am letting it be.

I've given you some points because you've helped my overall knowledge, and been supportive!

Thank you!

GinaFred
0
 

Author Closing Comment

by:ginafred
ID: 31504709
Although I ended up using an approach of my own, GRayL was most helpful and supportive.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22689993
Thanks much!  Glad to help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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 …
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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