Lookup Box

  • Thread starter Thread starter jwebster1979
  • Start date Start date
J

jwebster1979

I have a form that is called Purchace Orders in that form I have a couple of
different look up columns One specifically is a "supplier name". I have a
subform that is inventory Transaction where I have a lookup column labled
"Products Name"

I noticed on an old database I had that as time went on the lookup column
would display all of my products which would start to make it tiresome and
confusing to try and find a specific one for the supplier I am writing the
Purchase Order for.
Is there a way to have that lookup box "Product Name" in my inventory
Transaction subform, only show the product that the supplier I choose in my
main form of Purchase orders?
 
jwebster1979,
You really should be looking up your SupplierID, rather than
your SupplierName. It's better to make sure you always use a
unique identifier, as there could be duplicate names. I know that
you probably don't have any dupe Suppliers, but it's best to design "for
sure."
Same with ProductName...

Okay... lecture over...

Use the Supplier value from the main form to filter the results of your
subform listbox. Your ProductName list column could be filtered by... (ex.)
=Forms!frmYourMainFormName!lstSupplierName
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Al,
Thanks for the help but I guess my inexpierience is going to show here I am
not familier with code so I think I typed this right but I am not sure.
=Forms![ Purchase Orders]!1st [SupplierID]
This equation if correct would go in what part of the property sheet of the
product name field in my subform to filter?
[purchase orders] is my main form
and [supplier ID] is one part of my lookup column
Like I said I am not that familiar and am still learning so any help you can
give me would be great!
 
No that's not correct It should be:

=Forms![Purchase Orders]![lst SupplierID]

The form name and field name, must be inside your square brackets, and there
should be no spaces except as they appear in the names.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

jwebster1979 said:
Al,
Thanks for the help but I guess my inexpierience is going to show here I
am
not familier with code so I think I typed this right but I am not sure.
=Forms![ Purchase Orders]!1st [SupplierID]
This equation if correct would go in what part of the property sheet of
the
product name field in my subform to filter?
[purchase orders] is my main form
and [supplier ID] is one part of my lookup column
Like I said I am not that familiar and am still learning so any help you
can
give me would be great!


Al Campagna said:
jwebster1979,
You really should be looking up your SupplierID, rather than
your SupplierName. It's better to make sure you always use a
unique identifier, as there could be duplicate names. I know that
you probably don't have any dupe Suppliers, but it's best to design "for
sure."
Same with ProductName...

Okay... lecture over...

Use the Supplier value from the main form to filter the results of
your
subform listbox. Your ProductName list column could be filtered by...
(ex.)
=Forms!frmYourMainFormName!lstSupplierName
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




.
Click to expand...
Click to expand...
 
What part of my property sheet should that go in in the [product name] subform?

Arvin Meyer said:
No that's not correct It should be:

=Forms![Purchase Orders]![lst SupplierID]

The form name and field name, must be inside your square brackets, and there
should be no spaces except as they appear in the names.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

jwebster1979 said:
Al,
Thanks for the help but I guess my inexpierience is going to show here I
am
not familier with code so I think I typed this right but I am not sure.
=Forms![ Purchase Orders]!1st [SupplierID]
This equation if correct would go in what part of the property sheet of
the
product name field in my subform to filter?
[purchase orders] is my main form
and [supplier ID] is one part of my lookup column
Like I said I am not that familiar and am still learning so any help you
can
give me would be great!


Al Campagna said:
jwebster1979,
You really should be looking up your SupplierID, rather than
your SupplierName. It's better to make sure you always use a
unique identifier, as there could be duplicate names. I know that
you probably don't have any dupe Suppliers, but it's best to design "for
sure."
Same with ProductName...

Okay... lecture over...

Use the Supplier value from the main form to filter the results of
your
subform listbox. Your ProductName list column could be filtered by...
(ex.)
=Forms!frmYourMainFormName!lstSupplierName
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have a form that is called Purchace Orders in that form I have a
couple
of
different look up columns One specifically is a "supplier name". I have
a
subform that is inventory Transaction where I have a lookup column
labled
"Products Name"

I noticed on an old database I had that as time went on the lookup
column
would display all of my products which would start to make it tiresome
and
confusing to try and find a specific one for the supplier I am writing
the
Purchase Order for.
Is there a way to have that lookup box "Product Name" in my inventory
Transaction subform, only show the product that the supplier I choose
in
my
main form of Purchase orders?


.
Click to expand...
Click to expand...


.
Click to expand...
 
jwebster1979,
You indicated that you have a ProductName listbox in your subform.
What is the exact name of that listbox? I'll use lstProductName, but
you use what you have.
That listbox has a table or a query as a rowsource. In this case, it
should be a query. In the query design view for that query, you should have
at least 2 columns... ProductName and SupplierName.
Give the SupplierName a criteria of...
=Forms!YourMainFormName!SupplierName
When you go to select a ProductName in your sub's list,it should only
display products assocaited with you main form SupplierName.
(Remember to use your own form and control names)
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

