You now need to tell the append query what it needs to do. It will be taking the values from each of the text boxes on the Registration form and adding them into the fields of the Members table. So for example:
- Textbox called “FirstName” on the form will be appending to the “First Name” field of the database table
- Textbox called “DOB” on the form will be appending to the “DOB” field of the database table
AND SO ON
To make the process easier and help stop errors, we are going to use the Expression Builder
Field section in Query
- Click on the Forms box in the left hand box.
- This will display all of the forms in the database.
- Click on the Registration form
- A list of all of the text boxes on the form will appear here. If you had not named the text boxes correctly when you created the form you would have a problem e.g. what does Label0 mean?
- Double click on the UserName option in the middle column
- This will add a function to the top box.
- This function is telling the query where to pull information from.
- In this case it is: frmRegistration > UserName
- The same function from above has now been added to the first column of the append query.
- Repeat this for all of the other fields
- In the Append To row, select UserName from the drop down box. This will link the UserName text box on the form to the UserName field in the Members table
- Click on create macro
- Step one: If the FirstName, LastName or DOB text boxes are empty, we want a message box to pop up to tell the user to fix it
- Select If from the drop down menu
- Click the Builder button to start the Expression Builder
- The command to check if something is empty is called IsNull
- Click on the Functions option on the left hand side and then Built-In Functions
- Change the middle column to Inspection then double click on IsNull to add it to the top box
- You now need to replace <<expression>> with the name of the text box to be checked.
- Click on the Forms button in the left hand box. - Registration form -“member”
- Double click on the FirstName option in the middle box
Go to the end of the function type “Or” then repeat the process above to check the LastName and DOB fields as well
Creating a macro
- If the text boxes are empty, a message box should appear click the + icon to Add an action
- Click Add Else If to do another check
Use the Expression Builder to add the Age text box on the form and then add the condition < 15 at the end. Then add another message box to explain the error
- If this check is done and all of the fields are completed, then the macro needs to run the append query you created
- You need an Else option that runs if all the checks are successful.
- Choose the OpenQuery option from the drop down box.
- Choose the query name but leave the View and Data Mode as they are
- Also add a message box to tell the user the member is saved
The final thing to do is clear the data from the form after it has been used. The quickest way to do this is to close the form and open it straight back up again