Inconsistent query results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a program with a form with a combo box whose source is a query. The
user selects part # from the box. A command button opens another form that
finds that part# in the database and displays it. That form’s record source
is a query that looks up the part. That query gets the part# through this
parm [Forms]![frmSearchTerms]![cboDeuPart].

The database has 4 tables related by keys (with multiple fields) in each
table and the number of records is about 750K total. It is a history database
so no new records are being added, but it is impossible to test all records.

Two of us ran the program on two different PC’s (not on a network) with the
same exact program and database.

During final testing I discovered several cases where the query to show the
part# in the combo box cannot find the part on one of the pc’s.

Part#’s can be found in combo box, but the results are a blank screen
(couldn’t find the part) on the other pc.

Yet when I look at the results of the query directly I can find the part.
When I look at the specific table the part is in it is there. This applies
to each pc.

The part# is also in the combo query in the PC that displays the not in list
message.

Maybe 90% of the searches are ok and correct, but a few show these results.
There is no code involved in the searches. Only queries are the source of
the database searches.

So far, the same part’s that give the not in list message on one pc are the
same ones that show up in the list on the other pc, but show no query results.

I am doing lots of different tyoes of searches, some almost exactly like
this, but on a differrent field such as vendor part number, and those all
seem to work with the same type of query but with the other specific data as
the parameter.

Has anyone ever had such inconsistent results of using queries? Is there
something I am doing wrong? Is there more to searching a related table
database than using a simple query?

Any help would be appreciated.

Thanks,
Chuck
 
Select the CBO. Press [ctrl][c] (copy)
open a text document, and Paste the value.

Does it look like the value you expected?

(david)
 
Since you're dealing with a combo box, the first question is how many
columns are in the combo box? If more than one, which column is the Bound
Column? This is the "value" that will be returned by the combo box and it
may not be the value displayed in the combo box.
 
Thanks David and Wayne.

Cutting and pasting the value in the combo box into notepad shows the
correct value. Yet in the query itself I can paste this same value into the
Find box on the part# query column and it will find it.

The query for the combo box only has one bound column.

Note that everything works fine for over 95% of the searches. It's just a
few we've found that seem to have a problem finding. I must have done several
hundred searches in writing the program and they all worked.

Looking at the part numbers it cannot find, directly in the query that
returns them, I can see nothing different about any of them except in one
case there are a series of about six or seven that are nearly in consecutive
rows that it cannot find.

Thanks,

Chuck

Wayne Morgan said:
Since you're dealing with a combo box, the first question is how many
columns are in the combo box? If more than one, which column is the Bound
Column? This is the "value" that will be returned by the combo box and it
may not be the value displayed in the combo box.

--
Wayne Morgan
MS Access MVP


chuckh said:
I have a program with a form with a combo box whose source is a query. The
user selects part # from the box. A command button opens another form
that
finds that part# in the database and displays it. That form's record
source
is a query that looks up the part. That query gets the part# through this
parm [Forms]![frmSearchTerms]![cboDeuPart].

The database has 4 tables related by keys (with multiple fields) in each
table and the number of records is about 750K total. It is a history
database
so no new records are being added, but it is impossible to test all
records.

Two of us ran the program on two different PC's (not on a network) with
the
same exact program and database.

During final testing I discovered several cases where the query to show
the
part# in the combo box cannot find the part on one of the pc's.

