Combo Parameter

  • Thread starter Thread starter phuser
  • Start date Start date
P

phuser

I tried to filter the "Date" date range in a subform with vbs but I was
unable to get it to work, so now Im trying to use a combo box in a qry that
the subform is made from. I created a table with the "Name" (as key) and a
column with the "Parameter" parameter code, I made sure each code was
correct by inserting the code into the criteria of the qry, all work *great*
I created a combo box in the Main form from the DateFilter table, pointed
to the combo box to the qry, and made sure the combobox would requery
AfterUpdate the subform but nothing, no error no data just nothing.... I
tried to making column 1 the bound nothing, column 2 nothing ? I also tried
by making the "code" the key column in the DateFilter Table & deleted the
"name" column to see if that would work but it didnt.

P.
 
Please open the query in SQL view then copy and paste that text into a
message so that we can see what you have. Also, once you've picked a value
in the combo box, if you open the query directly (double click on it in the
database window) does it show what you want?
 
Here you go Wayne, Hope you can help

SELECT Export.Date, Export.Location, Export.InvExported, Export.BOReport,
Export.InvPosted, Export.CountType, Export.CountExported,
Export.VarienceReport, Export.CountPosted, Export.TransferExported,
Export.TransferPosted, Export.User
FROM Export
WHERE (((Export.Date)=[forms]![Export]![DateFilter]))
ORDER BY Export.Location;

Some Examples of the table,
Name: Parameter:
Today Date()
Past 7 Days Between Date() and Date()-6

and no, nothing appears in the query if I have the form open with a choice
and open the query directly
 
The first problem I see is that you've used Date as a field name. Date is a
reserved word. To use it as a field name and avoid a conflict, always
enclose it in brackets. The second thing I hope will help is defining the
data type of the parameter.

PARAMETERS [forms]![Export]![DateFilter] DateTime;
SELECT Export.[Date], Export.Location, Export.InvExported, Export.BOReport,
Export.InvPosted, Export.CountType, Export.CountExported,
Export.VarienceReport, Export.CountPosted, Export.TransferExported,
Export.TransferPosted, Export.User
FROM Export
WHERE (((Export.[Date])=[forms]![Export]![DateFilter]))
ORDER BY Export.Location;

--
Wayne Morgan
MS Access MVP


phuser said:
Here you go Wayne, Hope you can help

SELECT Export.Date, Export.Location, Export.InvExported, Export.BOReport,
Export.InvPosted, Export.CountType, Export.CountExported,
Export.VarienceReport, Export.CountPosted, Export.TransferExported,
Export.TransferPosted, Export.User
FROM Export
WHERE (((Export.Date)=[forms]![Export]![DateFilter]))
ORDER BY Export.Location;

Some Examples of the table,
Name: Parameter:
Today Date()
Past 7 Days Between Date() and Date()-6

and no, nothing appears in the query if I have the form open with a choice
and open the query directly
Click to expand...
 
Ok I've made some changes, I changed the names in the DateFilter table, I
renamed Parameter to DateTime and the Name to Description, still nothing, I
also change the name Date to ExportDate and made the changes below but it
still wont query.

Wayne Morgan said:
The first problem I see is that you've used Date as a field name. Date is
a reserved word. To use it as a field name and avoid a conflict, always
enclose it in brackets. The second thing I hope will help is defining the
data type of the parameter.

PARAMETERS [forms]![Export]![DateFilter] DateTime;
SELECT Export.ExportDate, Export.Location, Export.InvExported,
Export.BOReport,
Export.InvPosted, Export.CountType, Export.CountExported,
Export.VarienceReport, Export.CountPosted, Export.TransferExported,
Export.TransferPosted, Export.User
FROM Export
WHERE (((Export.ExportDate)=[forms]![Export]![DateFilter]))
ORDER BY Export.Location;

--
Wayne Morgan
MS Access MVP


phuser said:
Here you go Wayne, Hope you can help

SELECT Export.Date, Export.Location, Export.InvExported, Export.BOReport,
Export.InvPosted, Export.CountType, Export.CountExported,
Export.VarienceReport, Export.CountPosted, Export.TransferExported,
Export.TransferPosted, Export.User
FROM Export
WHERE (((Export.Date)=[forms]![Export]![DateFilter]))
ORDER BY Export.Location;

Some Examples of the table,
Name: Parameter:
Today Date()
Past 7 Days Between Date() and Date()-6

and no, nothing appears in the query if I have the form open with a
choice and open the query directly


Wayne Morgan said:
Please open the query in SQL view then copy and paste that text into a
message so that we can see what you have. Also, once you've picked a
value in the combo box, if you open the query directly (double click on
it in the database window) does it show what you want?

--
Wayne Morgan
MS Access MVP


I tried to filter the "Date" date range in a subform with vbs but I was
unable to get it to work, so now Im trying to use a combo box in a qry
that the subform is made from. I created a table with the "Name" (as
key) and a column with the "Parameter" parameter code, I made sure each
code was correct by inserting the code into the criteria of the qry, all
work *great* I created a combo box in the Main form from the DateFilter
table, pointed to the combo box to the qry, and made sure the combobox
would requery AfterUpdate the subform but nothing, no error no data just
nothing.... I tried to making column 1 the bound nothing, column 2
nothing ? I also tried by making the "code" the key column in the
DateFilter Table & deleted the "name" column to see if that would work
but it didnt.

P.
Click to expand...
Click to expand...
Click to expand...
 
PS.

What are the Link Child Field and Link Master Field properties between the
main form and the subform?
 
The command is to requery the subform AfterUpdate on the combo box, I tried
OnChange as well but still didnt do anything,
Also there is no Link between the Main & Subform, the Main form only
contains the Header & a couple of text boxes that are sent to the subform
and the combo box we are discussing.
 
But what is the command you're using? I'm wanting to check the syntax.

Also, it is possible to use a control as the Master Link. If you place the
name of the combo box in the Master Link property and the name of the date
field from the subform in the Child Link property, does it do what you want?
If so, you can remove the date filter from the query feeding the subform.
 
for requery I have just created a macro that will requery the Subform
"AfterUpdate" , I use it all the time, simple & works great

As for creating a Master & Child Link between the forms, I get an error msg
that they are incompatible data types.
 
Yes, the data types have to match. If one is number and the other text, it
won't work. If there is more than one column in the combo box, you may need
to specify the column that matches the field in the subform. The value in
the Bound Column is what will be used if you don't specify.
 
Back
Top