MVP: Office Development (HD) - Channel 9

RelationshipsA database relationship defines how two tables connect to each other. The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. As you build tables in Microsoft Access, or link to tables that are in SQL Server, Oracle or other big data -- or Excel range, text file, or wherever your data is (after all, it is Access!), it is a good idea to put all the tables on the Relationships Diagram, even if they don't relate to anything. Stretch* or shrink** field lists so everything shows -- and rearrange tables so the "1" side of the relationship is on the left (where values originate), and the "many" side on the right ... so data flows from left-to-right as data must be created.* stretch field lists wider and taller to show full names, and all the names. Drag another copy of the table to the diagram if it has numerous fields -- and adjust scrollbars to see everything. ** shrink: do you have extra space to the right or below in field lists?  long names? Maybe put the descriptive stuff in the Description and keep names short? Be sure to show the full table name too. No need to waste real estate on the screen with unnecessary space, so close the gaps (on forms and reports too! gaps are wasted space unless you want them, of course) and tighten things up.You can put queries on the diagram too ... perhaps just temporarily, to document them ~ Document the DiagramOnce you see everything on the Relationships Diagram, take a screen shot (or or 2 or 3, ...). If you don't have a clipping program like Snagit, you can press the Print Screen key (maybe Fn + PrintScreen) to copy an image of the screen to the Windows Clipboard. Then paste to Word, PowerPoint, or other program, add explanations and annotations if desired, and Save.Create RelationshipTo create a relationship in Access, first make sure the data type (and size) of the fields you want to join are the same.Data Type and SizeIt is common for numeric key fields to be Long Integer. AutoNumber is a special form of Long Integer that automatically gets numeric values as records are created.In this example, you see that PhoneTypeID is an AutoNumber in the PhoneTypes table, where it is actually a Long Integer, and is also the Primary Key. In the related table, PhoneTypeID is a Long Integer Foreign Key with no Default Value.If you are joining on text, make sure the field size matches and is not ridiculously big. Do not match on floating point numbers such as Single or Double; they are not accurate for exact comparisons.StepsOn the Relationships Diagram: 1. click on the Primary Key in the main table (or a field that has a Unique index), 2. drag to the Foreign Key in the related table, 3. and let go.4. In the dialog box that pops up, check: Enforce Referential IntegrityError creating relationship?If there is bad data, a relationship with referential integrity cannot be created. Fix the bad data and try again.  If you do not know how to fix it, try the Query Wizard (Create ribbon) and choose "Find Unmatched". The video illustrates deleting the record with bad data. That was because, in this case, a phone number with no contact has no relevance.  Alternately, you can keep the data and delete the bad foreign key value so referential integrity can be enforced on the relationship.Linked TablesIf a table is linked, you can show an additional relationship on the diagram, but you cannot create a real relationship (one with RI) that is not already there. If relationships are not defined, you can drag lines without enforcing referential integrity to document what is supposed to match.SummaryDefine relationships and arrange the diagram as you build your tables. It is much easier to do this as you go than it is to back-track ... and creating the relationships will often surface other issues that need to be addressed. Also think about Indexes. This video doesn't get into those, but they deserve attention too.Enforce Referential Integrity, unless you have a specific reason not to. Please Like, Comment, and Share ~ thank you have an awesome day,crystal (strive4peace) ~ connect to me, let's build it together 

Do you ever modify something, and then want to save with a new name? ... and then wished you could still see what you are doing when you think of a new name? Whether you are changinga query (or other object) in Accessa document in Worda workbook in Excela presentation in PowerPoint... or other ... same stepsWhat can you do? The Quick Access Toolbar (QAT) The Quick Access Toolbar, or QAT, is a handy place to put your favorite commands.This example uses renaming a query in Access to demonstrate -- but it doesn't matter what you are doing.  The Quick Access Toolbar (QAT) works the same for the Microsoft Office products ... and Save As is a common command.By adding "Save As" icon to the Quick Access Toolbar, you can still see what you are working on when the dialog box to specify a Name pops up.Steps:1. click on down arrow at end of QAT to Customize2. Choose "More Commands..." from the drop-down menu3. choose to show 'All Commands', instead of just the popular ones4. click on the last command in the list of QAT commands on the rightor whatever command you want to add the command below5. double-click <Separator> in the left list to add it to the right list below what is selected6. in the left list, double-click Save As to add it below the separator that is selected7. click OK to close the Customize the Quick Access Toolbar dialog box8. Now there is a convenient icon on the Quick Access Toolbar to use for saving with a new name nice! Please Like, Comment, and Share with your friends ~ have an awesome day,crystal

