Query to a combo box w/ criteria

[Guest]

[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis

 

[strive4peace]

Hi Travis,

actually, you want you combo's row source to be all the records, but
only ModelID records when you are in it and may want to pick from the
list...

you can modify the SQL for your combobox RowSource on the GotFocus and
LostFocus events

here is an example you can pattern after:

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the Product combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT ModelID, Model " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (ModelID =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY Model;"

'comment next line after everything works ok
debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

Exit_Proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_Proc
End function

'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

Hi Travis,

actually, you want you combo's row source to be all the records, but
only ModelID records when you are in it and may want to pick from the
list...

you can modify the SQL for your combobox RowSource on the GotFocus and
LostFocus events

here is an example you can pattern after:

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the Product combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT ModelID, Model " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (ModelID =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY Model;"

'comment next line after everything works ok
debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

Exit_Proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_Proc
End function

'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis

 

[Guest]

How do i delete the Code that I entered. I can't find it in my visual basics.
I want to delete it and try a new method but I can't even find it.

I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

Hi Travis,

actually, you want you combo's row source to be all the records, but
only ModelID records when you are in it and may want to pick from the
list...

you can modify the SQL for your combobox RowSource on the GotFocus and
LostFocus events

here is an example you can pattern after:

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the Product combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT ModelID, Model " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (ModelID =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY Model;"

'comment next line after everything works ok
debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

Exit_Proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_Proc
End function

'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis

 

[strive4peace]

The code needs to be in the module behind the form where you are using it.

go to the design view of your form

choose View, Code from the menu

paste the SetRowSource function in there

and then, choose Debug, Compile from the menu


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

Hi Travis,

actually, you want you combo's row source to be all the records, but
only ModelID records when you are in it and may want to pick from the
list...

you can modify the SQL for your combobox RowSource on the GotFocus and
LostFocus events

here is an example you can pattern after:

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the Product combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT ModelID, Model " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (ModelID =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY Model;"

'comment next line after everything works ok
debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

Exit_Proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_Proc
End function

'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis

 

[strive4peace]

to find the function, press CTRL-F for the Find Dialog box

find what --> SetRowSource

search--> current project

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

How do i delete the Code that I entered. I can't find it in my visual basics.
I want to delete it and try a new method but I can't even find it.

I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

Hi Travis,

actually, you want you combo's row source to be all the records, but
only ModelID records when you are in it and may want to pick from the
list...

you can modify the SQL for your combobox RowSource on the GotFocus and
LostFocus events

here is an example you can pattern after:

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the Product combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT ModelID, Model " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (ModelID =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY Model;"

'comment next line after everything works ok
debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

Exit_Proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_Proc
End function

'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Tdahlman wrote:
[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis

 

[Guest]

When I compile the code it gets to the following lines:

Me.combobox_controlname.RowSource = s
Me.combobox_controlname.Requery

it tells me that "Method or Data Memeber not found"
So i change it to

Me.combo40_controlname.RowSource = s
Me.combo40_controlname.Requery

It still gives me the same error message.
Do i need to change controlname or am i missing something else

Thanks again
Travis

The code needs to be in the module behind the form where you are using it.

go to the design view of your form

choose View, Code from the menu

paste the SetRowSource function in there

and then, choose Debug, Compile from the menu


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

Hi Travis,

actually, you want you combo's row source to be all the records, but
only ModelID records when you are in it and may want to pick from the
list...

you can modify the SQL for your combobox RowSource on the GotFocus and
LostFocus events

here is an example you can pattern after:

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the Product combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT ModelID, Model " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (ModelID =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY Model;"

'comment next line after everything works ok
debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

Exit_Proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_Proc
End function

'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Tdahlman wrote:
[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis

 

[strive4peace]

Hi Travis,

you need to look at the NAME property of your combobox...

if it is combo40 then you should change the lines to:

Me.combo40.RowSource = s
Me.combo40.Requery

But, ideally, if the name IS something ambiguous like "combo40", you
will change the name to make more sense...

~~~~ Properties and Methods ~~~~

Just like in the real world, every object has properties and methods.

Properties are like adjectives that describe an object
Methods are like verbs and define actions an object can do

For instance, you are a human and have properties such as hair color,
eye color, height, weight, ... and methods such as eat, walk, run, jump,
.... make babies -- Add to a collection

If you become familiar with the different types of objects that Access
can use and the properties that define them and the methods they can do
(and what triggers them), you will be on your way!

In the design view, you can show the property sheet, which will show
information for the selected item(s).

~~~~ turn on Properties window ~~~~

When you are in the design view, turn on/off the Properties window -->

1. from menu: View, Properties
OR
2. right-click and choose Properties from the shortcut menu

and then click on various objects. The properties window changes as you
change what is selected. If you have multiple objects selected, the
values for the properties they have in common will be displayed

Try it!

~~~~ setting Properties vs. resizing with handles ~~~~

I like to make the width of controls exact -- like 0.25, 0.3, 0.4, 0.5,
0.6, 0.75, 1, 1.25, etc

This is especially handing for lining up labels to controls under them

~~~~ selecting objects ~~~~

you can select multiple controls
1. click and drag the mouse and everything your imaginary rectangle
touches before you let go will be selected
OR
2. click, shift-click, etc
shift-click actually toggles the select status without affecting the
other items selected
OR
3. click (optionally, and drag) in a ruler
while the mouse is down, you will see a line extend across (vertical
ruler) or down (horizontal ruler)
if you click and drag, the ruler will turn dark indicating where you
started and stopped
-- everything the line/rectangle touches will be selected
OR
4. drop down the objects combo (left-most control on design toolbar) and
select something by its name

~~~~ select Form or Report ~~~~

To select the form (or report), you may:

1. click in the upper left corner where the rulers intersect
OR
2. click completely outside the designed area in the dark gray space
OR
3. press CTRL-R
OR
4. from menu: Edit, Select Form/Report

~~~~ building event code ~~~

To build an event, click in the property sheet for the appropriate
object in the appropriate location and then click the builder (...)
button off to the right

Access will provide the procedure declaration and the procedure end --
you put the lines in between.

~~~~ Name property ~~~~

Procedures are NAMED according to the object name (except the form
object), so ALWAYS change the NAME property of any object to something
logical.

When the properties window is displayed and only one thing is selected,
the Name appears in the title bar of the Properties window

If multiple items are selected, you will see "Multiple Selection" on the
title bar

~~~~ ControlSource, SourceObject ~~~~

It is important to realize that the Name is NOT what is displayed in a
control. If the control is (for instance a textbox or combo box), you
will see the ControlSource displayed.

If the object is (for instance) a subform or subreport, what you see
displayed is the SourceObject

For bound objects, I like to make the Name property match the source
property (this does not, btw, follow naming conventions, but for me, it
eases confusion)

As always, avoid using spaces and special characters when naming objects
-- use the underscore character _ to separate and use mixed case for
readability

~~~~ Recordset Property ~~~~

from the design view of a report or form:

turn on Properties
(from menu: View, Properties)

select the report or form
(click in the upper left where the rulers intersect)

click on the Data tab in Properties window

there you will see the RecordSet property
once you click in the property, you will see the builder button ... to
the right

If your report is based on a query, this takes you to the query design
screen

If your report is based on a SQL statement, you can modify it like the
design view of a query (you can also press SHIFT-F2 to use the Zoom Box
to change the SQL)

If your report is based on a table, you will be asked if you want to
make a query

~~~~ Builder Button ~~~~

The RowSource property for a combo or list box is like the RecordSource
property for a form or report -- you are choosing where the data will
come from that is displayed. The Builder Button ... will be displayed
when you click IN the property.

For choosing colors that are not on the color palette (like ForeColor,
BackColor, Bordercolor), click the builder button. Once in the palette
dialog box, click "Define Custom Colors" -- the dialog box will expand
and you can set the amounts for Red/Green/Blue or adjust
Hue/Saturation/Luminosity. There is also a slider control with a
triangle you can drag up or down to change the Luminosity (brightness).
I like to drag it up and fade out colors, especially for BackColor.

~~~~ Events ~~~~

"Properties" listed on the Events tab are actually methods ... such as
OnCurrent for form, AfterUpdate for Control, etc

~~~~ Learning the properties ~~~~

Explore the property sheet. Get familiar with how properties are
grouped on the tabs and the different properties for different objects.

~~~~ Help on Properties ~~~~

You can get help about any property by pressing F1 while in a property
on the property sheet where you want more information.

~~~~ Object Browser ~~~~

.... find out more about references... or just get Help!

in a code window to View the Object Browser:
1. from the menu --> View, Object Browser
OR
2. Press F2

On the left, you will probably see a Project window and a Properties
window below it
On the right, you will see the main Object Browser window

as you select a Class on the left, its members will appear in the pane
on the right

when you see something you want help on, press the F1 key and switch to
the Help window

When you are getting started, change the library to "VBA" (for instance,
instead of <all libraries>) and look at the classes (categories) of
functions -- click on a class and then click on a function in the right
pane. To get the help for that function, press F1

The VBA library is the most basic library and a great place to start
exploring.

To look up properties and methods for different objects like forms,
tabledefs, etc, change the library to Access
To look up ranges and sheets, change the library to Excel

explore the different libraries you have to pick from and see what is
available in each
(these are added or removed using Tools, References...)

when you are in the Object Browser window, the library that each
function/class is a member of is shown in the lower left corner of the
window

when you have an object selected, press F1 to get help.

~~~~ general help ~~~~

For general help, I find it interesting and informative to read the help
starting from the beginning of the Contents. In fact, if you have the
desire to print a ream of paper, it would be good to print it like a
book and read it so you can also take notes -- and you can read it away
from the computer -- a good time to put new information into your head
is just before you sleep ... let your subconscious figure it out!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

When I compile the code it gets to the following lines:

Me.combobox_controlname.RowSource = s
Me.combobox_controlname.Requery

it tells me that "Method or Data Memeber not found"
So i change it to

Me.combo40_controlname.RowSource = s
Me.combo40_controlname.Requery

It still gives me the same error message.
Do i need to change controlname or am i missing something else

Thanks again
Travis

The code needs to be in the module behind the form where you are using it.

go to the design view of your form

choose View, Code from the menu

paste the SetRowSource function in there

and then, choose Debug, Compile from the menu


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

:

Hi Travis,

actually, you want you combo's row source to be all the records, but
only ModelID records when you are in it and may want to pick from the
list...

you can modify the SQL for your combobox RowSource on the GotFocus and
LostFocus events

here is an example you can pattern after:

limit the combobox to specific records when it gets the focus, show all
records when you leave it

on the gotFocus event of the Product combobox, assign this:

=SetRowSource(true)


on the lostFocus event of the Product combobox, assign this:

=SetRowSource(false)


put this code behind the form/subform with the combobox -- and compile
it before testing

'~~~~~~~~~~~

private function SetRowSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mRecordID as long

s = "SELECT ModelID, Model " _
& " FROM Stores"

if pBooCriteria then
mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
if mRecordID <> 0 then
s = s & " WHERE (ModelID =" _
& mRecordID & ") "
end if
end if

s = s & "ORDER BY Model;"

'comment next line after everything works ok
debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

Exit_Proc:
exit function

Err_proc:
msgbox err.description,, _
"ERROR " & err.number & " SetRowSource"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume

resume Exit_Proc
End function

'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Tdahlman wrote:
[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis

 

[Guest]

Okay, now I compiled it and then opened my form and tried to use it and it
gives me another error message.

"application-defined or object-defined error"

I hit 'OK' and then it brings up my code and highlights the word "Stop" at
the very bottom of my code. Then when I close the code my combo box still has
all of it's options in it. It's not narrowed down yet.

Thanks
Travis

Hi Travis,

you need to look at the NAME property of your combobox...

if it is combo40 then you should change the lines to:

Me.combo40.RowSource = s
Me.combo40.Requery

But, ideally, if the name IS something ambiguous like "combo40", you
will change the name to make more sense...

~~~~ Properties and Methods ~~~~

Just like in the real world, every object has properties and methods.

Properties are like adjectives that describe an object
Methods are like verbs and define actions an object can do

For instance, you are a human and have properties such as hair color,
eye color, height, weight, ... and methods such as eat, walk, run, jump,
.... make babies -- Add to a collection

If you become familiar with the different types of objects that Access
can use and the properties that define them and the methods they can do
(and what triggers them), you will be on your way!

In the design view, you can show the property sheet, which will show
information for the selected item(s).

~~~~ turn on Properties window ~~~~

When you are in the design view, turn on/off the Properties window -->

1. from menu: View, Properties
OR
2. right-click and choose Properties from the shortcut menu

and then click on various objects. The properties window changes as you
change what is selected. If you have multiple objects selected, the
values for the properties they have in common will be displayed

Try it!

~~~~ setting Properties vs. resizing with handles ~~~~

I like to make the width of controls exact -- like 0.25, 0.3, 0.4, 0.5,
0.6, 0.75, 1, 1.25, etc

This is especially handing for lining up labels to controls under them

~~~~ selecting objects ~~~~

you can select multiple controls
1. click and drag the mouse and everything your imaginary rectangle
touches before you let go will be selected
OR
2. click, shift-click, etc
shift-click actually toggles the select status without affecting the
other items selected
OR
3. click (optionally, and drag) in a ruler
while the mouse is down, you will see a line extend across (vertical
ruler) or down (horizontal ruler)
if you click and drag, the ruler will turn dark indicating where you
started and stopped
-- everything the line/rectangle touches will be selected
OR
4. drop down the objects combo (left-most control on design toolbar) and
select something by its name

~~~~ select Form or Report ~~~~

To select the form (or report), you may:

1. click in the upper left corner where the rulers intersect
OR
2. click completely outside the designed area in the dark gray space
OR
3. press CTRL-R
OR
4. from menu: Edit, Select Form/Report

~~~~ building event code ~~~

To build an event, click in the property sheet for the appropriate
object in the appropriate location and then click the builder (...)
button off to the right

Access will provide the procedure declaration and the procedure end --
you put the lines in between.

~~~~ Name property ~~~~

Procedures are NAMED according to the object name (except the form
object), so ALWAYS change the NAME property of any object to something
logical.

When the properties window is displayed and only one thing is selected,
the Name appears in the title bar of the Properties window

If multiple items are selected, you will see "Multiple Selection" on the
title bar

~~~~ ControlSource, SourceObject ~~~~

It is important to realize that the Name is NOT what is displayed in a
control. If the control is (for instance a textbox or combo box), you
will see the ControlSource displayed.

If the object is (for instance) a subform or subreport, what you see
displayed is the SourceObject

For bound objects, I like to make the Name property match the source
property (this does not, btw, follow naming conventions, but for me, it
eases confusion)

As always, avoid using spaces and special characters when naming objects
-- use the underscore character _ to separate and use mixed case for
readability

~~~~ Recordset Property ~~~~

from the design view of a report or form:

turn on Properties
(from menu: View, Properties)

select the report or form
(click in the upper left where the rulers intersect)

click on the Data tab in Properties window

there you will see the RecordSet property
once you click in the property, you will see the builder button ... to
the right

If your report is based on a query, this takes you to the query design
screen

If your report is based on a SQL statement, you can modify it like the
design view of a query (you can also press SHIFT-F2 to use the Zoom Box
to change the SQL)

If your report is based on a table, you will be asked if you want to
make a query

~~~~ Builder Button ~~~~

The RowSource property for a combo or list box is like the RecordSource
property for a form or report -- you are choosing where the data will
come from that is displayed. The Builder Button ... will be displayed
when you click IN the property.

For choosing colors that are not on the color palette (like ForeColor,
BackColor, Bordercolor), click the builder button. Once in the palette
dialog box, click "Define Custom Colors" -- the dialog box will expand
and you can set the amounts for Red/Green/Blue or adjust
Hue/Saturation/Luminosity. There is also a slider control with a
triangle you can drag up or down to change the Luminosity (brightness).
I like to drag it up and fade out colors, especially for BackColor.

~~~~ Events ~~~~

"Properties" listed on the Events tab are actually methods ... such as
OnCurrent for form, AfterUpdate for Control, etc

~~~~ Learning the properties ~~~~

Explore the property sheet. Get familiar with how properties are
grouped on the tabs and the different properties for different objects.

~~~~ Help on Properties ~~~~

You can get help about any property by pressing F1 while in a property
on the property sheet where you want more information.

~~~~ Object Browser ~~~~

.... find out more about references... or just get Help!

in a code window to View the Object Browser:
1. from the menu --> View, Object Browser
OR
2. Press F2

On the left, you will probably see a Project window and a Properties
window below it
On the right, you will see the main Object Browser window

as you select a Class on the left, its members will appear in the pane
on the right

when you see something you want help on, press the F1 key and switch to
the Help window

When you are getting started, change the library to "VBA" (for instance,
instead of <all libraries>) and look at the classes (categories) of
functions -- click on a class and then click on a function in the right
pane. To get the help for that function, press F1

The VBA library is the most basic library and a great place to start
exploring.

To look up properties and methods for different objects like forms,
tabledefs, etc, change the library to Access
To look up ranges and sheets, change the library to Excel

explore the different libraries you have to pick from and see what is
available in each
(these are added or removed using Tools, References...)

when you are in the Object Browser window, the library that each
function/class is a member of is shown in the lower left corner of the
window

when you have an object selected, press F1 to get help.

~~~~ general help ~~~~

For general help, I find it interesting and informative to read the help
starting from the beginning of the Contents. In fact, if you have the
desire to print a ream of paper, it would be good to print it like a
book and read it so you can also take notes -- and you can read it away
from the computer -- a good time to put new information into your head
is just before you sleep ... let your subconscious figure it out!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

When I compile the code it gets to the following lines:

Me.combobox_controlname.RowSource = s
Me.combobox_controlname.Requery

it tells me that "Method or Data Memeber not found"
So i change it to

Me.combo40_controlname.RowSource = s
Me.combo40_controlname.Requery

It still gives me the same error message.
Do i need to change controlname or am i missing something else

Thanks again
Travis

The code needs to be in the module behind the form where you are using it.

go to the design view of your form

choose View, Code from the menu

paste the SetRowSource function in there

and then, choose Debug, Compile from the menu


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Tdahlman wrote:
I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

"strive4peace" wrote:

 

[strive4peace]

Hi Travis,

when you get to the STOP statement, press the F8 key to go to the next
line -- this will take you to "Resume"

then press F8 again -- this will be the line in your code with the problem


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Okay, now I compiled it and then opened my form and tried to use it and it
gives me another error message.

"application-defined or object-defined error"

I hit 'OK' and then it brings up my code and highlights the word "Stop" at
the very bottom of my code. Then when I close the code my combo box still has
all of it's options in it. It's not narrowed down yet.

Thanks
Travis

Hi Travis,

you need to look at the NAME property of your combobox...

if it is combo40 then you should change the lines to:

Me.combo40.RowSource = s
Me.combo40.Requery

But, ideally, if the name IS something ambiguous like "combo40", you
will change the name to make more sense...

~~~~ Properties and Methods ~~~~

Just like in the real world, every object has properties and methods.

Properties are like adjectives that describe an object
Methods are like verbs and define actions an object can do

For instance, you are a human and have properties such as hair color,
eye color, height, weight, ... and methods such as eat, walk, run, jump,
.... make babies -- Add to a collection

If you become familiar with the different types of objects that Access
can use and the properties that define them and the methods they can do
(and what triggers them), you will be on your way!

In the design view, you can show the property sheet, which will show
information for the selected item(s).

~~~~ turn on Properties window ~~~~

When you are in the design view, turn on/off the Properties window -->

1. from menu: View, Properties
OR
2. right-click and choose Properties from the shortcut menu

and then click on various objects. The properties window changes as you
change what is selected. If you have multiple objects selected, the
values for the properties they have in common will be displayed

Try it!

~~~~ setting Properties vs. resizing with handles ~~~~

I like to make the width of controls exact -- like 0.25, 0.3, 0.4, 0.5,
0.6, 0.75, 1, 1.25, etc

This is especially handing for lining up labels to controls under them

~~~~ selecting objects ~~~~

you can select multiple controls
1. click and drag the mouse and everything your imaginary rectangle
touches before you let go will be selected
OR
2. click, shift-click, etc
shift-click actually toggles the select status without affecting the
other items selected
OR
3. click (optionally, and drag) in a ruler
while the mouse is down, you will see a line extend across (vertical
ruler) or down (horizontal ruler)
if you click and drag, the ruler will turn dark indicating where you
started and stopped
-- everything the line/rectangle touches will be selected
OR
4. drop down the objects combo (left-most control on design toolbar) and
select something by its name

~~~~ select Form or Report ~~~~

To select the form (or report), you may:

1. click in the upper left corner where the rulers intersect
OR
2. click completely outside the designed area in the dark gray space
OR
3. press CTRL-R
OR
4. from menu: Edit, Select Form/Report

~~~~ building event code ~~~

To build an event, click in the property sheet for the appropriate
object in the appropriate location and then click the builder (...)
button off to the right

Access will provide the procedure declaration and the procedure end --
you put the lines in between.

~~~~ Name property ~~~~

Procedures are NAMED according to the object name (except the form
object), so ALWAYS change the NAME property of any object to something
logical.

When the properties window is displayed and only one thing is selected,
the Name appears in the title bar of the Properties window

If multiple items are selected, you will see "Multiple Selection" on the
title bar

~~~~ ControlSource, SourceObject ~~~~

It is important to realize that the Name is NOT what is displayed in a
control. If the control is (for instance a textbox or combo box), you
will see the ControlSource displayed.

If the object is (for instance) a subform or subreport, what you see
displayed is the SourceObject

For bound objects, I like to make the Name property match the source
property (this does not, btw, follow naming conventions, but for me, it
eases confusion)

As always, avoid using spaces and special characters when naming objects
-- use the underscore character _ to separate and use mixed case for
readability

~~~~ Recordset Property ~~~~

from the design view of a report or form:

turn on Properties
(from menu: View, Properties)

select the report or form
(click in the upper left where the rulers intersect)

click on the Data tab in Properties window

there you will see the RecordSet property
once you click in the property, you will see the builder button ... to
the right

If your report is based on a query, this takes you to the query design
screen

If your report is based on a SQL statement, you can modify it like the
design view of a query (you can also press SHIFT-F2 to use the Zoom Box
to change the SQL)

If your report is based on a table, you will be asked if you want to
make a query

~~~~ Builder Button ~~~~

The RowSource property for a combo or list box is like the RecordSource
property for a form or report -- you are choosing where the data will
come from that is displayed. The Builder Button ... will be displayed
when you click IN the property.

For choosing colors that are not on the color palette (like ForeColor,
BackColor, Bordercolor), click the builder button. Once in the palette
dialog box, click "Define Custom Colors" -- the dialog box will expand
and you can set the amounts for Red/Green/Blue or adjust
Hue/Saturation/Luminosity. There is also a slider control with a
triangle you can drag up or down to change the Luminosity (brightness).
I like to drag it up and fade out colors, especially for BackColor.

~~~~ Events ~~~~

"Properties" listed on the Events tab are actually methods ... such as
OnCurrent for form, AfterUpdate for Control, etc

~~~~ Learning the properties ~~~~

Explore the property sheet. Get familiar with how properties are
grouped on the tabs and the different properties for different objects.

~~~~ Help on Properties ~~~~

You can get help about any property by pressing F1 while in a property
on the property sheet where you want more information.

~~~~ Object Browser ~~~~

.... find out more about references... or just get Help!

in a code window to View the Object Browser:
1. from the menu --> View, Object Browser
OR
2. Press F2

On the left, you will probably see a Project window and a Properties
window below it
On the right, you will see the main Object Browser window

as you select a Class on the left, its members will appear in the pane
on the right

when you see something you want help on, press the F1 key and switch to
the Help window

When you are getting started, change the library to "VBA" (for instance,
instead of <all libraries>) and look at the classes (categories) of
functions -- click on a class and then click on a function in the right
pane. To get the help for that function, press F1

The VBA library is the most basic library and a great place to start
exploring.

To look up properties and methods for different objects like forms,
tabledefs, etc, change the library to Access
To look up ranges and sheets, change the library to Excel

explore the different libraries you have to pick from and see what is
available in each
(these are added or removed using Tools, References...)

when you are in the Object Browser window, the library that each
function/class is a member of is shown in the lower left corner of the
window

when you have an object selected, press F1 to get help.

~~~~ general help ~~~~

For general help, I find it interesting and informative to read the help
starting from the beginning of the Contents. In fact, if you have the
desire to print a ream of paper, it would be good to print it like a
book and read it so you can also take notes -- and you can read it away
from the computer -- a good time to put new information into your head
is just before you sleep ... let your subconscious figure it out!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

When I compile the code it gets to the following lines:

Me.combobox_controlname.RowSource = s
Me.combobox_controlname.Requery

it tells me that "Method or Data Memeber not found"
So i change it to

Me.combo40_controlname.RowSource = s
Me.combo40_controlname.Requery

It still gives me the same error message.
Do i need to change controlname or am i missing something else

Thanks again
Travis



:

The code needs to be in the module behind the form where you are using it.

go to the design view of your form

choose View, Code from the menu

paste the SetRowSource function in there

and then, choose Debug, Compile from the menu


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Tdahlman wrote:
I typed in everything you said too, but when I click on the combo box it give
me this message
"The expression On Got Focus you entered as the event property setting
produced the following error: The expression you entered has a function name
that Microsoft Accesss can't find.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluationg the function, event, or macro."

The if I click OK it doesn't limit my options in my combo box.

What might I be doing wrong?

"strive4peace" wrote:

 

[Guest]

It takes me to:
mRecordID = Nz(Forms!frm_NewQuote.ModelID_controlname)

and if I push it again it takes me to:
MsgBox Err.Description, , "ERROR " & Err.Number & " SetRowSource"

 

[strive4peace]

Hi Travis,

what is the NAME property of your control? I used ModelID_controlname
as an example, you need to substitute in what your name property
actually is...

please refer to my previous post with Properties and Methods

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

It takes me to:
mRecordID = Nz(Forms!frm_NewQuote.ModelID_controlname)

and if I push it again it takes me to:
MsgBox Err.Description, , "ERROR " & Err.Number & " SetRowSource"

[frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
other fields. [OrderInfoID] automatically updates into form
[frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
[Combo40] an option based on a query. The query need to be specific to the
[ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
gives me options based on all the [ModelID] entries. How do i make it only
give me options in my combobox that relate to the ModelID?
Thanks in advance.
Travis