05-15-2018 04:25 AM
05-15-2018 04:25 AM
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
05-18-2018 08:26 AM
08-12-2018 02:49 PM
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
' If no company is selected then you don't send a criteria to the report
DoCmd.OpenReport "rptCompany", acViewPreview
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.
08-20-2018 04:35 AM
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.