See how to to change the SQL for the RowSource of a list box using VBA (Visual Basic for Applications). Raise the bar on database applications that you build with Microsoft Access.With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written VBA code or looked at an SQL statement before, no problem! ... give it a chance. Unleash the power of Access.This technique of basing the Row Source of one control on the value in another is called "cascading", and you will probably hear that term more in reference to combo boxes.A list box has many similarities to a combo box such as Row Source, Column Count, Column Widths, and Column Heads. The Width of a list in a combo box is List Width; in a list box, it is the same as the control Width.As the customer changes, VBA runs to add criteria to the SQL statement for the list box. SQL is what a query stores to know what data to get, where it comes from, and how to sort. SQL is Structured Query Language. Don't let the acronym intimidate you.SQL statementAn SQL statement is simply a standardized way to get information from database tables. It specifies what to show (Select), and where data comes from (From). Optional clauses include criteria (Where), and how to sort (Order By). The basic syntax for an SQL statement is:SELECT fieldlistFROM tablenameWHERE criteriaORDER BY fieldlist;Row SourceTo get an SQL statement into the Row Source of a combo box or list box, you can: (1) Make a query to show what you want, switch to SQL view, copy the SQL statement, paste into the Row Source or (2) click in a control's Row Source property, then on the Builder button (...), specify what you want in the query builder, then save, and close the builder, or (3) write the SQL yourself.To make the demonstrated code work, copy the resulting SQL statement from the Row Source to the control's Tag property (bottom of Other tab on property sheet). Tag is not used by Access; it is a place where you can put whatever you want. In this case, it will store the SQL statement with no Where clause for each respective control. The code will read the basic SQL from the control's Tag property and then modify it to add criteria (Where clause).When the focus moves to a record, it becomes the current record, and the form Current event happens. On the property sheet, this is called On Current and can be set to a macro name, a function name, or [Event Procedure].The shortcut key to launch the Builder is Ctrl-F2.VBA codeLook at the code behind the form (class module). See how to call one procedure from another (so code can be encapsulated). Use the the control Tag (for whatever you want) to get the SQL statement without criteria, then add a Where clause (criteria), replace the Row Source with the new SQL statement, and build the list to show the latest data (requery).Learn line-by-line what is happening in the VBA code -- and see how to efficiently modify an SQL statement to limit the rows it returns. This knowledge will propel you to a new level.~~~related videos:Mainform + Subforms for data from mutiple tables with a Microsoft Access User Interface channel9.msdn.com/Blogs/MVP-Office-Dev/Mainform--Subforms-for-data-from-mutiple-tables-with-a-Microsoft-Access-User-InterfaceSubform to show Calculations in Microsoft Access channel9.msdn.com/Blogs/MVP-Office-Dev/Mainform--Subforms-for-data-from-mutiple-tables-with-a-Microsoft-Access-User-Interfacehave an awesome day, crystalAccess Basics (free book) www.AccessMVP.com/strive4peace Free 100-page book that covers essentials in AccessLearn VBA (free book) www.AccessMVP.com/strive4peace/VBA.htmconnect to me, let's build it together www.MsAccessGurus.com

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data.A Microsoft Access subform is used to synchronize the display of calculations to relevant data in the mainform.A subform control is a container, like a bucket, for a form or report. Its properties specify: what it contains (Source Object) , how it is linked (Link Master Fields, Link Child Fields), what it is called (Name), how big it is (Width, Height), where it is (Top, Left), what it looks like (Border Color, Border Style, Special Effect), whether or not it shows (Visible), if the user can modify values (Locked, Enabled), and so on.As you navigate from record to record in the mainform, data in the subform automatically changes. LinkMasterFields and LinkChildFields are used to synchronize the forms, without any more effort on your part!On the mainform, LinkMasterFields is a combobox that stores CustomerID but shows the customer name and more. What is displayed in the combobox is influenced by Column Count, ColumnWidths, and ListWidth.The form inside the subform control is a regular form designed to be used as a subform; and in this case, to display information only, so things like RecordSelectors and ScrollBars are turned off. The RecordSource for the subform (where it gets its data from) is a query that has 2 queries below it doing more calculations. See how queries are stacked to get statistics from multiple tables that are not directly related into one place.Take a deeper look at the SQL statements that Access stores when you create queries. Get an understanding of the different query views. Switch between SQL View, Design View, and Datasheet View. See how aliases are used for tablenames and calculated field names; and how a Left Join displays graphically with an arrow.And finally, see the magic of Access happen ~_________________________________________________________Download the database example:http://www.msAccessGurus.com/MainformSubformsPayments.htmVideo for mainform overview:Mainform + Subforms https://channel9.msdn.com/Blogs/MVP-Office-Dev/Mainform--Subforms-for-data-from-mutiple-tables-with-a-Microsoft-Access-User-Interface~~~Please Like, Comment, and Share with your friends ~have an awesome day,crystal

