Running Query blanks out field in table...

L

Lorian

Help! I'm using a Select query that I run from a button on a form. When the
user clicks the "Run Query" button, tho, and the results table appears, the
system has somehow deleted the information in the first 3 fields for one
record in the table. I can't figure this out - anyone have any ideas? Thank
you for any insight you can share!
 
J

John Vinson

Help! I'm using a Select query that I run from a button on a form. When the
user clicks the "Run Query" button, tho, and the results table appears, the
system has somehow deleted the information in the first 3 fields for one
record in the table. I can't figure this out - anyone have any ideas? Thank
you for any insight you can share!
Click to expand...

One suggestion for starters: it's rarely a good idea to use query
datasheets for user editing or viewing. You may want to make a Form
(for onscreen) or Report (for printing) based on this query.

Why three fields should blank out in a query I have *NO* idea. Could
you post the SQL view of the query? Is the information actually
deleted from the table, or just not displayed? What's peculiar about
that record?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

Lorian

Actually, they are running it from a form. I built a form in which the user
enters whatever they know about the document they're looking for, and then
they click the "Run Query" button that will run the query and return the
results of what documents have the attributes they entered. That's where it
comes up with the fields blanked out, but I don't think it's the same record
each time. I'll have to check that.
And I don't think it's there but just not displaying, because when I open
the table afterwards, those fields are still empty. Here's the SQL view -
thanks for your help!

SELECT [Product Keys].[Content/Project File Name], [Product Keys].[File
Location], [Product Keys].[Source Origination], [Product Keys].[Information
Product Type], [Product Keys].[CMS Product Version], [Product Keys].[Product
Line], [Product Keys].[User Profile(s)]
FROM [Product Keys]
WHERE (([Content/Project File Name]=Forms!FormToRunQuery![Content/Project
File Name] Or Nz(Forms!FormToRunQuery![Content/Project File Name],"")=""))
And (([Source Origination]=Forms!FormToRunQuery![Source Origination] Or
Nz(Forms!FormToRunQuery![Source Origination],"")="")) And (([Information
Product Type]=Forms!FormToRunQuery![Information Product Type] Or
Nz(Forms!FormToRunQuery![Information Product Type],"")="")) And (([CMS
Product Version]=Forms!FormToRunQuery![CMS Product Version] Or
Nz(Forms!FormToRunQuery![CMS Product Version],"")="")) And (([Product
Line]=Forms!FormToRunQuery![Product Line] Or Nz(Forms!FormToRunQuery![Product
Line],"")="")) And (([User Profile(s)]=Forms!FormToRunQuery![User Profile(s)]
Or Nz(Forms!FormToRunQuery![User Profile(s)],"")=""));
 
J

John Vinson

And I don't think it's there but just not displaying, because when I open
the table afterwards, those fields are still empty. Here's the SQL view -
thanks for your help!

SELECT [Product Keys].[Content/Project File Name], [Product Keys].[File
Location], [Product Keys].[Source Origination], [Product Keys].[Information
Product Type], [Product Keys].[CMS Product Version], [Product Keys].[Product
Line], [Product Keys].[User Profile(s)]
FROM [Product Keys]
WHERE (([Content/Project File Name]=Forms!FormToRunQuery![Content/Project
File Name] Or Nz(Forms!FormToRunQuery![Content/Project File Name],"")=""))
And (([Source Origination]=Forms!FormToRunQuery![Source Origination] Or
Nz(Forms!FormToRunQuery![Source Origination],"")="")) And (([Information
Product Type]=Forms!FormToRunQuery![Information Product Type] Or
Nz(Forms!FormToRunQuery![Information Product Type],"")="")) And (([CMS
Product Version]=Forms!FormToRunQuery![CMS Product Version] Or
Nz(Forms!FormToRunQuery![CMS Product Version],"")="")) And (([Product
Line]=Forms!FormToRunQuery![Product Line] Or Nz(Forms!FormToRunQuery![Product
Line],"")="")) And (([User Profile(s)]=Forms!FormToRunQuery![User Profile(s)]
Or Nz(Forms!FormToRunQuery![User Profile(s)],"")=""));
Click to expand...

There is nothing in this query that COULD alter the contents of any
table. I think the query is a "red herring" - *something* is changing
the contents of your table, but this isn't it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

Lorian

Thanks, John - so do you, or does anyone else have any ideas as to what could
be causing this? I'm really apprehensive about putting this out for wide use
in the company while this behavior is still going on. Managing one deletion
out of 15 records is easy - once everyone starts using the database and we
get hundreds of items in there, it will be a nightmare!

thanks!

John Vinson said:
And I don't think it's there but just not displaying, because when I open
the table afterwards, those fields are still empty. Here's the SQL view -
thanks for your help!