Part#'s can be found in combo box, but the results are a blank screen
(couldn't find the part) on the other pc.

Yet when I look at the results of the query directly I can find the part.
When I look at the specific table the part is in it is there. This
applies
to each pc.

The part# is also in the combo query in the PC that displays the not in
list
message.

Maybe 90% of the searches are ok and correct, but a few show these
results.
There is no code involved in the searches. Only queries are the source of
the database searches.

So far, the same part's that give the not in list message on one pc are
the
same ones that show up in the list on the other pc, but show no query
results.

I am doing lots of different tyoes of searches, some almost exactly like
this, but on a differrent field such as vendor part number, and those all
seem to work with the same type of query but with the other specific data
as
the parameter.

Has anyone ever had such inconsistent results of using queries? Is there
something I am doing wrong? Is there more to searching a related table
database than using a simple query?

Any help would be appreciated.

Thanks,
Chuck
Click to expand...
Click to expand...
 
Is this a split database (one with the tables in a back-end file)? If so,
have you compacted the back-end file recently? If it's not split, then
compact the whole file. To do so, you'll need everyone out of the database
so that you can get exclusive access to the file. When you compact, Access
will recreate the indexes. If these are messed up, it can cause the problem
you mention.

If that doesn't help, please post the SQL view of your query.
 
Hi Wayne,

My database compacts on close. I compacted it anyway following your
directions and that did not eliminate the problem. This particular program
does not have a split database.

The sql is:

SELECT Header.PuhOrderNbr, Lines.PulOrderNbr, Lines.PulItemNbr,
Header.PuhVendorCode, Lines.PulVendorPart, Lines.PulDueDate,
Lines.PulOrderDate, Lines.PulLastChgDate, Recv.PurDateReceived,
Lines.PulDateReopened, Lines.PulQtyOrdered, Lines.PulQtyReceived,
Lines.PulUnitPrice, Recv.PurReceiptNbr, Lines.PulUnitMeas, Header.PuhTaxable,
Desc.PudDesc, Lines.PulDeutschPart, Header.PuhRemarks, Header.PuhRqstdBy,
Header.PuhDeliverTo, Header.PuhConfirmingTo, Lines.PulClass, Header.PuhBuyer,
Header.PuhProjName, Header.PuhProjNbr, Header.PuhCertReq,
Header.PuhPymtTerms, Header.PuhFOBPoint, Header.PuhShipVia,
Header.PuhDPS_DMS, Header.PuhShipTo
FROM Recv INNER JOIN ([Desc] INNER JOIN (Header INNER JOIN Lines ON
Header.PuhOrderNbr = Lines.PulOrderNbr) ON (Desc.PudOrderNbr =
Lines.PulOrderNbr) AND (Desc.PudItemNbr = Lines.PulItemNbr)) ON
(Recv.PurOrderNbr = Lines.PulOrderNbr) AND (Recv.PurItemNbr =
Lines.PulItemNbr)
WHERE (((Lines.PulDeutschPart)=[Forms]![frmSearchTerms]![cboDeuPart]))
ORDER BY Lines.PulOrderNbr, Lines.PulDeutschPart;

Hope this helps.

Thanks for your help.

Chuck
 
Hi Wayne,

If it will help here is more info on the program/query.

System is a Purchase Order History system.
There are four tables. Lines table has about 525K records on it.

Header -prefix is puh; key is orderNbr; this is the entry into the db
Lines - prefix is pul; key is orderNbr, itemNbr; part# is on this table
(what I'm searching for); there are multiple lines per order no.
Recv- prefix is pur: key is orderNbr, itemNbr; each line can have multiple
receipts
Desc -prefix is pud; key is orderNbr, itemNbr, seqNbr; each line has
multiple descriptions

The keys are separate fields in the tables and are not treated as multiple
field keys.
Header is main table that has the order no.'s (one per record)
Lines is secondary table joined to header by orderNbr.
Desc is joined to Lines by orderNbr and also itemNbr.
Recv is joined to Lines by orderNbr and also itemNbr.

The search finds the part no. on Lnes table and displays fields from all
the tables.

There are several other searches on other fields, all using combo boxes and
similar queries, but with the specific field data coming from a
corresponding combobox. I have found no such problem with the other searches
(although if it happens with part# it might happen with the others). All
these search results are shown on the same form with different recordsources
corresponding to the query used in the search.

Maybe this might help.

Thanks,

Chuck
 
Clutching at straws --

Please post the query for the CBO.

I take it that Header and Lines are tables,
not queries? Do you have an aggregate queries
involved at all?

Try declaring "[Forms]![frmSearchTerms]![cboDeuPart]"
as a formal parameter.

Return [Forms]![frmSearchTerms]![cboDeuPart]) as a
field in the query.

Use a hidden textbox instead of the CBO. Update the
textbox in the CBO after update event. You will have
to manually requery the query.

(david)

chuckh said:
Hi Wayne,

My database compacts on close. I compacted it anyway following your
directions and that did not eliminate the problem. This particular program
does not have a split database.

The sql is:

SELECT Header.PuhOrderNbr, Lines.PulOrderNbr, Lines.PulItemNbr,
Header.PuhVendorCode, Lines.PulVendorPart, Lines.PulDueDate,
Lines.PulOrderDate, Lines.PulLastChgDate, Recv.PurDateReceived,
Lines.PulDateReopened, Lines.PulQtyOrdered, Lines.PulQtyReceived,
Lines.PulUnitPrice, Recv.PurReceiptNbr, Lines.PulUnitMeas, Header.PuhTaxable,
Desc.PudDesc, Lines.PulDeutschPart, Header.PuhRemarks, Header.PuhRqstdBy,
Header.PuhDeliverTo, Header.PuhConfirmingTo, Lines.PulClass, Header.PuhBuyer,
Header.PuhProjName, Header.PuhProjNbr, Header.PuhCertReq,
Header.PuhPymtTerms, Header.PuhFOBPoint, Header.PuhShipVia,
Header.PuhDPS_DMS, Header.PuhShipTo
FROM Recv INNER JOIN ([Desc] INNER JOIN (Header INNER JOIN Lines ON
Header.PuhOrderNbr = Lines.PulOrderNbr) ON (Desc.PudOrderNbr =
Lines.PulOrderNbr) AND (Desc.PudItemNbr = Lines.PulItemNbr)) ON
(Recv.PurOrderNbr = Lines.PulOrderNbr) AND (Recv.PurItemNbr =
Lines.PulItemNbr)
WHERE (((Lines.PulDeutschPart)=[Forms]![frmSearchTerms]![cboDeuPart]))
ORDER BY Lines.PulOrderNbr, Lines.PulDeutschPart;

Hope this helps.

Thanks for your help.

Chuck

Wayne Morgan said:
Is this a split database (one with the tables in a back-end file)? If so,
have you compacted the back-end file recently? If it's not split, then
compact the whole file. To do so, you'll need everyone out of the database
so that you can get exclusive access to the file. When you compact, Access
will recreate the indexes. If these are messed up, it can cause the problem
you mention.

If that doesn't help, please post the SQL view of your query.
Click to expand...
Click to expand...
 
You open the second form from a command button. The query you have below is
the Record Source for this form and so the second form is filtered by the
value in the combo box on the first form. When you are done with the second
form do you close it or hide it? If you issue a Requery to the second form
does it give the correct result?
 
Thanks Wayne and David,

David:
Header and Lines are tables. Not sure what aggregrate queries are, but I
doubt I have one. I just have a simple query. The part number is taken
directly from the cbo and is a returned field in the query.

Query for cbo:
SELECT DISTINCT Lines.PulDeutschPart, Desc.PudSeqNbr, Desc.PudDesc
FROM Lines INNER JOIN [Desc] ON (Lines.PulOrderNbr = Desc.PudOrderNbr) AND
(Lines.PulItemNbr = Desc.PudItemNbr)
WHERE (((Lines.PulDeutschPart) Is Not Null) AND ((Desc.PudSeqNbr)=1))
ORDER BY Desc.PudSeqNbr;

Wayne:
Yes, I open the second form from a command button. When I am done I leave
the second form open. However every time I query for a part (or any of the
other possible fields) I first close the second form, if it is open, so I
know for sure the record source will start fresh with the correct query as
the record source. Since the user has many possible things they can search
for (including searching from a third form) and they are all determined in
Form_Open (of the second form) I was forced to close the second form to make
sure it always had the correct record source, otherwise I’d have to change my
code and it was tricky setting it up as it was.

I tried keeping the form open, but it did not work correctly, so I resorted
to closing the form. Forgot why, but I think it may have been using one of
the other possible queries as the recordsource when I left the form open and
requeried from the first search selection form.

I don’t think I ever tried a requery on the second form as an option because
the record source is determined in Form_Open and that is not triggered again
in a requery to my knowledge.

Thanks,
Chuck


Wayne Morgan said:
You open the second form from a command button. The query you have below is
the Record Source for this form and so the second form is filtered by the
value in the combo box on the first form. When you are done with the second
form do you close it or hide it? If you issue a Requery to the second form
does it give the correct result?

--
Wayne Morgan
MS Access MVP


chuckh said:
Hi Wayne,

My database compacts on close. I compacted it anyway following your
directions and that did not eliminate the problem. This particular
program
does not have a split database.

The sql is:

SELECT Header.PuhOrderNbr, Lines.PulOrderNbr, Lines.PulItemNbr,
Header.PuhVendorCode, Lines.PulVendorPart, Lines.PulDueDate,
Lines.PulOrderDate, Lines.PulLastChgDate, Recv.PurDateReceived,
Lines.PulDateReopened, Lines.PulQtyOrdered, Lines.PulQtyReceived,
Lines.PulUnitPrice, Recv.PurReceiptNbr, Lines.PulUnitMeas,
Header.PuhTaxable,
Desc.PudDesc, Lines.PulDeutschPart, Header.PuhRemarks, Header.PuhRqstdBy,
Header.PuhDeliverTo, Header.PuhConfirmingTo, Lines.PulClass,
Header.PuhBuyer,
Header.PuhProjName, Header.PuhProjNbr, Header.PuhCertReq,
Header.PuhPymtTerms, Header.PuhFOBPoint, Header.PuhShipVia,
Header.PuhDPS_DMS, Header.PuhShipTo
FROM Recv INNER JOIN ([Desc] INNER JOIN (Header INNER JOIN Lines ON
Header.PuhOrderNbr = Lines.PulOrderNbr) ON (Desc.PudOrderNbr =
Lines.PulOrderNbr) AND (Desc.PudItemNbr = Lines.PulItemNbr)) ON
(Recv.PurOrderNbr = Lines.PulOrderNbr) AND (Recv.PurItemNbr =
Lines.PulItemNbr)
WHERE (((Lines.PulDeutschPart)=[Forms]![frmSearchTerms]![cboDeuPart]))
ORDER BY Lines.PulOrderNbr, Lines.PulDeutschPart;
Click to expand...
Click to expand...
 
The query for the combo box shows 3 columns, what is the Bound Column number
in the combo box's properties? By your previous description I suspect it
should be 2 (the PudSeqNbr field, unless PulDeutschPart is the part number
in which case it should be 1) or you need to use the Column property of the
combo box instead of the Value property so that you can refer to the second
column.

The question about the Requery was if you were hiding the form then
"opening" it again from a hidden state, you may still have the old record
source. If you close the form, this shouldn't be a problem.
 
Wayne,

The combo box has only one column, the part number: PulDeutschPart

The sequence number PudSeqNbr was used for sorting to show the description,
but now I am not using them. I guess I should remove them since you noticed
them and they are not used.

Ok. I close the form between searches so that is not the problem.

Chuck

Wayne Morgan said:
The query for the combo box shows 3 columns, what is the Bound Column number
in the combo box's properties? By your previous description I suspect it
should be 2 (the PudSeqNbr field, unless PulDeutschPart is the part number
in which case it should be 1) or you need to use the Column property of the
combo box instead of the Value property so that you can refer to the second
column.

The question about the Requery was if you were hiding the form then
"opening" it again from a hidden state, you may still have the old record
source. If you close the form, this shouldn't be a problem.

--
Wayne Morgan
MS Access MVP


chuckh said:
Thanks Wayne and David,

David:
Header and Lines are tables. Not sure what aggregrate queries are, but I
doubt I have one. I just have a simple query. The part number is taken
directly from the cbo and is a returned field in the query.

Query for cbo:
SELECT DISTINCT Lines.PulDeutschPart, Desc.PudSeqNbr, Desc.PudDesc
FROM Lines INNER JOIN [Desc] ON (Lines.PulOrderNbr = Desc.PudOrderNbr) AND
(Lines.PulItemNbr = Desc.PudItemNbr)
WHERE (((Lines.PulDeutschPart) Is Not Null) AND ((Desc.PudSeqNbr)=1))
ORDER BY Desc.PudSeqNbr;

Wayne:
Yes, I open the second form from a command button. When I am done I leave
the second form open. However every time I query for a part (or any of
the
other possible fields) I first close the second form, if it is open, so I
know for sure the record source will start fresh with the correct query as
the record source. Since the user has many possible things they can
search
for (including searching from a third form) and they are all determined in
Form_Open (of the second form) I was forced to close the second form to
make
sure it always had the correct record source, otherwise I'd have to change
my
code and it was tricky setting it up as it was.

I tried keeping the form open, but it did not work correctly, so I
resorted
to closing the form. Forgot why, but I think it may have been using one
of
the other possible queries as the recordsource when I left the form open
and
requeried from the first search selection form.

I don't think I ever tried a requery on the second form as an option
because
the record source is determined in Form_Open and that is not triggered
again
in a requery to my knowledge.
Click to expand...
Click to expand...
 
Try it again without the "distinct" key word.

Remove the unused columns.

Get rid of "Order by" - you only selected "PudSeqNbr = 1"

(david)


chuckh said:
Thanks Wayne and David,

David:
Header and Lines are tables. Not sure what aggregrate queries are, but I
doubt I have one. I just have a simple query. The part number is taken
directly from the cbo and is a returned field in the query.

Query for cbo:
SELECT DISTINCT Lines.PulDeutschPart, Desc.PudSeqNbr, Desc.PudDesc
FROM Lines INNER JOIN [Desc] ON (Lines.PulOrderNbr = Desc.PudOrderNbr) AND
(Lines.PulItemNbr = Desc.PudItemNbr)
WHERE (((Lines.PulDeutschPart) Is Not Null) AND ((Desc.PudSeqNbr)=1))
ORDER BY Desc.PudSeqNbr;

Wayne:
Yes, I open the second form from a command button. When I am done I leave
the second form open. However every time I query for a part (or any of
the
other possible fields) I first close the second form, if it is open, so I
know for sure the record source will start fresh with the correct query as
the record source. Since the user has many possible things they can
search
for (including searching from a third form) and they are all determined in
Form_Open (of the second form) I was forced to close the second form to
make
sure it always had the correct record source, otherwise I’d have to
change my
code and it was tricky setting it up as it was.

I tried keeping the form open, but it did not work correctly, so I
resorted
to closing the form. Forgot why, but I think it may have been using one
of
the other possible queries as the recordsource when I left the form open
and
requeried from the first search selection form.

I don’t think I ever tried a requery on the second form as an option
because
the record source is determined in Form_Open and that is not triggered
again
in a requery to my knowledge.

Thanks,
Chuck


Wayne Morgan said:
You open the second form from a command button. The query you have below
is
the Record Source for this form and so the second form is filtered by the
value in the combo box on the first form. When you are done with the
second
form do you close it or hide it? If you issue a Requery to the second
form
does it give the correct result?

--
Wayne Morgan
MS Access MVP


chuckh said:
Hi Wayne,

My database compacts on close. I compacted it anyway following your
directions and that did not eliminate the problem. This particular
program
does not have a split database.

The sql is:

SELECT Header.PuhOrderNbr, Lines.PulOrderNbr, Lines.PulItemNbr,
Header.PuhVendorCode, Lines.PulVendorPart, Lines.PulDueDate,
Lines.PulOrderDate, Lines.PulLastChgDate, Recv.PurDateReceived,
Lines.PulDateReopened, Lines.PulQtyOrdered, Lines.PulQtyReceived,
Lines.PulUnitPrice, Recv.PurReceiptNbr, Lines.PulUnitMeas,
Header.PuhTaxable,
Desc.PudDesc, Lines.PulDeutschPart, Header.PuhRemarks,
Header.PuhRqstdBy,
Header.PuhDeliverTo, Header.PuhConfirmingTo, Lines.PulClass,
Header.PuhBuyer,
Header.PuhProjName, Header.PuhProjNbr, Header.PuhCertReq,
Header.PuhPymtTerms, Header.PuhFOBPoint, Header.PuhShipVia,
Header.PuhDPS_DMS, Header.PuhShipTo
FROM Recv INNER JOIN ([Desc] INNER JOIN (Header INNER JOIN Lines ON
Header.PuhOrderNbr = Lines.PulOrderNbr) ON (Desc.PudOrderNbr =
Lines.PulOrderNbr) AND (Desc.PudItemNbr = Lines.PulItemNbr)) ON
(Recv.PurOrderNbr = Lines.PulOrderNbr) AND (Recv.PurItemNbr =
Lines.PulItemNbr)
WHERE (((Lines.PulDeutschPart)=[Forms]![frmSearchTerms]![cboDeuPart]))
ORDER BY Lines.PulOrderNbr, Lines.PulDeutschPart;
Click to expand...
Click to expand...
Click to expand...
 
Wayne thanks for your help.
David thanks for your help.

Solved the problem. My join type in my query was wrong. It was looking for
matches for all four of the tables on order no. and item no. However there
apperently were records that had no recv matching record and the case in
point was one of those cases.

Changing the join type from #1 to #3 to include all Lines records and
matching recv records solved it.

Thanks again.

Chuck
 
Back
Top