autodefine "off" /* INSTRUCTIONS Click 'New' to create a new record Click 'Update' to save currently displayed record Scrolling (prev/next) or closing application also saves currently displayed record */ $main window win ISTRING lname[41],fname[41],street[256],street2[256],city[41],state[11],phone[21],email[256],zipcode[10],date[41] INT id,count,bDirty UINT hStmt_select,hStmt_update,hStmt_insert,hStmt_delete POINTER pDB STRING filter 'Constants used for controls and menu IDs CONST STATUSBAR=1 CONST IDE_NAMES = 5 CONST IDE_FIRST = 10 CONST IDE_LAST = 11 CONST IDE_NUMBER = 12 CONST IDE_STREET2 = 13 CONST IDE_CITY = 14 CONST IDE_STATE = 15 CONST IDE_POSTAL = 16 CONST IDE_PHONE = 17 CONST IDE_EMAIL = 18 CONST IDB_PREV = 30 CONST IDB_NEXT = 31 CONST IDB_UPDATE = 32 CONST IDB_NEW = 33 CONST IDB_DELETE = 34 CONST IDS_RECID = 40 CONST IDS_RECNO = 41 CONST IDM_UPDATE = 32 CONST IDM_NEW = 33 CONST IDM_DELETE = 34 CONST IDM_QUIT = 53 'CREATEDIALOG win,0,0,1024,768,0x80CA0080|@SIZE,0,"Birchfield Buddies",&winhandler OPENWINDOW win,0,0,1024,768,@CAPTION|@MINBOX,0," ",&winhandler CONTROL win,@STATUS,"",0,0,0,0,0,STATUSBAR CONTROL win,@LISTBOX,"",8,8,200,710,0x50B10140,IDE_NAMES CONTROL win,@EDIT,"",299,24,130,20,0x50810080,IDE_FIRST CONTROL win,@EDIT,"",500,24,130,20,0x50810080,IDE_LAST CONTROL win,@EDIT,"",299,51,331,20,0x50810080,IDE_NUMBER CONTROL win,@EDIT,"",299,78,331,20,0x50810080,IDE_STREET2 CONTROL win,@STATIC,"First Name",239,27,57,17,0x5000010B,60 CONTROL win,@STATIC,"Last Name",441,28,55,18,0x5000010B,61 CONTROL win,@STATIC,"Number",254,54,38,17,0x5000010B,62 CONTROL win,@STATIC,"Street2",254,83,38,17,0x5000010B,63 CONTROL win,@EDIT,"",299,105,130,20,0x50810080,IDE_CITY CONTROL win,@STATIC,"City",268,110,24,17,0x5000010B,64 CONTROL win,@EDIT,"",514,105,116,20,0x50810080,IDE_STATE CONTROL win,@STATIC,"State/Province",437,108,76,17,0x5000010B,65 CONTROL win,@STATIC,"Postal Code",230,136,62,17,0x5000010B,66 CONTROL win,@EDIT,"",299,133,130,20,0x50810080,IDE_POSTAL CONTROL win,@STATIC,"Phone",437,137,39,17,0x5000010B,67 CONTROL win,@EDIT,"",478,133,152,20,0x50810080,IDE_PHONE CONTROL win,@STATIC,"Email Address",220,163,74,17,0x5000010B,68 CONTROL win,@EDIT,"",299,160,331,20,0x50810080,IDE_EMAIL CONTROL win,@SYSBUTTON,"<<",275,222,70,20,0x50000000,IDB_PREV CONTROL win,@SYSBUTTON,">>",354,222,70,20,0x50000000,IDB_NEXT CONTROL win,@STATIC,"Number of Records:",267,192,97,17,0x5000010B,69 CONTROL win,@STATIC,"",367,193,70,15,0x50000000,IDS_RECNO CONTROL win,@STATIC,"Record ID:",469,192,59,17,0x5000010B,70 CONTROL win,@STATIC,"",525,192,70,17,0x50000000,IDS_RECID CONTROL win,@BUTTON,"Update",480,222,70,20,0x50000000,IDB_UPDATE CONTROL win,@SYSBUTTON,"New",559,222,70,20,0x50000000,IDB_NEW INT entry SETCONTROLCOLOR win,IDE_NAMES,RGB(255,255,255),RGB(50,50,50) FOR entry=1 to 20 ADDSTRING win,IDE_NAMES,"Entry No"+STR$(entry) NEXT entry SETSELECTED win,IDE_NAMES,0 /* hStmt_select=NULL hStmt_update=NULL hStmt_insert=NULL hStmt_delete=NULL bDirty=FALSE */ hstmt_select = NULL:hstmt_update = NULL:hstmt_insert = NULL:hstmt_delete = NULL bDirty = FALSE 'SHOWDIALOG win pDB=OpenDatabase(win) IF pDB=NULL CLOSEDIALOG win END ENDIF SetupControls() IF(hStmt_select) THEN dbGetFirst(hStmt_select) CopyData(1) WAITUNTIL win = 0 END 'The win window message handler SUB winhandler(),INT SELECT @MESSAGE CASE @IDINITDIALOG SETICON win,LOADIMAGE(101,@IMGICON) CONTROLCMD win,STATUSBAR,@SWSETPANES,1,-1 SETCONTROLTEXT win,STATUSBAR,"Birchfield Buddies - An Inclusive Out of School Club" CENTERWINDOW win BEGINMENU win MENUTITLE "&File" MENUITEM "&Quit",0,IDM_QUIT MENUTITLE "&Edit" MENUITEM "&Update",0,IDM_UPDATE MENUITEM "&New",0,IDM_NEW MENUITEM "&Delete",0,IDM_DELETE ENDMENU 'STARTTIMER win,1000 CASE @IDCREATE CENTERWINDOW win CASE @IDCLOSEWINDOW IF bDirty THEN DoUpdate() CloseAll() closewindow win CASE @IDSIZE IF CONTROLEXISTS(win,STATUSBAR) CONTROLCMD win,STATUSBAR,@SWRESIZE INT left,top,width,height GETCLIENTSIZE win,left,top,width,height CONTROLCMD win,STATUSBAR,@SWSETPANES,1,-1 CONTROLCMD win,STATUSBAR,@SWSETPANETEXT,0,"Birchfield Buddies - An Inclusive Out of School Club" SETSIZE win,8,8,200,height-40,IDE_NAMES ENDIF CASE @IDCLOSEWINDOW IF bDirty THEN DoUpdate() CloseAll() CASE @IDMENUPICK SELECT @MENUNUM CASE IDM_QUIT IF bDirty THEN DoUpdate() CloseAll() CASE IDM_DELETE DoDelete() CASE IDM_NEW IF bDirty THEN DoUpdate() DoNew() CASE IDM_UPDATE DoUpdate() ENDSELECT CASE @IDCONTROL SELECT @CONTROLID CASE IDB_NEW IF @NOTIFYCODE=0 IF bDirty THEN DoUpdate() DoNew() ENDIF CASE IDB_PREV IF @NOTIFYCODE=0 IF bDirty THEN DoUpdate() IF(hStmt_select) IF dbGetPrev(hStmt_select) ENABLECONTROL win,IDB_NEXT,1 else dbGetFirst(hStmt_select) ENABLECONTROL win,IDB_PREV,0 endif CopyData(1) endif ENDIF CASE IDB_NEXT IF @NOTIFYCODE=0 IF bDirty THEN DoUpdate() if(hStmt_select) if dbGetNext(hStmt_select) ENABLECONTROL win,IDB_PREV,1 else dbGetLast(hStmt_select) ENABLECONTROL win,IDB_NEXT,0 endif CopyData(1) endif ENDIF CASE IDB_UPDATE 'IF @NOTIFYCODE=0 DoUpdate() 'any of the edit controls marks the form as 'dirty' and causes an auto update on scrolling CASE IDE_FIRST CASE& IDE_LAST CASE& IDE_NUMBER CASE& IDE_STREET2 CASE& IDE_CITY CASE& IDE_STATE CASE& IDE_POSTAL CASE& IDE_PHONE CASE& IDE_EMAIL IF @NOTIFYCODE=@ENCHANGE THEN bDirty=TRUE 'ENDIF ENDSELECT CASE @IDTIMER DoIdle() CASE @IDMENUINIT ENABLEMENUITEM win,IDM_UPDATE,bDirty ENABLEMENUITEM win,IDM_DELETE,count <> 0 ENDSELECT RETURN 0 ENDSUB SUB DoDelete() IF count AND hStmt_select dbFreeSQL(hStmt_select) hStmt_select = NULL hStmt_delete = dbExecSQL(pDB,"DELETE FROM addresses WHERE id =" + STR$(id)) dbFreeSQL(hStmt_delete) hStmt_delete = NULL count = dbCardinality(pDB,"addresses") if(count) InitSelect() dbGetFirst(hStmt_select) CopyData(1) ENABLECONTROL win,IDB_NEXT,1 ENABLECONTROL win,IDB_PREV,0 else ENABLECONTROL win,IDB_PREV,0 ENABLECONTROL win,IDB_NEXT,0 ENABLECONTROL win,IDB_UPDATE,0 lname = "":fname="":street="":street2="":city="":state="":zipcode="":phone="":email="" CopyData(1) endif ENDIF RETURN ENDSUB SUB DoNew() 'insert a blank record IF hStmt_select THEN dbFreeSQL(hStmt_select) hStmt_select = NULL hStmt_insert = dbExecSQL(pDB,"INSERT INTO addresses (fname,lname,street,street2,city,state,zip,phone,email) VALUES('','','','','','','','','')") dbFreeSQL(hStmt_insert) hStmt_insert = NULL count = dbCardinality(pDB,"addresses") InitSelect() dbGetLast(hStmt_select) CopyData(1) ENABLECONTROL win,IDB_PREV,1 ENABLECONTROL win,IDB_UPDATE,1 ENABLECONTROL win,IDB_NEXT,0 'make sure the edit controls are enabled ENABLECONTROL win,IDE_FIRST,1 ENABLECONTROL win,IDE_LAST,1 ENABLECONTROL win,IDE_NUMBER,1 ENABLECONTROL win,IDE_STREET2,1 ENABLECONTROL win,IDE_CITY,1 ENABLECONTROL win,IDE_STATE,1 ENABLECONTROL win,IDE_POSTAL,1 ENABLECONTROL win,IDE_PHONE,1 ENABLECONTROL win,IDE_EMAIL,1 'set focus to the first CONTROL SETFOCUS win,IDE_FIRST bDirty = TRUE RETURN ENDSUB SUB DoUpdate 'save the currently displayed record CopyData(0) hStmt_update = dbPrepareSQL(pDB,"UPDATE addresses SET fname=?,lname=?,street=?,street2=?,city=?,state=?,zip=?,phone=?,email=?,dtadded=? WHERE id="+STR$(id)) dbBindParameter(hStmt_update,1,fname,40) dbBindParameter(hStmt_update,2,lname,40) dbBindParameter(hStmt_update,3,street,255) dbBindParameter(hStmt_update,4,street2,255) dbBindParameter(hStmt_update,5,city,40) dbBindParameter(hStmt_update,6,state,10) dbBindParameter(hStmt_update,7,zipcode,10) dbBindParameter(hStmt_update,8,phone,20) dbBindParameter(hStmt_update,9,email,255) dbBindParameter(hStmt_update,10,date,40) date = DATE$() dbExecute(hStmt_update) dbFreeSQL(hStmt_update) hStmt_update = NULL bDirty = FALSE RETURN ENDSUB SUB CloseAll() 'check and close all statments just to be on the safe side IF hStmt_select THEN dbFreeSQL(hStmt_select) IF hStmt_update THEN dbFreeSQL(hStmt_update) IF hStmt_insert THEN dbFreeSQL(hStmt_insert) IF hStmt_delete THEN dbFreeSQL(hStmt_delete) IF pDB <> NULL THEN dbDisconnect(pDB) STOPTIMER win CLOSEDIALOG win RETURN ENDSUB SUB CopyData(direction as INT) IF direction = 1 'from variables to edit controls SETCONTROLTEXT win,IDS_RECID,LTRIM$(STR$(id)) SETCONTROLTEXT win,IDS_RECNO,LTRIM$(STR$(count)) SETCONTROLTEXT win,IDE_FIRST,fname SETCONTROLTEXT win,IDE_LAST,lname SETCONTROLTEXT win,IDE_NUMBER,street SETCONTROLTEXT win,IDE_STREET2,street2 SETCONTROLTEXT win,IDE_CITY,city SETCONTROLTEXT win,IDE_STATE,state SETCONTROLTEXT win,IDE_POSTAL,zipcode SETCONTROLTEXT win,IDE_PHONE,phone SETCONTROLTEXT win,IDE_EMAIL,email 'setting a controls text also sends the @ENCHANGE notification 'so mark the form as not modified bDirty = FALSE ELSE 'from edit controls to variables id = VAL(GETCONTROLTEXT(win,IDS_RECID)) fname = GETCONTROLTEXT(win,IDE_FIRST) lname = GETCONTROLTEXT(win,IDE_LAST) street = GETCONTROLTEXT(win,IDE_NUMBER) street2 = GETCONTROLTEXT(win,IDE_STREET2) city = GETCONTROLTEXT(win,IDE_CITY) state = GETCONTROLTEXT(win,IDE_STATE) zipcode = GETCONTROLTEXT(win,IDE_POSTAL) phone = GETCONTROLTEXT(win,IDE_PHONE) email = GETCONTROLTEXT(win,IDE_EMAIL) ENDIF RETURN ENDSUB SUB SetupControls() SETCONTROLTEXT win,IDS_RECNO,LTRIM$(STR$(count)) ENABLECONTROL win,IDB_UPDATE,0 IF count = 0 ENABLECONTROL win,IDB_PREV,0 ENABLECONTROL win,IDB_NEXT,0 DoIdle() ELSE ENABLECONTROL win,IDB_PREV,0 InitSelect() ENDIF CONTROLCMD win,IDE_FIRST,@EDSETLIMITTEXT,40 CONTROLCMD win,IDE_LAST,@EDSETLIMITTEXT,40 CONTROLCMD win,IDE_NUMBER,@EDSETLIMITTEXT,255 CONTROLCMD win,IDE_STREET2,@EDSETLIMITTEXT,255 CONTROLCMD win,IDE_CITY,@EDSETLIMITTEXT,40 CONTROLCMD win,IDE_STATE,@EDSETLIMITTEXT,10 CONTROLCMD win,IDE_POSTAL,@EDSETLIMITTEXT,9 CONTROLCMD win,IDE_PHONE,@EDSETLIMITTEXT,20 CONTROLCMD win,IDE_EMAIL,@EDSETLIMITTEXT,255 RETURN ENDSUB SUB InitSelect() STRING sel$ sel$ = "SELECT * FROM addresses" IF LEN(filter) THEN sel$ += " WHERE " + filter hStmt_select = dbExecSQL(pDB,sel$) IF hStmt_select dbBindVariable(hStmt_select,1,id) dbBindVariable(hStmt_select,2,fname) dbBindVariable(hStmt_select,3,lname) dbBindVariable(hStmt_select,4,street) dbBindVariable(hStmt_select,5,street2) dbBindVariable(hStmt_select,6,city) dbBindVariable(hStmt_select,7,state) dbBindVariable(hStmt_select,8,zipcode) dbBindVariable(hStmt_select,9,phone) dbBindVariable(hStmt_select,10,email) ENDIF RETURN ENDSUB SUB OpenDatabase(win as WINDOW),POINTER DEF pReturn as POINTER DEF hStmt as UINT pReturn = dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH() + "addressbook.mdb","",win) IF pReturn = NULL 'couldn't open so try creating dbCreateMDB(GETSTARTPATH() + "addressbook.mdb") pReturn = dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH() + "addressbook.mdb","",win) IF pReturn <> NULL 'add a table hStmt = dbExecSQL(pReturn,"CREATE TABLE addresses (id counter(1,1),fname varchar(40),lname varchar(40),street varchar(255),street2 varchar(255) )") 'check for errors IF LEN(dbGetErrorCode(hStmt)) MESSAGEBOX win,dbGetErrorText(hStmt),dbGetErrorCode(hStmt) ENDIF dbFreeSQL(hStmt) 'alter the table hStmt = dbExecSQL(pReturn,"ALTER TABLE addresses ADD city varchar(40),state varchar(10),zip varchar(9),phone varchar(20),email varchar(255),dtadded date") 'check for errors IF LEN(dbGetErrorCode(hStmt)) MESSAGEBOX win,dbGetErrorText(hStmt),dbGetErrorCode(hStmt) ENDIF dbFreeSQL(hStmt) ELSE MESSAGEBOX win,"Could not connect/create database","Error" ENDIF ENDIF 'are we open? IF pReturn <> NULL 'get the initial count of records count = dbCardinality(pReturn,"addresses") ENDIF RETURN pReturn ENDSUB SUB DoIdle() STRING strCaption UINT pos strCaption = GETCAPTION(win) pos=INSTR(strCaption,"*") IF bDirty IF pos = 0 strCaption += "*" SETCAPTION win,strCaption ENABLECONTROL win,IDB_UPDATE,1 ENDIF ELSE IF pos <> 0 strCaption = LEFT$(strCaption,pos-1) SETCAPTION win,strCaption ENABLECONTROL win,IDB_UPDATE,0 ENDIF ENDIF 'if count = 0 then disable the edit controls IF count = 0 ENABLECONTROL win,IDE_FIRST,0 ENABLECONTROL win,IDE_LAST,0 ENABLECONTROL win,IDE_NUMBER,0 ENABLECONTROL win,IDE_STREET2,0 ENABLECONTROL win,IDE_CITY,0 ENABLECONTROL win,IDE_STATE,0 ENABLECONTROL win,IDE_POSTAL,0 ENABLECONTROL win,IDE_PHONE,0 ENABLECONTROL win,IDE_EMAIL,0 SETCONTROLTEXT win,IDS_RECID,"*" ENDIF RETURN ENDSUB