How finding and entering data into multiple tables can be smooth and graceful using a mainform + subforms. The UI (User Interface) built with Microsoft Access responds with information and animation when users hover and click -- and calculates dynamic information for ready display. Behind the scenes, there is a solid structure with referential integrity.Download Access database (ACCDB format): msaccessgurus.com/MainformSubformsPayments.htm#DownloadDesigned for efficiency and flexibility.  A mainform is used to display and edit data in a main table, with subforms for displaying and editing data in related tables. Lots of whistles and bells to spark creativity. Respond to different screen sizes by anchoring subforms and controls so they stretch and shrink, use unbound combo boxes and list boxes to quickly filter and find records, allocate checks and charges over open orders with a custom calculator, show and update dynamic information, and streamline data entry.Hope you like this and get ideas. Please like, comment, and share with your friends. Thank you.~ have an awesome day ~crystal

Go from Access Query to shareable Calendar Report nearly instantly!Have you created a query in Microsoft Access with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.Here is an example of the output:To use this code, import the module into your working database (and then compile and save, of course).Download free tool from: msAccessGurus.com/HtmlCalendarReport.htmOr from Channel9: click the Download tab and then choose Source Code. The database is inside the zip file, and better named.There is a macro in the sample database that asks for a query name and then, presto, calendar! You may also wish to import the macros ... there is another one that opens the folder for the calendar files.Using vba, you can easily create and open a calendar with: Application.FollowHyperlink Create_HtmlCalendar(sQueryName)will open a web page with the calendar created from the query name stored in sQueryName.The Create_HtmlCalendar function creates the calendar as an HTML file (web page) and returns the path and filename when done.You have a calendar file you can email and share with others.You do not need to understand each line of this code to use this feature in YOUR databases ... only how to call it to make it give you what you want ... but if you want to take the time to understand, that is great.Ask questions below (remember comments are ONLY open for 30 days -- so ask now) if you need help -- and remember, if you don't know, chances are that someone else doesn't either. I want to give you a good tool -- and share my love of Access.A Calendar Report for one month will be made as an HTML file (web page) -- so everyone with a browser, which is just about everyone! can see it too!It just doesn't get any easier than this!Please Like, Comment, and Share with your friends. Thank you.Through sharing, we will all get better.respectfully,crystal ~ have an awesome day ~

