I'm sorry Al,
but how do we can create a default query behind the frame?
I'm assuming that you have both the DC and the AC query working now???
(you removed the AC table from the DC query... and the DC table from
theAC query).
Just one combo... but switch the Rowsource SQL statements via code, on
the AfterUpdate
event of Frame24.
(actually, a better name for Frame24 would be "frameACorDC" with DC =1
and AC =2
...always use "meaningful" names for objects)
If the default value for the Frame24 = 1, then make the default query
behind the combo
box the DC query. If the user changes Frame24 from 1 to 2, use the
AfterUpdate of Frame24
to insert the AC query's SQL statement into the combo's RowSource.
If the user changes Frame 24 from 2 to 1, insert the DC query's SQL
statement into the
combo's Rowsource.
And... Requery the combo after every Rowsource change.
Also, when browsing from record to record, the form needs to examine
Frame24, see what
value it is, and set the correct rowsource for the combo. Use the same
code as the
Frame24 AfterUpdate in the form's OnCurrent event.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your life."
You're right Al,
I add AC Fan model query inside of cboModel query builder.
but I'm sorry,I don't really understand about this
You should create a combo rowsource query for Frame = 1 that only
uses
ONLY the DC
Models table/query, and another combo rowsource query for Frame=2 that
only uses the ONLY
AC Models table/query, and switch those two as required by the Frame
value.
Did you mean I have to create one more combo box?
cho,
Let's just work with the query behind the combo. I used the SQL you
originally
submitted...
*Does this query (as a RowSouce) work?*
And This is the query builder sql statement of my cbo box.
SELECT [List Model DC Query].[Model DC Fan], [Model AC Query].[Model
AC
Fan]
FROM [List Model DC Query], [Model AC Query]
ORDER BY [List Model DC Query].[Model DC Fan];
I'm thinking not... It appears to me that you have two pass thru
queries in your
query. (the DC query and the AC query) with no relationship between the
two tables. That
creates a Cartesian relationship where every record in DC will be
"multiplied" by each
record in AC, so you'll see many records returned.
You should create a combo rowsource query for Frame = 1 that only
uses
ONLY the DC
Models table/query, and another combo rowsource query for Frame=2 that
only uses the ONLY
AC Models table/query, and switch those two as required by the Frame
value.
Try this for the DC combo query. Make that the default for the
combo.
I have no way to
test, so use it as an example, and tweak to suit.
SELECT [List Model DC Query].[Model DC Fan]
FROM [List Model DC Query]
ORDER BY [List Model DC Query].[Model DC Fan];
For AC...
SELECT [Model AC Query].[Model AC Fan]
FROM [Model AC Query]
ORDER BY [Model AC Query].[Model AC Fan];
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your life."
It appears to me that you have two pass thru queries in your query
behind the combo
box.
OK this is the VBA code,
Private Sub Frame24_AfterUpdate()
If Frame24 = 1 Then
cboModel.RowSource = "SELECT [List Model DC Query].[Model DC
Fan]
FROM [List Model DC Query],[Model AC Query] ORDER BY [List Model DC
Query].[Model DC Fan];"
ElseIf Frame24 = 2 Then
cboModel.RowSource = "SELECT [Model AC Query].[Model DC Fan]
FROM
[List Model DC Query],[Model AC Query] ORDER BY [Model AC
Query].[Model
DC
Fan];"
End If
cboModel.Requery
End Sub
I also add the same code to the on current even of the form like you
say.
And This is the query builder sql statement of my cbo box.
SELECT [List Model DC Query].[Model DC Fan], [Model AC Query].[Model
AC
Fan]
FROM [List Model DC Query], [Model AC Query]
ORDER BY [List Model DC Query].[Model DC Fan];
cho,
We'll need to see your code, copied exactly as you have it now.
Indicate the events as
well as the code.
The concept of changing the combo's Rowsource using VBA does
work...
(SomeCombo.Rowsource = "Some SQL Statement")
But, the SQL statements must be correct...
Here's a trick I use to always make sure I have a working SQL
statement.
Go to the query design grid, and create a query that works just
the
way
you want it...
for the Frame =1 Rowsource.
Select View/SQL View, and you'll see a textual representation of
the
SQL statement you
just built.
Cut & Paste that text into your VB Rowsource function, making
sure
to
enclose it it
quotes (as the syntax requires), and making sure that if that text
won't
fit on one line,
that you use line continuation (" _").
Now go back to query design, and create a working query for the
Frame =
2 Rowsource,
and repeat the process.
That way you know that both SQL statements will work when called
upon
in your code. If
that doesn't work for you, then you have some other problem.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your
life."
Hi Al,
it doesn't work.Instead it made something strange occured.
It duplicates combobox value for many times.
And for option 2 it asks me a parameter value.
Any others idea or advise?
cho,
Using the AfterUpdate event of YourFrame...
If MYFrame = 1 Then
MyCombo.RowSource = "SELECT [List Model DC Query].[Model
DC
Fan]
FROM [List Model
DC Query],
[Model AC Query] ORDER BY [List Model DC Query].[Model DC
Fan];"
Elseif MyFrame = 2 Then
MyCombo.RowSource = "SELECT [Model AC Query].[Model DC
Fan]
FROM
[List Model DC
Query], [Model
AC Query] ORDER BY [Model AC Query].[Model DC Fan];"
End If
MyCombo.Requery
You'll also need the same code in the OnCurrent event of the
form.
Didn't test, but that should do it...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your
life."
MyFrame.RowSource =
Hi All,
I have a frame which has 2 option.Option 1 and 2.
I also have a combo box.
I want when option 1 in my frame is clicked,
my combo box row source is
"SELECT [List Model DC Query].[Model DC Fan] FROM [List Model
DC
Query],
[Model AC Query] ORDER BY [List Model DC Query].[Model DC
Fan];"
and when the 2nd option of frame is clicked,it row source
becomes
"SELECT [Model AC Query].[Model DC Fan] FROM [List Model DC
Query],
[Model AC Query] ORDER BY [Model AC Query].[Model DC Fan];"
please help to write the code and on what control even have to
be placed.
regards,
cho
