UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Parameter Query Not Working, Access 2010    
 
   
EllenMarie
post Apr 20 2017, 06:48 AM
Post#1



Posts: 141
Joined: 4-August 10



Hello,
I have a table that is indexed on two fields(composite unique index). (see http://www.UtterAccess.com/forum/index.php...wtopic=2042927; Index On Two Fields, Combination Needs To Be Unique, Access 2010). Additionally, one of those fields has a look-up list.

My "FindSubmissionNumber" parameter query doesn't appear to be working. Have I overloaded my fields with too many things to preclude use of a parameter query?

Is there a way to add an extraneous field with the look up feature that will programatically update the "type" field once the record is entered? At this point the look up would be removed from the indexed field.

I'll be adding forms for easy data entry.

Thanks in advance for your help,
Ellen
Attached File(s)
Attached File  test1.zip ( 25.89K )Number of downloads: 2
 
Go to the top of the page
 
ScottGem
post Apr 20 2017, 07:15 AM
Post#2


UtterAccess VIP / UA Clown
Posts: 31,963
Joined: 21-January 04
From: LI, NY


First it is NOT recommended that you use lookup fields on the table level. this causes more problems then they are worth. Lookups should be done on the form level using list controls. So I would recommend removing the lookup from the table. Second, how is DocNum generated?

But the problem with your query is that you are querying a field that has no data in it. You are prompting to "submission number", but you don't define what that is. And I wouldn't use a parameter prompt anyway. They are too limiting. I would use a form to supply criteria to the query using the syntax:

Forms!formname!controlname




--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
EllenMarie
post Apr 20 2017, 07:26 AM
Post#3



Posts: 141
Joined: 4-August 10



OK. I'll add the lookup at the form level. Didn't know that was an option. The DOCNUM is not a generated field.
"submission number" is TYPE+DOCNUM.
Thanks
Go to the top of the page
 
EllenMarie
post Apr 20 2017, 07:33 AM
Post#4



Posts: 141
Joined: 4-August 10



there will be other fields for data entry. This is a stripped down version.
Go to the top of the page
 
ScottGem
post Apr 20 2017, 08:09 AM
Post#5


UtterAccess VIP / UA Clown
Posts: 31,963
Joined: 21-January 04
From: LI, NY


QUOTE
"submission number" is TYPE+DOCNUM.


Ok, then your query needs a calculated column where you concatenate the 2 fields. Then you should have a form with a combobox where the user can select from that column. In addition. The table should have a surrogate PK like an Autonumber.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
EllenMarie
post Apr 20 2017, 08:22 AM
Post#6



Posts: 141
Joined: 4-August 10



Thanks, Scott! I now have a query with those two fields concatenated. And it works with a parameter query. YES! Next step is to prepare the forms with the look ups.
Go to the top of the page
 
ScottGem
post Apr 20 2017, 08:48 AM
Post#7


UtterAccess VIP / UA Clown
Posts: 31,963
Joined: 21-January 04
From: LI, NY


Glad to assist

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd April 2017 - 02:46 PM