MS Access Combo box search

Search Records by using Combo Box

In this lesson I am going to explain how to add a combo box to search data on a table with the help of a combo box.You can add a combo box on a Form under the design view and you have to add only a small vba code to accomplish this task.

Here is a example data table that I have used to test my method.

Columns used are as follows;
ID     =Number
StuName  = Text
StuAddress = Text
StuDOB  = Date
StuMobile = Text

table name is tblMain


Once you have created the table you have to create a form based on it.To do this quickly you can use Form Wizard.Once you select form Wizard,It will give you a option to add desired filed to appear on the form.this time select all the table fields to create a Form.

Once you created the form under the Form Wizard then you have to add combo box to this form.This can be done under the design view of the form.Please see following screen shot to understand this.

You have to select ID column of our table as the Row Source for this combo Box.Form Name is frmMain

Then You have to put small VBA code behind the Combo Box to make this task sucess. Then by right clicking Combo Box select "Build Event" then select Code Builder,Finaly you have to enter following code under the "Private Sub cboFind_AfterUpdate()" event.

Following is the code you should use to make this tack success

Option Compare Database

Private Sub cboFind_AfterUpdate()

Dim strCriteria As String

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

strCriteria = "[ID] = " & Me![cboFind]

rst.FindFirst strCriteria

Me.Bookmark = rst.Bookmark

End Sub

Finally, Once you entered above code Run your access form to check this.

When you select desired record from the Combo Box it will automatically load the relevant record to the Form from Our Table.

Down Load Sample File by clicking following link

GENERATE REPORTS IN MS ACCESS ACCORDING TO DATE RANGES

GENERATE  REPORTS IN MS ACCESS ACCORDING TO DATE RANGES

This blog post explains how to create ms access form to generate reports according to the given date rangers.Video tutorial for this lesson is available in You Tube.Video is linked to this blog,please see below;
First Step of this process is to create a MS Access database to test this.you can create simple database to do this.In my example I have created a database called "Data Range".

Then I created  a table called tblData.Please see below screen shot to understand table structure.

Please download sample database to see full table fields.

Once you create the table you have to create a Query based the tblData. There you have to choose column you want to display in your access report.

Follow this path to create a Query based on the table.

Select "Create" from the ribbon and then select "Query Design".

Design Query according to the above image.Then you have to create a Form to enter data.

Take two labels and name them as "Start Date" and "End Date".

You need two text boxes and name text boxes as follows;

Text Box 1 = txtdatefrom

Text Box 2 =txtdateto

Put Five Command Buttons on the form,and you have to name them as follows;

Button 01 

Caption  = "Today"

Button Name = cmdtoday

Button 02

Caption  = "Week"

Button Name = cmdweek

Button 03

Caption  = "Week"

Button Name = cmdweek

Button 04

Caption  = "Month"

Button Name = cmdmonth

Button 05

Caption  = "Year"

Button Name = cmdyear

Button 06

Caption  = "Report"

Button Name = cmdReport

Form Name = frm_DateRange

Based on the created Query generate report and keep its name as Query 1 for testing purpose.Report is as follows when it opens in Design View

Once you complete above steps you have small coding part in using VBA.Click on the Visual Basic Icon to access the code editor.Please view the below image for more details;

Once you accessed to the VBA code editor,you have to do coding part as follows;

Coding Part

Option Compare Database

Private Sub cmdtoday_Click()

'Sets the Date From and Date To text boxes

'to Today's Date

    Me!txtdatefrom = Date

    Me!txtDateTo = Date

End Sub

========================================================================

Private Sub cmdweek_Click()

'Sets the Date From and Date To text boxes

'to show complete working week (Mon - Fri)

    

    Dim today

    today = Weekday(Date)

    

    Me!txtdatefrom = DateAdd("d", (today * -1) + 2, Date)

    Me!txtDateTo = DateAdd("d", 6 - today, Date)

End Sub

========================================================================

Private Sub cmdmonth_Click()

'Sets the Date From and Date To text boxes

'to show complete month (from start to end of current month)

    Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date))

    Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))

End Sub

========================================================================

Private Sub cmdyear_Click()

'Sets the Date From and Date To text boxes

'to show complete current year

    Me!txtdatefrom = CDate("01/01/" & Year(Date))

    Me!txtDateTo = DateAdd("d", -1, DateAdd("yyyy", 1, Me!txtdatefrom))

    

End Sub

========================================================================

Private Sub cmdReport_Click()

    On Error GoTo Err_cmdReport_Click

    Dim stDocName As String

    stDocName = "Rpt_Date"

    

'Check values are entered into Date From and Date To text boxes

'if so run report or cancel request

    If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then

        MsgBox "Please ensure that a report date range is entered into the form", _

               vbInformation, "Required Data..."

        Exit Sub

    Else

        DoCmd.OpenReport stDocName, acPreview

    End If

Exit_cmdReport_Click:

    Exit Sub

Err_cmdReport_Click:

    MsgBox Err.Description

    Resume Exit_cmdReport_Click

End Sub

After you have placed codes as above you have to set a criteria for "Business Date" in your query design.It can be shown as follows;

>=[Forms]![frm_DateRange]![txtDateFrom] And <=[Forms]![frm_DateRange]![txtDateTo] 

Once you have completed all the above steps you can test your programe.It is as follows;

Here I have given YEARLY Data to view Report.Then It will shoe you the yearly data in your access report "Query 1"

Report is for above criteria is as follows;

You can download sample file by clicking following link;

Download

For Video tutorial of this lesson please play following video.