T
Trevor
I'm trying to add a new record an Access DB table with one of the fields
containing a 'link' vale to a record in another table. I'm using a DD list
from which to select the 'linked' record (a Client name table which as unique
ref field as autonumber). I've created a form to add a record (whick worked
successfully) and then modified it to include the DD list. The DD list should
complete 'Field1' with the reference value for inclusion in the new record.
I've pasted the HTML for my latest attempt below, but I'm struggling to work
out how to put the 'Client reference' value from the DD into 'Field1'.
Additionally on the simple add that worked the confirmation page displays
'Fieldn' and its value rather than the DB column name, how do I change this?
<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Err.Clear
strErrorUrl = ""
If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear
Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"
Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"
fp_conn.Open Application("CopyScheduling_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"
fp_rs.Open """Project table""", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"
fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(10)
Dim arFormDBFields0(10)
Dim arFormValues0(10)
arFormFields0(0) = "Field10"
arFormDBFields0(0) = "Rate for SC days"
arFormValues0(0) = Request("Field10")
arFormFields0(1) = "Field11"
arFormDBFields0(1) = "Proj Commentary"
arFormValues0(1) = Request("Field11")
arFormFields0(2) = "Field1"
arFormDBFields0(2) = "Client Reference"
arFormValues0(2) = Request("Field1")
arFormFields0(3) = "Field3"
arFormDBFields0(3) = "Project Name"
arFormValues0(3) = Request("Field3")
arFormFields0(4) = "Field4"
arFormDBFields0(4) = "Purchase Order"
arFormValues0(4) = Request("Field4")
arFormFields0(5) = "Field5"
arFormDBFields0(5) = "Non-chargable work"
arFormValues0(5) = Request("Field5")
arFormFields0(6) = "Field6"
arFormDBFields0(6) = "Emergency work"
arFormValues0(6) = Request("Field6")
arFormFields0(7) = "Field7"
arFormDBFields0(7) = "Rate for HL days"
arFormValues0(7) = Request("Field7")
arFormFields0(8) = "Field8"
arFormDBFields0(8) = "Rate for LL days"
arFormValues0(8) = Request("Field8")
arFormFields0(9) = "Field9"
arFormDBFields0(9) = "Rate for PM days"
arFormValues0(9) = Request("Field9")
FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0
fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"
fp_rs.Close
fp_conn.Close
FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"Add_Project_form3.asp",_
"Return to the form."
End If
End If
Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")
%>
<% Response.Buffer = True %>
<html>
<head>
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Project table -- New</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>
<body bgcolor="#FFFFFF" background="images/Background.bmp">
<table width="100%" align=left>
<tr>
<td>
<p align="center">
<b><font size=+3> Add a Project / Assignment </font></b>
</td>
</tr>
</table>
<p>
<br clear="all">
<hr>
<form>
<b>Client </b><nobr>
<!--webbot bot="DatabaseRegionStart" s-columnnames="Client Reference,Client
Name" s-columntypes="3,202" s-dataconnection="CopyScheduling"
b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="Client Name"
s-menuvalue="Client Reference" b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
i-listformat="0" b-makeform="FALSE" s-recordsource="Client Table"
s-displaycolumns="Client Name,Client Reference" s-criteria s-order="[Client
Name] +" s-sql="SELECT * FROM "Client Table" ORDER BY "Client Name" ASC"
b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields
s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0"
botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0);
background-color: rgb(255,255,0)">Database</span> " startspan --><!--#include
file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM ""Client Table"" ORDER BY ""Client Name"" ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="CopyScheduling"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Client Name"
fp_sMenuValue="Client Reference"
fp_sColTypes="&Client Reference=3&Client Name=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="33564" --><select
NAME="Field1" SIZE="1">
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc"
startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot
bot="AspInclude" endspan i-checksum="62210" -->
<option VALUE="<%=FP_FieldHTML(fp_rs,"Client
Reference")%>"><%=FP_FieldHTML(fp_rs,"Client Name")%>
</option>
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc"
startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot
bot="AspInclude" endspan i-checksum="62218" -->
</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside preview=" <span style="color: rgb(0,0,0); background-color:
rgb(255,255,0)">Results</span> " startspan --><!--webbot
bot="DatabaseRegionEnd" endspan --></nobr></form>
<form METHOD="POST" action="--WEBBOT-SELF--" onsubmit="return
FrontPage_Form2_Validator(this)" language="JavaScript" name="FrontPage_Form2">
<!--webbot bot="SaveDatabase" SuggestedExt="asp"
S-DataConnection="CopyScheduling" S-RecordSource="Project table"
S-Form-Fields="Field10 Field11 Field1 Field3 Field4 Field5 Field6 Field7
Field8 Field9" S-Form-DBFields="Rate\ for\ SC\ days Proj\ Commentary Client\
Reference Project\ Name Purchase\ Order Non-chargable\ work Emergency\ work
Rate\ for\ HL\ days Rate\ for\ LL\ days Rate\ for\ PM\ days"
U-ASP-Include-Url="_fpclass/fpdbform.inc" startspan
U-Database-URL="fpdb/scheduling.mdb" --><input TYPE="hidden" NAME="VTI-GROUP"
VALUE="0"><!--#include file="_fpclass/fpdbform.inc"--><!--webbot
bot="SaveDatabase" endspan i-checksum="40548" -->
</p>
<p><b>Project Name</b><br>
<!--webbot bot="Validation" s-display-name="Field3" s-data-type="String"
b-value-required="False" i-maximum-length="50" --><input type="TEXT"
name="Field3" size="50" value="" maxlength="50"><br>
</p>
<p><b>Purchase Order</b><br>
<!--webbot bot="Validation" s-display-name="Field4" s-data-type="String"
b-value-required="False" i-maximum-length="50" --><input type="TEXT"
name="Field4" size="50" value="" maxlength="50"><br>
</p>
<p><table width="50%"><tr><td align=center>
<b>Non-chargeable (FoC) work</b><br>
<select size="1" name="Field5"><option value="1">Yes</option><option
selected value="0">No</option></select><br>
</td><td align=center>
<b>Emergency Support</b><br>
<select size="1" name="Field6"><option value="1">Yes</option><option
selected value="0">No</option></select><br>
</td></tr></table></p>
<p>
<p><table width="50%"><tr><td align=center>
<b>Rate for HL days</b><br>
<!--webbot bot="Validation" s-display-name="Field7" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field7" size="19" value="" maxlength="19"><br>
</td><td align=center>
<b>Rate for LL days</b><br>
<!--webbot bot="Validation" s-display-name="Field8" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field8" size="19" value="" maxlength="19"><br>
</td><td align=center>
<b>Rate for PM days</b><br>
<!--webbot bot="Validation" s-display-name="Field9" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field9" size="19" value="" maxlength="19"><br>
</td><td align=center>
<b>Rate for SC days</b><br>
<!--webbot bot="Validation" s-display-name="Field10" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field10" size="19" value="" maxlength="19"><br>
</td></tr></table></p>
<p><b>Commentary</b><br>
<!--webbot bot="Validation" s-display-name="Field11" s-data-type="String"
b-value-required="False" i-maximum-length="250" --><input type="TEXT"
name="Field11" size="64" value="" maxlength="250"><br>
</p>
<p>
<table table width="50%"><tr><td align=center>
<input type="submit" value=" OK "></td><td><input type="reset" value="
Reset ">
</td></tr></table></p>
</form>
</body>
</html>
containing a 'link' vale to a record in another table. I'm using a DD list
from which to select the 'linked' record (a Client name table which as unique
ref field as autonumber). I've created a form to add a record (whick worked
successfully) and then modified it to include the DD list. The DD list should
complete 'Field1' with the reference value for inclusion in the new record.
I've pasted the HTML for my latest attempt below, but I'm struggling to work
out how to put the 'Client reference' value from the DD into 'Field1'.
Additionally on the simple add that worked the confirmation page displays
'Fieldn' and its value rather than the DB column name, how do I change this?
<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Err.Clear
strErrorUrl = ""
If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear
Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"
Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"
fp_conn.Open Application("CopyScheduling_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"
fp_rs.Open """Project table""", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"
fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(10)
Dim arFormDBFields0(10)
Dim arFormValues0(10)
arFormFields0(0) = "Field10"
arFormDBFields0(0) = "Rate for SC days"
arFormValues0(0) = Request("Field10")
arFormFields0(1) = "Field11"
arFormDBFields0(1) = "Proj Commentary"
arFormValues0(1) = Request("Field11")
arFormFields0(2) = "Field1"
arFormDBFields0(2) = "Client Reference"
arFormValues0(2) = Request("Field1")
arFormFields0(3) = "Field3"
arFormDBFields0(3) = "Project Name"
arFormValues0(3) = Request("Field3")
arFormFields0(4) = "Field4"
arFormDBFields0(4) = "Purchase Order"
arFormValues0(4) = Request("Field4")
arFormFields0(5) = "Field5"
arFormDBFields0(5) = "Non-chargable work"
arFormValues0(5) = Request("Field5")
arFormFields0(6) = "Field6"
arFormDBFields0(6) = "Emergency work"
arFormValues0(6) = Request("Field6")
arFormFields0(7) = "Field7"
arFormDBFields0(7) = "Rate for HL days"
arFormValues0(7) = Request("Field7")
arFormFields0(8) = "Field8"
arFormDBFields0(8) = "Rate for LL days"
arFormValues0(8) = Request("Field8")
arFormFields0(9) = "Field9"
arFormDBFields0(9) = "Rate for PM days"
arFormValues0(9) = Request("Field9")
FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0
fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"
fp_rs.Close
fp_conn.Close
FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"Add_Project_form3.asp",_
"Return to the form."
End If
End If
Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")
%>
<% Response.Buffer = True %>
<html>
<head>
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Project table -- New</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>
<body bgcolor="#FFFFFF" background="images/Background.bmp">
<table width="100%" align=left>
<tr>
<td>
<p align="center">
<b><font size=+3> Add a Project / Assignment </font></b>
</td>
</tr>
</table>
<p>
<br clear="all">
<hr>
<form>
<b>Client </b><nobr>
<!--webbot bot="DatabaseRegionStart" s-columnnames="Client Reference,Client
Name" s-columntypes="3,202" s-dataconnection="CopyScheduling"
b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="Client Name"
s-menuvalue="Client Reference" b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
i-listformat="0" b-makeform="FALSE" s-recordsource="Client Table"
s-displaycolumns="Client Name,Client Reference" s-criteria s-order="[Client
Name] +" s-sql="SELECT * FROM "Client Table" ORDER BY "Client Name" ASC"
b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields
s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0"
botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
u-dbrgn2="_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0);
background-color: rgb(255,255,0)">Database</span> " startspan --><!--#include
file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM ""Client Table"" ORDER BY ""Client Name"" ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="CopyScheduling"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Client Name"
fp_sMenuValue="Client Reference"
fp_sColTypes="&Client Reference=3&Client Name=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="33564" --><select
NAME="Field1" SIZE="1">
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc"
startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot
bot="AspInclude" endspan i-checksum="62210" -->
<option VALUE="<%=FP_FieldHTML(fp_rs,"Client
Reference")%>"><%=FP_FieldHTML(fp_rs,"Client Name")%>
</option>
<!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc"
startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot
bot="AspInclude" endspan i-checksum="62218" -->
</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside preview=" <span style="color: rgb(0,0,0); background-color:
rgb(255,255,0)">Results</span> " startspan --><!--webbot
bot="DatabaseRegionEnd" endspan --></nobr></form>
<form METHOD="POST" action="--WEBBOT-SELF--" onsubmit="return
FrontPage_Form2_Validator(this)" language="JavaScript" name="FrontPage_Form2">
<!--webbot bot="SaveDatabase" SuggestedExt="asp"
S-DataConnection="CopyScheduling" S-RecordSource="Project table"
S-Form-Fields="Field10 Field11 Field1 Field3 Field4 Field5 Field6 Field7
Field8 Field9" S-Form-DBFields="Rate\ for\ SC\ days Proj\ Commentary Client\
Reference Project\ Name Purchase\ Order Non-chargable\ work Emergency\ work
Rate\ for\ HL\ days Rate\ for\ LL\ days Rate\ for\ PM\ days"
U-ASP-Include-Url="_fpclass/fpdbform.inc" startspan
U-Database-URL="fpdb/scheduling.mdb" --><input TYPE="hidden" NAME="VTI-GROUP"
VALUE="0"><!--#include file="_fpclass/fpdbform.inc"--><!--webbot
bot="SaveDatabase" endspan i-checksum="40548" -->
</p>
<p><b>Project Name</b><br>
<!--webbot bot="Validation" s-display-name="Field3" s-data-type="String"
b-value-required="False" i-maximum-length="50" --><input type="TEXT"
name="Field3" size="50" value="" maxlength="50"><br>
</p>
<p><b>Purchase Order</b><br>
<!--webbot bot="Validation" s-display-name="Field4" s-data-type="String"
b-value-required="False" i-maximum-length="50" --><input type="TEXT"
name="Field4" size="50" value="" maxlength="50"><br>
</p>
<p><table width="50%"><tr><td align=center>
<b>Non-chargeable (FoC) work</b><br>
<select size="1" name="Field5"><option value="1">Yes</option><option
selected value="0">No</option></select><br>
</td><td align=center>
<b>Emergency Support</b><br>
<select size="1" name="Field6"><option value="1">Yes</option><option
selected value="0">No</option></select><br>
</td></tr></table></p>
<p>
<p><table width="50%"><tr><td align=center>
<b>Rate for HL days</b><br>
<!--webbot bot="Validation" s-display-name="Field7" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field7" size="19" value="" maxlength="19"><br>
</td><td align=center>
<b>Rate for LL days</b><br>
<!--webbot bot="Validation" s-display-name="Field8" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field8" size="19" value="" maxlength="19"><br>
</td><td align=center>
<b>Rate for PM days</b><br>
<!--webbot bot="Validation" s-display-name="Field9" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field9" size="19" value="" maxlength="19"><br>
</td><td align=center>
<b>Rate for SC days</b><br>
<!--webbot bot="Validation" s-display-name="Field10" s-data-type="String"
b-value-required="False" i-maximum-length="19" --><input type="TEXT"
name="Field10" size="19" value="" maxlength="19"><br>
</td></tr></table></p>
<p><b>Commentary</b><br>
<!--webbot bot="Validation" s-display-name="Field11" s-data-type="String"
b-value-required="False" i-maximum-length="250" --><input type="TEXT"
name="Field11" size="64" value="" maxlength="250"><br>
</p>
<p>
<table table width="50%"><tr><td align=center>
<input type="submit" value=" OK "></td><td><input type="reset" value="
Reset ">
</td></tr></table></p>
</form>
</body>
</html>