Solved

Combobox selection displays first record in table

Posted on 2008-10-09
11
690 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
  • 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now