Show triangles and circles in Access queries using Unicode characters. Graphically indicate if values have increased, decreased, or stayed the same. The data is MSFT closing price from Nasdaq's historical stock pricesHow can you compare a value with the previous value in another record and calculate the difference in a query? And then show triangles and circles indicating up, down, or same ... and stagger them for greater grasp? This lesson also covers how to handle non-American date formats. Performance slow? Convert domain aggregate function DMax to a subquery. ~~~this video shows another example of using Unicode in a query:Show Bar Graphs in Access Query using Unicode charactershttps://channel9.msdn.com/Blogs/MVP-Office-Dev/Show-Bar-Graphs-in-Access-Query-using-Unicode-characters~~~Unicode:Note: Some fonts have more, and better, Unicode representations than others. For Windows standard built-in fonts, Arial Unicode MS and Lucida Sans Unicode have fair coverage.  Common fonts such as Arial, times New Roman, and Calibri can be okay too.If you cannot show the Unicode characters used to demonstrate, try these instead:filled circle  --> 9679down-pointing triangle --> 9660up-pointing triangle --> 9650 ~~~please Subscribe, Like, Comment, and Share ~ thank you 

Do you want to know how to programmatically change graphs and charts in Access? This 19-minute video shows how to use VBA to drive charts from a menu form that collects criteria, change data for the graph, set the chart title, x-axis title, and modify scales and formats. To download the database example, go to www.msAccessGurus.com then Free Tools then Graphing in Access.The menu form is called f_Graph_MENU. It has a button ("Chart") that runs VBA code and opens the graph. The time frame can be changed from Days to Weeks to Months. Appearance is changed by setting format. How data looks and what data is being displayed is based on criteria such as a possible start date or date range. On the right are other properties that are read when the graph is rendered such as Chart Title, format for X and Y axes, X-Axis Title, and Minimum and Maximum Scale for the Y-Axis.The f_Graph form has what is now called the Microsoft Graph Chart control, and has code behind the form to change where the data comes from and how it looks.The f_PopupCalendar form is for collecting dates and generally triggered by double-clicking a control with a date. If a date OpenArg is sent, the starting calendar date can be specified if the control has no value.The sample tables are called t_GraphData and t_GraphTopics.The qGraph query is the RowSource for the Microsoft Graph Chart control. By defining a query and then modifying the SQL for the query before the chart is opened, you can get exactly what you want.you also may be interested inHow To Make a Graph with Microsoft Accesshttps://channel9.msdn.com/Blogs/MVP-Office-Dev/How-To-Make-a-Graph-with-Microsoft-Access if you want to show graphs and symbols in queries, watch:Show Bar Graphs! in Access Query using Unicode characters https://channel9.msdn.com/Blogs/MVP-Office-Dev/Show-Bar-Graphs-in-Access-Query-using-Unicode-charactersandTriangles! and Circles! in a Microsoft Access Query -- Get Previous Record too https://channel9.msdn.com/Blogs/MVP-Office-Dev/Triangles-and-Circles-in-a-Microsoft-Access-Query-Get-Previous-Record-too~~~please Subscribe, Like, Comment, and Share ~ thank you 

Do you want to know how to make a graph with Microsoft Access? The easiest way is to first create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formatting, chart type, titles and legend.The trick to getting the chart to show exactly what you want is to set the RowSource for the chart object after the wizard is done.To format the graph, double-click the chart object in the form design -- then right-click on what you want to change to get a context-sensitive menu.Tp programmatically manipulate a chart, watch:Change Graphs and Charts in Microsoft Access using VBA https://channel9.msdn.com/Blogs/MVP-Office-Dev/Change-Graphs-and-Charts-in-Microsoft-Access-with-VBAand it you want to make bar charts for each record of a query (yes, you can do this!), watch:Show Bar Graphs in Access Query using Unicode characters https://channel9.msdn.com/Blogs/MVP-Office-Dev/Show-Bar-Graphs-in-Access-Query-using-Unicode-charactersto add Triangles and Circles! watch:https://channel9.msdn.com/Blogs/MVP-Office-Dev/Triangles-and-Circles-in-a-Microsoft-Access-Query-Get-Previous-Record-too~~~please Subscribe, Like, Comment, and Share thank you 

