How to create Access form to enter data

How to create Access form to enter data

From our previous lesson 01 we have learnt that how to create access database and how to enter data in to a designed table.Entering data into a table directly is somewhat difficult when there are large volume of data.Then access we have good option to enter data easily,it is Access Forms.We can design access form to feed data in to a access table.So lets learn simply how we can design access form to enter data.

Step 01

Fig 01

As we designed from our first lesson we already have a table like Fig 01.Then we can create a access form for above table very easily.

Fig 02

Follow the instructions as per Fig 02.Then Form Wizard will appear on your desktop.

Fig 03

When you click on the Form Wizard Fig 03 dialog box will appear,there you should select your table to create form for that table.Then on this dialog box on the left hand side there is a window called Available Fields,it shows the all the available fields of your designed table.then on the right hand side there is anther window which allows you to select desired fields for your table,with the help of the arrow marks appear among the two windows allows to user to select field to your access form.(Please see following Fig 04 image for more )

Fig 04

After selecting field you need click on the Next button.Then anther dialog box comes as per Fig 05.

Fig 05

Fig 06

Give a name to your Form and Click on the finish as per Fig 06.

Fig 07

Then Form is created and it looks like Fig 07,then you can easily enter data to your table via this form.

MS Access for Beginners

MS Access for Beginners

01.Create Access Data Base

Open MS Access first.

Fig 01

As shown in the Fig 01 open access.then you have to create your first data base.

Fig 02

As explained in the Fig 02 to create youe new Database give a desired name for your database.In this example name given for this example database is "FirstDataBase".And also you can select the destination to save your database.It can be Desktop or My document.In this example it is saved inside the my document.

 Fig 03

Once you clicked on the Create Button then you can get a Window like Fig 03.There you have to create a Table for your first database.Basically databases are consisted with 

• Tables
• Forms
• Quaries
• Reports

So the foundation of a database is Table.Normally when you hit on the Create button,it opens the database as shown in the Fig 03.there by default your new table is automatically loaded to design according to your requirements. If not please click on the Create tab then select Table to crate your first new table to save your data.

Fig 04

To create your new table please click on the table design button appear under Create Button as explained in Fig 04.

Fig 05

Once you hit on the table design button you can get a window like Fig 05.Then there you can see a Field calles "Field Name" there you should give column names of your table.So it is up to you.So here following table columns are used to create this expeditionary database (Sample Database)

• StudentID             = Number
• StudentName       = Text
• StudentAddress   = Number
• StudentAge          = Number
• StudentRegDate  = Date
• StudentEmail      = Text
• Remarks             = Memo (Select Data Type as Memo if you need to include more informations)

Table name is "Student".

Fig 06

It is required to enter your  table column names under Field Name and you have to select Data Type of your table columns by its nature,Then you have to assign PRIMARY KEY for your data table.Primary Key is the Unique data column in your table.For example if you use National Identitiy Card no for each student to recognize them,then National ID Card No can be assigned as a Primary Key of your table.Because it is unique to each and every student..Once you completed this then you need to save your table.Here,table is saved under the name called "Student"

Fig 07

Follow the Fig 07 to save you table.

Fig 08

Once you assigned a primary Key for a specific column,there is a special icon(KEY Symbol)apper on the left hand corner of your table design.This is shown from Fig 09 image.

Fig 09

Now table is completed.then it is required to enter data to this table.so this can be  directly enter to the table as it is.so to enter data to this table we have to exit for the table design mode.To do that click on the view ans select data sheet view.Then you are allowed to enter data to your new table.

See Fig 10

Fig 10

Once you clicked Datasheet View it is as Fig 11.I have enter sample data for your reference.

Fig 11

There we have assigned a Primary key for StudentID. So you can check that it is not allowed to users to enter duplicate data. Because we assign a primary key only for a unique data.Later this primary key column is very important to build up relationships among different tables.Please see Fig 12 screen shot.

Fig 12

How to search record using Combobox

How to search record using Combo box in MS ACCESS

This blog post is going to explain how to use combo box to find a record in Access Database.

First of all you have to create a Sample database as shown in the following image.There you have to create a access table as follows

Name of the sample table is StudentInfo_Main.

Based on the above table then your next step is to create a Form according to the below image.

We are going to add combo box to this Form.Then Click on  the Design Tab and Select Combo box Icon and put it on the Form interface as shown in the below image.

After you adding combo box to your form then you need to create a Query and you have to add this query as a Row Source of your combo box.Please see below image to create your query.

Once you create a query as above select the combo box and Right click and then go to the Properties,there select QrySearch as row source of the combo box.Under the property sheet select  format tab and give "2" for column count.

Select the combo box and go to the Event Tab in the Property Sheet.Then Select After update as the event procedure.

Next step is to adding small VBA script part for combo box values.To do this select combo box and right click on it and select "Buil Event" then Code Editor.Befor this do not forget to change the combo box name to cboTest.Then under the combo box AfterUpdate place the followin code.

Private Sub cboTest_AfterUpdate()

'Moves to Customer Name text box and

    'finds the record of whatever name is selected in the combo box

    DoCmd.ShowAllRecords

    Me!StudentName.SetFocus

    DoCmd.FindRecord Me!cboTest

    

    'Set value of combo box equal to an empty string

    Me!cboTest.Value = ""

End Sub

Once all the steps are completed as above then time is good to check our work.so final output is as below;

Download Sample File - Please Click on the link

Disable Design View of a MS Access Database using VBA Code

Disable Design View of a MS Access Database using VBA Code

Private Sub Form_Open(Cancel As Integer)
Me.ShortcutMenu = False
End Sub

Put above code in Code Editor under the Form Open Event.

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.