Troubles with a parameter query

  • Thread starter Thread starter kirby
  • Start date Start date
K

kirby

I am using an OpenForm Macro that filters on a parameter query to open
a record in the user-friendly form view. The query prompts the user for
the "job no." and then displays the correct record. It has worked fine,
but lately it is failing to open records that I know exist.

The macro has to be working because it still brings up the form, it's
just empty. That is the typical behavior when no record is found.
However, I found the record in the table, so I know it exists. I
assumed it was user error, but even when I copy and paste the "job no."
from the table into the parameter box, it doesn't show up. This has
happened with multiple records.

Is my query tired? Dying? It's never done this to me before and I'm
feeling a little betrayed.

Any ideas what might be causing it?
Thanks so much!
 
You probably need to change the Query Fluid. It wears out every 300M or so.

Post your SQL Statement.
 
Query Fluid, who knew?
Here is my SQL. Thanks for your help, I am obviously in way over my
head!

SELECT [Q and D Database].[LS Number], [Q and D Database].[In Date], [Q
and D Database].[Due Date], [Q and D Database].[Out Date], [Q and D
Database].Description, [Q and D Database].[Type of Work], [Q and D
Database].[Country/10], [Q and D Database].[Source/Target], [Q and D
Database].Billing, [Q and D Database].Classification, [Q and D
Database].Requester, [Q and D Database].[Requester Info], [Q and D
Database].Translator, [Q and D Database].[Translator 2], [Q and D
Database].Reviewer, [Q and D Database].[Reviewer 2], [Q and D
Database].[Word Count], [Q and D Database].[Page Count], [Q and D
Database].Comments, [Q and D Database].FileLSTGGS, [Q and D
Database].FileLSTROM, [Q and D Database].[Fiscal Year], [Q and D
Database].[Project Type], [Q and D Database].[LS/T Branch], [Q and D
Database].[Source File], [Q and D Database].[Target File], [Q and D
Database].[Translation out], [Q and D Database].[Translation due], [Q
and D Database].[Translation in], [Q and D Database].[Review out], [Q
and D Database].[Review due], [Q and D Database].[Review in]
FROM [Q and D Database]
WHERE ((([Q and D Database].[LS Number]) Like [Enter LS Number] &
"*"));
 
Hi,

maybe there is a space starting the field, so I would try:

WHERE ((([Q and D Database].[LS Number]) Like "*" & [Enter LS Number] &
"*"));



Or maybe an option changed the wildcards, from * to % so try:

WHERE ((([Q and D Database].[LS Number]) Like "%" & [Enter LS Number] &
"%"));




Hoping it may help,
Vanderghast, Access MVP
 
Another thing-
Since I couldn't open the record, I decided to try to re-create it. In
theory, that shouldn't be possible because it would create a duplicate
primary key (LS number is the primary key). But my table doesn't catch
it! I can find both LS numbers, look at them, and nothing. It's as if
my table didn't know the first one was created. Not Good!!!! Can it
just disregard a record like that? Yikes!
 
Can you run the query without the form?

If so, does the query return results? If not then we must look at the
query. If the query returns records, then you need to look at the form and
its properties. And perhaps the code opening the form.


Michel Walsh said:
Hi,

maybe there is a space starting the field, so I would try:

WHERE ((([Q and D Database].[LS Number]) Like "*" & [Enter LS Number] &
"*"));



Or maybe an option changed the wildcards, from * to % so try:

WHERE ((([Q and D Database].[LS Number]) Like "%" & [Enter LS Number] &
"%"));




Hoping it may help,
Vanderghast, Access MVP




kirby said:
Query Fluid, who knew?
Here is my SQL. Thanks for your help, I am obviously in way over my
head!

SELECT [Q and D Database].[LS Number], [Q and D Database].[In Date], [Q
and D Database].[Due Date], [Q and D Database].[Out Date], [Q and D
Database].Description, [Q and D Database].[Type of Work], [Q and D
Database].[Country/10], [Q and D Database].[Source/Target], [Q and D
Database].Billing, [Q and D Database].Classification, [Q and D
Database].Requester, [Q and D Database].[Requester Info], [Q and D
Database].Translator, [Q and D Database].[Translator 2], [Q and D
Database].Reviewer, [Q and D Database].[Reviewer 2], [Q and D
Database].[Word Count], [Q and D Database].[Page Count], [Q and D
Database].Comments, [Q and D Database].FileLSTGGS, [Q and D
Database].FileLSTROM, [Q and D Database].[Fiscal Year], [Q and D
Database].[Project Type], [Q and D Database].[LS/T Branch], [Q and D
Database].[Source File], [Q and D Database].[Target File], [Q and D
Database].[Translation out], [Q and D Database].[Translation due], [Q
and D Database].[Translation in], [Q and D Database].[Review out], [Q
and D Database].[Review due], [Q and D Database].[Review in]
FROM [Q and D Database]
WHERE ((([Q and D Database].[LS Number]) Like [Enter LS Number] &
"*"));
Click to expand...
Click to expand...
 
If you are certain that the two records have the same primary key and that
the field is the primary key, then I would suspect that you have a corrupted
index (or indexes) in your database. In that case check out these
instructions from
Allen Browne.

1. Make a backup copy of the mdb file, without overwriting any existing
backups, in case something goes wrong.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

4. If the tables are attached, open the data file, and repeat steps 1 - 3
for that file as well.

5. Close Access. Decompile the database by entering something like this at
the command prompt while Access is not running. It is all one line, and
include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

6. Open Access, and compact again.

7. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

8. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

9. Compact again.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html
 
I tried Michael's suggested SQL (adding the wild cards), and that
solved the problem. Yay!!!! I am still curious as to why this
happened all of a sudden, so I may experiment with the corrupted index
theory. This can't be the first time that an extra space or something
has been inadvertently added, and nothing like this has ever happened
before. Especially not the primary key issue.
To answer the other question- my query was not giving any results, so
the form must not have been at fault.
Thank you so much for the advice.
 
The fun continues. Several of the users entered duplicate records
since they couldn't open the original record. Now I am trying to
delete the original records. However, when I try to delete them an
error message pops up: "The search key was not found."
This is bad because it's pulling up two almost-identical records in my
queries, etc. How can I delete those records? Why would it tell me
that the key isn't there when I can see it on the screen? Does this
sound like a symptom of corruption?
Thanks again
 
Back
Top