G
Guest
Hi,
I wanted to make the subject 'going insane' but that wouldn't help someone
find my question.
This is about as bizarre a thing as I've seen. The Access app rolled out in
December 2004 and worked fine until late April. The problem, a particular
procedure code should return five records, it now returns the oldest of the
five records, five times!
This is the properly working query when passing through to DB2 or run on
Quest.
SELECT PCL.* FROM RM_OMAP.PROCEDURE_CODE_LKUP PCL WHERE PCL.CODEPROCSERV=
'A4522' AND PCL.CODETYPESERV= 'A'
This is the query as run in Access which returns bad results. SELECT PCL.*
FROM PROCEDURE_CODE_LKUP AS PCL WHERE (((PCL.CODEPROCSERV)= 'A4522') AND
((PCL.CODETYPESERV)= 'A'))
Note I have run queries by specifically naming the fields instead of the
Select PCL.* and I get the same results.
The table is in DB2, when going against the form I use a linked table into
Access. When I convert the query to a pass through when I'm testing it works
fine and returns proper results. I also get proper results when I use ADO. My
problem at this point is I'm using the continous forms feature of Access and
it appears to me that it's using DAO. I either need a fix so it returns
proper records, or an alternative to the continous forms feature that can use
the ADO recordset.
When I run the SQL in the forms RecordSource property in the Access query
builder it also returns the oldest record 5 times. It randomly displays all
five records or just one record, seems like it's doing a select distinct some
of the time.
BTW this problem happens on machines using the DB2 ODBC drivers for Windows.
Run time clients 7.2 and 8.2. It also happens on machines with MDAC 2.5, 2.7
and 2.8 installed. All systems have Jet 4.0 and DAO 3.6. Client machines are
NT4, Windows2000 and XP. There was a firewall change on the network at
roughly the same time this happened but I'm told my connections do not go
through that firewall.
Oh, even more perplexing this isn't the only spot that I have a query
sending back multiple values to a continuous form. The other two places in
the app work fine. (I've probably cursed myself now.) Other than different
data the VBA code is the same in all locations. Code is pasted below my
signature.
Thanks in advance for any help you can offer.
~Mark
(or should I sign it banging head in Oregon?)
Working continous form VBA code.
DoCmd.OpenForm "frmResults", acNormal, , , , acWindowNormal
Form_frmResults.Visible = False
Form_frmResults.RecordSource = strSearchSELECTFROMSQL &
strSearchWHERESQL & strSearchORDERBYSQL
Form_frmResults.Requery
Form_frmResults.Visible = True
Broken continous form VBA code.
DoCmd.OpenForm "frmProcCodeSearch", acNormal, , , , acWindowNormal
Form_frmProcCodeSearch.Visible = False
Form_frmProcCodeSearch.RecordSource = strFormSQL
Form_frmProcCodeSearch.Requery
Form_frmProcCodeSearch.Visible = True
I wanted to make the subject 'going insane' but that wouldn't help someone
find my question.
This is about as bizarre a thing as I've seen. The Access app rolled out in
December 2004 and worked fine until late April. The problem, a particular
procedure code should return five records, it now returns the oldest of the
five records, five times!
This is the properly working query when passing through to DB2 or run on
Quest.
SELECT PCL.* FROM RM_OMAP.PROCEDURE_CODE_LKUP PCL WHERE PCL.CODEPROCSERV=
'A4522' AND PCL.CODETYPESERV= 'A'
This is the query as run in Access which returns bad results. SELECT PCL.*
FROM PROCEDURE_CODE_LKUP AS PCL WHERE (((PCL.CODEPROCSERV)= 'A4522') AND
((PCL.CODETYPESERV)= 'A'))
Note I have run queries by specifically naming the fields instead of the
Select PCL.* and I get the same results.
The table is in DB2, when going against the form I use a linked table into
Access. When I convert the query to a pass through when I'm testing it works
fine and returns proper results. I also get proper results when I use ADO. My
problem at this point is I'm using the continous forms feature of Access and
it appears to me that it's using DAO. I either need a fix so it returns
proper records, or an alternative to the continous forms feature that can use
the ADO recordset.
When I run the SQL in the forms RecordSource property in the Access query
builder it also returns the oldest record 5 times. It randomly displays all
five records or just one record, seems like it's doing a select distinct some
of the time.
BTW this problem happens on machines using the DB2 ODBC drivers for Windows.
Run time clients 7.2 and 8.2. It also happens on machines with MDAC 2.5, 2.7
and 2.8 installed. All systems have Jet 4.0 and DAO 3.6. Client machines are
NT4, Windows2000 and XP. There was a firewall change on the network at
roughly the same time this happened but I'm told my connections do not go
through that firewall.
Oh, even more perplexing this isn't the only spot that I have a query
sending back multiple values to a continuous form. The other two places in
the app work fine. (I've probably cursed myself now.) Other than different
data the VBA code is the same in all locations. Code is pasted below my
signature.
Thanks in advance for any help you can offer.
~Mark
(or should I sign it banging head in Oregon?)
Working continous form VBA code.
DoCmd.OpenForm "frmResults", acNormal, , , , acWindowNormal
Form_frmResults.Visible = False
Form_frmResults.RecordSource = strSearchSELECTFROMSQL &
strSearchWHERESQL & strSearchORDERBYSQL
Form_frmResults.Requery
Form_frmResults.Visible = True
Broken continous form VBA code.
DoCmd.OpenForm "frmProcCodeSearch", acNormal, , , , acWindowNormal
Form_frmProcCodeSearch.Visible = False
Form_frmProcCodeSearch.RecordSource = strFormSQL
Form_frmProcCodeSearch.Requery
Form_frmProcCodeSearch.Visible = True