Start Free Trial

asked on

Convert Access Database Into Browser Based System

I have attached a an Access DB. What I want to do is to convert the access DB and forms into a browser based solution. Reason being, we are going to need to have multiple people accessing different forms of the system at the same time.

This system is to track repair orders for tablet computers. Repair orders are entered, and then a technician will repair the machine. The repair is going to be an addition to this system.

To explain this simply there are a few parts....

1. Lookup Warranty - This is the form "TabletWarrantyLookup"
This form tells us if the machine is still eligible for warranty repair

2. Add Repair Order - This is the form "RepairOrdersForm"
This form is where the repair needed is entered into the DB. This includes the diagnostic like HDD and Fan need to be repaired by selecting them from the drop down. We're hoping to expand this a little and have a "order parts" portion where the parts can be "ordered." At the end of the day, we print an order parts report and send this to the part company. We will also need to enter in that a part has arrived so that we know the machines that are ready to be repaired. If the machine is being shipped out, the print label button is needed too.

3. New portion needed - Repair Ticket - This is when the machine is being repaired. We need to determine EXACTLY what was done to the machine such as new HDD, RAM, System Board, etc. When this ticket is closed at the completion of a repair, the repair order is closed.

The reports within the current system will still be needed:
-Open Repair Orders
-Repair History by Serial Number

Hopefully, someone is out there that is willing to give me a hand with this! It's something that is much over due in my department.

Thanks!!!
MGM-Dev-Version.mdb

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Unfortunately, you cannot directly convert an Access mdb to a web / browser based application. What you can do is use an mdb as a 'back end' (tables only, maybe some queries) ... and connect a browser front end to it ... using VB.Net and ASP.Net or some other similar technology.

mx

ASKER

Yes. I'm looking for the person that will help me get this done. it's not a complex system.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

If you are looking for a developer, you can check the profiles of some of the Experts. Many are for hire. I would also check those in other zones like VB.Net ... Dreamweaver, etc.

mx

ASKER

Thanks. I'm hoping someone will help get me going here and then we can work to finish it.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Well, what you are talking about is a full blown web application and beyond the scope of a single question, which is what the site is mainly for. We are not really here to design applications for you; instead to help you with specific question along the way.

mx

ASKER

OK....

So, a little googling and some good ol' fashion hard work...

http://www.georgeballane.com/tablets/

Click on Add Repair Order. I want to make issue 1, 2, 3, etc. be drop downs. How do I do that? I'm using access as the DB and VBScript on the ASP pages. Thanks.

Hi there,

As DatabaseMX has stated, you can build web pages to act as the "forms" that you have (you just need to redevelop them in ASP / HTML), which connect to your existing Access database.

If you look through here:
http://www.webwizguide.com/kb/asp_tutorials/default.asp

That can give you an indication of what is required to get your web pages to talk to your Access database. Then it's just a matter of executing queries, and returning these results to the forms.

Have a look through that, build the sample that is shown, and see whether you can get familiar with it. We can help convert one or two forms, but the whole lot might take a while.

Regards,

Rob.

OK, so you've already done that....good. A drop down box is created in this fashion:

<select size='1' name='lst_object'>
<option id="opt_hat" value="Hat">Hat</option>
<option id="opt_gloves" value="Gloves">Gloves</option>
<option id="opt_coat" value="Coat">Coat</option>
</select>

and the selected value of it can be referenced with
lst_object.Value

in the code.

Regards,

Rob.

ASKER

RobSampson,

Please look at my last post.....I've already done this. I've started coverting it myself, but I'm not sure how to do EVERYTHING like drop downs for certain fields. That's where I need the help now.

ASKER

I want the values of the drop down to be populated with the values in a column in a table within the database.

That's fine. I can help from here. You've made a good start. Check my last post for the drop down box syntax, and see how you go.

Regards,

Rob.

OK, so you would need to dynamically create the drop down box, with a For loop and a SQL query.

I'll run up a quick test so that I can show how that's done.....give me a few minutes.

Rob.

ASKER

The code you posted for the drop down box is just basic HTML... how do I populate it from a table within the database?

ASKER

Thanks. I'll be here.

OK, here's a sample ASP page that would load a database called "test.mdb" with a password of "password"

It then pulls a record id and description (both are fields) in a table called Tabe1, and populates a drop down box with the row data.

I'm off for the day, but if you need it explained, I can do that tomorrow.

Regards,

Rob.

<html>
<head>
<title>My First ASP Page</title>
</head>
<body bgcolor="white" text="black">
<%
Response.Write "Select from the drop-down box below:<BR>"
Dim adoCon         'Holds the Database Connection Object
Dim rsOptions    'Holds the recordset for the records in the database
Dim strSQL         'Holds the SQL query to query the database 
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("test.mdb") & ";Jet OLEDB:Database Password=password"
'Create an ADO recordset object
Set rsOptions = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Record_ID,Description FROM Table1;"
'Open the recordset with the SQL query 
rsOptions.Open strSQL, adoCon
Response.Write "<select size='1' name='lst_object'>"
'Loop through the recordset
While Not rsOptions.EOF
    'Write the HTML to display the current record in the recordset
    Response.Write ("<option id='opt_" & rsOptions("Record_ID") & "'  value='" & rsOptions("Record_ID") & "'> " & rsOptions("Description") & "</option>")
    Response.Write ("<br>")
    'Move to the next record in the recordset 
    rsOptions.MoveNext 
Wend
Response.Write "</select>"
'Reset server objects
rsOptions.Close
Set rsOptions = Nothing
Set adoCon = Nothing
%>
</body>
</html>

Open in new window

ASKER

So, I got this to work.... with my database, and tables....

Attached is the code.

I want to add it to a form, so that the value carries through to the databse when the form is saved.

I'll attach the code from the add form in the next post.

<html> 
<head> 
<title>My First ASP Page</title> 
</head> 
<body bgcolor="white" text="black"> 
<% 
Response.Write "Select from the drop-down box below:<BR>" 
Dim adoCon         'Holds the Database Connection Object 
Dim rsOptions    'Holds the recordset for the records in the database 
Dim strSQL         'Holds the SQL query to query the database  
'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 
'Create an ADO recordset object 
Set rsOptions = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsOptions.Open strSQL, adoCon 
Response.Write "<select size='1' name='lst_object'>" 
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
'Reset server objects 
rsOptions.Close 
Set rsOptions = Nothing 
Set adoCon = Nothing 
%> 
</body> 
</html>

Open in new window

ASKER

Add form code.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsPageContent    'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("ID"))

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 

'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("mgmtabletdb.mdb") 

'Set an active connection to the Connection object using DSN connection 
'adoCon.Open "DSN=cms" 

'Create an ADO recordset object 
Set rsPageContent = Server.CreateObject("ADODB.Recordset") 

'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT repairorders.* FROM repairorders WHERE InternalRepair_ID=" & lngRecordNo

'Open the recordset with the SQL query 
rsPageContent.Open strSQL, adoCon
%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><input name="Manufacturer" type="text" id="Manufacturer" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="Last Name" type="text" id="Last Name" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
        <p>Issue # 1 </p>
        </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_1" type="text" id="Issue_ID_1" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2 </p>
      </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_2" type="text" id="Issue_ID_2" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3 </p>
          </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_3" type="text" id="Issue_ID_3" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4 </p>
      </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_4" type="text" id="Issue_ID_4" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5 </p>
      </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_5" type="text" id="Issue_ID_5" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type </div></td>
      <td>&nbsp;</td>
      <td><input name="WarrantyType" type="text" id="WarrantyType" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="173"><div align="right">Entered By   </div></td>
      <td width="10">&nbsp;</td>
      <td width="513"><input name="EnteredBy" type="text" id="EnteredBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsPageContent.Close
Set rsPageContent = Nothing
Set adoCon = Nothing
%>

Open in new window

ASKER

These are the issue IDs.... they are going to be there 5 times, but I just need to get one of them working.

Here's some code for the addrepairorder_add.asp file that will write to the database two of the form's values (obtained by the Request). It uses an INSERT statement, so if you're familiar with those, you can change it to add the required fields.

Regards,

Rob.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsPageContent    'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database

'Read in the record number to be updated
'lngRecordNo = CLng(Request.QueryString("ID"))

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 

'Set an active connection to the Connection object using a DSN-less connection 
'adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb")

'Initialise the strSQL variable with an SQL statement to query the database 
'strSQL = "Insert INTO repairorders(Issue_ID_1) VALUES('" & Request.Form("Issue_ID_1") & "')"
strSQL = "Insert INTO Table1(Record_ID,Description) VALUES('" & Request.Form("Issue_ID_1") & "','" & Request.Form("OtherProblems") & "');"
Response.Write strSQL & "<br>"
On Error Resume Next
adoCon.Execute strSQL
If Err.Number = 0 Then
	Response.Write "Thank you. Your data has been submitted."
Else
	Response.Write Err.Number & " - " & Err.Description
End If
%>

Open in new window

P.S. If you have trouble writing to the database, check this article:
http://support.microsoft.com/kb/175168

Regards,

Rob.

ASKER

I'm not sure if we were talking about the same thing.... first on the form, which I've attached... I need it to make the drop downs for the issue IDs.... using the code that you helped me get to work.

THEN.... we can look at the add form which will add the record to the database.
addrepairorder.htm

Oh, I see....sorry, I went one step ahead. OK, I think this should work, I have added the code at the relevant spot during the form code.

Regards,

Rob.

Oh, and I took out this bit....not sure if you need it....

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders WHERE InternalRepair_ID=" & lngRecordNo

Regards,

Rob.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsPageContent    'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 
'Create an ADO recordset object 
Set rsOptions = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsOptions.Open strSQL, adoCon 

%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><input name="Manufacturer" type="text" id="Manufacturer" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="Last Name" type="text" id="Last Name" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue  </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='lst_object'>" 
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%>
      </td>
    </tr>
    <tr>
      <td><div align="right">
        <p>Issue # 1 </p>
        </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_1" type="text" id="Issue_ID_1" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2 </p>
      </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_2" type="text" id="Issue_ID_2" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3 </p>
          </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_3" type="text" id="Issue_ID_3" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4 </p>
      </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_4" type="text" id="Issue_ID_4" size="15"></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5 </p>
      </div></td>
      <td>&nbsp;</td>
      <td><input name="Issue_ID_5" type="text" id="Issue_ID_5" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type </div></td>
      <td>&nbsp;</td>
      <td><input name="WarrantyType" type="text" id="WarrantyType" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="173"><div align="right">Entered By   </div></td>
      <td width="10">&nbsp;</td>
      <td width="513"><input name="EnteredBy" type="text" id="EnteredBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsPageContent.Close
Set rsPageContent = Nothing
Set adoCon = Nothing
%>

Open in new window

Oh, and change these lines

rsPageContent.Close
Set rsPageContent = Nothing

to this

rsOptions.Close
Set rsOptions= Nothing

Rob.

ASKER

OK.... So I did that and replicated this code 5 times... changed the "name" to be 1st_object, 2nd_object, 3rd_object, etc.

The page only populates the first one. The rest of them are empty. Code is below.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsPageContent    'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 
'Create an ADO recordset object 
Set rsOptions = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsOptions.Open strSQL, adoCon 

%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><input name="Manufacturer" type="text" id="Manufacturer" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="Last Name" type="text" id="Last Name" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue # 1 </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='lst_object'>" 
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%>      </td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='2nd_object'>" 
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3</p>
          </div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='3rd_object'>" 
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='4th_object'>" 
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='5th_object'>" 
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type </div></td>
      <td>&nbsp;</td>
      <td><input name="WarrantyType" type="text" id="WarrantyType" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="173"><div align="right">Entered By   </div></td>
      <td width="10">&nbsp;</td>
      <td width="513"><input name="EnteredBy" type="text" id="EnteredBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsOptions.Close
Set rsOptions = Nothing
Set adoCon = Nothing
%>

