Re: changing cbobox row source through vba code

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Good deal!
I know that email is not the "best" way to work with someone.
"Text" is a hard way to communicate on problems such as these.
Good luck...

--
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."

"cho" <chooriang@xxxxxxxxx> wrote in message
news:e%23kBQwygHHA.596@xxxxxxxxxxxxxxxxxxxxxxx
Yup,you're right Al,
tank's a lot.It works fine now.
have a nice day



"Al Campagna" <alcampagna@msnewsgroups> wrote in message
news:ey#1nWPgHHA.5044@xxxxxxxxxxxxxxxxxxxxxxx
cho,
You're not reading my post carefully...
I wrote...
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.***

The Frame just provides a value of either 1 or 2... it has no query
behind it.
If Frame = 1 set the *Combo* Rowsource to your DC query.
If Frame = 2 set the *Combo* Rowsource to your AC query.

I can not express it any more clearly than that.
--
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."

"cho" <chooriang@xxxxxxxxx> wrote in message
news:uOrY3uLgHHA.4980@xxxxxxxxxxxxxxxxxxxxxxx
I'm sorry Al,
but how do we can create a default query behind the frame?



"Al Campagna" <alcampagna@msnewsgroups> wrote in message
news:#Q2KCD9fHHA.3632@xxxxxxxxxxxxxxxxxxxxxxx
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."


"cho" <chooriang@xxxxxxxxx> wrote in message
news:e0XRJizfHHA.1216@xxxxxxxxxxxxxxxxxxxxxxx
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?



"Al Campagna" <alcampagna@msnewsgroups> wrote in message
news:uQghU5wfHHA.408@xxxxxxxxxxxxxxxxxxxxxxx
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.
"cho" <chooriang@xxxxxxxxx> wrote in message
news:eF0C%23dvfHHA.4552@xxxxxxxxxxxxxxxxxxxxxxx
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];





"Al Campagna" <alcampagna@msnewsgroups> wrote in message
news:uaGGxZpfHHA.588@xxxxxxxxxxxxxxxxxxxxxxx
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."


"cho" <chooriang@xxxxxxxxx> wrote in message
news:u9$0cUkfHHA.1816@xxxxxxxxxxxxxxxxxxxxxxx
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?


"Al Campagna" <alcampagna@msnewsgroups> wrote in message
news:#w6gBxcfHHA.3648@xxxxxxxxxxxxxxxxxxxxxxx
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 =
"cho" <chooriang@xxxxxxxxx> wrote in message
news:%23hDAXDcfHHA.3928@xxxxxxxxxxxxxxxxxxxxxxx
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
























.

Quantcast