Do you ever wonder how to prompt for a file or folder from within an Access database?I'm going to show you how to select a file or folder using the FileDialog object that comes with Microsoft Office. This is available from Microsoft Access of course.  I also have some VBA code here you can download to help you do this.~~ Links ~~Select a File or Folder using the FileDialog Objecthttp://bytes.com/topic/access/insights/916710-select-file-folder-using-filedialog-objectDownload: FileDialog.Ziphttp://bytes.com/attachments/attachment/5382d1314897792/filedialog.zipBytes free forum to get Access helphttp://Bytes.com/Access and Excel Consultinghttp://www.AccessConsultantUK.co.uk/Adrian, AKA NeoPahttp://Bytes.com/profile/51203/NeoPa/

Did you know that you can show a bar graph in an Access query? It is easy using a simple formula to show Unicode block characters. Use the built-in functions String  and ChrW (variation of Chr) to display a graph by repeating a Unicode character a calculated (or specified) number of times -- and this is all text!Graphs appear on every record in the color you want ... give life to numbers.  Hopes this enables you ideas to visualize your data in new ways ~this video shows another example of using Unicode in a query:Triangles! and Circles! in a Microsoft Access Query -- Get Previous Record toohttps://channel9.msdn.com/Blogs/MVP-Office-Dev/Triangles-and-Circles-in-a-Microsoft-Access-Query-Get-Previous-Record-tooNote: Some fonts have more, and better, Unicode representations than others. For Windows standard built-in fonts, Arial Unicode MS and Lucida Sans Unicode have fair coverage.  Common fonts such as Arial, times New Roman, and Calibri can be okay too.please Subscribe, Like, Comment, and Share ~ thank you  

Azure Bot Service enables rapid intelligent bot development powered by Microsoft Bot Framework, and run in a server-less environment. This recording will give you a heads up how you could enable bot features within your SharePoint Framework (SPFx) app and you could advance your solution with implementing your own logic with client side object model (CSOM) or with consuming REST endpoints. Clone "SP Rider" sample in the GitHub to make your learning experience more interesting. 

LUIS (Language Understanding Intelligence Service) is a great way for Bot Developers to add Artificial Intelligence (AI) into their bots.This introduction to LUIS focuses on how to get something up and running quickly that's usable, rather than going into every feature that LUIS has. It can be used by developers of Skype for Business bots, Bot Framework bots, even developers building solutions on other bot platforms.

Bots are the new Apps! Find out how to build your first bot using the Microsoft Bot Framework with me. It's just me and my laptop, no slides, no polish - just me showing you how to build your first bot and publish it for use in Skype, Teams, Slack etc etc.I'm a Microsoft Office Servers & Services MVP but you don't need to know anything special about communications technologies to get started building your new creation. I'll go through the templates and tools you can use to jump-start your development.