Open in new window

Add

rsOptions.MoveFirst

before each of these lines
Response.Write "</select>"

Regards,

Rob.

ASKER

How do I default the first value to be blank?

Underneath each of these lines
Response.Write "<select size='1' name='lst_object'>"

you can add this
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")

That will create a "dummy" option, which is blank, at the top.

Regards,

Rob.

ASKER

Below is the code for the ADD to the database. I changed the field names to those that are auto populated... its not working... I'm guessing something needs to be changed because of the way it's populating.

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
rsAddGame.AddNew 

'Add a new record to the recordset
rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
rsAddGame.Fields("LastName") = Request.Form("LastName")
rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
rsAddGame.Fields("1st_object") = Request.Form("1st_object")
rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
rsAddGame.Fields("4th_object") = Request.Form("4th_object")
rsAddGame.Fields("5th_object") = Request.Form("5th_object")
rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
rsAddGame.Fields("RMA") = Request.Form("RMA")
rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")
rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
rsAddGame.Fields("Status") = Request.Form("Status")

'Write the updated recordset to the database
rsAddGame.Update 
 
'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Redirect to the orders.asp page
Response.Redirect "recordupdated.htm"
%>

Open in new window

Hmmm, above this

rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
rsAddGame.Fields("LastName") = Request.Form("LastName")
rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
rsAddGame.Fields("1st_object") = Request.Form("1st_object")
rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
rsAddGame.Fields("4th_object") = Request.Form("4th_object")
rsAddGame.Fields("5th_object") = Request.Form("5th_object")
rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
rsAddGame.Fields("RMA") = Request.Form("RMA")
rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")
rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
rsAddGame.Fields("Status") = Request.Form("Status")

If you put this

Respone.Write "DateShipped: " & Request.Form("DateShipped") & "<br>"
Respone.Write "Manufacturer" & Request.Form("Manufacturer") & "<br>"
Respone.Write "CompanyRepName" & Request.Form("CompanyRepName") & "<br>"
Respone.Write "LastName" & Request.Form("LastName") & "<br>"
Respone.Write "SerialNumber" & Request.Form("SerialNumber") & "<br>"
Respone.Write "1st_object" & Request.Form("1st_object") & "<br>"
Respone.Write "2nd_object" & Request.Form("2nd_object") & "<br>"
Respone.Write "3rd_object" & Request.Form("3rd_object") & "<br>"
Respone.Write "4th_object" & Request.Form("4th_object") & "<br>"
Respone.Write "5th_object" & Request.Form("5th_object") & "<br>"
Respone.Write "WarrantyType" & Request.Form("WarrantyType") & "<br>"
Respone.Write "EnteredBy" & Request.Form("EnteredBy") & "<br>"
Respone.Write "RMA" & Request.Form("RMA") & "<br>"
Respone.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
Respone.Write "InternalNotes" & Request.Form("InternalNotes") & "<br>"
Respone.Write "Status" & Request.Form("Status") & "<br>"

It should write the form values to the page for you to see what it is reading. Does it show you the correct values?

You need to comment out the update statements for now, if they're causing issues.

Also comment out the redirect so you can read the text.

Regards,

Rob.

ASKER

Microsoft VBScript runtime error '800a01a8'

Object required: 'Respone'

/tablets/addrepairorder_add.asp, line 33

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
rsAddGame.AddNew 

Respone.Write "DateShipped" & Request.Form("DateShipped") & "<br>"
Respone.Write "Manufacturer" & Request.Form("Manufacturer") & "<br>"
Respone.Write "CompanyRepName" & Request.Form("CompanyRepName") & "<br>"
Respone.Write "LastName" & Request.Form("LastName") & "<br>"
Respone.Write "SerialNumber" & Request.Form("SerialNumber") & "<br>"
Respone.Write "1st_object" & Request.Form("1st_object") & "<br>"
Respone.Write "2nd_object" & Request.Form("2nd_object") & "<br>"
Respone.Write "3rd_object" & Request.Form("3rd_object") & "<br>"
Respone.Write "4th_object" & Request.Form("4th_object") & "<br>"
Respone.Write "5th_object" & Request.Form("5th_object") & "<br>"
Respone.Write "WarrantyType" & Request.Form("WarrantyType") & "<br>"
Respone.Write "EnteredBy" & Request.Form("EnteredBy") & "<br>"
Respone.Write "RMA" & Request.Form("RMA") & "<br>"
Respone.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
Respone.Write "InternalNotes" & Request.Form("InternalNotes") & "<br>"
Respone.Write "Status" & Request.Form("Status") & "<br>"


'Add a new record to the recordset
'rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
'rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
'rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
'rsAddGame.Fields("LastName") = Request.Form("LastName")
'rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
'rsAddGame.Fields("1st_object") = Request.Form("1st_object")
'rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
'rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
'rsAddGame.Fields("4th_object") = Request.Form("4th_object")
'rsAddGame.Fields("5th_object") = Request.Form("5th_object")
'rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
'rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
'rsAddGame.Fields("RMA") = Request.Form("RMA")
'rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")
'rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
'rsAddGame.Fields("Status") = Request.Form("Status")

'Write the updated recordset to the database
rsAddGame.Update 
 
'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Redirect to the orders.asp page
Response.Redirect "recordupdated.htm"
%>

Open in new window

ASKER

Are you interested in some for-hire work to get this system setup where it should be? This is for my job, and I'm spending way too much time on it. We KNOW exactly what we want with it, but I have some money in my budget to hire someone for this.

Let me know.

Oh bugger, I forgot the "s". Change each
Respone.Write

to
Response.Write

Re your hire offer....ASP is not a strong point of mine. I fly a bit blind with it sometimes.....I'm sure you could find someone much better suited in the ASP zone. I will help you here as much as I can though, if you still require my assistance.

Regards,

Rob.

ASKER

I would love to continue here as long as possible. I will look at this later today and post a reply. Thanks.

ASKER

So I changed this to response.... The record is being created in the DB but all of the fields are blank. Here's the code.

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
rsAddGame.AddNew 

Response.Write "DateShipped" & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer" & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName" & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName" & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber" & Request.Form("SerialNumber") & "<br>"
Response.Write "1st_object" & Request.Form("1st_object") & "<br>"
Response.Write "2nd_object" & Request.Form("2nd_object") & "<br>"
Response.Write "3rd_object" & Request.Form("3rd_object") & "<br>"
Response.Write "4th_object" & Request.Form("4th_object") & "<br>"
Response.Write "5th_object" & Request.Form("5th_object") & "<br>"
Response.Write "WarrantyType" & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy" & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA" & Request.Form("RMA") & "<br>"
Response.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
Response.Write "InternalNotes" & Request.Form("InternalNotes") & "<br>"
Response.Write "Status" & Request.Form("Status") & "<br>"


'Add a new record to the recordset
'rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
'rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
'rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
'rsAddGame.Fields("LastName") = Request.Form("LastName")
'rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
'rsAddGame.Fields("1st_object") = Request.Form("1st_object")
'rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
'rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
'rsAddGame.Fields("4th_object") = Request.Form("4th_object")
'rsAddGame.Fields("5th_object") = Request.Form("5th_object")
'rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
'rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
'rsAddGame.Fields("RMA") = Request.Form("RMA")
'rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")
'rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
'rsAddGame.Fields("Status") = Request.Form("Status")

'Write the updated recordset to the database
rsAddGame.Update 
 
'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Redirect to the orders.asp page
Response.Redirect "recordupdated.htm"
%>

Open in new window

It's still creating the blank record because you've got AddNew and Update still running. You can comment those out for now.

You should also comment out the Response.Redirect "recordupdated.htm" for now, so you can see the output from the Response.Write lines.

So, when you do that, do you see the data displayed on screen for each field as you would like it in the database? There may be something missing. If there is nothing missing, and it all looks OK, I'll add some error checking to the code to see if we can get it to produce an error.

Regards,

Rob.

ASKER

The data is displayed on the screen, but the record is still blank.

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
'rsAddGame.AddNew 

Response.Write "DateShipped" & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer" & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName" & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName" & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber" & Request.Form("SerialNumber") & "<br>"
Response.Write "1st_object" & Request.Form("1st_object") & "<br>"
Response.Write "2nd_object" & Request.Form("2nd_object") & "<br>"
Response.Write "3rd_object" & Request.Form("3rd_object") & "<br>"
Response.Write "4th_object" & Request.Form("4th_object") & "<br>"
Response.Write "5th_object" & Request.Form("5th_object") & "<br>"
Response.Write "WarrantyType" & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy" & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA" & Request.Form("RMA") & "<br>"
Response.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
Response.Write "InternalNotes" & Request.Form("InternalNotes") & "<br>"
Response.Write "Status" & Request.Form("Status") & "<br>"


'Add a new record to the recordset
'rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
'rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
'rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
'rsAddGame.Fields("LastName") = Request.Form("LastName")
'rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
'rsAddGame.Fields("1st_object") = Request.Form("1st_object")
'rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
'rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
'rsAddGame.Fields("4th_object") = Request.Form("4th_object")
'rsAddGame.Fields("5th_object") = Request.Form("5th_object")
'rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
'rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
'rsAddGame.Fields("RMA") = Request.Form("RMA")
'rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")
'rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
'rsAddGame.Fields("Status") = Request.Form("Status")

'Write the updated recordset to the database
'rsAddGame.Update 
 
'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Redirect to the orders.asp page
'Response.Redirect "recordupdated.htm"
%>

Open in new window

Hmmm, OK, so run this.....it has a bit of error checking. Let me know what message you get on the page.

Regards,

Rob.

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
'rsAddGame.AddNew 

Response.Write "DateShipped" & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer" & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName" & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName" & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber" & Request.Form("SerialNumber") & "<br>"
Response.Write "1st_object" & Request.Form("1st_object") & "<br>"
Response.Write "2nd_object" & Request.Form("2nd_object") & "<br>"
Response.Write "3rd_object" & Request.Form("3rd_object") & "<br>"
Response.Write "4th_object" & Request.Form("4th_object") & "<br>"
Response.Write "5th_object" & Request.Form("5th_object") & "<br>"
Response.Write "WarrantyType" & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy" & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA" & Request.Form("RMA") & "<br>"
Response.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
Response.Write "InternalNotes" & Request.Form("InternalNotes") & "<br>"
Response.Write "Status" & Request.Form("Status") & "<br>"


'Add a new record to the recordset
rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
rsAddGame.Fields("LastName") = Request.Form("LastName")
rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
rsAddGame.Fields("1st_object") = Request.Form("1st_object")
rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
rsAddGame.Fields("4th_object") = Request.Form("4th_object")
rsAddGame.Fields("5th_object") = Request.Form("5th_object")
rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
rsAddGame.Fields("RMA") = Request.Form("RMA")
rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")
rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
rsAddGame.Fields("Status") = Request.Form("Status")

'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Write the updated recordset to the database
On Error Resume Next
rsAddGame.Update 
If Err.Number = 0 Then
	Response.Write "<BR><BR>Data has been added to the database."
	'Redirect to the orders.asp page
	'Response.Redirect "recordupdated.htm"
Else
	Response.Write "<BR><BR>Error writing data to the database.<BR>Error: " & Err.Number & "<BR>Description: " & Err.Description
End If
%>

Open in new window

ASKER

DateShipped10/10/2010
Manufacturer12345
CompanyRepNamelinda
LastName
SerialNumber0123456789
1st_object
2nd_object1
3rd_object2
4th_object2
5th_object4
WarrantyType132
EnteredByMCHIT
RMA123
Repaired By
InternalNotes132123
StatusOpen

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/tablets/addrepairorder_add.asp, line 57

Oh I see....in the actual form itself (not the add page), you have this for the last name field:
<td><input name="Last Name" type="text" id="Last Name" size="15"></td>

but in the Add code, you're trying to reference LastName, not Last Name. It really is that picky!