SELECT [Product Keys].[Content/Project File Name], [Product Keys].[File
Location], [Product Keys].[Source Origination], [Product Keys].[Information
Product Type], [Product Keys].[CMS Product Version], [Product Keys].[Product
Line], [Product Keys].[User Profile(s)]
FROM [Product Keys]
WHERE (([Content/Project File Name]=Forms!FormToRunQuery![Content/Project
File Name] Or Nz(Forms!FormToRunQuery![Content/Project File Name],"")=""))
And (([Source Origination]=Forms!FormToRunQuery![Source Origination] Or
Nz(Forms!FormToRunQuery![Source Origination],"")="")) And (([Information
Product Type]=Forms!FormToRunQuery![Information Product Type] Or
Nz(Forms!FormToRunQuery![Information Product Type],"")="")) And (([CMS
Product Version]=Forms!FormToRunQuery![CMS Product Version] Or
Nz(Forms!FormToRunQuery![CMS Product Version],"")="")) And (([Product
Line]=Forms!FormToRunQuery![Product Line] Or Nz(Forms!FormToRunQuery![Product
Line],"")="")) And (([User Profile(s)]=Forms!FormToRunQuery![User Profile(s)]
Or Nz(Forms!FormToRunQuery![User Profile(s)],"")=""));
Click to expand...

There is nothing in this query that COULD alter the contents of any
table. I think the query is a "red herring" - *something* is changing
the contents of your table, but this isn't it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
J

John Vinson

Thanks, John - so do you, or does anyone else have any ideas as to what could
be causing this? I'm really apprehensive about putting this out for wide use
in the company while this behavior is still going on. Managing one deletion
out of 15 records is easy - once everyone starts using the database and we
get hundreds of items in there, it will be a nightmare!
Click to expand...

It's something in your code; something on one of your forms (i.e. you
may have some bound controls which users are blanking out); something
in some OTHER query - not this one. Since I cannot see your database I
can do no more than speculate what it might be.

But stop worring about THIS QUERY, because this query - by itself - is
*incapable* of causing this effect. Look at your code, your forms,
your *other* queries.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
J

John Vinson

Thanks, John - so do you, or does anyone else have any ideas as to what could
be causing this? I'm really apprehensive about putting this out for wide use
in the company while this behavior is still going on. Managing one deletion
out of 15 records is easy - once everyone starts using the database and we
get hundreds of items in there, it will be a nightmare!
Click to expand...

On relooking at your query: are the combo boxes used as criteria in
the query *bound*? I.e. do they have Control Source properties other
than blank? If so, when the user selects a record on the form; selects
a combo box; blanks it out; and runs the search - Access will
dutifully update the field to which the combo box is bound to NULL,
since that's exactly what you're asking it to do!

Combo boxes used as query criteria MUST be *UNBOUND*.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

Lorian

Thanks, John - I'll bet that's it! I'll check it out... thanks again! (there
are no other queries, and no one could be blanking it out because so far I'm
the only one using the thing...
 
L

Lorian

OK - i do think that was the problem. Now, correcting this by making all the
combo boxes unbound has caused another issue: when I run the query, only the
first record in the table appears, even if it doesn't match the criteria. How
do I need to change the query now that the controls are unbound? Thanks!
 
J

John Vinson

OK - i do think that was the problem. Now, correcting this by making all the
combo boxes unbound has caused another issue: when I run the query, only the
first record in the table appears, even if it doesn't match the criteria. How
do I need to change the query now that the controls are unbound? Thanks!
Click to expand...

You need TWO SEPARATE CONTROLS for each field - one (a combo box,
unbound) to enter the criteria, and a different control (combo box or
textbox, your choice) to display the results. Or, you can use the
builtin Query By Form feature to use the same form and controls for
both purposes.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

Lorian

(sorry I took so long to respond - I moved over to another project until today)
Thanks, John - I think that will do it. I've got the knowledge base article
 
L

Lorian

It looks like the QBF would work, except that I have so many fields that the
user could either specify or leave null (to accept all values for that field)
that I'm afraid the criteria will get unworkable very quickly. How do I use a
separate control to display the results? Maybe that would work better. Thanks
again for all your help...

Lorian
 
L

Lorian

Actually, since each control just has 2 possible outcomes - to either match
the entry or be null, maybe it will work with the QBF, So i'm trying that.
Thanks...
 
L

Lorian

Thank you, thank you!

I had just finished writing a new post asking for help with the SQL because
it wasn't returning the correct results, but then I tried removing the bits
that the system had put in about "<>False", and now it's working beautifully
- thank you - you're a lifesaver!!
 
Top