La Comunidad de Office 365 y el Grupo de Usuarios de SharePoint de España (SUGES) en colaboración con ENCAMINA organizan un nuevo WebCast en el que hablaremos sobre el nuevo Framework que Microsoft ha publicado para nuestro servidor favorito, el SharePoint Framework. Veremos, desde varios puntos de vistas, en que consiste este Framework, como usarlo, que API nos ofrece y como integrarlo dentro de nuestro modelo de desarrollo.Como bien nos explica Adrian en la revista CompartiMOSS (http://www.compartimoss.com/revistas/numero-28/que-es-el-sharepoint-framework) como desarrolladores de SharePoint llevamos unos años sin novedades y hemos puesto todas las expectativas en este nuevo modelo de desarrollo de WebParts para SharePoint. Adrián Díaz Cervera. MVP desde el año 2014 en la categoría de Office Servers and Services. Ingeniero de Informática por la Universidad Politécnica de Valencia. Miembro activo de varias comunidades técnicas de Microsoft como la Comunidad Office 365, SUGES o Valencia.NET. Speaker en los principales eventos sobre tecnología Microsoft. Profesionalmente lleva desarrollando con tecnologías Microsoft más de 10 años y actualmente está centrado en el desarrollo sobre SharePoint, Office y Azure. Actualmente trabaja en ENCAMINA como Responsable de Arquitectura Software.  Además, es colaborador habitual de la revista digital de habla hispana CompartiMOSS dedicada a SharePoint/Office 365.Santiago Porras Rodriguez. Experto en desarrollo de experiencias de usuario en soluciones de cualquier ámbito y tecnología. Apasionado de las nuevas tecnologías y actualmente centrado en SharePoint, Office 365, Azure, ASP.NET MVC, Windows 10, Windows Phone y HTML5. Además, compagina su vida laboral con la difusión de conocimientos y ayuda tecnológica mediante la participación en TenerifeDev, grupo de usuarios de .NET de Tenerife, como moderador de los foros de SharePoint en MSDN y TechNet y, escribiendo artículos tanto en su blog personal en Geeks, como en la revista digital CompartiMOSS. Mobile & Cloud Experience Lead en ENCAMINA y MVP en Windows Platform y Visual Studio Development. Alberto Diaz Martin. Cuenta con más de 14 años de experiencia en la Industria IT, todos ellos trabajando con tecnologías Microsoft. Actualmente, es Head of Innovation and Principal Team Leader en ENCAMINA, liderando el desarrollo de software con tecnología Microsoft, y miembro del equipo de Dirección. Para la comunidad, trabaja como organizador y speaker de las conferencias más relevantes del mundo Microsoft en España, en las cuales es uno de los referentes en SharePoint, Office 365 y Azure. Autor de diversos libros y artículos en revistas profesionales y blogs, en 2013 empezó a formar parte del equipo de Dirección de CompartiMOSS, una revista digital sobre tecnologías Microsoft. Desde 2011 ha sido nombrado Microsoft MVP, reconocimiento que ha renovado por sexto año consecutivo. Se define como un geek, amante de los smartphones y desarrollador. Fundador de TenerifeDev (www.tenerifedev.com), un grupo de usuarios de .NET en Tenerife, y coordinador de SUGES (Grupo de Usuarios de SharePoint de España, www.suges.es)

This video how to create tab look in SharePoint Custom List. Many people looking for a Webpart for Tab in SharePoint list. There might be one but you can also create one by yourself following this video.

Part 4 of a 4-part Access series.  Microsoft MVPs Juan and Andy present on tips and tricks to help speed up development.  This session includes information on how to better utilize the immediate window, a tip on using Tags within forms, a great way to remember where you left off when writing code, ways to store code for use in other databases, understanding declarations/variables and much more.  Juan Soto owns IT Impact (Illinois) and Andy Tabisz owns WorkSmart Database Masters (Michigan).

Part 3 of a 4-part Access series, recorded March, 2016.  What should you do if your Access database is running slow?  When is the best time to upgrade your data to SQL Server?  How do you make the transition?  What are the pitfalls to avoid?  How long should a conversion take?  Microsoft MVPs Juan Soto, owner of IT Impact and Andy Tabisz, owner of WorkSmart Database Masters explore this topic and give you the straight scoop.

Part 2 of a 4-part Access series.  Power BI is a powerful business tool to visually drill down into your data in many ways.  This session is an introduction on how to connect Power BI to data in your Access Web App (SQL Azure back end) and what features are available within Power BI.  Presented by Andy Tabisz, Microsoft MVP and owner of WorkSmart Database Masters and Juan Soto, Microsoft MVP and owner of IT Impact.

Part 1 of a 4-part Access series.  Access Web Apps (AWAs) are a great tool to easily build web databases which can be accessed through any web browser.  But, since it does not come with a report-writer tool, users may want to connect to this data though other software to build reports and graphs or for other needs.  This videos demonstrates two ways to connect to the Access Web App data, either through Access's automated report database tool or through an ODBC connection.  Presenters are Andy Tabisz, Microsoft MVP, WorkSmart Database Masters (Michigan) and Juan Soto, Microsoft MVP, IT Impact (Illinois).