So, the best thing to do would be to change
<td><input name="Last Name" type="text" id="Last Name" size="15"></td>

to this
<td><input name="LastName" type="text" id="LastName" size="15"></td>

*and* make sure that the Field Name *in the database* is LastName as well.

You're also not inserting the OtherProblems data into the record, so you may want to add
rsAddGame.Fields("OtherProblems") = Request.Form("OtherProblems")

to the Add code.

Regards,

Rob.

ASKER

OK... I think we're getting there....

DateShipped10/10/2011
Manufacturermanuf
CompanyRepNamerepname
LastNamelastname
SerialNumber0123456798
1st_object
2nd_object3
3rd_object3
4th_object4
5th_object2
WarrantyTypewarranty
EnteredByentered by
RMArma
Repaired By
InternalNotesinternal notes
StatusOpen
OtherProblemsother issues

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/tablets/addrepairorder_add.asp, line 58
-----------------

Add code below.

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
'rsAddGame.AddNew 

Response.Write "DateShipped" & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer" & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName" & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName" & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber" & Request.Form("SerialNumber") & "<br>"
Response.Write "1st_object" & Request.Form("1st_object") & "<br>"
Response.Write "2nd_object" & Request.Form("2nd_object") & "<br>"
Response.Write "3rd_object" & Request.Form("3rd_object") & "<br>"
Response.Write "4th_object" & Request.Form("4th_object") & "<br>"
Response.Write "5th_object" & Request.Form("5th_object") & "<br>"
Response.Write "WarrantyType" & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy" & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA" & Request.Form("RMA") & "<br>"
Response.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
Response.Write "InternalNotes" & Request.Form("InternalNotes") & "<br>"
Response.Write "Status" & Request.Form("Status") & "<br>"
Response.Write "OtherProblems" & Request.Form("OtherProblems") & "<br>"


'Add a new record to the recordset
rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
rsAddGame.Fields("LastName") = Request.Form("LastName")
rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
rsAddGame.Fields("1st_object") = Request.Form("1st_object")
rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
rsAddGame.Fields("4th_object") = Request.Form("4th_object")
rsAddGame.Fields("5th_object") = Request.Form("5th_object")
rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
rsAddGame.Fields("RMA") = Request.Form("RMA")
rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")
rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
rsAddGame.Fields("Status") = Request.Form("Status")
rsAddGame.Fields("OtherProblems") = Request.Form("OtherProblems")

'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Write the updated recordset to the database
On Error Resume Next
rsAddGame.Update 
If Err.Number = 0 Then
	Response.Write "<BR><BR>Data has been added to the database."
	'Redirect to the orders.asp page
	'Response.Redirect "recordupdated.htm"
Else
	Response.Write "<BR><BR>Error writing data to the database.<BR>Error: " & Err.Number & "<BR>Description: " & Err.Description
End If
%>

Open in new window

ASKER

it looks like it didnt take 1st_object. I double checked them.... i don't see an error....

Here's the form code.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsPageContent    'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 
'Create an ADO recordset object 
Set rsOptions = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsOptions.Open strSQL, adoCon 

%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><input name="Manufacturer" type="text" id="Manufacturer" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="LastName" type="text" id="LastName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue # 1 </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='lst_object'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%>      </td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsOptions.MoveFirst
Response.Write "<select size='1' name='2nd_object'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3</p>
          </div></td>
      <td>&nbsp;</td>
      <td><%
rsOptions.MoveFirst
Response.Write "<select size='1' name='3rd_object'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsOptions.MoveFirst
Response.Write "<select size='1' name='4th_object'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsOptions.MoveFirst
Response.Write "<select size='1' name='5th_object'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsOptions.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsOptions("Issue_ID") & "'  value='" & rsOptions("Issue_ID") & "'> " & rsOptions("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsOptions.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type </div></td>
      <td>&nbsp;</td>
      <td><input name="WarrantyType" type="text" id="WarrantyType" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="173"><div align="right">Entered By   </div></td>
      <td width="10">&nbsp;</td>
      <td width="513"><input name="EnteredBy" type="text" id="EnteredBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsOptions.Close
Set rsOptions = Nothing
Set adoCon = Nothing
%>

Open in new window

Oh, in the form code, the "name" is
Response.Write "<select size='1' name='lst_object'>"

which is LST_OBJECT, not 1ST_OBJECT

Change that and see what you get.

And change the Response.Write section to this to make it more readable:

Response.Write "DateShipped: " & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer: " & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName: " & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName: " & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber: " & Request.Form("SerialNumber") & "<br>"
Response.Write "1st_object: " & Request.Form("1st_object") & "<br>"
Response.Write "2nd_object: " & Request.Form("2nd_object") & "<br>"
Response.Write "3rd_object: " & Request.Form("3rd_object") & "<br>"
Response.Write "4th_object: " & Request.Form("4th_object") & "<br>"
Response.Write "5th_object: " & Request.Form("5th_object") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"
Response.Write "WarrantyType: " & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy: " & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA: " & Request.Form("RMA") & "<br>"
Response.Write "Repaired By: " & Request.Form("Repaired By") & "<br>"
Response.Write "InternalNotes: " & Request.Form("InternalNotes") & "<br>"
Response.Write "Status: " & Request.Form("Status") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"

Regards,

Rob.

ASKER

DateShipped: 10/10/2010
Manufacturer: 12345
CompanyRepName: linda
LastName: BALLANE
SerialNumber: 0123456789
1st_object: 2
2nd_object: 2
3rd_object: 1
4th_object: 2
5th_object: 1
OtherProblems: 123
WarrantyType: 132
EnteredBy: 123
RMA: 123456
Repaired By:
InternalNotes: 132123
Status: Open
OtherProblems: 123

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/tablets/addrepairorder_add.asp, line 59

OK, again, with Repaired By:
Response.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
and
rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")

The form code has
<input name="RepairedBy" type="text" id="RepairedBy">

which has no space, so change
Response.Write "Repaired By" & Request.Form("Repaired By") & "<br>"
and
rsAddGame.Fields("Repaired By") = Request.Form("Repaired By")

to
Response.Write "RepairedBy" & Request.Form("RepairedBy") & "<br>"
and
rsAddGame.Fields("RepairedBy") = Request.Form("RepairedBy")

and again, make sure that the *field name* in the Database is RepairedBy with no space as well. Then it work correctly.

Regards,

Rob.

ASKER

Made those changes....

DateShipped: 10/10/2010
Manufacturer: 12345
CompanyRepName: linda
LastName: BALLANE
SerialNumber: 0123456789
1st_object: 2
2nd_object: 1
3rd_object: 1
4th_object: 1
5th_object: 1
OtherProblems: 123
WarrantyType: 132
EnteredBy: 123
RMA: 123456
RepairedBy: 12
InternalNotes: 12345
Status: Open
OtherProblems: 123

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/tablets/addrepairorder_add.asp, line 59

Line 59 in the code is:

rsAddGame.Fields("1st_object") = Request.Form("1st_object")

Add code below.

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
'rsAddGame.AddNew 

Response.Write "DateShipped: " & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer: " & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName: " & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName: " & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber: " & Request.Form("SerialNumber") & "<br>"
Response.Write "1st_object: " & Request.Form("1st_object") & "<br>"
Response.Write "2nd_object: " & Request.Form("2nd_object") & "<br>"
Response.Write "3rd_object: " & Request.Form("3rd_object") & "<br>"
Response.Write "4th_object: " & Request.Form("4th_object") & "<br>"
Response.Write "5th_object: " & Request.Form("5th_object") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"
Response.Write "WarrantyType: " & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy: " & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA: " & Request.Form("RMA") & "<br>"
Response.Write "RepairedBy: " & Request.Form("RepairedBy") & "<br>"
Response.Write "InternalNotes: " & Request.Form("InternalNotes") & "<br>"
Response.Write "Status: " & Request.Form("Status") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"


'Add a new record to the recordset
rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
rsAddGame.Fields("LastName") = Request.Form("LastName")
rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
rsAddGame.Fields("1st_object") = Request.Form("1st_object")
rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
rsAddGame.Fields("4th_object") = Request.Form("4th_object")
rsAddGame.Fields("5th_object") = Request.Form("5th_object")
rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
rsAddGame.Fields("RMA") = Request.Form("RMA")
rsAddGame.Fields("RepairedBy") = Request.Form("RepairedBy")
rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
rsAddGame.Fields("Status") = Request.Form("Status")
rsAddGame.Fields("OtherProblems") = Request.Form("OtherProblems")

'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Write the updated recordset to the database
On Error Resume Next
rsAddGame.Update 
If Err.Number = 0 Then
	Response.Write "<BR><BR>Data has been added to the database."
	'Redirect to the orders.asp page
	'Response.Redirect "recordupdated.htm"
Else
	Response.Write "<BR><BR>Error writing data to the database.<BR>Error: " & Err.Number & "<BR>Description: " & Err.Description
End If
%>

Open in new window

ASKER

OH WAIT!!!

That would be because in the DB.... the field names are Issue_ID_1, Issue_ID_2, etc.... where do I change that to be right in the code?

Ah, good. Glad you spotted that! :-)

On the form code, change these (substituting the relevant numbers):
Response.Write "<select size='1' name='1st_object'>"

to these (substituting the relevant numbers):
Response.Write "<select size='1' name='Issue_ID_1'>"

and in the add code, change
Response.Write "1st_object: " & Request.Form("1st_object") & "<br>"
Response.Write "2nd_object: " & Request.Form("2nd_object") & "<br>"
Response.Write "3rd_object: " & Request.Form("3rd_object") & "<br>"
Response.Write "4th_object: " & Request.Form("4th_object") & "<br>"
Response.Write "5th_object: " & Request.Form("5th_object") & "<br>"

to this
Response.Write "Issue_ID_1: " & Request.Form("Issue_ID_1") & "<br>"
Response.Write "Issue_ID_2: " & Request.Form("Issue_ID_2") & "<br>"
Response.Write "Issue_ID_3: " & Request.Form("Issue_ID_3") & "<br>"
Response.Write "Issue_ID_4: " & Request.Form("Issue_ID_4") & "<br>"
Response.Write "Issue_ID_5: " & Request.Form("Issue_ID_5") & "<br>"

and change
rsAddGame.Fields("1st_object") = Request.Form("1st_object")
rsAddGame.Fields("2nd_object") = Request.Form("2nd_object")
rsAddGame.Fields("3rd_object") = Request.Form("3rd_object")
rsAddGame.Fields("4th_object") = Request.Form("4th_object")
rsAddGame.Fields("5th_object") = Request.Form("5th_object")

to this
rsAddGame.Fields("Issue_ID_1") = Request.Form("Issue_ID_1")
rsAddGame.Fields("Issue_ID_2") = Request.Form("Issue_ID_2")
rsAddGame.Fields("Issue_ID_3") = Request.Form("Issue_ID_3")
rsAddGame.Fields("Issue_ID_4") = Request.Form("Issue_ID_4")
rsAddGame.Fields("Issue_ID_5") = Request.Form("Issue_ID_5")

Regards,

Rob.

ASKER

This is KINDA success.... At least we got to an error message.

DateShipped: 10/10/2010
Manufacturer: 12345
CompanyRepName: linda
LastName: BALLANE
SerialNumber: 0123456789
Issue_ID_1: 2
Issue_ID_2: 1
Issue_ID_3: 1
Issue_ID_4: 1
Issue_ID_5: 1
OtherProblems: 123
WarrantyType: 132
EnteredBy: 123
RMA: 123
RepairedBy: 123123
InternalNotes: 12345
Status: Open
OtherProblems: 123

Error writing data to the database.
Error: 424
Description: Object required

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
'rsAddGame.AddNew 

