Microsoft access tips

Many to many relationships / junction tables e.g. books & authors
The steps below allow the user to enter details about a book and details about the authors of the book all on one form.

1. Create table "Books" with "bookid" as autonumber & primary key
2. Create table "Authors" with "authorid" as autonumber & primary key, and surname, firstname etc as text other fields
3. Create table "BookAuthors" with "bookid" and "authorid" as numbers & non-primary keys
4. Relationships - add tables Books, Authors, BookAuthors - link bookid to bookid and authorid to authorid with referential integrity ticked
5. Form wizard - add tables Books, Authors, BookAuthors, include bookid from Books and authorid from BookAuthors and leave out other -ids as well as the surname from authors, use Books as main form and rest as subform
6. Form design - select subform, cut, goto design ribbon, choose tab control, draw tab control on form, select a tab control page, paste
7a. Select authorid (in the subform), change to combo box, click the "..." by properties-data-row source to create query, inside the query design add table Authors, add authorid as first field, and any other fields to sort by and display (e.g. surname of author), exit and save the query
7b. Properties-format - change column count to match number of columns used in the query, change column widths (use 0 for the first column so it won't be displayed e.g. 0;2.5cm;... ) and change list width to the total width of the column widths
7c. Properties-data-Limit to list - change to Yes (so not in list event can be triggered)
7d. Properites-Event-On Not in List - [Event Procedure] build -

Private Sub AuthorID_NotInList(NewData As String, Response As Integer)
strSQL = "Insert Into Authors ([Surname]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
End Sub

8. Right click on bookid on main form and set properties-all-visible to No

Explanations:
Steps 1-4: Junction table is useful for Many to many relationships. The BookAuthors table links books to authors and allow one book to have many authors

Step 5-8: This creates the main form based on Books and a subform for storing the authors of each book, bookid from Books must be present on mainform so the BookAuthors subform can link to it, bookid can be set to hidden or grayed out etc later on (e.g. in step 8), and authorid from BookAuthors must be included so the other fields on Authors (included in the subform) can link to it. The subform has BookAuthors so user can select the appropriate author from the Authors table. The subform also has other fields from Authors so user can update the author details as well. The surname field is left out because authorid (steps 7a-d) will be setup to substitute for the surname field (7a 7b displays surname and 7c 7d allow user to input new surname)

Other tips

1. #name? error: if a "string" is used in assignment use """string""" (three double quotes) instead.
2. if you change the name of a control, be sure to update the name in the vba module as well
3. if the form uses more than one tables and you can't set one of the keys to null (3162 error "You tried to assign the Null value to a variable that is not a Variant data type.") then check the record source and change INNER JOIN to RIGHT JOIN. (see http://objectmix.com/ado-dao-rdo-rds/212806-you-tried-assign-null-value.html )
4. if the combobox is based on a query and shows #deleted after records got deleted then setup the OnEnter event to requery the combobox e.g.

   Private Sub PatientID_Enter()
   Me.PatientID.Requery
   End Sub

Relinking URLs

Relink.exe (wu, fs)

Relink [options] html_file [local paths to scan]

Converts hrefs to local urls
Html file backed up with .orig extension

Options:
-c list conversions
-u list unconverted urls
-n no conversion (-u -n lists all hrefs)
-d leave URIs decoded in the html source (converts %##)

This 3kb command line tool converts/fixes the url links in an html or hhc file to local links if the file exists locally. It treats anything that comes after href=, src= or value= as urls. It scans the xxx_files\ folder and the source folder of the html file. Additional paths to scan can be specified. e.g. relink abc\index.html img src will relink urls in index.html to use existing local files in the following folders: img\, src\, abc\index_html\, and abc\

It can only convert one file at a time but this is easily remedied using the recursive for command e.g. for /r %f in (*.html) do relink "%f". It is free, portable, uses very little memory (~110kb) while running, and does not modify any registry entries. Virustotal report 0/43. Download links: wu, fs

File repository

This is my temporary file repository. Currently it uses Wupload, Filesonic, and Megaupload.

HTML Tools
Relink.exe (wu, fs): Tool to convert urls in html files to link to existing local files

Batch recursion

Here are 3 short scripts to demonstrate directory recursion
Script 1

@echo off
::demonstrates relative directory recursion
::does not detect hidden files

:main
for %%v in (*.*) do echo %%v
for /d %%v in (*.*) do call :sub "%%v"
goto :eof

:sub
for %%f in ("%~1\*.*") do echo %%f
for /d %%d in ("%~1\*.*") do call :sub "%%d"
goto :eof

Script 2

@echo off
::demonstrates directory recursion
::does detect hidden files but outputs fullpath
for /r %%v in (*.*) do echo %%v

Script 3

@echo off
::demonstrates directory recursion
::detects hidden files & outputs relative path

setlocal enabledelayedexpansion
for /r %%v in (*.*) do set n=%%v&echo !n:%CD%\=!

Right click lock for Dungeon Siege III

This is an autohotkey script for people who don't like to hold the right mouse button down all the time to move the character. It basically enables right clicklock. The current duration to hold the right mouse key down to activate click lock is 350ms but can obviously be modified to something that is not 350. I've noted that sometimes the game can crash and the right click can remain locked (left click should clear it but may not always work), so use this script at your own caution and make sure you don't have any vital program running before you run this script.

; Right click lock (Rt mouse button will be kept held down with a long click -- short clicks allowed for short movements)

#IfWinActive, Dungeon Siege III
#MaxThreadsPerHotkey 3

; Force the keyboard hook to be used, which as a side-effect prevents the Send command from triggering hotkeys.
#UseHook

~Lbutton::HoldRMB = 0

~Rbutton::
TC := A_TickCount
KeyWait, RButton ;wait for right mouse button to be released
If HoldRMB || (A_TickCount-TC) < 350
{
HoldRMB = 0
Return
}
HoldRMB = 1
Loop {
Send {RButton down}
Sleep 40
if !HoldRMB
break
}
Send {RButton up}
HoldRMB = 0
return

To have this script execute automatically, you can create a batch file in the game directory

start c:\progra~1\AutoHotkey\AutoHotkey.exe /r "ds3.ahk"
start "Dungeon Siege III" "Dungeon Siege III.exe"
exit

and point the Dungeon Siege III play shortcut to it (instead of the executable)

Getting password in batch file

This batch script gets the password without displaying user input. Tested only in XP. Vbs script is required.

@echo off
echo wscript.echo CreateObject("ScriptPW.Password").GetPassword()>%temp%\getpwd.vbs
set pwd=Password: <nul
for /f "delims=" %%i in ('cscript /nologo %temp%\GetPwd.vbs') do set pwd=%%i
echo.
echo "%pwd%"

The last line shows the user password for testing purposes. Note that password must be enclosed in double quotes to prevent bad inputs that contains &.

Pathology Quiz Websites

Pathorama: Lung cytology, urinary cytology, serrated polyps, EIN
John Hopkins: Surgical Pathology, GIT Pathology, Cytology
Bethesda