1 / 8

Database expressions Creating automation in forms

Database expressions Creating automation in forms. Generating an automatic ID. Expressions. Dmax(“Field name”,”TABLE NAME”)+1 # finds the highest value in field from table addss 1 to it Val([FIELD NAME]) # switches datatype to a value Right([FIELD NAME],2)

xyla-salinas
Download Presentation

Database expressions Creating automation in forms

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database expressionsCreating automation in forms Generating an automatic ID

  2. Expressions Dmax(“Field name”,”TABLE NAME”)+1 # finds the highest value in field from table addss 1 to it Val([FIELD NAME]) # switches datatype to a value Right([FIELD NAME],2) # gets 2 characters from the right hand side of the field =DMax("Val(Right([StudentID],4))","TBL_Students")+1

  3. Ucase() # switch to uppercase Lcase() # switch to lowercase &””& # concatenation Left([StudentForename],1)&””&Left([StudentForename],1) # returns first letter first name followed by first letter surname

  4. Generating next ID number • Create a form based on the table that you are creating a new entry for • Any fields that make up part of the ID need to be hidden from the user and an unbound field created in its place that the user can enter the data into • To make the bound forename and surname fields invisible set the visible property to ‘no’, you will also need to do this for the label for these fields • You will also need to make an unbound field that just generates the next number for IDs – this can be hidden from the user • The three unbound fields required for this example are highlighted in red • Make sure all fields are named appropriately so you can refer to them later

  5. Generating next ID number pt 2 • Using the field you created for finding the next ID number create the default value above • DON’T FORGET TO USE YOUR OWN TABLE AND FIELD NAMES!

  6. Generating next ID number pt 3 • Set both of your bound first name and surname fields to get their value from the unbound version • =[UNBOUND FIELD NAME] will automatically fill the field based on what the user enters into the unbound field. • This allows this information to be saved into the table • Do the same for both forename and surname fields

  7. Generating next ID number pt 4 • Set up the default value of the ID field as per the scenario (you may need to work this out by looking at the data) • Concatenate the various parts of fields together using &””& • NOTE: the fields referred to will always be the UNBOUND fields

  8. Generating next ID number pt 5 • Create a macro on the on lost focus property of what ever is the last field the user needs to fill in in order to have all the information necessary for the computer to generate an ID (DO NOT PUT IT ON THE FIRST FIELD, IT WILL NOT WORK!) • The macro will simply requery the form and update the fields with the information as per the expressions used in the default values • You should now have generated an ID • Make sure you read the scenario carefully and study the data so you know what makes up your ID – it will not necessarily be the same as this scenario

More Related