Response.Write "DateShipped: " & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer: " & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName: " & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName: " & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber: " & Request.Form("SerialNumber") & "<br>"
Response.Write "Issue_ID_1: " & Request.Form("Issue_ID_1") & "<br>"
Response.Write "Issue_ID_2: " & Request.Form("Issue_ID_2") & "<br>"
Response.Write "Issue_ID_3: " & Request.Form("Issue_ID_3") & "<br>"
Response.Write "Issue_ID_4: " & Request.Form("Issue_ID_4") & "<br>"
Response.Write "Issue_ID_5: " & Request.Form("Issue_ID_5") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"
Response.Write "WarrantyType: " & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy: " & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA: " & Request.Form("RMA") & "<br>"
Response.Write "RepairedBy: " & Request.Form("RepairedBy") & "<br>"
Response.Write "InternalNotes: " & Request.Form("InternalNotes") & "<br>"
Response.Write "Status: " & Request.Form("Status") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"


'Add a new record to the recordset
rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
rsAddGame.Fields("LastName") = Request.Form("LastName")
rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
rsAddGame.Fields("Issue_ID_1") = Request.Form("Issue_ID_1")
rsAddGame.Fields("Issue_ID_2") = Request.Form("Issue_ID_2")
rsAddGame.Fields("Issue_ID_3") = Request.Form("Issue_ID_3")
rsAddGame.Fields("Issue_ID_4") = Request.Form("Issue_ID_4")
rsAddGame.Fields("Issue_ID_5") = Request.Form("Issue_ID_5")
rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
rsAddGame.Fields("RMA") = Request.Form("RMA")
rsAddGame.Fields("RepairedBy") = Request.Form("RepairedBy")
rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
rsAddGame.Fields("Status") = Request.Form("Status")
rsAddGame.Fields("OtherProblems") = Request.Form("OtherProblems")

'Reset server objects
'rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

'Write the updated recordset to the database
On Error Resume Next
rsAddGame.Update 
If Err.Number = 0 Then
	Response.Write "<BR><BR>Data has been added to the database."
	'Redirect to the orders.asp page
	'Response.Redirect "recordupdated.htm"
Else
	Response.Write "<BR><BR>Error writing data to the database.<BR>Error: " & Err.Number & "<BR>Description: " & Err.Description
End If
%>

Open in new window

Oh, I was destroying rsAddGame before the Update.....try this.

<%@ LANGUAGE = VBScript %> 

<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddGame   'Holds the recordset for the new record to be added
Dim strSQL               'Holds the SQL query to query the database 

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MGMTabletDB.mdb")

'Create an ADO recordset object
Set rsAddGame= Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT repairorders.* FROM repairorders;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddGame.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddGame.LockType = 3

'Open the recordset with the SQL query 
rsAddGame.Open strSQL, adoCon 

'Tell the recordset we are adding a new record to it
rsAddGame.AddNew 

Response.Write "DateShipped: " & Request.Form("DateShipped") & "<br>"
Response.Write "Manufacturer: " & Request.Form("Manufacturer") & "<br>"
Response.Write "CompanyRepName: " & Request.Form("CompanyRepName") & "<br>"
Response.Write "LastName: " & Request.Form("LastName") & "<br>"
Response.Write "SerialNumber: " & Request.Form("SerialNumber") & "<br>"
Response.Write "Issue_ID_1: " & Request.Form("Issue_ID_1") & "<br>"
Response.Write "Issue_ID_2: " & Request.Form("Issue_ID_2") & "<br>"
Response.Write "Issue_ID_3: " & Request.Form("Issue_ID_3") & "<br>"
Response.Write "Issue_ID_4: " & Request.Form("Issue_ID_4") & "<br>"
Response.Write "Issue_ID_5: " & Request.Form("Issue_ID_5") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"
Response.Write "WarrantyType: " & Request.Form("WarrantyType") & "<br>"
Response.Write "EnteredBy: " & Request.Form("EnteredBy") & "<br>"
Response.Write "RMA: " & Request.Form("RMA") & "<br>"
Response.Write "RepairedBy: " & Request.Form("RepairedBy") & "<br>"
Response.Write "InternalNotes: " & Request.Form("InternalNotes") & "<br>"
Response.Write "Status: " & Request.Form("Status") & "<br>"
Response.Write "OtherProblems: " & Request.Form("OtherProblems") & "<br>"


'Add a new record to the recordset
rsAddGame.Fields("DateShipped") = Request.Form("DateShipped")
rsAddGame.Fields("Manufacturer") = Request.Form("Manufacturer")
rsAddGame.Fields("CompanyRepName") = Request.Form("CompanyRepName")
rsAddGame.Fields("LastName") = Request.Form("LastName")
rsAddGame.Fields("SerialNumber") = Request.Form("SerialNumber")
rsAddGame.Fields("Issue_ID_1") = Request.Form("Issue_ID_1")
rsAddGame.Fields("Issue_ID_2") = Request.Form("Issue_ID_2")
rsAddGame.Fields("Issue_ID_3") = Request.Form("Issue_ID_3")
rsAddGame.Fields("Issue_ID_4") = Request.Form("Issue_ID_4")
rsAddGame.Fields("Issue_ID_5") = Request.Form("Issue_ID_5")
rsAddGame.Fields("WarrantyType") = Request.Form("WarrantyType")
rsAddGame.Fields("EnteredBy") = Request.Form("EnteredBy")
rsAddGame.Fields("RMA") = Request.Form("RMA")
rsAddGame.Fields("RepairedBy") = Request.Form("RepairedBy")
rsAddGame.Fields("InternalNotes") = Request.Form("InternalNotes")
rsAddGame.Fields("Status") = Request.Form("Status")
rsAddGame.Fields("OtherProblems") = Request.Form("OtherProblems")

'Write the updated recordset to the database
On Error Resume Next
rsAddGame.Update 
If Err.Number = 0 Then
	Response.Write "<BR><BR>Data has been added to the database."
	'Redirect to the orders.asp page
	'Response.Redirect "recordupdated.htm"
Else
	Response.Write "<BR><BR>Error writing data to the database.<BR>Error: " & Err.Number & "<BR>Description: " & Err.Description
	Err.Clear
	On Error GoTo 0
End If

'Reset server objects
rsAddGame.Close
Set rsAddGame = Nothing
Set adoCon = Nothing

%>

Open in new window

ASKER

YEY!!! I also checked the DB and it's there....

DateShipped: 10/10/2010
Manufacturer: 12345
CompanyRepName: linda
LastName: BALLANE
SerialNumber: 0123456789
Issue_ID_1: 2
Issue_ID_2: 1
Issue_ID_3: 1
Issue_ID_4: 1
Issue_ID_5: 1
OtherProblems: 123
WarrantyType: 132
EnteredBy: 123
RMA: 123
RepairedBy: 123123
InternalNotes: 12345
Status: Open
OtherProblems: 123

Data has been added to the database.
--------------------------------

Great... so now I need to get some of the other fields to also have drop downs... Like Manufacturer needs to be a drop down...

Table is "Manufacturer" ID # is Manu_ID and the column that shoudl populate the drop down is "Manufacturer"

I'm afraid to mess with this thing!

Ha ha, it's OK, just keep a backup before we change each page....

Try this for your form code. To add another drop down, I've created another recordset for the Manufacturers table with this

'Create an ADO recordset object
Set rsManufacturers = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Manu_ID,Manufacturer FROM Manufacturer;"
'Open the recordset with the SQL query
rsManufacturers.Open strSQL, adoCon

Then replaced the Input box with this:
<%
Response.Write "<select size='1' name='Manufacturer'>"
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset
While Not rsIssues.EOF
'Write the HTML to display the current record in the recordset
Response.Write ("<option id='opt_" & rsManufacturers("Manu_ID") & "' value='" & rsManufacturers("Manufacturer") & "'> " & rsManufacturers("Manufacturers") & "</option>")
Response.Write ("<br>")
'Move to the next record in the recordset
rsManufacturers.MoveNext
Wend
Response.Write "</select>"
%>

and finally added
rsManufacturers.Close
Set rsManufacturers = Nothing

That should be it.

Regards,

Rob.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsIssues    'Holds the recordset for the records in the database
Dim rsManufacturers 'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 

'Create an ADO recordset object 
Set rsIssues = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsIssues.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsManufacturers = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Manu_ID,Manufacturer FROM Manufacturer;" 
'Open the recordset with the SQL query  
rsManufacturers.Open strSQL, adoCon 

%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='Manufacturer'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsManufacturers("Manu_ID") & "' value='" & rsManufacturers("Manufacturer") & "'> " & rsManufacturers("Manufacturers") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsManufacturers.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="LastName" type="text" id="LastName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue # 1 </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='Issue_ID_1'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>      </td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_2'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3</p>
          </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_3'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_4'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_5'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type </div></td>
      <td>&nbsp;</td>
      <td><input name="WarrantyType" type="text" id="WarrantyType" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="173"><div align="right">Entered By   </div></td>
      <td width="10">&nbsp;</td>
      <td width="513"><input name="EnteredBy" type="text" id="EnteredBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsIssues.Close
Set rsIssues = Nothing
Set adoCon = Nothing
%>

Open in new window

ASKER

I found one typo in there... but I fixed it....

Here's the code that you have... I'm also posting a capture of the form page since it's not showing the entire form any longer.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsIssues    'Holds the recordset for the records in the database
Dim rsManufacturers 'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 

'Create an ADO recordset object 
Set rsIssues = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsIssues.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsManufacturers = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Manu_ID,Manufacturer FROM Manufacturer;" 
'Open the recordset with the SQL query  
rsManufacturers.Open strSQL, adoCon 

%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='Manufacturer'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsManufacturers("Manu_ID") & "' value='" & rsManufacturers("Manufacturer") & "'> " & rsManufacturers("Manufacturer") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsManufacturers.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="LastName" type="text" id="LastName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue # 1 </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='Issue_ID_1'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>      </td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_2'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3</p>
          </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_3'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_4'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_5'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type </div></td>
      <td>&nbsp;</td>
      <td><input name="WarrantyType" type="text" id="WarrantyType" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="173"><div align="right">Entered By   </div></td>
      <td width="10">&nbsp;</td>
      <td width="513"><input name="EnteredBy" type="text" id="EnteredBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsIssues.Close
Set rsIssues = Nothing
Set adoCon = Nothing
%>

Open in new window

Oh DOH! Copy and paste error. In this bit

<%
Response.Write "<select size='1' name='Manufacturer'>"
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset
While Not rsIssues.EOF
'Write the HTML to display the current record in the recordset
Response.Write ("<option id='opt_" & rsManufacturers("Manu_ID") & "' value='" & rsManufacturers("Manufacturer") & "'> " & rsManufacturers("Manufacturer") & "</option>")
Response.Write ("<br>")
'Move to the next record in the recordset
rsManufacturers.MoveNext
Wend
Response.Write "</select>"
%>

Notice that it's got
While Not rsIssues.EOF

Ooops.....wrong recordset.

Change that line to this
While Not rsManufacturers.EOF

and it should be OK.

Rob.

ASKER

sweet...it worked! Attached updated code for you....

OK... my goal would be to get this page done today so that there is no need to come back to it.... I've got 2 more that need drop downs....

warranty type & entered by

Table: WarrantyType
Warr_ID
WarrantyType

Table: Employee
Empl_ID
Employee

If we can get these done... we're home free for this page for tonight (I'm Eastern time so I'm beat.... it's 1 am here.)

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsIssues    'Holds the recordset for the records in the database
Dim rsManufacturers 'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 

'Create an ADO recordset object 
Set rsIssues = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsIssues.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsManufacturers = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Manu_ID,Manufacturer FROM Manufacturer;" 
'Open the recordset with the SQL query  
rsManufacturers.Open strSQL, adoCon 

