Access Search for specific record

Question

I have an access database (tables held as SharePoint lists).

I have a form called 'Review' that has a combo box that holds company names - the user can select a company name here which populates a sub form called 'ReviewDetail'. The form 'Review' record source is a table called 'Companies'.

When the user selects a company from the combo box all records are returned (for all months). The user can then switch between reviews using buttons on the sub form. This subforms record source is a table called 'Reviews'.

On form Review I use the below (on change), which brings back the records.

DoCmd.SearchForRecords , "", acFirst, "[Company] = " & "'" & [Forms]![Review]![Company] & "'"

I need to be able to incorporate the Month from the combo box to bring back that specific record.

I have tried:

DoCmd.SearchForRecords , "", acFirst, "[Company] = " & "'" & [Forms]![Review]![Company] & "' AND [Month] = '" & Me.Month & "'"

but this doesn't work.

This is an application that I have been given to maintain (not created by me).

How can I just get back the specific record? I think I am getting confused around the 2 different tables (note that the table Companies doesn't hold month values).

Please clarify whether the Month combobox resides on the Main Form, or the Sub Form. — Jericho Johnson
@JerichoJohnson the Month combobox is on the Sub Form, the Company combo box is on the main form. — TAS1987

Jericho Johnson Jericho Johnson · Accepted Answer · 2018-10-02T13:21:22

Ok, your description of the form and subform is a little vague, so let me lay out what I think you are describing, and then present a solution, and you can let us know if this is correct or not.

Your Main form, named Review is bound to the Company table as the RecordSource and is showing detail fields for a single Company. You also have a Combobox on this Main form which has a list of all of the Companies in it.

You have a Subform, named ReviewDetail that is bound to the Reviews table, which contains ALL Reviews for ALL Companies. The Subform is using subform linking to link the Company on the Mainform to the Company on the Subform so that at runtime, the Subform only displays the Reviews for the current Company shown on the Main form.

When a user selects a Company from the Combobox, you have code in the [OnChange] event that effectively causes two things happen. First, the Main form jumps to the selected company to show the details of that company (via your code). Second, the Subform, which is linked to the Main form by company, immediately filters to display only the Reviews for the selected company.

Now, you want to add a Combobox on the Subform to further filter the Subform for a particular Month.

I am not sure how you are getting the list of Months in your Combobox, but I would do the following: I would have a small table with the Months called tblMonths with two columns, MonthID and MonthName

MonthID  MonthName
=======  =============
      1  January
      2  February
      3  March
   etc...
     12  December

Then I would set the RowSource of the Months Combobox on the Subform to:

SELECT 0 AS MonthID, "(ALL)" AS MonthName FROM tblMonths
UNION SELECT MonthID, MonthName FROM tblMonths
ORDER BY MonthID;

This will give you a Combobox with all of the Months AND an "ALL" option at the top to allow the user to display All months, or just a single month. And, your Combobox will have two columns, so you will need to format it appropriately to display only the 2nd column, with properties similar to the following:

Now, in the [AfterUpdate] event of your ComboBox, which is on your Subform, you will use the following code (assume that the Combobox name is cboMonth)

Dim mssql As String    

'Note, by concatenating a zero length string to the current value of the
'    Combox and making sure the length is greater than zero, we can
'    easily confirm that the user actually selected one of the items
'    in the list, as opposed to accidentally clearing out the Combobox
'If we did not check this before relying on the value of the Combobox,
'    our code would produce an error at runtime if the user did clear the Combobox
If Len(Me.cboMonth & "") > 0 Then
    If Me.cboMonth = 0 Then   'They chose "ALL"
        Me.FilterOn = False
    Else
        mssql = "[Month] = """ & Me.cboMonth.Column(1) & """"
        Me.Filter = mssql
        Me.FilterOn = True
    End If
Else
    'Since the user cleared the Combobox, let's set the value
    'back to "ALL" and display ALL records
    Me.cboMonth = 0
    Me.FilterOn = False
End If

With this code, the Subform will filter by the selected Month, OR show all Months based on what the user selects. If the field on the Subform containing the "Month" is not named Month as you seem to indicate in your sample code, then you will need to change [Month] in my code above to the correct name, in square brackets.

Finally, to improve your Company Combobox on the Main form, I would change both the Event and the code that you are using to jump to a particular company. Again, I would use the AfterUpdate event instead of the OnChange event. Much more reliable in these situations. And, I would use the following code (assume Company Combobox is named cboCompany):

Dim rst As DAO.RecordSet
Dim mssql As String

If Len(Me.cboCompany & "") > 0 Then
    Set rst = Me.RecordsetClone
    mssql = "[Company]=""" & Me.cboCompany & """"
    rst.FindFirst mssql
    Me.Bookmark = rst.Bookmark
    'If you then want to default the "Months" Combobox on the Subform to "ALL"
    Me.ReviewDetail.Form.cboMonth = 0
    me.cboCompany = Null
End If
Set rst = Nothing

With this code, the Main form jumps to the selected Company (or does nothing if the user simply clears the Combobox) and sets the Months Combobox on the Subform back to the "ALL" value so that all Reviews are initially displayed. Further, it then clears the Company Combobox so that it is ready for the next selection by the user.

Access Search for specific record

1 Answers