Microsoft Access forms

I've created a microsoft database to enter security clearance data.  I added a switchboard  where the user will be able to click on a  command button to enter, view, or update information.  Here is how I would like the information to display.

1. I would like to create a parameter query  where the user can enter in the individual's name.
2. When the name is entered in the parameter it will bring back a list of all individuals with the same first or last name? A last name, first name and MI fields are included in the query.  I would like for the user to enter first  name or last name or both to get results.
If the user incorrectly spells the name, I would like for a entire list of everyone to display,

3. Once the query opens, I would like for the user to be able to select a name from the list, by clicking on the name.  
4. When the name is clicked, the individuals detailed record will display in read only mode.  

Example.  The user needs to view an Individual name Bob Jones's detail information.  However, there are three Bob Jones' listed in the data base.  The user will select from the switcboard "View Security Clearance Detail", the parameter will display requesting the individual's name, the user will enter last name, first name or both.  Once the parameter executes, the two individuals name bob jones will display in a data list format.  I want the user to be able to review the list and click on the correct Bob Jones from the listed recorded and a detailed form displays with all his information to view only.  I also, would like to do this with a query to display a form which allow the user to edit the security clearance detail information on that user.

Can you assist me by starting me in the right direction of how to build this query and form?  I have attached a sample database.  The database is not relational.  It was requested not to be a relational database.  If no one meets the criteria entered in the parameter, I want a full list to displays of all individuals. I want it to display similar to the employee and employee detail forms in the northwinds database  


Thanks.
NDAlog-2013-09-20.accdb
CindyApplication Systems Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AccessGuy1763Commented:
I looked at your database and your requirements and I can't think of a way to do everything you're looking for without at least a little VBA.  Since your database has absolutely no code in it, I'm a bit concerned about your comfort level with implementing and maintaining my suggested solution.  Nonetheless, here we go.

I think that what you are really asking for is a search form with a small level of complexity to it.  It won't require a lot of VBA code, but I'm a programmer at heart so coming up with a design that has absolutely none is a bit tough for me (although it certainly might be possible).

I have attached your database to my post with two new forms, frmSearchSample and frmNDALOGSample.  I believe frmSearchSample is what you're looking for.  frmNDALOGSample is just a simple display form that pops up when you double click any value in the listbox.
NDAlog-2013-09-20.accdb
0
CindyApplication Systems Analyst IIAuthor Commented:
Thanks this is a great start.  However, I can do VBA code, I just need to know which code to use.  The reason I don't use a lot of VBA code in my designs is because the people I work didn't want anything fancy until now.  I think  my first design is really will work for a simple way of entering data, however, it is not what they want now.  With all that said, please provide me the VBA code that will allow the form to execute when the user enters data in Last Name or the  First Name fields, click search and the database will automatically display any record with either the first name or last name included in the unbound form, then the user can select the name and it displays the form and clears the form.  Thanks.
0
CindyApplication Systems Analyst IIAuthor Commented:
I would like your opinion on the switchboard.  Should I keep it where the user has to access three forms to add, edit or view or should I make it so that the user can add, view with the ability to edit using the frmSearchSample form?  Thanks.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AccessGuy1763Commented:
The necessary code to do what you're asking for is in the sample I attached to this thread.  Feel free to copy/modify it to better suit your needs.  Here is all the code from frmSearchSample:

Option Compare Database
Option Explicit

