GENERIC MSACCESS 97 FORMS GENERATION<BR><BR>I got an idea from this story:<BR><BR>http://www.4guysfromrolla.com/webtech/082699-1.shtml<BR><BR>and used it&#039s code in the one of the files below. It&#039s a script, that changing only the DSN name and tableName, will generate a rough, workable form allowing you to submit a record to an Access 97 database.<BR><BR>If you copy the below text below into three separate files with the indicated file names and change the DSN name and tableName variables in genericForm.asp it should work.<BR><BR>Hope that some of you could look and make improvements as a learning tool for myself and everyone...thanks,<BR><BR>Richard Lowe<BR><BR>&#060;% &#039_____begin genericForm.asp______ %&#062;<BR>&#060;html&#062;<BR>&#060;head&#062;<BR >&#060;/head&#062;<BR>&#060;body&#062;<BR>&#060;!--#include file="subEmptyElement.asp"--&#062;<BR>&#060;%<BR>&#039 ************************************************** ************<BR>&#039INPUT YOUR DSN AND TABLE NAME HERE<BR>Dim myDSN<BR>myDSN = "DSN=&#060;myDSNName&#062;"<BR><BR>Dim tableName<BR>tableName = "myTableName"<BR>&#039 ************************************************** ************<BR><BR>&#039 ************************************************** **********<BR>&#039 THE SUB THAT WRITES THE CORRECT &#060;INPUT&#062; TAG DEPENDING OF FIELD TYPE<BR>&#039 ************************************************** ************<BR>Sub writeFormElement(Name, typeNumber, sizeOf)<BR>Dim tN<BR> tN = typeNumber<BR>Dim typeString<BR>Dim caseNumber<BR>Dim CaseString(12)<BR><BR>If sizeOf = "" Then<BR> sizeOf = 25 &#039 SET THE DEFAULT SIZE OF A TEXT BOX<BR>End if<BR>CaseString(0) = "&#124 2&#124 3&#124 4&#124 6&#124 17&#124 72&#124 200&#124" &#039 CASES FOR A TEXT BOX<BR>CaseString(1) = "&#124 11&#124" &#039 CASE FOR A CHECKBOX (BOOLEAN)<BR>CaseString(2) = "&#124 201&#124" &#039 CASE FOR A MEMO BOX (TEXTAREA TAG) (ALSO HREF?)<BR>CaseString(3) = "&#124 135&#124" &#039 CASE FOR A DATE<BR>CaseString(4) = "&#124 205&#124" &#039 CASE TO IGNORE<BR>If isNumeric(tN) Then<BR> typeString = "&#124" & Cstr(tN) & "&#124" &#039 CONVERT THE NUMBER PASSED TO A STRING FOR EASY COMPARISON<BR>For i = 1 to emptyElement(CaseString)<BR> if inStr(CaseString(i), typeString) &#062; 0 Then<BR> caseNumber = i<BR> End if<BR>Next<BR>&#039 SET THE CASE NUMBER FOR THE CASE STATEMENT.<BR>&#039 IT&#039S DONE THIS WAY BECAUSE THERE ARE MANY TYPES THAT MAP TO ONLY ONE CASE<BR>Select Case caseNumber<BR>Case 0 %&#062;<BR> &#060;Input type ="text" size="&#060;% response.write sizeOf %&#062;" name= "&#060;% response.write Name %&#062;"&#062;<BR><BR>&#060;%<BR>Case 1 %&#062;<BR>&#060;Input type="checkbox" name= "&#060;% response.write Name %&#062;"&#062;<BR><BR>&#060;%<BR>Case 2 %&#062;<BR>&#060;textarea cols="25" rows = "6" wrap="soft" name = "&#060;% response.write Name %&#062;"&#062;&#060;/textarea&#062;<BR><BR>&#060;%<BR>Case 3 %&#062;<BR>&#060;input type ="text" size = "14" name= "&#060;% response.write Name %&#062;"&#062;<BR><BR>&#060;%<BR>Case 4<BR>response.write "THIS FIELD BEING IGNORED<BR>"<BR>End Select %&#062;<BR>&#060;%<BR>&#039 IF IT&#039S NOT A NUMBER TO START WITH, WE DON&#039T WANT IT!!!!<BR>Else<BR>response.write "YOU PASSED A NON-NUMERIC VALUE TO Sub writeFromElement()"<BR>End if<BR>End Sub<BR>&#039 ************************************************** **********<BR>&#039 END SUB<BR>&#039 ************************************************** ***********<BR>%&#062;<BR><BR>&#060;!-- WRITE THE FORM THAT WILL ADD THE DATA //--&#062;<BR><BR>&#060;%<BR><BR><BR>set objConn = Server.CreateObject("ADODB.Connection")<BR>mySQL ="SELECT * " & _<BR>"FROM " & _<BR>tableName<BR>objConn.Open myDSN<BR>set RS = Server.CreateObject("ADODB.Recordset")<BR>RS.Open mySQL, objConn, 1, 1<BR><BR>%&#062;<BR>&#060;!-- CREATE THE FORM TO SUBMIT TO genSubmit.asp //--&#062;<BR>&#060;form name="genForm" method="post" action="genSubmit.asp"&#062;<BR>&#060;table bgcolor="Navy" border="1" cellpadding="1" cellspacing="0" width = "50%" align="center"&#062;&#060;%<BR>Dim sizeOfIt<BR>sizeOfIt = 0<BR>for Each thing in RS.Fields<BR>&#039 FOR TEXT BOX CREATION, USE THE definedSize PROPERTY TO SET THE size= ATTRIBUTE OF &#060;INPUT TYPE="TEXTBOX"&#062;<BR> if thing.definedSize &#060; 300 Then<BR> sizeOfIt = thing.definedSize<BR> End if<BR>%&#062;<BR>&#060;tr&#062;<BR>&#060;td align="right" width="25%"&#062;<BR>&#060;font face="Verdana" color="white" &#062;&#060;b&#062;&#060;% response.write thing.Name & " : " %&#062;&#060;/b&#062;&#060;/font&#062;&#060;<BR>/td&#062;<BR>&#060;td width = "75%"&#062;&#060;% writeFormElement thing.Name, thing.type, sizeOfIt %&#062;&#060;/td&#062;<BR>&#060;/tr&#062;<BR>&#060;%<BR>Next<BR>Rs.Close<BR>objConn .Close<BR>%&#062;<BR>&#060;tr&#062;<BR>&#060;td align="right" width="25%"&#062;<BR>&#060;font face="Verdana" color="white" &#062;<BR>&#060;input type = "submit" value="Submit"&#062;&#060;/b&#062;&#060;/font&#062;<BR>&#060;/td&#062;<BR>&#060;/tr&#062;<BR>&#060;td width = "75%"&#062;<BR>&#060;/table&#062;<BR>&#060;!-- PASS THE TABLE NAME AND DSN TO THE NEXT PAGE //--&#062;<BR>&#060;input type ="hidden" name="tableName" value="&#060;% response.write tableName %&#062;"&#062;<BR>&#060;input type ="hidden" name="DSNString" value="&#060;% response.write myDSN %&#062;"&#062;<BR><BR>&#060;/form&#062;<BR>&#060;/body&#062;<BR>&#060;/html&#062;<BR>&#060;% &#039_____end genericForm.asp______ %&#062;<BR><BR><BR><BR><BR>&#060;% &#039_____begin genSubmit.asp______ %&#062;<BR><BR>&#060;%<BR><BR>&#039 ************************************************** **********<BR>&#039 MODIFIED CODE FROM THE 4guysfromrolla.com LINK ABOVE<BR>&#039 ************************************************** *********<BR><BR>&#039Our ADO constants we&#039ll need<BR>Const adOpenForwardOnly = 0<BR>Const adLockOptimistic = 3<BR>Const adCmdTable = &H0002<BR><BR><BR>&#039Create our connection object and open a connection to our database<BR>Dim objConn<BR>Set objConn = Server.CreateObject("ADODB.Connection")<BR><BR>obj Conn.ConnectionString = Request("DSNString")<BR>objConn.Open<BR><BR><BR>&# 039Create a recordset object<BR>Dim objRS<BR>Set objRS = Server.CreateObject("ADODB.Recordset")<BR><BR>&#03 9Open a table view for the table name specified by Request("TableName")<BR>Dim strTableName<BR><BR>strTableName = Request("TableName")<BR>response.write Request("TableName")<BR>objRS.Open strTableName, objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable<BR><BR><BR>&#039Add a new record...<BR>objRS.AddNew<BR><BR>&#039Iterate through the fields of the table...<BR>Dim fld<BR>For Each fld in objRS.Fields<BR> &#039If a value for the column name was passed in,<BR> &#039set the column name equal to the value passed through the form...<BR> if Len(Request(fld.Name)) &#062; 0 then<BR> response.write fld.Name & ":" & Request(fld.Name) & "<BR>"<BR> if Request(fld.Name) = "on" Then<BR> fld.Value = -1<BR> Else<BR> fld.Value = Request(fld.Name)<BR> End if<BR> End if<BR>Next<BR><BR>&#039We&#039re done, so update the record<BR>objRS.Update<BR><BR><BR>&#039Clean Up...<BR>objRS.Close<BR>Set objRS = Nothing<BR><BR>objConn.Close<BR>Set objConn = Nothing<BR><BR>%&#062;<BR>&#060;% &#039_____end genSubmit.asp______ %&#062;<BR><BR><BR><BR>&#060;% &#039_____begin subEmptyElement.asp______ %&#062;<BR><BR>&#060;% &#039 ************************************************** ***********<BR>&#039 THIS FUNCTION DETERMINES THE FIRST EMPTY ELEMENT OF A<BR>&#039 STRING ARRAY<BR>&#039 ************************************************** ************<BR>Function emptyElement(testArray)<BR>dim elementCount<BR>elementCount = 0<BR>For i = 0 to Ubound(testArray)<BR> if testArray(i) = "" Then<BR> Exit For<BR> Else<BR> elementCount = elementCount +1<BR> End if<BR>Next<BR> emptyElement = elementCount<BR>End Function %&#062;<BR><BR>&#060;% &#039_____end subEmptyElement.asp______ %&#062;<BR><BR>