%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='Manufacturer'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsManufacturers.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsManufacturers("Manu_ID") & "' value='" & rsManufacturers("Manufacturer") & "'> " & rsManufacturers("Manufacturer") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsManufacturers.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="LastName" type="text" id="LastName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue # 1 </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='Issue_ID_1'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>      </td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_2'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3</p>
          </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_3'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_4'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_5'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type </div></td>
      <td>&nbsp;</td>
      <td><input name="WarrantyType" type="text" id="WarrantyType" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="173"><div align="right">Entered By   </div></td>
      <td width="10">&nbsp;</td>
      <td width="513"><input name="EnteredBy" type="text" id="EnteredBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsIssues.Close
Set rsIssues = Nothing
Set adoCon = Nothing
%>

Open in new window

Wow, you need some sleep ;-)

It's nearly 7pm here....so I've got a while left to the new year!

This code should do the other drop down boxes.

Regards,

Rob.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsIssues    'Holds the recordset for the records in the database
Dim rsManufacturers 'Holds the recordset for the records in the database
Dim rsWarrantyType 'Holds the recordset for the records in the database
Dim rsEnteredBy 'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 

'Create an ADO recordset object 
Set rsIssues = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsIssues.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsManufacturers = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Manu_ID,Manufacturer FROM Manufacturer;" 
'Open the recordset with the SQL query  
rsManufacturers.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsWarrantyType = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Warr_ID,WarrantyType FROM WarrantyType;" 
'Open the recordset with the SQL query  
rsWarrantyType.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsEnteredBy = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Empl_ID,Employee FROM Employee;" 
'Open the recordset with the SQL query  
rsEnteredBy.Open strSQL, adoCon 
%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='Manufacturer'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsManufacturers.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsManufacturers("Manu_ID") & "' value='" & rsManufacturers("Manufacturer") & "'> " & rsManufacturers("Manufacturer") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsManufacturers.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="LastName" type="text" id="LastName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue # 1 </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='Issue_ID_1'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>      </td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_2'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3</p>
          </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_3'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_4'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_5'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>
      <%
Response.Write "<select size='1' name='WarrantyType'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsWarrantyType.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsWarrantyType("Warr_ID") & "' value='" & rsWarrantyType("WarrantyType") & "'> " & rsWarrantyType("WarrantyType") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsWarrantyType.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>
<%
Response.Write "<select size='1' name='EnteredBy'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsEnteredBy.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsEnteredBy("Empl_ID") & "' value='" & rsEnteredBy("Employee") & "'> " & rsEnteredBy("Employee") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsEnteredBy.MoveNext  
Wend 
Response.Write "</select>" 
%>
      </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsIssues.Close
rsManufacturers.Close
rsWarrantyType.Close
rsEnteredBy.Close
Set rsIssues = Nothing
Set rsManufacturers = Nothing
Set rsWarrantyType = Nothing
Set rsEnteredBy = Nothing
Set adoCon = Nothing
%>

Open in new window

ASKER

I assume you are in Australia? It's now 3:00 AM here. I'm going to bed this time. I really thank you so much for your help.... I can't tell you how much I appreciate it.

I will post again tomorrow.... Happy New Year!

Yes, Victoria, Australia. GMT +10. Just after 10pm....two hours of 2009 left!

I'll be back next year :-)

Rob.

ASKER

Happy New Year...

Here's what I want to do now. On the add form, I want to have the date field be the popup calendar in the following link.

http://blog.davglass.com/files/yui/cal2/

Code of the add form is attached.

<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsIssues    'Holds the recordset for the records in the database
Dim rsManufacturers 'Holds the recordset for the records in the database
Dim rsWarrantyType 'Holds the recordset for the records in the database
Dim rsEnteredBy 'Holds the recordset for the records in the database
Dim strSQL           'Holds the SQL query for the database
Dim lngRecordNo    'Holds the record number to be updated

'Create an ADO connection object 
Set adoCon = Server.CreateObject("ADODB.Connection") 
'Set an active connection to the Connection object using a DSN-less connection 
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password=" 

'Create an ADO recordset object 
Set rsIssues = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Issue_ID,Issue FROM IssueList;" 
'Open the recordset with the SQL query  
rsIssues.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsManufacturers = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Manu_ID,Manufacturer FROM Manufacturer;" 
'Open the recordset with the SQL query  
rsManufacturers.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsWarrantyType = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Warr_ID,WarrantyType FROM WarrantyType;" 
'Open the recordset with the SQL query  
rsWarrantyType.Open strSQL, adoCon 

'Create an ADO recordset object 
Set rsEnteredBy = Server.CreateObject("ADODB.Recordset") 
'Initialise the strSQL variable with an SQL statement to query the database 
strSQL = "SELECT Empl_ID,Employee FROM Employee;" 
'Open the recordset with the SQL query  
rsEnteredBy.Open strSQL, adoCon 
%> 


<html>
<head>
<title>News Update Form</title>
<style type="text/css">
<!--
.style2 {
	font-size: 36px;
	font-weight: bold;
	font-family: Arial, Helvetica, sans-serif;
	color: #0000CC;
}
.style5 {font-size: 24px}
-->
</style>
</head>
<body bgcolor="white" text="black">
<!-- Begin form code -->
<form name="form" method="post" action="addrepairorder_add.asp">
  <p>&nbsp;</p>
  <table width="605" border="0" align="center" cellpadding="00" cellspacing="0" bgcolor="#FFFFFF">
    <tr>
      <td colspan="3"><div align="center">
        <p><span class="style2">Add Repair Order </span></p>
        </div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3"><div align="center"><em>        Enter Repair Order in the Text Boxes below. 
          When you are done, click Add Repair Order. </em></div></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Order Date </div></td>
      <td>&nbsp;</td>
      <td><input name="DateShipped" type="text" id="DateShipped" size="15"> 
      (mm/dd/yyyy) </td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Manufacturer</div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='Manufacturer'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsManufacturers.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsManufacturers("Manu_ID") & "' value='" & rsManufacturers("Manufacturer") & "'> " & rsManufacturers("Manufacturer") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsManufacturers.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Company Rep Name </div></td>
      <td>&nbsp;</td>
      <td><input name="CompanyRepName" type="text" id="CompanyRepName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Student Last Name </div></td>
      <td>&nbsp;</td>
      <td><input name="LastName" type="text" id="LastName" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Serial Number  </div></td>
      <td>&nbsp;</td>
      <td><input name="SerialNumber" type="text" id="SerialNumber" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
<td><div align="right">Issue # 1 </div></td>
<td>&nbsp;</td>
      <td>
<%
Response.Write "<select size='1' name='Issue_ID_1'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>      </td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 2</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_2'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 3</p>
          </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_3'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 4</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_4'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td><div align="right">
          <p>Issue # 5</p>
      </div></td>
      <td>&nbsp;</td>
      <td><%
rsIssues.MoveFirst
Response.Write "<select size='1' name='Issue_ID_5'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsIssues.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsIssues("Issue_ID") & "'  value='" & rsIssues("Issue_ID") & "'> " & rsIssues("Issue") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsIssues.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Other Issues Described </div></td>
      <td>&nbsp;</td>
      <td><input name="OtherProblems" type="text" id="OtherProblems" size="15"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Warranty Type: </div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='WarrantyType'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsWarrantyType.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsWarrantyType("Warr_ID") & "' value='" & rsWarrantyType("WarrantyType") & "'> " & rsWarrantyType("WarrantyType") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsWarrantyType.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Entered By:</div></td>
      <td>&nbsp;</td>
      <td><%
Response.Write "<select size='1' name='EnteredBy'>" 
Response.Write ("<option id='opt_blank' value='opt_blank'></option>")
'Loop through the recordset 
While Not rsEnteredBy.EOF 
    'Write the HTML to display the current record in the recordset 
    Response.Write ("<option id='opt_" & rsEnteredBy("Empl_ID") & "' value='" & rsEnteredBy("Employee") & "'> " & rsEnteredBy("Employee") & "</option>") 
    Response.Write ("<br>") 
    'Move to the next record in the recordset  
    rsEnteredBy.MoveNext  
Wend 
Response.Write "</select>" 
%></td>
    </tr>
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Status</div></td>
      <td>&nbsp;</td>
      <td><input name="Status" type="text" id="Status" value="Open"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">RMA</div></td>
      <td>&nbsp;</td>
      <td><input name="RMA" type="text" id="RMA"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Repaired By </div></td>
      <td>&nbsp;</td>
      <td><input name="RepairedBy" type="text" id="RepairedBy"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="right">Internal Notes </div></td>
      <td>&nbsp;</td>
      <td><input name="InternalNotes" type="text" id="InternalNotes"></td>
    </tr>
    
    
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Add Repair Order"></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p><input type="hidden" name="InternalRepair_ID">
  </p>
</form>
<!-- End form code -->
</body>
</html>

<%
'Reset server objects
rsIssues.Close
rsManufacturers.Close
rsWarrantyType.Close
rsEnteredBy.Close
Set rsIssues = Nothing
Set rsManufacturers = Nothing
Set rsWarrantyType = Nothing
Set rsEnteredBy = Nothing
Set adoCon = Nothing
%>

Open in new window

ASKER

Happy New Year, Rob... Where are ya!?

Thanks!

LOL! Still here....I'll check it out today. Just took a quick break for New Year's.

Rob.

ASKER

That's fine. We all need a break every once and a while!!! Thanks!

Hi, well, I can't get that calendar working, because it requires an install of the Yahoo tools package that I cannot do, but I got an older version of the one from here:
http://www.webreference.com/dhtml/column71/

that I've used before.

I have zipped up the new code, some images used by the calendar, and the JS file behind the calendar. This should work for you. You will need to rename add_asp.txt to add.asp

Regards,

Rob.

Hmmm, zip attached.
Add-With-Caldendar.zip

ASKER

Rob,

That works just fine! I love this! I can't thank you enough for this help. We're trying to get this to be ready to go live in the next 2 or 3 months.... at our pace... we'll be done sooner!

I'm going to convert the next form to ASP and let you know where I get stuck. Give me a few.

Thanks,
George

Sure, good luck.

Rob.

ASKER

Ok.... I created the ASP form... moved the VB code over from Access.... here's the error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/tablets/WarrantyLookup.asp, line 27

Dim db As DAO.Database
-------^

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Private Sub btnSearchTabWar_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim SqlFind As String
    Dim QualityCareItems
    Dim CurrentYearToUse
    Dim QualityCareStartMonthToUse
    Dim QualityCareStartDayToUse
    Dim QualityCareEndMonthToUse
    Dim QualityCareEndDayToUse
    Dim QualityCareFinalStart
    Dim QualityCareFinalStop
    Dim QualityEligible
    Dim UnderWarranty
    
    Me.txtTabWarLookup.SetFocus
    If Me.txtTabWarLookup.Text = "" Then
        MsgBox "You must input a valid Serial Number to Continue"
        Exit Sub
    End If
    Me.Filter = "SerialNumber='" & Me.txtTabWarLookup & "'"
    Me.FilterOn = True
    
    QualityEligible = 0
    UnderWarranty = 0
    CurrentYearToUse = DatePart("yyyy", Date)
    QualityCareStartMonthToUse = DatePart("m", Me.WarrantyStart)
    QualityCareStartDayToUse = DatePart("d", Me.WarrantyStart)
    QualityCareEndMonthToUse = DatePart("m", Me.WarrantyEnd)
    QualityCareEndDayToUse = DatePart("d", Me.WarrantyEnd)
    QualityCareFinalStart = QualityCareStartMonthToUse & "/" & QualityCareStartDayToUse & "/" & CurrentYearToUse
    QualityCareFinalStop = QualityCareEndMonthToUse & "/" & QualityCareEndDayToUse & "/" & CurrentYearToUse
    
    Me.txtRegularWarrantyStatus.SetFocus
    If Date >= Me.WarrantyStart And Date <= Me.WarrantyEnd Then
        Me.txtRegularWarrantyStatus.ForeColor = vbGreen
        Me.txtRegularWarrantyStatus.Text = "UNDER WARRANTY"
        UnderWarranty = 1
    Else
        Me.txtRegularWarrantyStatus.ForeColor = vbRed
        Me.txtRegularWarrantyStatus.Text = "NOT UNDER WARRANTY"
        UnderWarranty = 0
    End If
    
    SqlFind = "Select * from RepairOrders Where RepairOrders.DateShipped >= #" & QualityCareFinalStart & _
        "# AND RepairOrders.DateShipped <= #" & QualityCareFinalStop & "# and RepairOrders.SerialNumber = '" & Me.SerialNumber & "'"
         
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SqlFind)
    If Not rs.EOF Then
        rs.MoveLast
        QualityCareItems = rs.RecordCount
        QualityEligible = 0
    Else
        QualityCareItems = 0
        QualityEligible = 1
    End If
    
    If UnderWarranty = 0 Then
        QualityEligible = 0
    End If
    If QualityEligible = 1 Then
        Me.txtQualityCareStatus.SetFocus
        Me.txtQualityCareStatus.ForeColor = vbGreen
        Me.txtQualityCareStatus.Text = "ELIGIBLE FOR QUALITY CARE"
    Else
        Me.txtQualityCareStatus.SetFocus
        Me.txtQualityCareStatus.ForeColor = vbRed
        Me.txtQualityCareStatus.Text = "NOT ELIGIBLE FOR QUALITY CARE"
    End If
    
    rs.Close
    
    

