Limiting Records in a Form

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

What I want to do is put a combo box in the form header. When I make a
selection in the combo box, I want the for to update and display other fields
for any records matching the combo box selection. For example, I want to
choose "Blue" from the combo box, and I want the form to display any products
with "Blue" in the "color" field etc...

"RA" wrote:

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

This is what I use and you can deceide if it works for you.

First I created a query that takes information from the combo box (Blue);

Then I create a macro that RunsQuery with the field name of the field to be
filled in;

Then I have the box to be filled in as a combo box also;

I assign the field to the query with in the "On Entry" as run the Macro;

What happens is I select "Blue", when I tab to or click on the next field a
drop down box of the choices appears.

Will that solution work for you?

"devine" wrote:

What I want to do is put a combo box in the form header. When I make a
selection in the combo box, I want the for to update and display other fields
for any records matching the combo box selection. For example, I want to
choose "Blue" from the combo box, and I want the form to display any products
with "Blue" in the "color" field etc...

"RA" wrote:

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

Devine,

See VBA Help on the OpenForm event.

Reopen the form with the optional Where parameter:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourField] =" & Me![YourComboBox]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Hope that helps.
Sprinks




"devine" wrote:

What I want to do is put a combo box in the form header. When I make a
selection in the combo box, I want the for to update and display other fields
for any records matching the combo box selection. For example, I want to
choose "Blue" from the combo box, and I want the form to display any products
with "Blue" in the "color" field etc...

"RA" wrote:

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

That worked just fine. Thank you. But the form won't refresh when I make a
choice in the combo box. When I open the form, whatever choice was in the
combo box comes up on the form, but when I make another choice, nothing
refreshes. How do I set it so that it will refresh when other combo box
choices are made? (thanks again)
----------------------------------------------------------------------------------------

"Sprinks" wrote:

Devine,

See VBA Help on the OpenForm event.

Reopen the form with the optional Where parameter:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourField] =" & Me![YourComboBox]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Hope that helps.
Sprinks




"devine" wrote:

What I want to do is put a combo box in the form header. When I make a
selection in the combo box, I want the for to update and display other fields
for any records matching the combo box selection. For example, I want to
choose "Blue" from the combo box, and I want the form to display any products
with "Blue" in the "color" field etc...

"RA" wrote:

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

devine,

If the choice is showing in the form when you open it, you must have it
bound to a field. It should be unbound. Change the ControlSource property
to blank.

Also, if it is opening to the right record when you open the form, you must
have placed the code in the form OnOpen event or OnCurrent event. Remove it
from there and place it in the combo box' AfterUpdate event.

If you wish, you can explicitly move to your combo box when you activate the
form:

Me![YourComboBox].SetFocus

Hope that helps.
Sprinks

"devine" wrote:

That worked just fine. Thank you. But the form won't refresh when I make a
choice in the combo box. When I open the form, whatever choice was in the
combo box comes up on the form, but when I make another choice, nothing
refreshes. How do I set it so that it will refresh when other combo box
choices are made? (thanks again)
----------------------------------------------------------------------------------------

"Sprinks" wrote:

Devine,

See VBA Help on the OpenForm event.

Reopen the form with the optional Where parameter:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourField] =" & Me![YourComboBox]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Hope that helps.
Sprinks




"devine" wrote:

What I want to do is put a combo box in the form header. When I make a
selection in the combo box, I want the for to update and display other fields
for any records matching the combo box selection. For example, I want to
choose "Blue" from the combo box, and I want the form to display any products
with "Blue" in the "color" field etc...

"RA" wrote:

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

Thank you so much for all your help. You have solved my issue. No I know
how these relationships work. I can use this knowledge for the rest of my
projects. Thank you again!
--------------------------------------------------------------------------------------------
"Sprinks" wrote:

devine,

If the choice is showing in the form when you open it, you must have it
bound to a field. It should be unbound. Change the ControlSource property
to blank.

Also, if it is opening to the right record when you open the form, you must
have placed the code in the form OnOpen event or OnCurrent event. Remove it
from there and place it in the combo box' AfterUpdate event.

If you wish, you can explicitly move to your combo box when you activate the
form:

Me![YourComboBox].SetFocus

Hope that helps.
Sprinks

"devine" wrote:

That worked just fine. Thank you. But the form won't refresh when I make a
choice in the combo box. When I open the form, whatever choice was in the
combo box comes up on the form, but when I make another choice, nothing
refreshes. How do I set it so that it will refresh when other combo box
choices are made? (thanks again)
----------------------------------------------------------------------------------------

"Sprinks" wrote:

Devine,

See VBA Help on the OpenForm event.

Reopen the form with the optional Where parameter:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourField] =" & Me![YourComboBox]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Hope that helps.
Sprinks




"devine" wrote:

What I want to do is put a combo box in the form header. When I make a
selection in the combo box, I want the for to update and display other fields
for any records matching the combo box selection. For example, I want to
choose "Blue" from the combo box, and I want the form to display any products
with "Blue" in the "color" field etc...

"RA" wrote:

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here

Glad you solved it. My pleasure.

Sprinks

"devine" wrote:

Thank you so much for all your help. You have solved my issue. No I know
how these relationships work. I can use this knowledge for the rest of my
projects. Thank you again!
--------------------------------------------------------------------------------------------
"Sprinks" wrote:

devine,

If the choice is showing in the form when you open it, you must have it
bound to a field. It should be unbound. Change the ControlSource property
to blank.

Also, if it is opening to the right record when you open the form, you must
have placed the code in the form OnOpen event or OnCurrent event. Remove it
from there and place it in the combo box' AfterUpdate event.

If you wish, you can explicitly move to your combo box when you activate the
form:

Me![YourComboBox].SetFocus

Hope that helps.
Sprinks

"devine" wrote:

That worked just fine. Thank you. But the form won't refresh when I make a
choice in the combo box. When I open the form, whatever choice was in the
combo box comes up on the form, but when I make another choice, nothing
refreshes. How do I set it so that it will refresh when other combo box
choices are made? (thanks again)
----------------------------------------------------------------------------------------

"Sprinks" wrote:

Devine,

See VBA Help on the OpenForm event.

Reopen the form with the optional Where parameter:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourField] =" & Me![YourComboBox]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Hope that helps.
Sprinks




"devine" wrote:

What I want to do is put a combo box in the form header. When I make a
selection in the combo box, I want the for to update and display other fields
for any records matching the combo box selection. For example, I want to
choose "Blue" from the combo box, and I want the form to display any products
with "Blue" in the "color" field etc...

"RA" wrote:

I do something like that with a query attached to the field pulling results
based on the other field. I use it to pull a list of values so I'm not sure
if that is the direction you want to go.

"devine" wrote:

I want to be able to enter a value in a form field and have the form to
refresh to display all records matching that value.

Example:

Lookup Field: type value here
__________________________________________________ _______
1 Record 1 displayed here
2 Record 2 displayed here
3 Record 3 displayed here
4 Record 4 displayed here