I have 2 tables in a mdb file. One have project_name (pk) and its details. The other have project_name (fk) with the environments associated with it. Now I am trying to have a page created using the access only, which have a dropdown list with all the project_name. It is created with element taken from the table having project_name as primary key (pk). Now, my idea was to display the relevant environments associated with a project when ever any one of them is selected from the dropdown list. Below is the function written for onchange even of the dropdown list. It is not working. The record count gives -1 even though the table has records in it. I am exhausted looking for the problem. Please someone help me... :-(

By the way this is my first time with javascript and access.
I am basically a PL/SQL guy. So, please forgive me for my silliness...

The code --
function selectDropdown2(){var dropdownValue=document.getElementById("DropdownLis t1").value;
document.getElementById("Label2").innerText = "project --> " + dropdownValue;
adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;
var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\temp\\envstat.mdb";
var ConnectObj = new ActiveXObject("ADODB.Connection");
//var ConnectObj = Server.CreateObject("ADODB.Connection"); --for server side
var RS = new ActiveXObject("ADODB.Recordset");
//var RS = Server.CreateObject("ADODB.Recordset"); --for server side
var sql = "SELECT * FROM env_proj_alloc_stat WHERE project_name = '" + dropdownValue + "';";
ConnectObj.Open(myConnect);
RS.Open(sql, ConnectObj, adOpenForwardOnly, adLockReadOnly, adCmdText);
//RS.GetRows;
var recordCount = RS.RecordCount;
var fieldcount = RS.Fields.Count;
document.getElementById("Label3").innerText = "sql --> " + sql
//alert ("recordCount: " + recordCount);
//var strRet = "the result -- ";
//RS.BOF
//while(!RS.EOF)
//{strRet = strRet + ';' + RS.Fields("env_alias");
//RS.MoveNext()
//}
//var recordCount = 0;

document.getElementById("Label4").innerText = "EOF --> " + RS.EOF
document.getElementById("Label5").innerText = "record count --> " + recordCount
document.getElementById("Label6").innerText = "field count --> " + fieldcount
//RS.MoveFirst;
document.getElementById("Label7").innerText = "BOF --> " + RS.BOF
//for (var i=1; i<6; i++)
//{
//alter ("The number is " + i + "<br>");
//alter ("column -- " + RS.Fields(i).Name);
//}

//for each x in RS.Fields
// document.getElementById("Label8").innerText = "name --> " + x.name;
// document.getElementById("Label9").innerText = "value --> " + x.value;
//next

//while (!RS.EOF)
//recordCount = RS.RecordCount;
//document.getElementById("Label8").innerText = "record --> " + RS.Index(1);
//alter ("inside loop");
// for each x in RS.Fields
// Response.Write(x.project_name)
// Response.Write(" = ")
// Response.Write(x.value)
// Response.Write(" have ")
// Response.Write(x.env_alias)
// Response.Write(" = ")
// Response.Write(x.value & "<br />")
// next
// Response.Write("<br />")
//RS.MoveNext()
//loop

//if RS.Supports(adApproxPosition)=true then
// i=RS.RecordCount
// response.write("The number of records is: " & i)
//end if


document.getElementById("Label10").innerText = "<--- i am here --> "

//if (RS.BOF && RS.EOF)
//{ alert ("no records");//No data
//}

RS.Close();
ConnectObj.Close();
//return strRet;
}

Your code is unlikely to work unless the IE browser security settings are set to 'none-existent'. A web page cannot usually access a local file, especially via ActiveX. You should make sure that you have errors notifications turned on (in settings advanced) so that you can tell if the ActiveX objects are being created.

If you have overcome this hurdle then your problem maybe the way you are retrieving the dropdown value. Try hardcoding a value that you know will work and seeing what happens. The normal way to get a dropdown value is: RecordCount isn't always populated, especially with a forward only recordset cursor until all records have been read.

__________________
Joe
http://joe.fawcett.name/

well i got around the previous issue...now i am able to get the data. the record count is still -1 but as joefawcett said, i guess its not always popualted or something. now the new issue is i am trying to get data into the spreadsheet activex object inside the access form. but my date fields in the db are displayed as some number in the form. i tried to find something like spreadsheet1.column(2).datatype = Date; or something. but i am still looking why is that there is not much help from MS on these things like you have the oracle documentation..which got anything and everything at one place.

my code is as below --

function selectDropdown2(){
var dropdownValue=document.getElementById("DropdownLis t1").value;
Spreadsheet1.Cells.ClearContents();

??? Spreadsheet1.Columns. ?????

document.getElementById("Label2").innerText = "";
adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;
var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\temp\\envstat.mdb";
var ConnectObj = new ActiveXObject("ADODB.Connection");
ConnectObj.Open(myConnect);
var rs = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT * FROM env_proj_alloc_stat WHERE project_name = '" + dropdownValue + "';";
rs.Open(sql, ConnectObj, adOpenForwardOnly, adLockReadOnly, adCmdText);
var recordCount = rs.RecordCount;
var fieldcount = rs.Fields.Count;
if(!rs.bof) {
rs.MoveFirst();
var x = 0;
while(!rs.eof) {
x = x+1;
//------------------the below works but commented for now--------
//for (var y=0;y < fieldcount;y++){
//Spreadsheet1.Cells(x, y+1).Value = rs.Fields.Item(y);
//}
//------------------------------------------------------------------

//------------------the below date stuff is not working-----
var d1 = new date();
d1 = rs.Fields.Item(2);
document.getElementById("Label2").innerText = "start date --" + d1;
//--------------------------

Spreadsheet1.Cells(x, 1).Value = rs.Fields.Item(0);
Spreadsheet1.Cells(x, 2).Value = rs.Fields.Item(1);
Spreadsheet1.Cells(x, 3).Value = rs.Fields.Item(2);
Spreadsheet1.Cells(x, 4).Value = rs.Fields.Item(3);
Spreadsheet1.Cells(x, 5).Value = rs.Fields.Item(4);

rs.MoveNext();
}
}
else {
document.getElementById("Label2").innerText = "No data found";
};
rs.Close();
ConnectObj.Close();
}

the access db returns the date column value as -- Sun Jan 1 00:00:00 UTC 2012

i tried everything on the face of the earth --
//------- start date --Sun Jan 1 00:00:00 UTC 2012
var s1 = rs.Fields.Item(2);
//var part = s1.split(" ");
//var str = "my date --> "+parseInt(part[5],10)+string(part[1])+parseInt(part[2],10);
//var mydate = new Date(parseInt(part[5],10),string(part[1]),parseInt(part[2],10));
//var month = mydate.getMonth();
//var year = mydate.getYear();
//var s2=month;
//var s3=year;
//var s4=getWeek(parseInt(part[2],10),parseInt(part[1],10)-1,parseInt(part[0],10));
//var s5 = s2 + s3 + s4;
//var d1 = Date.parse(s1.replace(/[a-z]/gi,''));
//var s2 = s1.replace(/"00:00:00 UTC "/gi,'');
//var d1 = new date(s1);
document.getElementById("Label2").innerText = "start date -- " + s1; //part[1] + part[2] + part[3];

but nothing seems to work...i am in coder's block now (synonymous to writer's block ;-p) help..........

This might help: Changing Cell-formating in Excel via VBA

In your case I think you need to set the NumberFormat of the cell to "dd/mm/yyyy" or similar.

You can search online for Excel VBA object API or use the recording trick from the first link above.

__________________
Joe
http://joe.fawcett.name/

this worked Spreadsheet1.Columns(3).NumberFormat = "dd/mm/yyyy";

also for the other stuff, i infact changed the sql query altogether --
var sql = "SELECT project_name,env_alias,format(alloc_start_dt,'dd/mm/yyyy'),alloc_end_tentative_dt,alloc_end_actual_dt FROM env_proj_alloc_stat WHERE project_name = '" + dropdownValue + "';";

thank you for your comments Joe