End Sub
%>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" /></td>
        <td width="159"><input name="btnSearchTabWar" type="submit" id="Search" value="Search" /></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="Model Number" type="text" id="Model Number" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" /></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</body>
</html>

Open in new window

OK, this needs to be changed to a self-referencing form. I'll have a crack at it today, but most of the code needs changing.

Regards,

Rob.

OK, that was the first time I've ever done a self-referencing form. This seems OK, although I couldn't understand what you're doing with the Eligle for Quality Care bit.

Try this code out. It's a bit tricky, but what the page does is determine whether a search has been made, and if it has, it pulls the values from the database to populate the fields with, otherwise it makes the text boxes empty.

Regards,

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim recordFound
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select * From RepairOrders Where RepairOrders.SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		End If
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then
    If Date >= dteWarrantyStart And Date <= dteWarrantyEnd Then %> style="font:forecolor=#008000;" value="UNDER WARRANTY" <% Else %> style="font:forecolor=#FF0000;" value="NOT UNDER WARRANTY" <% End If
    End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then
    If Date >= dteWarrantyStart And Date <= dteWarrantyEnd Then %> style="font:forecolor=#008000;" value="ELIGBLE FOR QUALITY CARE" <% Else %> style="font:forecolor=#FF0000;" value="NOT ELIGBLE FOR QUALITY CARE" <% End If
    End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

ASKER

When I put in a serial number that exists... this is the error that comes up:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/tablets/WarrantyLookup.asp, line 56

Now let me explain Quality Care and Warranty.

All machines are elligible for warranty repair an unlimited number of times between the start and end date. Those dates are the values within the Warranty Start and Warranty End fields. That one is simple.

Quality Care repair is eligible once per year. The year is determined from the Warranty start date to 1 year later. So it's the anniversary. So, if the machine is warranty starts on August 5th 2009 and there is a Quality care repair between Aug 5th 2009 and Aug 4, 2010, the machine is NOT eligible for Quality Care repair. The machine is eligible again on Aug 5 2010 for a Quality Care repair.

All quality care repairs must be within the warranty start and finish dates.

Do you understand that? Let me know if you need clarification.

Yes, that makes sense. I can code that in. As for your error, I assumed that the data displayed on screen would come from the RepairOrders table, but seeing as you appear to determine the Quality Care from that table, that must be wrong. So, you should be able to change this line:
strSQL = "Select * From RepairOrders Where RepairOrders.SerialNumber = '" & strSearchTerm & "';"

so that it looks at the correct table for the Warranty info, and I'll code in the lookup for the Quality Care shortly.

Regards,

Rob.

ASKER

I'm not sure if I'm answering your question here or not....

The RepairOrders table has a field that determins if it's a Warranty Repair or a Quality Care repair. We're looking at the RepairOrders table for this process.

The data that is displayed on this page when a serial number is entered...does that ALL come from the RepairOrders table? Can you write the structure of that table (field names and data types)?

Rob.

P.S. I don't have Access 2007 so I was unable to open your original database.

ASKER

Here's the DB in 97-2003 format
MGMTabletDB.mdb

