?
Solved

Combobox selection displays first record in table

Posted on 2008-10-09
11
Medium Priority
?
722 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses

752 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