Home

Parameter Query

%3CLINGO-SUB%20id%3D%22lingo-sub-193765%22%20slang%3D%22en-US%22%3EParameter%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193765%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20form%20with%20several%20combo%20boxes.%20table%20is%20updated%20and%20getting%20all%20records.%3C%2FP%3E%3CP%3EWhen%20I%20created%20query%20without%20any%20parameters%20i%20got%20all%20records.%20But%20when%20I%20am%20adding%20parameter%20like%20%5BEnter%20Company%20Name%5D.%20I%20am%20getting%20all%20fields%20blank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-193765%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-234252%22%20slang%3D%22en-US%22%3ERe%3A%20Parameter%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-234252%22%20slang%3D%22en-US%22%3E%3CP%3EUsually%20if%20you%20give%20a%20parameter%20a%20query%20you%20also%20need%20to%20inform%20the%20database%20what%20you%20want%20done%20with%20that%20parameter%20eg%20re-query%20the%20data%20selecting%20only%20the%20submitted%20filter%20(for%20a%20select%20query).%3C%2FP%3E%3CP%3EIf%20the%20parameter%20is%20part%20of%20a%20multi%20table%20query%20you%20must%20also%20ensure%20that%20the%20field%20you%20are%20querying%20on%20is%20either%20key%20or%20a%20foreign%20key%20in%20associated%20tables%20(nb%20the%20key%20doesn't%20have%20to%20be%20visible)%20otherwise%20you%20may%20get%20situations%20where%20Cartesian%20joins%20happen.%20You%20may%20also%20have%20to%20look%20at%20the%20design%20of%20your%20tables%20to%20establish%20what%20level%20of%20Normal%20Form%20is%20being%20used%20-%20most%20tables%20would%20go%20to%203rd%20Normal%20form%20in%20design.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227444%22%20slang%3D%22en-US%22%3ERe%3A%20Parameter%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227444%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20parameters%20like%20this%20in%20a%20query%20is%20not%20a%20good%20practice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20better%20approach%20is%20to%20provide%20a%20form%20where%20the%20user%20can%20select%20the%20company%20name%20from%20a%20list%20(List%20box%20or%20Combo%20box)%20of%20Valid%20Choices%20and%20then%20use%20that%20as%20the%20criteria%20for%20opening%20a%20report.%20For%20example%3C%2FP%3E%3CP%3E%26nbsp%3BIf%20Not%20IsNull(Me.lstCompany)%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20DoCmd.OpenReport%20%22rptCompany%22%2C%20acViewPreview%2C%20%2C%20%22Company_ID%20%3D%20%22%20%26amp%3B%20Me.lstCompany%3CBR%20%2F%3EElse%3C%2FP%3E%3CP%3E'%26nbsp%3BIf%20no%20company%20is%20selected%20then%20you%20don't%20send%20a%20criteria%20to%20the%20report%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20DoCmd.OpenReport%20%22rptCompany%22%2C%20acViewPreview%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3Eand%20you%20will%20notices%20that%20in%20this%20example%20I've%20used%20the%20Primary%20Key%20of%20the%20Company%20table%20for%20the%20filter.%20The%20list%20box%20uses%20this%20field%20as%20its%20first%20column%2C%20actually%20column(0)%2C%26nbsp%3Bwith%20its%20column%20width%20set%20to%20zero%20and%20the%20Company%20Name%20as%20the%20second%20column%20that%20the%20user%20does%20see.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGiving%20users%20direct%20access%20to%20a%20table%20or%20query%20is%20not%20good%20practice.%20Users%20should%20be%20presented%20with%20data%20in%20either%20a%20form%20or%20report%20where%20there%20is%20some%20control%20(eg%20Validation)%20on%20what%20the%20user%20can%20add%2C%20edit%20or%20delete.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-195270%22%20slang%3D%22en-US%22%3ERe%3A%20Parameter%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-195270%22%20slang%3D%22en-US%22%3EWe'd%20have%20to%20have%20more%20details.%3CBR%20%2F%3E%3CBR%20%2F%3EFirst%2C%20where%20are%20you%20trying%20to%20add%20this%20parameter%3F%20How%3F%20The%20best%20way%20to%20explain%20it%20is%20to%20show%20us%20the%20actual%20SQL%20from%20the%20query.%20%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193914%22%20slang%3D%22en-US%22%3ERE%3A%20Parameter%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193914%22%20slang%3D%22en-US%22%3EHi%20Guys%2C%20Please%20somebody%20help%20me%20to%20solve%20this%20issue%20thanks%3C%2FLINGO-BODY%3E
Safeesh Rawther
New Contributor

I have created form with several combo boxes. table is updated and getting all records.

When I created query without any parameters i got all records. But when I am adding parameter like [Enter Company Name]. I am getting all fields blank

 

Thanks

 

 

 

 

4 Replies
Hi Guys, Please somebody help me to solve this issue thanks
We'd have to have more details.

First, where are you trying to add this parameter? How? The best way to explain it is to show us the actual SQL from the query.

Using parameters like this in a query is not a good practice.

 

A better approach is to provide a form where the user can select the company name from a list (List box or Combo box) of Valid Choices and then use that as the criteria for opening a report. For example

 If Not IsNull(Me.lstCompany) Then
    DoCmd.OpenReport "rptCompany", acViewPreview, , "Company_ID = " & Me.lstCompany
Else

' If no company is selected then you don't send a criteria to the report
    DoCmd.OpenReport "rptCompany", acViewPreview
End If

and you will notices that in this example I've used the Primary Key of the Company table for the filter. The list box uses this field as its first column, actually column(0), with its column width set to zero and the Company Name as the second column that the user does see.

 

Giving users direct access to a table or query is not good practice. Users should be presented with data in either a form or report where there is some control (eg Validation) on what the user can add, edit or delete.

Usually if you give a parameter a query you also need to inform the database what you want done with that parameter eg re-query the data selecting only the submitted filter (for a select query).

If the parameter is part of a multi table query you must also ensure that the field you are querying on is either key or a foreign key in associated tables (nb the key doesn't have to be visible) otherwise you may get situations where Cartesian joins happen. You may also have to look at the design of your tables to establish what level of Normal Form is being used - most tables would go to 3rd Normal form in design.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies