Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

Combobox selection displays first record in table

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
ginafred
Asked:
ginafred
  • 6
  • 5
1 Solution
 
GRayLCommented:
To highlight a specific row in a combobox

Me.cboSelFacultyEdit.SetFocus
Me.cboSelFacultyEdit.ListIndex = n  -  where n=0 (first) to n-1 records
0
 
ginafredInstructional DesignerAuthor Commented:
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
 
GRayLCommented:
How do you use the combobox selection to load the form with the record(s) for the faculty person with that ID?
0
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.

 
ginafredInstructional DesignerAuthor Commented:
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
 
ginafredInstructional DesignerAuthor Commented:
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
 
GRayLCommented:
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
 
ginafredInstructional DesignerAuthor Commented:
It's because only a subset of faculty are eligible to be added.  The query gives us this subset.
0
 
GRayLCommented:
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
 
ginafredInstructional DesignerAuthor Commented:
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
 
ginafredInstructional DesignerAuthor Commented:
Although I ended up using an approach of my own, GRayL was most helpful and supportive.
0
 
GRayLCommented:
Thanks much!  Glad to help.
0

Featured Post

Independent Software Vendors: 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!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now