jwebster1979 said:
What part of my property sheet should that go in in the [product name]
subform?

Arvin Meyer said:
No that's not correct It should be:

=Forms![Purchase Orders]![lst SupplierID]

The form name and field name, must be inside your square brackets, and
there
should be no spaces except as they appear in the names.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

jwebster1979 said:
Al,
Thanks for the help but I guess my inexpierience is going to show here
I
am
not familier with code so I think I typed this right but I am not sure.
=Forms![ Purchase Orders]!1st [SupplierID]
This equation if correct would go in what part of the property sheet of
the
product name field in my subform to filter?
[purchase orders] is my main form
and [supplier ID] is one part of my lookup column
Like I said I am not that familiar and am still learning so any help
you
can
give me would be great!


:

jwebster1979,
You really should be looking up your SupplierID, rather than
your SupplierName. It's better to make sure you always use a
unique identifier, as there could be duplicate names. I know that
you probably don't have any dupe Suppliers, but it's best to design
"for
sure."
Same with ProductName...

Okay... lecture over...

Use the Supplier value from the main form to filter the results of
your
subform listbox. Your ProductName list column could be filtered by...
(ex.)
=Forms!frmYourMainFormName!lstSupplierName
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

message
I have a form that is called Purchace Orders in that form I have a
couple
of
different look up columns One specifically is a "supplier name". I
have
a
subform that is inventory Transaction where I have a lookup column
labled
"Products Name"

I noticed on an old database I had that as time went on the lookup
column
would display all of my products which would start to make it
tiresome
and
confusing to try and find a specific one for the supplier I am
writing
the
Purchase Order for.
Is there a way to have that lookup box "Product Name" in my
inventory
Transaction subform, only show the product that the supplier I
choose
in
my
main form of Purchase orders?


.
Click to expand...


.
Click to expand...
Click to expand...
 
Al,
So I guess I have a new problem my "Supplier" is not in that query. can I
add it and add the purchase order main form to the query and then do what you
told me?

Al Campagna said:
jwebster1979,
You indicated that you have a ProductName listbox in your subform.
What is the exact name of that listbox? I'll use lstProductName, but
you use what you have.
That listbox has a table or a query as a rowsource. In this case, it
should be a query. In the query design view for that query, you should have
at least 2 columns... ProductName and SupplierName.
Give the SupplierName a criteria of...
=Forms!YourMainFormName!SupplierName
When you go to select a ProductName in your sub's list,it should only
display products assocaited with you main form SupplierName.
(Remember to use your own form and control names)
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

jwebster1979 said:
What part of my property sheet should that go in in the [product name]
subform?

Arvin Meyer said:
No that's not correct It should be:

=Forms![Purchase Orders]![lst SupplierID]

The form name and field name, must be inside your square brackets, and
there
should be no spaces except as they appear in the names.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Al,
Thanks for the help but I guess my inexpierience is going to show here
I
am
not familier with code so I think I typed this right but I am not sure.
=Forms![ Purchase Orders]!1st [SupplierID]
This equation if correct would go in what part of the property sheet of
the
product name field in my subform to filter?
[purchase orders] is my main form
and [supplier ID] is one part of my lookup column
Like I said I am not that familiar and am still learning so any help
you
can
give me would be great!


:

jwebster1979,
You really should be looking up your SupplierID, rather than
your SupplierName. It's better to make sure you always use a
unique identifier, as there could be duplicate names. I know that
you probably don't have any dupe Suppliers, but it's best to design
"for
sure."
Same with ProductName...

Okay... lecture over...

Use the Supplier value from the main form to filter the results of
your
subform listbox. Your ProductName list column could be filtered by...
(ex.)
=Forms!frmYourMainFormName!lstSupplierName
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

message
I have a form that is called Purchace Orders in that form I have a
couple
of
different look up columns One specifically is a "supplier name". I
have
a
subform that is inventory Transaction where I have a lookup column
labled
"Products Name"

I noticed on an old database I had that as time went on the lookup
column
would display all of my products which would start to make it
tiresome
and
confusing to try and find a specific one for the supplier I am
writing
the
Purchase Order for.
Is there a way to have that lookup box "Product Name" in my
inventory
Transaction subform, only show the product that the supplier I
choose
in
my
main form of Purchase orders?


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
jwebster1979,
You already have the Supplier control on your form, with a value.
Since there should be a relationship between the ProductName, and
the Supplier, just add that column to the design grid, and give it the
criteria as indicated.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

jwebster1979 said:
Al,
So I guess I have a new problem my "Supplier" is not in that query. can I
add it and add the purchase order main form to the query and then do what
you
told me?

Al Campagna said:
jwebster1979,
You indicated that you have a ProductName listbox in your subform.
What is the exact name of that listbox? I'll use lstProductName, but
you use what you have.
That listbox has a table or a query as a rowsource. In this case,
it
should be a query. In the query design view for that query, you should
have
at least 2 columns... ProductName and SupplierName.
Give the SupplierName a criteria of...
=Forms!YourMainFormName!SupplierName
When you go to select a ProductName in your sub's list,it should only
display products assocaited with you main form SupplierName.
(Remember to use your own form and control names)
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

jwebster1979 said:
What part of my property sheet should that go in in the [product name]
subform?

:

No that's not correct It should be:

=Forms![Purchase Orders]![lst SupplierID]

The form name and field name, must be inside your square brackets, and
there
should be no spaces except as they appear in the names.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message
Al,
Thanks for the help but I guess my inexpierience is going to show
here
I
am
not familier with code so I think I typed this right but I am not
sure.
=Forms![ Purchase Orders]!1st [SupplierID]
This equation if correct would go in what part of the property sheet
of
the
product name field in my subform to filter?
[purchase orders] is my main form
and [supplier ID] is one part of my lookup column
Like I said I am not that familiar and am still learning so any help
you
can
give me would be great!


:

jwebster1979,
You really should be looking up your SupplierID, rather than
your SupplierName. It's better to make sure you always use a
unique identifier, as there could be duplicate names. I know that
you probably don't have any dupe Suppliers, but it's best to design
"for
sure."
Same with ProductName...

Okay... lecture over...

Use the Supplier value from the main form to filter the results
of
your
subform listbox. Your ProductName list column could be filtered
by...
(ex.)
=Forms!frmYourMainFormName!lstSupplierName
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

message
I have a form that is called Purchace Orders in that form I have a
couple
of
different look up columns One specifically is a "supplier name".
I
have
a
subform that is inventory Transaction where I have a lookup
column
labled
"Products Name"

I noticed on an old database I had that as time went on the
lookup
column
would display all of my products which would start to make it
tiresome
and
confusing to try and find a specific one for the supplier I am
writing
the
Purchase Order for.
Is there a way to have that lookup box "Product Name" in my
inventory
Transaction subform, only show the product that the supplier I
choose
in
my
main form of Purchase orders?


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Got it, thanks for your patience!!

Al Campagna said:
jwebster1979,
You already have the Supplier control on your form, with a value.
Since there should be a relationship between the ProductName, and
the Supplier, just add that column to the design grid, and give it the
criteria as indicated.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

jwebster1979 said:
Al,
So I guess I have a new problem my "Supplier" is not in that query. can I
add it and add the purchase order main form to the query and then do what
you
told me?

Al Campagna said:
jwebster1979,
You indicated that you have a ProductName listbox in your subform.
What is the exact name of that listbox? I'll use lstProductName, but
you use what you have.
That listbox has a table or a query as a rowsource. In this case,
it
should be a query. In the query design view for that query, you should
have
at least 2 columns... ProductName and SupplierName.
Give the SupplierName a criteria of...
=Forms!YourMainFormName!SupplierName
When you go to select a ProductName in your sub's list,it should only
display products assocaited with you main form SupplierName.
(Remember to use your own form and control names)
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

What part of my property sheet should that go in in the [product name]
subform?

:

No that's not correct It should be:

=Forms![Purchase Orders]![lst SupplierID]

The form name and field name, must be inside your square brackets, and
there
should be no spaces except as they appear in the names.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message
Al,
Thanks for the help but I guess my inexpierience is going to show
here
I
am
not familier with code so I think I typed this right but I am not
sure.
=Forms![ Purchase Orders]!1st [SupplierID]
This equation if correct would go in what part of the property sheet
of
the
product name field in my subform to filter?
[purchase orders] is my main form
and [supplier ID] is one part of my lookup column
Like I said I am not that familiar and am still learning so any help
you
can
give me would be great!


:

jwebster1979,
You really should be looking up your SupplierID, rather than
your SupplierName. It's better to make sure you always use a
unique identifier, as there could be duplicate names. I know that
you probably don't have any dupe Suppliers, but it's best to design
"for
sure."
Same with ProductName...

Okay... lecture over...

Use the Supplier value from the main form to filter the results
of
your
subform listbox. Your ProductName list column could be filtered
by...
(ex.)
=Forms!frmYourMainFormName!lstSupplierName
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

message
I have a form that is called Purchace Orders in that form I have a
couple
of
different look up columns One specifically is a "supplier name".
I
have
a
subform that is inventory Transaction where I have a lookup
column
labled
"Products Name"

I noticed on an old database I had that as time went on the
lookup
column
would display all of my products which would start to make it
tiresome
and
confusing to try and find a specific one for the supplier I am
writing
the
Purchase Order for.
Is there a way to have that lookup box "Product Name" in my
inventory
Transaction subform, only show the product that the supplier I
choose
in
my
main form of Purchase orders?


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top