Query Form

[toni.jimenez]

Hi,

I am very NEW to Access but an Oracle Developer for the
past 10 years. What I am trying to do is build a query
screen to get parameters to send to a report. I have an
ID that is made of three separate fields (Year,Number,
Sequence). I have built combo box for the year and pull
the distinct year from the table. I have built another
comb box for the number but can't figure out how to have
it just pull up the numbers that correspond for that
year. If anyone is an Oracle programmer this would be the
same as building a LOV for the Number field with this
select as the record group query:

Select Number
from table_name
where year=block.year

I would do this all as non-base table (I think this is the
same as unbounded) as I am only wanting to get the
criteria not update the table and then build the
where_clause to send to a report.

I want to do the same for sequence, pull the sequence
numbers in a combo box for the year AND number. I can
just write the select stmt if I can figure out the right
syntax.

Thanks,

Toni

 

[Rick B]

Sounds like you want a combo box to display contents based on the entry in a
preiously selected combobox? If that is true, do a search for "cascading
combo" box. This has been asked and answered countless times in these
newsgroups.

Rick B


message Hi,

I am very NEW to Access but an Oracle Developer for the
past 10 years. What I am trying to do is build a query
screen to get parameters to send to a report. I have an
ID that is made of three separate fields (Year,Number,
Sequence). I have built combo box for the year and pull
the distinct year from the table. I have built another
comb box for the number but can't figure out how to have
it just pull up the numbers that correspond for that
year. If anyone is an Oracle programmer this would be the
same as building a LOV for the Number field with this
select as the record group query:

Select Number
from table_name
where year=block.year

I would do this all as non-base table (I think this is the
same as unbounded) as I am only wanting to get the
criteria not update the table and then build the
where_clause to send to a report.

I want to do the same for sequence, pull the sequence
numbers in a combo box for the year AND number. I can
just write the select stmt if I can figure out the right
syntax.

Thanks,

Toni

 

[toni.jimenez]

Thanks. If I knew what things were called it would be
easier to search. Will search for that.

Toni

 

[Marshall Barton]

I am very NEW to Access but an Oracle Developer for the
past 10 years. What I am trying to do is build a query
screen to get parameters to send to a report. I have an
ID that is made of three separate fields (Year,Number,
Sequence). I have built combo box for the year and pull
the distinct year from the table. I have built another
comb box for the number but can't figure out how to have
it just pull up the numbers that correspond for that
year. If anyone is an Oracle programmer this would be the
same as building a LOV for the Number field with this
select as the record group query:

Select Number
from table_name
where year=block.year

I would do this all as non-base table (I think this is the
same as unbounded) as I am only wanting to get the
criteria not update the table and then build the
where_clause to send to a report.

I want to do the same for sequence, pull the sequence
numbers in a combo box for the year AND number. I can
just write the select stmt if I can figure out the right
syntax.

Set the number combo box's RowSource to an SQL string (or
saved query) like this:

SELECT [Number]
FROM tablename
WHERE [Year] = Forms!formname.yearcomboname

and the sequence combo box:

SELECT [Sequence]
FROM tablename
WHERE [Year] = Forms!formname.yearcomboname
AND [Number] = Forms!formname.numbercomboname

You'll also want to use some code in the Year combo box's
AfterUpdate event procedure to clear any old value and to
requery with the new criteria:

Me.numbercombo = Null
Me.sequencecombo = Null
Me.numbercombo.Requery

and in the number combo box's AfterUpdate event:

Me.sequencecombo = Null
Me.sequencecombo.Requery