Month: October 2015

Select Background Color from a field

Dim longColor As Long

Dim strColor As String

MainSQL = Select …

Set db = CurrentDb

Set rs = db.OpenRecordset(MainSQL)

strColor = rs![BackColor]

longColor = Val("&H" & strColor & "&")

Detail.BackColor = longColor

Posted by editor

Start Options

File / Open / Options/ Current Data Base

· Application Title

· Display Form ( Default Form)

· Display Status Bar

· Windows ( Overlapping, Tabbed )

· Enable ( Views, layout, Design, etc. )

· Display Navigation (more Options )

Posted by editor

Open Options

File / Open / Options/ Current Data Base

· Application Title

· Display Form ( Default Form)

· Display Status Bar

· Windows ( Overlapping, Tabbed )

· Enable ( Views, layout, Design, etc. )

· Display Navigation (more Options )

Posted by editor

No Ribbon

DoCmd.ShowToolbar "Ribbon", acToolbarNo

Posted by editor

Left, Right, Len, Trim

SELECT store.full, InStr([full],”-“) AS dash, instr(full, “,”) as comma, Left([full],dash-1) AS StoreNum, mid(full,dash + 1 , comma – dash – 1) AS city, trim( right( full, len(full) – comma)) as state
FROM store;

Posted by editor

SELECT store.full, InStr([full],”-“) AS dash, instr(full, “,”) as comma, Left([full],dash-1) AS StoreNum, mid(full,dash + 1 , comma – dash – 1) AS city, trim( right( full, len(full) – comma)) as state FROM store;

Posted by editor

INSERT INTO

INSERT INTO Customers1
SELECT Customers0.[Company] AS Company, Customers0.[LastName] AS LastName, firstn AS firstName, 1 AS selected
FROM Customers0;

Posted by editor

Select into

INSERT INTO Customers1
SELECT Customers0.[Company] AS Company, Customers0.[LastName] AS LastName, firstn AS firstName, 1 AS selected FROM Customers0;

Have a Nice day!

Posted by editor

Left, right, mid, len , trim

SELECT store.full, InStr([full],”-“) AS dash, instr(full, “,”) as comma, Left([full],dash-1) AS StoreNum, mid(full,dash + 1 , comma – dash – 1) AS city, trim( right( full, len(full) – comma)) as state FROM store;

Posted by editor

The ultimate hunger buster

Ever grab a snack but then feel hungry again 20 minutes later? Next time, reach for a banana. It’s loaded with Resistant Starch (RS), a healthy carb that fills you up and helps to boost your metabolism. Slightly underripe medium-sized bananas have 12.5 grams of RS—more than most other foods. Ripe bananas give you 4.7 grams of RS, still enough to keep hunger pangs away. Check out these tasty ways to work in this wonder food

Tropical Salad
Make a fruit salad with 1 sliced peeled banana, 1 sliced peeled kiwi, and 1/2 diced peeled ripe mango. Squirt juice of 1/4 lime over the salad, and serve.Tropical Fruit Salad

Posted by editor

VBA Recordset

https://blog.udemy.com/vba-recordset/
VBA Recordset: How to Handle Sets of Records Easily

APRIL 2, 2014 BY RICHA

Visual Basic for Applications or VBA is an important event driven programming language. It is used for creating programs which enhance the Microsoft Office suite of applications. Visual Basic for Applications is perfect for developing specific applications, whether these are office resources, graphics programs, file sorting programs, or any other kind of Windows-based software programs. Today we look at the useful and frequently used Recordset function, in this intermediate level tutorial. You require a basic level of familiarity with Microsoft Access. If you’re new to them, you should first do this introductory course to Microsoft Access. For folks who are familiar with VBA, you can just do a quick brush up with this VBA tutorial.
Continue reading →

Posted by editor

Walkthrough: Writing Queries in Visual Basic

Visual Studio 2015

This walkthrough demonstrates how you can use Visual Basic language features to write Language-Integrated Query (LINQ) query expressions. The walkthrough demonstrates how to create queries on a list of Student objects, how to run the queries, and how to modify them. The queries incorporate several features that were new in Visual Basic 2008, including object initializers, local type inference, and anonymous types.

After completing this walkthrough, you will be ready to move on to the samples and documentation for the specific LINQ provider you are interested in. LINQ providers include LINQ to SQL, LINQ to DataSet, and LINQ to XML.
Continue reading →

Posted by editor

Run SQL

Public Sub DoSQL()

Dim SQL As String

SQL = “UPDATE Employees ” & _

“SET Employees.Title = ‘Regional Sales Manager’ ” & _

“WHERE Employees.Title = ‘Sales Manager'”

DoCmd.RunSQL SQL

End Sub

Posted by editor

VB SQL

Dim sqlConnection1 As New SqlConnection(“Your Connection String”)

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = “SELECT * FROM Customers”

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1
sqlConnection1.Open()

reader = cmd.ExecuteReader()
‘ Data is accessible through the DataReader object here.
sqlConnection1.Close()

Posted by editor

SQL-specific

There are three main types of SQL-specific query: union queries, pass-through queries, and data-definition queries.

Union queries combine data from two or more tables, but not in the same manner as other queries. Whereas most queries combine data by concatenating rows, union queries combine data by appending rows. Union queries differ from append queries in that union queries do not change the underlying tables. Union queries append the rows in a recordset that does not persist after the query is closed.

Pass-through queries are not processed by the database engine that comes with Access; rather, they are passed directly to a remote database server that does the processing and then passes the results back to Access.

Data-definition queries are a special type of query that does not process data; instead, data-definition queries create, delete or modify other database objects.

SQL-specific queries cannot be opened in Design view. They can only be opened in SQL view, or run. Except for data-definition queries, running a SQL-specific query opens it in Datasheet view.

Posted by editor