Online Data Entry Jobs
Many people use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a built-in data entry form that Excel can create automatically. This data form works with either a normal range of data or a range that has been example of a data form in use. Figure 12-13 Some users prefer to use Excel's built-in data form for data-entry tasks. Figure 12-13 Some users prefer to use Excel's built-in data form for data-entry tasks.
Next is one of the more complex UserForms that you'll encounter. I designed it as a replacement for Excel's Data Form, shown in Figure 15-18. You'll recall this is the dialog box that appears when you choose Data Form. Figure 15-18 Excel's Data Form. Figure 15-18 Excel's Data Form. Like Excel's Data Form, my Enhanced Data Form works with a list in a worksheet. But as you can see in Figure 15-19, it has a dramatically different appearance and offers several advantages. Figure 15-19 The author's Enhanced Data Form. Figure 15-19 The author's Enhanced Data Form.
The Enhanced Data Form features the following enhancements, as listed in Table 15-1. Table 15-1 COMPARING THE ENHANCED DATA FORM WITH THE EXCEL DATA FORM Enhanced Data Form Excel Data Form Limited to 32 fields. Enhanced Data Form Includes an Undo button for Data Entry, Insert Record, Delete Record, and New Record. Excel Data Form Excel's Data Form does not. Data Form is not written in VBA and cannot be customized. The Enhanced Data Form is a commercial product (sort of). It is available on the companion CD-ROM,and it can be used and distributed freely. Access to the complete VBA source is available for a modest fee.
Let's create a form for quick data entry for a call center doing a one-minute customer contact. The user's task is to call the customer and find out if they've heard of the Widget and whether they're interested in finding out more about the product. If the customer is interested, the user will mark that on the form as well so sales can follow up.
For some reason, the command to access the data form is not in the Excel 2007 Ribbon. In order to access the data form from Excel's user interface, you must add it to your Quick Access Toolbar (QAT) To use a data entry form, you must arrange your data so that Excel can recognize it as a table. Start by entering headings for the columns in the first row of your data entry range. Select any cell in the table and click the Form button on your QAT. Excel then displays a dialog box customized to your data. You can use the Tab key to move between the text boxes and supply information. If a cell contains a formula, the formula result appears as text (not as an edit box). In other words, you cannot modify formulas from the data entry form. When you complete the data form, click the New button. Excel enters the data into a row in the worksheet and clears the dialog box for the next row of data.
Next is one of the more complex UserForms that you'll encounter. I designed it as a replacement for Excel's built-in Data Form, which is shown in Figure 15-26. Figure 15-26 Excel's Data Form. Figure 15-26 Excel's Data Form. Note Displaying Excel's Data Form is not easy in Excel 2007. You need to use the Excel Options dialog box, click the Customization tab, and add the Form command from the Commands Not in the Ribbon group. Then, the Form command will appear on your Quick Access Toolbar. Like Excel's Data Form, my Enhanced Data Form works with a list in a worksheet. But as you can see in Figure 15-27, it has a dramatically different appearance and offers several advantages. Figure 15-27 My Enhanced Data Form. Figure 15-27 My Enhanced Data Form.
When the New Site Data Entry Screen window appears, enter the information as shown in Figure 26-3 below. New Site Data Entry Screen New Site Data Entry Screen The Asset Management data entry form also displays a number of icons with a question mark. Each icon is actually a command button with a Click event attached to it. When you click on the question mark button you will get a simple form with help information pertaining to the form's section or the data entry screen. For example, the following Click event procedure is executed upon clicking the question mark button in the Room Information section on the Asset Management data entry form Asset Management Data Entry Lookup Help Figure 26-4 By clicking the question mark button in each section of the data entry form, users can get detailed guidelines on how to work with the section.
Make appropriate selections on the Asset Management data entry form. 4. The Add New Asset Type Data Entry Screen window will appear as shown in Figure 26-5. 5. In the Add New Asset Type Data Entry Screen window, enter Zip Drive in the Asset Type text box and click the Save button. 7. Click the Add New Asset Type button in the Hardware Information section to return to the Add New Asset Type Data Entry Screen window. The left-hand side of the data entry screen (see Figure 26-5) displays a list box with the currently available assets. The user can double-click any item in the list and the DblClick event procedure shown below will determine whether the item can be deleted or not. 9. Click the Cancel button to exit the Add New Asset Type Data Entry Screen window.
As a database developer, you always want your forms to make data entry as quick and error-free as possible. Although you can do plenty along those lines in Access without using VBA, you can often make things just a bit better by writing a little VBA code.
Now activate Excel and run the procedure again by clicking the Data Entry button. i Finally, the dialog box is reset to make it ready for the next entry. Notice that clicking OK doesn't close the dialog box. To end data entry, click the Cancel button. Use the custom dialog box for data entry. Use the custom dialog box for data entry.
Prepare a data entry worksheet, as shown in Figure 11-2 4. Prepare a data entry worksheet, as shown in Figure 11-2 The cmdSave_Click procedure starts off with validating the user's input in the Last Name, First Name, and Salary text boxes. If the user entered correct data, VBA assigns to the variable Index the number of the first empty row on the active sheet for data entry purposes. The next statement enables the form's list box control.
VbOKOnly Or vbCritical, Data Entry Error Exit Sub End If vbOKOnly Or vbCritical, Data Entry Error Exit Sub End If vbOKOnly Or vbCritical, Data Entry Error Exit Sub End If MsgBox You must enter a number for the Sheet and + Row fields, and a letter for the Column field. , vbOKOnly Or vbCritical, Data Entry Error
As an example, imagine that you are creating an accounting system that will be used by both a data entry group and the financial analysis staff of a company. The data entry group will use the windows of the application to enter and change data in the system. The financial analysis group may require read-only access to the data. You may want to provide customized Help files to each group, describing their own needs within the application. If you limit yourself to setting the Help file of the application just at design time, you could only provide a single Help file. By setting the Help file at runtime, you are not constrained to a single file.
The constraints mentioned in the scenario are clearly data-entry and display rules, and therefore are best implemented as part of the user interface component. For more information, see the section titled Designing VB Data-Access Components for a Multitier Application.
Another nice finishing touch is to make sure the tab stops are in the desired order. You have no doubt used an application where you tried to tab through the fields to fill in information quickly and found yourself in a field that was out of order. To provide a better user experience and make data entry and navigation as easy as possible, you should make sure the tab stops have a logical order. Access makes it very easy to view and modify tab stops. To view and modify the current tab stops for a form, simply select View C Tab Order. A screen like the one shown in Figure 6.12 will be displayed where you can modify the order.
You may want to use a web page to collect data from a user and save it in a Microsoft Access form. The following hands-on example creates a simple data entry form that contains two fields. The purpose of this form is to allow users to enter new shippers into the Northwind database Shippers table. Data Entry Screen Now let's request this data entry page from the browser. Data Entry Screen Figure 28-28 When you request the ASP page prepared in Hands-On 28-13, you are presented with the data entry screen for the Northwind database Shippers table. Figure 28-28 When you request the ASP page prepared in Hands-On 28-13, you are presented with the data entry screen for the Northwind database Shippers table.
An Excel VBA application can be anything from a small task that performs a useful service to a large application that completely shields the user from Excel's basic interface perhaps using a combination of forms and automatic macros. Excel and VBA provide many features for developers to build applications that cater for different types of end users. The VBA programmer will need to consider who will use the applications and what sort of skills and experience they have. For example, a novice user - such as a data entry clerk - may find dialog boxes more helpful than a raw worksheet. This type of system could well be designed in a foolproof manner. On the other hand, managers may find an interface that automates the presentation of data -such as by using charts and graphs - more suitable. Thus, when designing an appropriate interface with an Excel VBA application, the following points should be considered
I Control choice VBA provides you with a good selection of useful tools in the Toolbox. If you find these choices limiting, you can always add new controls to the Toolbox. Always use the right control for the job. Not every data-entry task requires a TextBox. Some tasks require a
Here's how the OKButton_Click procedure works First, the procedure makes sure that the proper worksheet (Sheet1) is active. It then uses Excel's COUNTA function to determine the next blank cell in column A. Next, it transfers the text from the TextBox control to column A. It then uses a series of If statements to determine which OptionButton was selected and writes the appropriate text (Male, Female, or Unknown) to column B. Finally, the dialog box is reset to make it ready for the next entry. Notice that clicking OK doesn't close the dialog box. To end data entry (and unload the UserForm), click the Close button.
Let's say you are designing a data entry form for customers. Let's also say you want to allow the users to type in a customer number, and have the form immediately display the record for the customer with that number. There are several ways you can do this, not all of them satisfactory.
If you do need to import data into Access from an HTML or XML file (or just want to experiment with these options) you can use the Import HTML or XML Job Data form (frmlmportHTMLXMLData), which opens if you select the Import HTML or XML Data option on the main menu and click the button to its left (see Figure 10.11).
This limitation is not typically a problem for the average application, as a user is happy to view data in this manner. For example, opening the customer form, based on the Customers table or a query thereof, displays all customers in the data source. The customer can navigate to the desired customer, and perform any maintenance. Now, imagine that there is a data entry form for stock market ticker symbols and their daily closing prices. With only one form, comparing two or more stocks may be rather difficult. Performing a side-by-side comparison of the vital statistics of two or more stocks would be challenging when only one can be displayed at a time.
Combo boxes are a great way to help speed up the data entry processes for your user. In Access 2007, Allow Value List Edits and List Items Edit Form enable you to eliminate some of the code that we used to capture data-entry errors. Of course, you can still use the Not In List event to trap user input and provide special handling. The combo box is definitely worth spending some time learning about so that you can leverage its versatility and benefits.
One of the requirements for the data entry template is that it needs to be able to edit and add content in an XML document. To meet this requirement, you will have to provide Excel with an XML Schema Description (XSD). Although you could try to create your own XSD from scratch, there is enough complexity around developing XSD files to make that option a rather unappealing one. Instead, you can leverage Excel to create an XSD for you. Note that fake data is placed into the elements. This will allow Excel to identify the data type for each element when it infers a schema. You will also note the inclusion of an empty Employee element. This will ensure that the Employee element is tagged as a repeating element in Excel's schema. Why do you need this particular element to repeat The data entry template will need to accept multiple entries at the employee level a repeating employee element will satisfy this requirement. Be sure to save your file as an XML document.
Although it is convenient to let Excel handle the mapping of XML data to your spreadsheet, creating your own XML maps gives you a bit more flexibility and control over how your data is used. In particular, creating your own XML maps allows you to write back to your XML document, selectively map elements individually, and integrate data from multiple XML documents. In this section, you walk through a scenario where creating a custom XML map will help you build a simple data entry template for employee invoices. As you go through this example, keep in mind that this is one of many possible scenarios that benefit from a custom XML mapping.
With your newly created XSD in hand, you can create your XML-based data entry template. Start by opening a new Excel workbook and activating the XML Source task pane by selecting XML Source in the Developer tab. Next, click the XML Maps button to activate the XML Maps dialog box, shown here in Figure 12-6. At this point, you can drag the Employee parent element to your spreadsheet to create a data entry table. From here, you can add a little formatting to achieve a particular look and feel. Now here comes the impressive bit. Enter some data into the template, as demonstrated here in Figure 12-7.
This method displays the data form associated with the worksheet. Note that for the ShowDataForm method to work without generating an error, Excel must be able to determine that the current selection is part of a list. For information on the use of data forms, see the Excel 8 help topic Guidelines for creating a list on a worksheet or the Excel 9 help topic About data forms. Note that the procedure in which the ShowDataForm method is called will pause while the data form is displayed. When the data form is closed, the procedure will resume at the line following the call to ShowDataForm. (In other words, the data form is modal.) Figure 18-2 illustrates the data form for a worksheet. Figure 18-2. A data form
The txtPercent_Change procedure ensures that only values between 0 and 100 can be entered into the text box. The procedure uses the On Error Resume Next statement to ignore data entry errors. If the user enters a non-numeric value into the text box (or a number greater than 100), Visual Basic will reset the text box value to zero (0). Each time a spin button is pressed, the text box value is incremented or decremented by one.
When used in code, ActiveSheet.ShowDataForm displays dates and numbers according to U.S. formats but interprets them according to WRS. Hence, if you have a date of February 10, 2007, shown in the worksheet in the dd mm yyyy order of 10 02 2007, Excel will display it on the data form as 2 10 2007. If you change this to the 11th (2 11 2007), Excel will store November 2, 2007 in the sheet. Similarly, if you are using Norwegian number formats, a number of 1-decimal-234 will be displayed on the form as 1.234. Change that to read 1.235 and Excel stores 1235, one thousand times too big. Because this is a rarely used feature, our suggestion is to leave it buried in the Ribbon command well and write your own data-entry UserForm.
The best way to learn about events that a form, report, and or control can respond to is to develop an application that addresses a specific problem. The downloadable files for this book contain a form that is used for data entry and lookup purposes. The subject of the Asset Management form is keeping track of computer assets in numerous schools within a school district. We will use this form to further experiment with event programming. The form is divided into four easy-to-maintain sections as illustrated in Figure 26-1. Figure 26-1 Custom data entry form. Figure 26-1 Custom data entry form.
We typically start designing the database's switchboard by adding two command buttons One to launch the main data entry form (or forms as the case might be) and one to exit the database. The following steps will create a command button to launch the MySwitchboard form for data entry The next time you launch your database, the MySwitchboard form will automatically load for your users. You can have several switchboards within your application. For example, you might want to have a main switchboard that allows your users to explore various aspects of the application such as data entry, reporting, and application options (such as toolbar customizations or security options). When your users select the reporting button, you can launch a separate reporting switchboard with buttons for all of the reports in your application.
Completed employee data entry UserForm 3. Name the text boxes and buttons per Table 9-1. Figure 9-23. Completed employee data entry UserForm 3. Name the text boxes and buttons per Table 9-1. Put the data from the data entry form on the worksheet Close the data entry form Dim rng As Excel.Range With Globals.ThisAddIn.Application rng .Range( A1 ) rng.Value HR Data Entry System rng.Font.Bold True rng.Font.Size 16 rng .Range( A2 ) 2. Click the New Employee button on the HR task pane to display the data entry form. HR Data Entry
One final note to bring this point home is that maybe not every tab is used during every data entry session. There may be one set of users who only use one subset of the tabs, while another set of users uses a different subset. Also, in the example of surveys, there may be some tabs that are skipped if a question is answered a certain way. 'until all data entry is correct on Tab1
On the other hand, there may be situations where it is not feasible or cost-effective to stop data entry and request that the Admin enter a new value. Instead, the combo box is used to increase data entry speed, but only to limit excessive similar entries from being added. For example, job titles are sometimes very common, but other times outrageously unique. Receptionist, Administrative Assistant, and Project Manager are all standard values that are repeatable values, but Senior Herbal Fragrance Arranger may be a less common choice. In the case of this particular procedure, the ParamArray is used to assist in the capture of even more data than just the one value that was entered in the combo box. Implementing this gives the ability to enter a second, third, or nth piece of information pertaining to the new data value. For example, suppose a new department code is added, you code-prompt the user for the new department name as well. If you capture only the code, then it would require...
Macros enable you to automate just about any part of your spreadsheet. For example, you can automate data entry by creating a macro that enters headings in a worksheet or replaces column titles with new labels. Macros also enable you to check for duplicate entries in a selected area of your worksheet. With a macro, you can quickly apply formatting to several
The data entry form that you are designing will have some very strict field-level validation to ensure that accounting and other business codes are correct as you tab from field to field. Which text box event should you use to enforce this validation 48. You decide to update your Visual Basic 5 application to take advantage of the new validation features found in Visual Basic 6. Editing your main data entry form, you place code in all of the appropriate validation events. You also change the Cancel button's Cancel property to True, but you leave the CausesValidation property to its default value.
Also, notice that the totals row at the foot of the data entry area is heavily bordered to make it stand out. An intermediate user may also benefit from the use of application specific balloon Help. A complete Excel novice user might b e b est served by designing customised dialog boxes for data entry, or forms with other window controls as a front end, possibly rendering the worksheet invisible to the user. For example, in Figure 10.16, a dialog box is used to input data for adding a new representative into worksheet cells. The design of this system was looked at earlier in this chapter. For a novice user with no knowledge of spreadsheets this interface style would be better than using a raw worksheet to add the data. Figure 10.16 A data entry form suitable for a novice user
An ASP script can contain a form that is used for collecting user input. Assume that you need to gather information about patients visiting an urgent care center in your town. It's been requested that your data entry display screen has a web interface. Normally when you collect data on a web page, the information is saved into some sort of a database, like SQL Server or Access. However, your client particularly requested that the data from the input fields be saved directly to an Excel file. Let's see how you can provide this interface. Patient Data Entry Screen ASP form with data validation during the data entry ASP form with data validation during the data entry
There are two approaches to error handling. The first is to let the error occur, then do something about it the second is to prevent the error from occurring. Each has its own merits. Supporters of the first approach argue that most errors occur very infrequently. For example, users of a computer system are by and large well trained and make few data entry errors. Therefore, the extra processing to prevali-date such things as numeric values is a waste of processing time and power it's more efficient to execute an error-handling routine occasionally than to invoke a validation routine for every entry. On the other hand, supporters of the second approach argue that it's not good practice to allow errors to be generated and that if there are techniques available that prevent an error from occurring, they should be used. A remote server that's offline, for example, could take several minutes to generate an error due to network timeouts, whereas a correctly written validation function...
Spreadsheets in this category are often candidates for applications, especially if end users need to perform moderately sophisticated operations. However, Excel's built-in Data Form dialog box and its AutoFilter feature make working with databases so easy that even beginning users can master simple database operations quickly.
But consider for a moment the case where the user is currently on a control such as a TextBox with incorrect data (a misspelled password, for instance) and has clicked on a Help button in a toolbar or on a Cancel button to abort data entry on this form. You should set CausesValidation to False on controls where the user should always be able to set focus, regardless of whether there is a problem with data validation on some other control. For example a Cancel button should probably have its CausesValidation property set to False, because once the user decides to cancel a data entry session, the validity of individual data fields does not matter.
Create a new form based on the Customers table. Add all the fields from the Customers table and save the new form as Customers Data Entry. 2. In the Design view of the Customers Data Entry form, choose Edit Select Form. 7. Open the Customers Data Entry form in Form view mode. 9. Close the Customers Data Entry form.
When you write code behind a form, there are certain things to consider, such as the flow of the application, searching for data, data entry, and validation. While reports in Access 2007 are still read-only, they have been greatly enhanced to allow for some of the form-type scenarios that you might have created in the past such as search, sorting, and filtering.
You might need a form that asks about the machine environment or how to interact with the data. Forms can also provide formatted output in ways that a message box can't. You can include fields on the form that format data and present it in a useful manner. You use forms constantly in Windows, in the form of dialog boxes and data entry windows.
The Click event occurs when the user clicks a control with the left mouse button or presses an Enter key when a command button placed on a form has its Default property set to Yes. The Click event applies only to forms, form sections, and controls on a form. The Asset Management data entry form contains several command buttons that allow the user to add new values to appropriate combo box selections. For example, when the user clicks a button labeled Add New School, the following Click event procedure is triggered The above event procedure opens the New Site Data Entry Screen dialog (Figure 26-2) where the user can enter new school information. Figure 26-2 This data entry form is used for adding new schools to the database. Figure 26-2 This data entry form is used for adding new schools to the database. When the user clicks the Save button on the New Site Data Entry Screen, the Click event procedure attached to this button ensures that Notice that the New Site Data Entry Screen form...
Suppose that you have a workbook that other people use for data entry. Any values entered must be greater than 1,000. You can write a simple macro that Excel executes whenever someone enters data into a cell. (Entering data is an event a WorksheetChange even.) If the user enters a value less than 1,000, the macro displays a dialog box reprimanding the user. The DataOData ToolsOData Validation command in Excel provides another way to perform this type of data-entry checking without even using VBA. This is just one example of how you can take advantage of an event. Keep reading for some more examples.
Speech recognition continues to improve. It can be used for both commands and data entry. And, since the Access Forms and Controls behave more like standard Windows dialog boxes, they are more familiar and intuitive for users to master. Additionally there are seemingly hundreds of keyboard shortcuts. Access not only recognizes the standard shortcuts of other Office menu commands, but there are fairly intuitive keyboard shortcuts for menu and toolbar items. Plus, as was mentioned in the error checking, developers can confidently create keyboard shortcuts for any (and all) command buttons on their forms.
For example, let us suppose that Fred Nurk belongs to a group called Data Entry, and the Data Entry group has dbSecInsertData and dbSecReplaceData permissions to Table1, but that the administrator has explicitly granted him dbSecDeleteData permissions, but accidentally revoked his individual dbReplaceData permissions to the same table. Fred's total permissions are the sum of all permissions dbSecInsertData + dbSecDeleteData.
The BeforeUpdate events can be used to validate user input, and as such, they are used most often on the form or record rather than the field. During data entry, users are typically allowed to fill in all the information for one record before they are prompted to resolve erroneous or missing. But that's not always the case, so we'll explain both approaches. The next example uses the On Click event of a command button. First you need a simple form for data entry. On the Property Sheet, set the form's properties as shown in the following table. Allow Data-Entry
As in prior versions of Access, if the Limit To List property is set to No, the user is allowed to type whatever he wants as many times as he wants. The combo box only helps the user select previously defined values without restricting him to only those values, and it allows users to enter the same value multiple times and with myriad variations. When Limit To List is set to Yes, the user can select only the values in the list. However, if the user enters a value that isn't in the list, it can trigger a response depending on whether you've captured bad data entry using the On Not In List event and or based on the settings for the new Allow Value List Edits property along with the List Items Edit Form property. All this is by way of saying you should be aware that the On Not In List event occurs before the settings of the new properties are even considered.
With .Controls.Add(Type msoControlButton) .OnAction ShowDataForm .Faceld 264 .Caption Data Form .TooltipText Show Data Form End With SShow Data Form The first table entry shown in Figure 15-14 and Figure 15-15 adds a new popup menu to the Worksheet menu bar called Custom. The second entry adds a menu item called Show Data Form to the Custom menu, as shown in Figure 15-16. 1 SShow Data Form 1 SShow Data Form 211 Dsc 264 Show Data Form
Office XP comes with a speech recognition feature that makes it possible to read text on demand. The text can be read automatically on data entry by clicking the Speak on Enter button on the Text to Speech toolbar (choose Tools Speech). You can also control the speech feature via the Speech object from a VBA procedure. This object has the Speak method that causes the text to be spoken by Excel. To cause the active cell to be spoken on entry or when it is finished being edited, set the SpeakCellOnEnter property of the Speech object to True
Finally, you looked at the Excel 2007 UserForm object and its Toolbox. UserForms allow you to create data entry forms and data display screens from within the Excel VBE. The Toolbox contains many common controls that you can use on the UserForm, including text boxes, combo boxes, and command buttons. It's similar to the Toolbox in Microsoft Access.
Dialog boxes in COM Add-ins follow the same guidelines as dialog boxes in any other Visual Basic project, with one precaution do not attempt to show a modal dialog box directly from your COM Add-in. Outlook (and for that matter, any other Office application) cannot display a modal dialog box when that dialog box is displayed directly from a COM Add-in or an ActiveX DLL component. Figure 14-11 illustrates a Visual Basic About dialog box displayed from a COM Add-in. By displaying Visual Basic modeless dialog boxes, you can add a wealth of functionality to your COM Add-in application. If you are connected to your network, you can display information from corporate databases, provide data entry forms, or enable users to create settings that might otherwise not be available on a custom Tools Options property page. An enormous number of third-party ActiveX custom controls are available for your dialog boxes, in addition to the intrinsic and extrinsic ActiveX controls that ship with Visual...
Create a new function named Save that returns a success flag. This function needs to know the row number of the next available row for data entry it needs to know what sheet that row is on and if there are no errors, it must return a Boolean True value. Here is the code for the Save method
Online Data Entry Jobs Official Download Page
The legit version of Online Data Entry Jobs is not distributed through other stores. An email with the special link to download the ebook will be sent to you if you ordered this version.