Simulating a LookUp in a Data Access page

[marle35]

I have followed the instructions in Microsoft's page

http://support.microsoft.com/kb/234301

to do the equivalent of a lookup function. When I do a Document.All.Item("ControlName").Value = "somehardcodedvalue" assignment in the Current event, it works just fine. When I try, like in those instructions, to access a value all ready in one of the controls (like in a select statement to open up a recordset) it gives an "unknown run-time error." I am using banded pages (all records showing one one page). My code looks like this:

Dim rst
Dim strSQL

Set rst = CreateObject("ADODB.Recordset")

strSQL = "SELECT tblHistory.OrderID, tblHistory.Comment, " & _
"tblHistory.EntryTime FROM tblHistory " & _
"WHERE tblHistory.OrderID = '" & _
Document.All.Item("txtOrderID").Value & _
"' ORDER BY tblHistory.EntryTime DESC;"

rst.Open strSQL, MSODSC.Connection

If Not rst.EOF Then
Document.All.Item("txtComment1").Value = Rst("Comment").Value
Document.All.Item("txtTime1").Value = Rst("EntryTime").Value
rst.MoveNext
If Not rst.EOF Then
Document.All.Item("txtComment2").Value = Rst("Comment").Value
Document.All.Item("txtTime2").Value = Rst("EntryTime").Value
End If
End If

rst.Close

The run-time error occurs at the rst open command. When I debug it, the strSQL does not contain any value for where Document.All.Item("txtOrderID").Value should be. Any ideas?

 

[fneily]

First you didn't include the script so I can't see if you made any mistake there.
I don't see the Connection.
I followed the stated referenced and it worked fine. See my code below. Be sure to change the Source path to your database. Also, close Access and test it in IE.

<SCRIPT language=vbscript event=Current(oEventInfo) for=MSODSC>
<!--
Dim Con
Dim Rst

Set Con = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test4.mdb;Jet OLEDB:System " & _
"Database=C:\Program Files\Microsoft Office\Office\System.mdw;User " & _
"ID=Admin;"

Rst.Open "SELECT Amount FROM [employee_table] WHERE ID2 = " & _
chr(39) & Document.All.Item("ID2").Value & chr(39), Con

Document.All.Item("Amttest").Value = Rst("Amount").Value

Rst.Close
Con.Close

-->
</SCRIPT>

 

[marle35]

I tried my same code, but set the grouping to show 1 record at a time instead of all records. It works when only 1 record shows. If that was my problem, how do I make it work when I need all records to show?

Basically, I need to be able to access each control's value on every record when all records are showing and display information beside it that it is related to each particular record.