My test machine is old... :-( Are you able to save in Access 2000? If not, I'll install Office 2003...

Rob.

ASKER

Try this.
MGMTabletDB0000.mdb

Sorry, installing Access 2003 now. Back shortly....

ASKER

That last one should have been 2000.... I have noticed 2007 doesnt work well moving back past 97-2003 versions.

I'm here... take your time.

OK, try this code. For the Warranty Repair, I check whether the current date falls within WarrantyStart and WarrantyEnd, and for the Quality Care, I just read RepairOrders.WarrantyType.

Regards,

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim recordFound
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
Dim strWarrantyType
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select RepairOrders.SerialNumber,Tablets.SerialNumber,Tablets.Manufacturer,Tablets.ModelNumber,Tablets.Configuration,Tablets.WarrantyStart,Tablets.WarrantyEnd,RepairOrders.WarrantyType From RepairOrders,Tablets Where RepairOrders.SerialNumber = '" & strSearchTerm & "' AND RepairOrders.SerialNumber = Tablets.SerialNumber;"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("RepairOrders.SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		    strWarrantyType = rsOrders("WarrantyType")
		End If
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then
    If Date >= dteWarrantyStart And Date <= dteWarrantyEnd Then %> style="font:forecolor=#008000;" value="UNDER WARRANTY" <% Else %> style="font:forecolor=#FF0000;" value="NOT UNDER WARRANTY" <% End If
    End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then %> value="<%=strWarrantyType%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

ASKER

Nah, this isnt working right....

It should check the tablets table then display the warranty start and end date. So like I put in dates into the "tablets table in warranty start and warranty end.... i searched for that serial number.... no record found but there should be.

If todays date is within the warranty dates, then it's eligible for warranty.

If there is a "Quality Care" value in the repair orders warranty type field, within the 1 year anniversary of start date, then it's not quality care eligible.

This one is confusing! Do you follow this?

OK, what about this.

Regards,

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim rsRepair
Dim recordFound
Dim notRepaired
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select SerialNumber,Manufacturer,ModelNumber,Configuration,WarrantyStart,WarrantyEnd From Tablets Where SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		End If
		'Create an ADO recordset object 
		Set rsRepair = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select WarrantyType From RepairOrders Where SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsRepair.Open strSQL, adoCon 
	    If rsRepair.EOF Then
	    	notRepaired = True
	    Else
	    	notRepaired = False
	    End If
		'Reset server objects
		rsOrders.Close
		rsRepair.Close
		Set rsOrders = Nothing
		Set rsRepair = Nothing
		Set adoCon = Nothing
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then
    If Date >= dteWarrantyStart And Date <= dteWarrantyEnd Then %> style="font:forecolor=#008000;" value="UNDER WARRANTY" <% Else %> style="font:forecolor=#FF0000;" value="NOT UNDER WARRANTY" <% End If
    End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then
    If (Date >= dteWarrantyStart And Date <= dteWarrantyEnd) And notRepaired = True Then %> style="font:forecolor=#008000;" value="ELIGBLE FOR QUALITY CARE" <% Else %> style="font:forecolor=#FF0000;" value="NOT ELIGBLE FOR QUALITY CARE" <% End If
    End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

ASKER

Testing it now... stand by.

ASKER

Ok... We're almost there....

Right now, it seems to be saying, if there is any repair in the repair orders table, it loses quality care repair. There can be unlimited warranty repairs and it is still quality care eligible. The quality care eligible only disappears if quality care is used in the anniversary year.

OK, I went back and had a look at your form code. I have replicated that now. See how this goes.

Regards,

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim rsRepair
Dim recordFound
Dim notRepaired
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select SerialNumber,Manufacturer,ModelNumber,Configuration,WarrantyStart,WarrantyEnd From Tablets Where SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		    dteQualityCareStart = dteWarrantyStart
		    dteQualityCareStop = DateAdd("yyyy", 1, dteQualityCareStart)
			If Date >= dteWarrantyStart And Date <= dteWarrantyEnd Then
				strWarrantyStatus = "UNDER WARRANTY"
			Else
				strWarrantyStatus = "NOT UNDER WARRANTY"
			End If
			'Create an ADO recordset object 
			Set rsRepair = Server.CreateObject("ADODB.Recordset") 
			'Initialise the strSQL variable with an SQL statement to query the database 
			strSQL = "Select DateShipped,WarrantyType From RepairOrders Where DateShipped >= #" & dteQualityCareStart & _
				"# AND DateShipped <= #" & dteQualityCareStop & "# And SerialNumber = '" & strSearchTerm & "';"
			'Open the recordset with the SQL query  
			rsRepair.Open strSQL, adoCon 
		    If Not rsRepair.EOF Then
		    	strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
		    Else
		    	If strWarrantyStatus = "UNDER WARRANTY" Then
		    		strQualityEligible = "ELIGIBLE FOR QUALITY CARE"
		    	Else
		    		strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
		    	End If
		    End If
			'Reset server objects
			rsRepair.Close
			Set rsRepair = Nothing
			rsOrders.Close
			Set rsOrders = Nothing
			Set adoCon = Nothing
		End If
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then %> value="<%=strWarrantyStatus%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then %> value="<%=strQualityEligible%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

ASKER

The Quality Care in this example is ALWAYS eligible. It's not changing no matter what dates I put.

Really? When I tested against a test system in there: R9610308 it said NOT UNDER WARRANTY and NOT ELIGIBLE FOR QUALITY CARE....

Rob.

ASKER

Using the attached database.....

On the tablets table.... look at serial number 8511586 (first record) - Start 8/1/09 end 8/1/2010

Repairs table, last record... is for SN 8511586 with a quality care repair with ship date within those dates.

You can see the live forms at www.georgeballane.com/tablets

The attached DB is what is on the live system.

Going to bed now... I'll see your next post in the morning. Thanks!

MGMTabletDB.mdb

OK, it appears to be getting confused with dd/mm/yyyy and mm/dd/yyyy formats.

If you run this code, you will see at the top what it thinks the dates are, so once you show me what the top three lines say, we'll work out how to get around that.

Also, what is the date format on the web server, as opposed to the date format in the Database? Make sure they're matching.

Regards,

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim rsRepair
Dim recordFound
Dim notRepaired
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select SerialNumber,Manufacturer,ModelNumber,Configuration,WarrantyStart,WarrantyEnd From Tablets Where SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		    dteQualityCareStart = dteWarrantyStart
		    dteQualityCareStop = DateAdd("yyyy", 1, dteQualityCareStart)
		    Response.Write "<BR>Date: " & MonthName(Month(Date)) & " " & Day(Date) & " " & Year(Date)
		    Response.Write "<BR>Start: " & MonthName(Month(dteWarrantyStart)) & " " & Day(dteWarrantyStart) & " " & Year(dteWarrantyStart)
		    Response.Write "<BR>End: " & MonthName(Month(dteWarrantyEnd)) & " " & Day(dteWarrantyEnd) & " " & Year(dteWarrantyEnd)
			If Date >= CDate(dteWarrantyStart) And Date <= CDate(dteWarrantyEnd) Then
				strWarrantyStatus = "UNDER WARRANTY"
			Else
				strWarrantyStatus = "NOT UNDER WARRANTY"
			End If
			Response.Write "<BR>Status: " & strWarrantyStatus & "<BR>"
			'Create an ADO recordset object 
			Set rsRepair = Server.CreateObject("ADODB.Recordset") 
			'Initialise the strSQL variable with an SQL statement to query the database 
			strSQL = "Select DateShipped,WarrantyType From RepairOrders Where DateShipped >= #" & dteQualityCareStart & _
				"# AND DateShipped <= #" & dteQualityCareStop & "# And SerialNumber = '" & strSearchTerm & "';"
			'Open the recordset with the SQL query  
			rsRepair.Open strSQL, adoCon 
		    If Not rsRepair.EOF Then
		    	strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
		    Else
		    	If strWarrantyStatus = "UNDER WARRANTY" Then
		    		strQualityEligible = "ELIGIBLE FOR QUALITY CARE"
		    	Else
		    		strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
		    	End If
		    End If
			'Reset server objects
			rsRepair.Close
			Set rsRepair = Nothing
			rsOrders.Close
			Set rsOrders = Nothing
			Set adoCon = Nothing
		End If
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then %> value="<%=strWarrantyStatus%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then %> value="<%=strQualityEligible%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

To clarify a bit further, my "system" date in Regional Settings is dd/mm/yyyy and the WarrantyEnd date in the database is 1/08/2010 which, by the dd/mm/yyyy rule, represents August 1, 2010. You may need to specify the Format in the field properties in Design view on the database, *but*, I would expect that as long as your web server and the database dates are consistent, you shouldn't have any problems.

Regards,

Rob.

ASKER

It doesn't seem to be working... using the database I uploaded. The under warranty part is fine, but not the Quality Care.

Any Ideas?

What does the date at the top of the page say when you use the latest code? Does it look right?

Rob.

ASKER

It's spelled out, January 5 2009.... the format in the DB is MM/DD/YYYY

ASKER

Date: January 5 2010
Start: August 1 2009
End: December 12 2009
Status: NOT UNDER WARRANTY

Oh yeah, I didn't mention that. What the "spelled out" date shows is what the system thinks the Month and Day parts of the date are.

So, for the Start date, is it supposed to be August 1, 2009, or is it supposed to be January 8, 2009?

Today's date is January 5, so if your *system* short date format is dd/mm/yyyy then the database date format should also be dd/mm/yyyy, and January 8 would therefore be 01/08/2009.

However, if your system date is mm/dd/yyyy, then 01/08/2009 would come out as August 1, 2009. Does that make sense? Dates are confusing in this respect. I think if you can keep the web server and database formats consistent, we should get somewhere.

Regards,

Rob.

ASKER

The server is mm/dd/yyyy. The DB is mm/dd/yyyy.

Not sure what I'm missing.

Hmmm, under this line:
                  Response.Write "<BR>Status: " & strWarrantyStatus & "<BR>"

put this
                  Response.Write "<BR>Start: " & MonthName(Month(dteQualityCareStop)) & " " & Day(dteQualityCareStop) & " " & Year(dteQualityCareStop)
                  Response.Write "<BR>Quality Status: " & strQualityEligible & "<BR>"

and see what the quality date is.....

With the dates you wrote above, it is correct in saying that it's not under warranty, so we'll see what the Quality Elibility is.

Regards,

Rob.

ASKER

I'm getting confused!

http://www.georgeballane.com/tablets/
Click on Warranty Status
Enter serial 8511586

Here are new dates:

Start: 8/1/2009
End 1/6/2010

I have a quality care repair on 01/04/2010.....

Response on the top of the page is:
Date: January 5 2010
Start: August 1 2009
End: January 6 2010
Status: UNDER WARRANTY

Start: August 1 2010
Quality Status:

--------------------------------------
Would this be easier to redesign this a little? I hate to go back after you've spent so much time on this...

How about if along the top it after the Serial Number is entered it has the warranty start and warranty end dates. Then, in two columns we write all dates where Warranty Type is "Warranty Repair" in column 1 and column 2 we write all dates when Quality Care is the Warranty Type in the RepairOrders table for the serial number that was searched.

Does that make it easier? We can manually, just by looking at the dates and determine the information we need to know. I rather we just got SOMETHING to work that does the job and were able to move on! Let me know what you think.

Sorry.....one more time with test output, if you don't mind.....this will output the dates that it's calculating against, and you can tell me whether it comes out right.

I get this for the output:
Date: January 6 2010
Start: January 8 2009
End: January 8 2010
Status: UNDER WARRANTY

Quality Care Stop: January 8 2010
Quality Status: ELIGIBLE FOR QUALITY CARE

but my format is dd/mm/yyyy so it'll be different to yours.

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim rsRepair
Dim recordFound
Dim notRepaired
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select SerialNumber,Manufacturer,ModelNumber,Configuration,WarrantyStart,WarrantyEnd From Tablets Where SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		    dteQualityCareStart = dteWarrantyStart
		    dteQualityCareStop = DateAdd("yyyy", 1, dteQualityCareStart)
		    Response.Write "<BR>Date: " & MonthName(Month(Date)) & " " & Day(Date) & " " & Year(Date)
		    Response.Write "<BR>Start: " & MonthName(Month(dteWarrantyStart)) & " " & Day(dteWarrantyStart) & " " & Year(dteWarrantyStart)
		    Response.Write "<BR>End: " & MonthName(Month(dteWarrantyEnd)) & " " & Day(dteWarrantyEnd) & " " & Year(dteWarrantyEnd)
			If Date >= CDate(dteWarrantyStart) And Date <= CDate(dteWarrantyEnd) Then
				strWarrantyStatus = "UNDER WARRANTY"
			Else
				strWarrantyStatus = "NOT UNDER WARRANTY"
			End If
			Response.Write "<BR>Status: " & strWarrantyStatus & "<BR>"
            Response.Write "<BR>Quality Care Stop: " & MonthName(Month(dteQualityCareStop)) & " " & Day(dteQualityCareStop) & " " & Year(dteQualityCareStop)
			'Create an ADO recordset object 
			Set rsRepair = Server.CreateObject("ADODB.Recordset") 
			'Initialise the strSQL variable with an SQL statement to query the database 
			strSQL = "Select DateShipped,WarrantyType From RepairOrders Where DateShipped >= #" & dteQualityCareStart & _
				"# AND DateShipped <= #" & dteQualityCareStop & "# And SerialNumber = '" & strSearchTerm & "';"
			'Open the recordset with the SQL query  
			rsRepair.Open strSQL, adoCon 
		    If Not rsRepair.EOF Then
		    	strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
		    Else
		    	If strWarrantyStatus = "UNDER WARRANTY" Then
		    		strQualityEligible = "ELIGIBLE FOR QUALITY CARE"
		    	Else
		    		strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
		    	End If
		    End If
            Response.Write "<BR>Quality Status: " & strQualityEligible & "<BR>"
			'Reset server objects
			rsRepair.Close
			Set rsRepair = Nothing
			rsOrders.Close
			Set rsOrders = Nothing
			Set adoCon = Nothing
		End If
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then %> value="<%=strWarrantyStatus%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then %> value="<%=strQualityEligible%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

ASKER

I get....

Date: January 5 2010
Start: August 1 2009
End: January 6 2010
Status: UNDER WARRANTY

Quality Care Stop: August 1 2010
Quality Status: ELIGIBLE FOR QUALITY CARE

I'm testing now...

ASKER

No, its not working...

I made a start date: 8/1/2007
End date: 8/1/2010

I put in a repair warranty type of quality care of 6/1/2010 which would make this not eligible for quality care and it says that it is.....

Date: January 5 2010
Start: August 1 2007
End: August 1 2010
Status: UNDER WARRANTY

Quality Care Stop: August 1 2008
Quality Status: ELIGIBLE FOR QUALITY CARE

OK, I think there's something wrong with the SQL query for the date calculations....I ran this manually inside the database:
Select DateShipped,WarrantyType From RepairOrders Where DateShipped >= #01/01/2009# And DateShipped <= #01/01/2011# And SerialNumber = '8511586';

and it didn't bring back anything, but this
Select DateShipped,WarrantyType From RepairOrders Where SerialNumber = '8511586';

showed two records....I'll check it out.....

ASKER

Sounds good... ill check in the morning. going to sleep now. Thanks!

OH!!!! I just realised that the DateShipped field in the RepairOrders table is TEXT, not DATE / TIME!

When I changed the *type* of that field to date / time, it worked fine!

Rob.

ASKER

OK... so I changed that in my DB and I don't see how it's working fine. This this attached database. In this case.... since there is a repair order on 12/27/09.... this machine serial number 8511586 is NOT eligible for Quality Care... it says it is.
MGMTabletDB.mdb

ASKER

I think maybe you aren't following quality care.

Quality care is eligible each year between purchase date and anniversary. Quality care eligibility starts again at anniversary 1. If during the first year, QC wasn't used, you lose it. If it was used during year 1, you get a NEW one at anniversary 1, the same is true each year during the duration of the warranty. Follow that?

Oh, right....so it's like a "service", that you have to keep ongoing to keep it eligible.....

So you only get Quality Care once per year, but must use it to have it next year, up until the warranty expires?

ASKER

You have the option to use it each year. If you don't use it in year 1, you can use it in year 2 and so on. If you don't use it in year 1, you don't get 2 in year 2... you only get 1 per year.

If you use it sometime during year 1, you get a new one at the start of year 2, no matter when you use it during year 1.

>> If you don't use it in year 1, you can use it in year 2 and so on. If you don't use it in year 1, you don't get 2 in year 2

should that be
If you ***do*** use it in year 1, you can use it in year 2 and so on. If you don't use it in year 1, you don't get 2 in year 2

Anyway, based on the above, I have coded it such that it checks that you have used Quality Care for *each* year up to the current year. If you missed one (I just check all years instead of the previous year), then you are not eligible.
If you have never used it and are in the first year, you are eligble.
If you have used it in all years, you are not eligible.

Sorry about the length of time for this one....

Regards,

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim rsRepair
Dim recordFound
Dim notRepaired
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select SerialNumber,Manufacturer,ModelNumber,Configuration,WarrantyStart,WarrantyEnd From Tablets Where SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		    'Response.Write "<BR>Date: " & MonthName(Month(Date)) & " " & Day(Date) & " " & Year(Date)
		    'Response.Write "<BR>Start: " & MonthName(Month(dteWarrantyStart)) & " " & Day(dteWarrantyStart) & " " & Year(dteWarrantyStart)
		    'Response.Write "<BR>End: " & MonthName(Month(dteWarrantyEnd)) & " " & Day(dteWarrantyEnd) & " " & Year(dteWarrantyEnd)
			If Date >= CDate(dteWarrantyStart) And Date <= CDate(dteWarrantyEnd) Then
				strWarrantyStatus = "UNDER WARRANTY"
			Else
				strWarrantyStatus = "NOT UNDER WARRANTY"
			End If
            Response.Write "<BR>Warranty Status: " & strWarrantyStatus & "<BR>"
			If strWarrantyStatus = "UNDER WARRANTY" Then
				'Create an ADO recordset object 
				Set rsRepair = Server.CreateObject("ADODB.Recordset") 
				'Initialise the strSQL variable with an SQL statement to query the database 
				strSQL = "Select DateShipped,WarrantyType From RepairOrders Where SerialNumber = '" & strSearchTerm & "' And WarrantyType='Quality Care' ORDER BY DateShipped;"
				'Open the recordset with the SQL query  
				rsRepair.Open strSQL, adoCon
			    dteQualityCareStart = CDate(dteWarrantyStart)
			    dteQualityCareStop = CDate(DateAdd("yyyy", 1, dteWarrantyStart))
			    strQualityEligible = "ELIGIBLE FOR QUALITY CARE"
			    If rsRepair.EOF = True Then
			    	If dteQualityCareStop < Date Then strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
			    Else
			    	intAnniversariesToToday = DateDiff("yyyy", dteWarrantyStart, Date)
			    	Response.Write "Anniversaries To Today: " & intAnniversariesToToday
				    intRecordNum = 0
				    While Not rsRepair.EOF And intRecordNum < intAnniversariesToToday
				    	intRecordNum = intRecordNum + 1
				    	Response.write "<BR>Checking record " & intRecordNum & " up to " & intAnniversariesToToday
				    	dteQualityCareStart = DateAdd("yyyy", intRecordNum - 1, dteWarrantyStart)
				    	dteQualityCareStop = DateAdd("yyyy", intRecordNum, dteWarrantyStart)
				    	If dteQualityCareStop > dteWarrantyEnd Then dteQualityCareStop = dteWarrantyEnd
			    		If CDate(rsRepair("DateShipped")) >= dteQualityCareStart And CDate(rsRepair("DateShipped")) <= dteQualityCareStop Then
			    			If intRecordNum = intAnniversariesToToday Then
				    			Response.Write "<BR>QUALITY CARE USED IN YEAR " & intRecordNum & " between " & dteQualityCareStart & " and " & dteQualityCareStop & " on " & CDate(rsRepair("DateShipped"))
			    				strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
			    			Else
				    			Response.Write "<BR>QUALITY CARE USED IN YEAR " & intRecordNum & " between " & dteQualityCareStart & " and " & dteQualityCareStop & " on " & CDate(rsRepair("DateShipped"))
			    			End If
			    		Else
			    			If intAnniversariesToToday > intRecordNum Then
				    			Response.Write "<BR>YOU DID NOT USE QUALITY CARE IN YEAR " & intRecordNum & " between " & dteQualityCareStart & " and " & dteQualityCareStop & " on " & CDate(rsRepair("DateShipped"))
			    				strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
			    			End If
			    		End If
				    	rsRepair.MoveNext
					Wend
				End If
				rsRepair.Close
				Set rsRepair = Nothing
	    	Else
	    		strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
		    End If
            Response.Write "<BR>Quality Status: " & strQualityEligible & "<BR>"
			'Reset server objects
			rsOrders.Close
			Set rsOrders = Nothing
			Set adoCon = Nothing
		End If
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then %> value="<%=strWarrantyStatus%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then %> value="<%=strQualityEligible%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

ASKER

I hated Quality Care when I was first introduced to it.

Let me explain it another way to see if we are on the same page....

You can ONLY have one quality care per anniversary year.
- If it's used in year 1, you get another at the start of year two, year three, etc.
- If it's not used in year 1, you lose it.
- They don't carry over. You get one per year no matter what happens in the previous year.

Does this match your code?

>> - They don't carry over. You get one per year no matter what happens in the previous year.

So, as *long* as you use it in year one, you get it in say, year 3, even if you didn't use it in year 2?

So, NOT using it in year 1, or having used it in the current year, is the only way to lose it?

If that's case, my code doesn't quite match that yet....

ASKER

<<So, NOT using it in year 1, or having used it in the current year, is the only way to lose it?>>

You get one QC each year no matter if you use it or dont.

OK, I think I got it this time....fingers crossed ;-)