Private Sub cmdSearch_Click()

    Dim strSQL As String
    
    strSQL = "SELECT tbl_NDALOG.ID, [Last_Name] & " & Chr(34) & ", " & Chr(34) & " & [First_Name] AS FullName, " & _
             "tbl_NDALOG.Last_Name, tbl_NDALOG.First_Name, tbl_NDALOG.MI FROM tbl_NDALOG"
                      
    If IsNull(Me.txtSearchFirstName) = False Then
    
        strSQL = strSQL & " WHERE First_Name='" & Me.txtSearchFirstName & "'"
        
    End If
    
    If IsNull(Me.txtSearchLastName) = False Then
    
        If strSQL = "" Then
        
            strSQL = strSQL & " WHERE Last_Name='" & Me.txtSearchLastName & "'"
            
        Else
        
            strSQL = strSQL & " AND Last_Name='" & Me.txtSearchLastName & "'"
            
        End If
        
    End If
    
    Me.lstSearchResults.RowSource = strSQL
    
    Me.lstSearchResults.Requery
    
    If Me.lstSearchResults.ListCount = 0 Then
    
        strSQL = "SELECT tbl_NDALOG.ID, [Last_Name] & " & Chr(34) & ", " & Chr(34) & " & [First_Name] AS FullName, " & _
                 "tbl_NDALOG.Last_Name, tbl_NDALOG.First_Name, tbl_NDALOG.MI FROM tbl_NDALOG"
        
        Me.lstSearchResults.RowSource = strSQL
        
        Me.lstSearchResults.Requery
        
        Me.lblSearchResults.Caption = "Search Results(0 - Showing all records)"
        
    Else
    
        Me.lblSearchResults.Caption = "Search Results(" & Me.lstSearchResults.ListCount & ")"
        
    End If
    
End Sub

Private Sub lstSearchResults_DblClick(Cancel As Integer)

    If IsNull(Me.lstSearchResults.Column(0)) Then
    
        Call MsgBox("Please select a record to view.", vbOKOnly, "Select a record")
        
    Else
    
        DoCmd.OpenForm "frmNDALogSample", acNormal, , "id=" & Me.lstSearchResults.Column(0), acFormEdit
        
    End If
    
End Sub

Open in new window


Regarding your switchboard, I do like having a separate button which opens the form in Data Entry mode like you have.  I would replace both the "View" and "Update" buttons on your switchboard to one that points at your new search form.  The user could then search for the record they want and view/update by double clicking and opening the same form in Edit mode filtered to the appropriate ID.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CindyApplication Systems Analyst IIAuthor Commented:
The tech was right on target with what I needed to complete this project.
0
CindyApplication Systems Analyst IIAuthor Commented:
One more question> In the frmSearchSample form, I would like for the first name and last name fields to be drop downs where I can select the name of the individual after being added in the database.  I know how to change them to  drop down fields, but cannot seem to tie them into the Search Result field.  Do I modify the code? Thanks.
0
AccessGuy1763Commented:
In regards to changing the fields to comboboxes, I'm going to take a stab at what I think is happening.  I think you have a RowSource for your combo boxes where the first column is some type of ID field.  When the SQL is constructed through the code, it is grabbing this ID, rather than the name, and finding no matches when it uses the ID to search against first and last name.  If I'm right, you would receive no error messages, but the listbox would never populate with any results.

If this is your problem, you have two ways you can fix it:
1) Have the combobox RowSource ONLY select the First (txtSearchFirstName) and Last  (txtSearchLastName) Name fields, respectively.  The code would then grab the right value again and everything would start working.
2) Fix the code from lines 11 to 31 as follows so it grabs the right column from the combobox:

    If IsNull(Me.txtSearchFirstName) = False Then
    
        strSQL = strSQL & " WHERE First_Name='" & Me.txtSearchFirstName.column(1) & "'"
        
    End If
    
    If IsNull(Me.txtSearchLastName) = False Then
    
        If strSQL = "" Then
        
            strSQL = strSQL & " WHERE Last_Name='" & Me.txtSearchLastName.column(1) & "'"
            
        Else
        
            strSQL = strSQL & " AND Last_Name='" & Me.txtSearchLastName.column(1) & "'"
            
        End If
        
    End If

    debug.print strSQL

   Me.lstSearchResults.RowSource = strSQL

Open in new window


You could really benefit from some simple debugging in this situation.  I've added a "debug.print" command to the code above which prints the fully constructed SQL to the Immediate Window in the VB Editor.  You can retrieve this and examine it  (even put it into a blank query to test it) for any problems.  If you're still having an issue try to post back with that SQL statement so I can troubleshoot it.
0
CindyApplication Systems Analyst IIAuthor Commented:
Thanks again for your help.  When I select first name (only) it will display every one in the list with the same first name, however, when I select last  name only, I get no results, in the display screen, but I get a message stating the number of results.  Can you please tell me what is missing in the code, that is not allowing me to do just a last name search.  I want to be able to enter first and last name and get results, first name only and get results and last name only and get results.  The results are attached.
error-in-code---NDA.docx
0
AccessGuy1763Commented:
I tested it out and discovered that this is a logic problem that I think exists with the fields being text boxes as well.  I made the mistake of having line 19 checking for an empty string for strSQL when that will never be true with the way the code is written.  Instead, I changed it to check to see if the first name search field is empty, which was really the intention of that logic check in the first place anyway:

Lines 17 - 29:
   If IsNull(Me.txtSearchLastName) = False Then
    
        If isnull(me.txtSearchFirstName) Then
        
            strSQL = strSQL & " WHERE Last_Name='" & Me.txtSearchLastName & "'"
            
        Else
        
            strSQL = strSQL & " AND Last_Name='" & Me.txtSearchLastName & "'"
            
        End If
        
    End If

Open in new window

0
CindyApplication Systems Analyst IIAuthor Commented:
Can you please combine all the code changes for me to add in the event procedure?  Thanks.
0
AccessGuy1763Commented:
Here is all of the code in the module for frmSearchSample:

Option Compare Database
Option Explicit

Private Sub cmdSearch_Click()

    Dim strSQL As String
    
    strSQL = "SELECT tbl_NDALOG.ID, [Last_Name] & " & Chr(34) & ", " & Chr(34) & " & [First_Name] AS FullName, " & _
             "tbl_NDALOG.Last_Name, tbl_NDALOG.First_Name, tbl_NDALOG.MI FROM tbl_NDALOG"
                      
    If IsNull(Me.txtSearchFirstName) = False Then
    
        strSQL = strSQL & " WHERE First_Name='" & Me.txtSearchFirstName & "'"
        
    End If
    
    If IsNull(Me.txtSearchLastName) = False Then
    
        If IsNull(Me.txtSearchFirstName) Then
        
            strSQL = strSQL & " WHERE Last_Name='" & Me.txtSearchLastName & "'"
            
        Else
        
            strSQL = strSQL & " AND Last_Name='" & Me.txtSearchLastName & "'"
            
        End If
        
    End If
    
    Me.lstSearchResults.RowSource = strSQL
    
    Me.lstSearchResults.Requery
    
    If Me.lstSearchResults.ListCount = 0 Then
    
        strSQL = "SELECT tbl_NDALOG.ID, [Last_Name] & " & Chr(34) & ", " & Chr(34) & " & [First_Name] AS FullName, " & _
                 "tbl_NDALOG.Last_Name, tbl_NDALOG.First_Name, tbl_NDALOG.MI FROM tbl_NDALOG"
        
        Me.lstSearchResults.RowSource = strSQL
        
        Me.lstSearchResults.Requery
        
        Me.lblSearchResults.Caption = "Search Results(0 - Showing all records)"
        
    Else
    
        Me.lblSearchResults.Caption = "Search Results(" & Me.lstSearchResults.ListCount & ")"
        
    End If
    
End Sub

Private Sub lstSearchResults_DblClick(Cancel As Integer)

    If IsNull(Me.lstSearchResults.Column(0)) Then
    
        Call MsgBox("Please select a record to view.", vbOKOnly, "Select a record")
        
    Else
    
        DoCmd.OpenForm "frmNDALogSample", acNormal, , "id=" & Me.lstSearchResults.Column(0), acFormEdit
        
    End If
    
End Sub

Open in new window


I actually still had the whole database file as well with my sampe, so I attached it.  If you have any further issues you can use it to do a direct comparison.
NDAlog-2013-09-20.accdb
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.