asked on

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

AccessGuy1763

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

Cindy

ASKER

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.

Cindy

ASKER

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.

ASKER CERTIFIED SOLUTION

AccessGuy1763

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cindy

ASKER

The tech was right on target with what I needed to complete this project.

Cindy

ASKER

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.

AccessGuy1763

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.

Cindy

ASKER

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

AccessGuy1763

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

Cindy

ASKER

Can you please combine all the code changes for me to add in the event procedure? Thanks.

AccessGuy1763

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