I have calculated the Quality Care start and end dates for the current anniversary year (by iterating through years, up to the end of the warranty, until today falls within that range), and then searched the RepairOrders table for a record on that date.

Regards,

Rob.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
	color: #0000FF;
	font-size: 36px;
}
-->
</style>
</head>


<body>
<%
Dim boolFirstTime
Dim strSearchTerm
Dim validSearch
Dim rsOrders
Dim rsRepair
Dim recordFound
Dim notRepaired
Dim strSerialNumber
Dim strManufacturer
Dim strModelNumber
Dim strConfiguration
Dim dteWarrantyStart
Dim dteWarrentyEnd
boolFirstTime = True
If Request.Form("btnSearchTabWar") = "Search" Then
	boolFirstTime = False
    If Request.Form("txtTabWarLookup") = "" Then
        strSearchTerm = ""
        validSearch = False
	Else
		strSearchTerm = Request("txtTabWarLookup")
		validSearch = True
		'Create an ADO connection object 
		Set adoCon = Server.CreateObject("ADODB.Connection") 
		'Set an active connection to the Connection object using a DSN-less connection 
		adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mgmtabletdb.mdb") & ";Jet OLEDB:Database Password="
		'Create an ADO recordset object 
		Set rsOrders = Server.CreateObject("ADODB.Recordset") 
		'Initialise the strSQL variable with an SQL statement to query the database 
		strSQL = "Select SerialNumber,Manufacturer,ModelNumber,Configuration,WarrantyStart,WarrantyEnd From Tablets Where SerialNumber = '" & strSearchTerm & "';"
		'Open the recordset with the SQL query  
		rsOrders.Open strSQL, adoCon 
	    If rsOrders.EOF Then
	    	recordFound = False
	    Else
	    	recordFound = True
		    strSerialNumber = rsOrders("SerialNumber")
		    strManufacturer = rsOrders("Manufacturer")
		    strModelNumber = rsOrders("ModelNumber")
		    strConfiguration = rsOrders("Configuration")
		    dteWarrantyStart = rsOrders("WarrantyStart")
		    dteWarrantyEnd = rsOrders("WarrantyEnd")
		    dteQualityCareStart = dteWarrantyStart
		    dteQualityCareStop = DateAdd("yyyy", 1, dteQualityCareStart)
		    Response.Write "<BR>Date: " & MonthName(Month(Date)) & " " & Day(Date) & " " & Year(Date)
		    Response.Write "<BR>Start: " & MonthName(Month(dteWarrantyStart)) & " " & Day(dteWarrantyStart) & " " & Year(dteWarrantyStart)
		    Response.Write "<BR>End: " & MonthName(Month(dteWarrantyEnd)) & " " & Day(dteWarrantyEnd) & " " & Year(dteWarrantyEnd)
			If Date >= CDate(dteWarrantyStart) And Date <= CDate(dteWarrantyEnd) Then
				strWarrantyStatus = "UNDER WARRANTY"
	            While dteQualityCareStop <= Date
	            	dteQualityCareStart = DateAdd("yyyy", 1, dteQualityCareStart)
	            	dteQualityCareStop = DateAdd("yyyy", 1, dteQualityCareStop)
	            Wend
	            If dteQualityCareStop > dteWarrantyEnd Then dteQualityCareStop = dteWarrantyEnd
	            Response.Write "<BR>Current Anniversary Quality Care Start: " & MonthName(Month(dteQualityCareStart)) & " " & Day(dteQualityCareStart) & " " & Year(dteQualityCareStart)
	            Response.Write "<BR>Current Anniversary Quality Care Stop: " & MonthName(Month(dteQualityCareStop)) & " " & Day(dteQualityCareStop) & " " & Year(dteQualityCareStop)
				'Create an ADO recordset object 
				Set rsRepair = Server.CreateObject("ADODB.Recordset") 
				'Initialise the strSQL variable with an SQL statement to query the database 
				strSQL = "Select DateShipped,WarrantyType From RepairOrders Where DateShipped >= #" & dteQualityCareStart & _
					"# AND DateShipped <= #" & dteQualityCareStop & "# And SerialNumber = '" & strSearchTerm & "';"
				'Open the recordset with the SQL query  
				rsRepair.Open strSQL, adoCon 
			    If Not rsRepair.EOF Then
	            	Response.Write "<BR>Quality Care was used on " & MonthName(Month(CDate(rsRepair("DateShipped")))) & " " & Day(CDate(rsRepair("DateShipped"))) & " " & Year(CDate(rsRepair("DateShipped")))
			    	strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
			    Else
		    		strQualityEligible = "ELIGIBLE FOR QUALITY CARE"
			    End If
			Else
				strWarrantyStatus = "NOT UNDER WARRANTY"
	    		strQualityEligible = "NOT ELIGIBLE FOR QUALITY CARE"
			End If
            Response.Write "<BR>Warranty Status: " & strWarrantyStatus
            Response.Write "<BR>Quality Status: " & strQualityEligible
            Response.Write "<BR>"
			'Reset server objects
			rsRepair.Close
			Set rsRepair = Nothing
			rsOrders.Close
			Set rsOrders = Nothing
			Set adoCon = Nothing
		End If
	End If
End If
%>
<form name="lookupform" method="post" action=<%Request.ServerVariables("URL")%>>
<table width="741" height="227" border="0">
  <tr>
    <td colspan="3"><h1 align="center"><span class="style1">Warranty Lookup</span> </h1></td>
  </tr>
  <tr>
    <td colspan="3"><table width="367" height="27" border="0" align="center">
      <tr>
        <td colspan="3" align="center">
        	<%If boolFirstTime = False And validSearch = False Then %><font color="red"><b>Please enter a serial number.</b></font><% Else %><br><% End If %>
        </td>
       </tr>
       <tr>
        <td width="158"><div align="right">Serial Number </div></td>
        <td width="36"><input name="txtTabWarLookup" type="text" id="txtTabWarLookup" <%If boolFirstTime = False Then %> value="<%=strSearchTerm%>" <% End If%>/></td>
        <td width="159"><input name="btnSearchTabWar" type="Submit" id="Search" value="Search"/>
		</td>
	  <tr>
	     <td colspan="3" align="center">
	     <%If validSearch = True And recordFound = False Then %><font color="red"><b>No record was found for <%=strSearchTerm%>.<br></b></font><% Else %><br><% End If %>
	     </td>
	  </tr>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="213" height="26"><p align="right">&nbsp;</p>    </td>
    <td width="279">&nbsp;</td>
    <td width="235">&nbsp;</td>
  </tr>
  <tr>
    <td><p align="right">Serial Number</p>    </td>
    <td><input name="SerialNumber" type="text" id="SerialNumber" <%If recordFound = True Then %> value="<%=strSerialNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Manufacturer</div></td>
    <td><input name="Manufacturer" type="text" id="Manufacturer" <%If recordFound = True Then %> value="<%=strManufacturer%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Model Number </div></td>
    <td><input name="ModelNumber" type="text" id="ModelNumber" <%If recordFound = True Then %> value="<%=strModelNumber%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">
      <p>Configuration</p>
      </div></td>
    <td><input name="configuration" type="text" id="configuration" <%If recordFound = True Then %> value="<%=strConfiguration%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty Start </div></td>
    <td><input name="WarrantyStart" type="text" id="WarrantyStart" <%If recordFound = True Then %> value="<%=dteWarrantyStart%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Warranty End </div></td>
    <td><input name="WarrantyEnd" type="text" id="WarrantyEnd" <%If recordFound = True Then %> value="<%=dteWarrantyEnd%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Regular Warranty Status </div></td>
    <td><input name="txtRegularWarrantyStatus" type="text" id="txtRegularWarrantyStatus" <%If recordFound = True Then %> value="<%=strWarrantyStatus%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right">Quality Care Status </div></td>
    <td><input name="txtQualityCareStatus" type="text" id="txtQualityCareStatus" <%If recordFound = True Then %> value="<%=strQualityEligible%>" <% Else %> value="" <% End If%>/></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><div align="right"></div></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

Open in new window

ASKER

Can we switch this over to email, this thread is really loading so slow because it's so long....
I can give you my email if you are OK with using email.

BTW - it is viewing "Warranty Repair" as a quality care repair. Only Quality Care counts against quality care warranty. The warranty repair is allowed an unlimited number of times during the warranty period.

Oh yeah, so change this:
                        strSQL = "Select DateShipped,WarrantyType From RepairOrders Where DateShipped >= #" & dteQualityCareStart & _
                              "# AND DateShipped <= #" & dteQualityCareStop & "# And SerialNumber = '" & strSearchTerm & "';"

to this
                        strSQL = "Select DateShipped,WarrantyType From RepairOrders Where DateShipped >= #" & dteQualityCareStart & _
                              "# AND DateShipped <= #" & dteQualityCareStop & "# And SerialNumber = '" & strSearchTerm & "' And WarrantyType = 'Quality Care';"

If that's the end of this page, start a new EE question, post the link here, and we'll continue there.

Rob.

ASKER

That works now!

here's the new link.

https://www.experts-exchange.com/questions/25026078/VB-Script-Help.html

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial