List Box / Combo Box

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam

Your code does not do what you describe in your post. The code does not
reference a list box at all. What is appears to be doing is creating a rew
record source for your form and requerying the form. The After Update event
of the form is then trying to select a new current record based on the value
of a field in a sub form.

In your post you said you are tyring to populate a listbox filtered on a
value in a combo, so it is unclear what you are trying to do.

If you can post back with a description of what you want to do and how the
subform figures into this, perhaps we can give you some help.

"PHisaw" wrote:

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam

Klatuu,

Thank you so much for the prompt reply. I'm sorry for the confusion- I have
a subform open to datasheet and stated list box. Anyway, I have it working
somewhat. I have two fields "Fluid" and "FluidListedBySundyne". I realize
it's not good to have two fields that are basically the same, but there are
some differences that the user will need to see. What I want to happen is
the datasheet subform to list all records whether it is from "Fluid" or
"FluidListedBySundyne" based on a LIKE entry from the combo box.

Example:
Enter "CAUSTIC" in combo box; returns records below
Fluid FluidBySundyne
12% Caustic BE Caustic

I'll repost the code I'm using.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] Like '*" & Me.CboFluid & "*'" & _
"OR qCompiledList.[FluidListedBySundyne] Like '*" & Me.CboFluid &
"*';"
Me.RecordSource = strSQL
End Sub

On the SQL for the record source of the subform, I've entered the name of
the combo box into the criteria row of the "Fluid" column and the
"FluidBy..." column.
Do I need the code below for the AfterUpdate of the subform? I removed it
and didn't see where it made a difference.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] Like '*" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I really appreciate your help, very frustrated at this point.
Thanks,
Pam

"Klatuu" wrote:

Your code does not do what you describe in your post. The code does not
reference a list box at all. What is appears to be doing is creating a rew
record source for your form and requerying the form. The After Update event
of the form is then trying to select a new current record based on the value
of a field in a sub form.

In your post you said you are tyring to populate a listbox filtered on a
value in a combo, so it is unclear what you are trying to do.

If you can post back with a description of what you want to do and how the
subform figures into this, perhaps we can give you some help.

"PHisaw" wrote:

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam

Rather than resetting the Record Source of the subform, what you combo should
be doing (and it almost is with a little modification) is creating a Where
clause without the word where. Then setting the sub form's filter property
based on that Where clause. So basically, In the AFter Update event of the
Combo all you need to do is set the sub form's Fitler property and set the
Filter property to True. A requery will not be necessary, because applying
the filter will do that.

"PHisaw" wrote:

Klatuu,

Thank you so much for the prompt reply. I'm sorry for the confusion- I have
a subform open to datasheet and stated list box. Anyway, I have it working
somewhat. I have two fields "Fluid" and "FluidListedBySundyne". I realize
it's not good to have two fields that are basically the same, but there are
some differences that the user will need to see. What I want to happen is
the datasheet subform to list all records whether it is from "Fluid" or
"FluidListedBySundyne" based on a LIKE entry from the combo box.

Example:
Enter "CAUSTIC" in combo box; returns records below
Fluid FluidBySundyne
12% Caustic BE Caustic

I'll repost the code I'm using.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] Like '*" & Me.CboFluid & "*'" & _
"OR qCompiledList.[FluidListedBySundyne] Like '*" & Me.CboFluid &
"*';"
Me.RecordSource = strSQL
End Sub

On the SQL for the record source of the subform, I've entered the name of
the combo box into the criteria row of the "Fluid" column and the
"FluidBy..." column.
Do I need the code below for the AfterUpdate of the subform? I removed it
and didn't see where it made a difference.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] Like '*" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I really appreciate your help, very frustrated at this point.
Thanks,
Pam

"Klatuu" wrote:

Your code does not do what you describe in your post. The code does not
reference a list box at all. What is appears to be doing is creating a rew
record source for your form and requerying the form. The After Update event
of the form is then trying to select a new current record based on the value
of a field in a sub form.

In your post you said you are tyring to populate a listbox filtered on a
value in a combo, so it is unclear what you are trying to do.

If you can post back with a description of what you want to do and how the
subform figures into this, perhaps we can give you some help.

"PHisaw" wrote:

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam

Klatuu,
Are you saying I need to remove all the code in the combo's AfterUpdate
event and replace it with something else? How do I create a Where clause
without the word Where? I think what you are telling me sounds easier than
what I have, but I'm not sure how to go about it. Can you help further?
Thanks,
Pam

"Klatuu" wrote:

Rather than resetting the Record Source of the subform, what you combo should
be doing (and it almost is with a little modification) is creating a Where
clause without the word where. Then setting the sub form's filter property
based on that Where clause. So basically, In the AFter Update event of the
Combo all you need to do is set the sub form's Fitler property and set the
Filter property to True. A requery will not be necessary, because applying
the filter will do that.

"PHisaw" wrote:

Klatuu,

Thank you so much for the prompt reply. I'm sorry for the confusion- I have
a subform open to datasheet and stated list box. Anyway, I have it working
somewhat. I have two fields "Fluid" and "FluidListedBySundyne". I realize
it's not good to have two fields that are basically the same, but there are
some differences that the user will need to see. What I want to happen is
the datasheet subform to list all records whether it is from "Fluid" or
"FluidListedBySundyne" based on a LIKE entry from the combo box.

Example:
Enter "CAUSTIC" in combo box; returns records below
Fluid FluidBySundyne
12% Caustic BE Caustic

I'll repost the code I'm using.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] Like '*" & Me.CboFluid & "*'" & _
"OR qCompiledList.[FluidListedBySundyne] Like '*" & Me.CboFluid &
"*';"
Me.RecordSource = strSQL
End Sub

On the SQL for the record source of the subform, I've entered the name of
the combo box into the criteria row of the "Fluid" column and the
"FluidBy..." column.
Do I need the code below for the AfterUpdate of the subform? I removed it
and didn't see where it made a difference.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] Like '*" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I really appreciate your help, very frustrated at this point.
Thanks,
Pam

"Klatuu" wrote:

Your code does not do what you describe in your post. The code does not
reference a list box at all. What is appears to be doing is creating a rew
record source for your form and requerying the form. The After Update event
of the form is then trying to select a new current record based on the value
of a field in a sub form.

In your post you said you are tyring to populate a listbox filtered on a
value in a combo, so it is unclear what you are trying to do.

If you can post back with a description of what you want to do and how the
subform figures into this, perhaps we can give you some help.

"PHisaw" wrote:

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam

Yes, you can do away with all the other code and try this (untested air code)
Private Sub CboFluid_AfterUpdate()
Dim strFilter As String

strFilter = "qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "'"
Me.SubFormControlName.Form.Filter = strFilter
Me.SubFormControlName.Form.FilterOn = True

End Sub

Note, where you see SubFormControlName is not necessarily the name of the
sub form. It is the name of the control on the form the sub form is attached
to. Be aware if there is a syntax problem in the above code, it will
probably be here. I sometimes get a little fuzzy on getting this right.

"PHisaw" wrote:

Klatuu,
Are you saying I need to remove all the code in the combo's AfterUpdate
event and replace it with something else? How do I create a Where clause
without the word Where? I think what you are telling me sounds easier than
what I have, but I'm not sure how to go about it. Can you help further?
Thanks,
Pam

"Klatuu" wrote:

Rather than resetting the Record Source of the subform, what you combo should
be doing (and it almost is with a little modification) is creating a Where
clause without the word where. Then setting the sub form's filter property
based on that Where clause. So basically, In the AFter Update event of the
Combo all you need to do is set the sub form's Fitler property and set the
Filter property to True. A requery will not be necessary, because applying
the filter will do that.

"PHisaw" wrote:

Klatuu,

Thank you so much for the prompt reply. I'm sorry for the confusion- I have
a subform open to datasheet and stated list box. Anyway, I have it working
somewhat. I have two fields "Fluid" and "FluidListedBySundyne". I realize
it's not good to have two fields that are basically the same, but there are
some differences that the user will need to see. What I want to happen is
the datasheet subform to list all records whether it is from "Fluid" or
"FluidListedBySundyne" based on a LIKE entry from the combo box.

Example:
Enter "CAUSTIC" in combo box; returns records below
Fluid FluidBySundyne
12% Caustic BE Caustic

I'll repost the code I'm using.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] Like '*" & Me.CboFluid & "*'" & _
"OR qCompiledList.[FluidListedBySundyne] Like '*" & Me.CboFluid &
"*';"
Me.RecordSource = strSQL
End Sub

On the SQL for the record source of the subform, I've entered the name of
the combo box into the criteria row of the "Fluid" column and the
"FluidBy..." column.
Do I need the code below for the AfterUpdate of the subform? I removed it
and didn't see where it made a difference.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] Like '*" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I really appreciate your help, very frustrated at this point.
Thanks,
Pam

"Klatuu" wrote:

Your code does not do what you describe in your post. The code does not
reference a list box at all. What is appears to be doing is creating a rew
record source for your form and requerying the form. The After Update event
of the form is then trying to select a new current record based on the value
of a field in a sub form.

In your post you said you are tyring to populate a listbox filtered on a
value in a combo, so it is unclear what you are trying to do.

If you can post back with a description of what you want to do and how the
subform figures into this, perhaps we can give you some help.

"PHisaw" wrote:

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam

Klatuu,

Thanks so much for the reply and help. It's working perfectly!

Pam

"Klatuu" wrote:

Yes, you can do away with all the other code and try this (untested air code)
Private Sub CboFluid_AfterUpdate()
Dim strFilter As String

strFilter = "qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "'"
Me.SubFormControlName.Form.Filter = strFilter
Me.SubFormControlName.Form.FilterOn = True

End Sub

Note, where you see SubFormControlName is not necessarily the name of the
sub form. It is the name of the control on the form the sub form is attached
to. Be aware if there is a syntax problem in the above code, it will
probably be here. I sometimes get a little fuzzy on getting this right.

"PHisaw" wrote:

Klatuu,
Are you saying I need to remove all the code in the combo's AfterUpdate
event and replace it with something else? How do I create a Where clause
without the word Where? I think what you are telling me sounds easier than
what I have, but I'm not sure how to go about it. Can you help further?
Thanks,
Pam

"Klatuu" wrote:

Rather than resetting the Record Source of the subform, what you combo should
be doing (and it almost is with a little modification) is creating a Where
clause without the word where. Then setting the sub form's filter property
based on that Where clause. So basically, In the AFter Update event of the
Combo all you need to do is set the sub form's Fitler property and set the
Filter property to True. A requery will not be necessary, because applying
the filter will do that.

"PHisaw" wrote:

Klatuu,

Thank you so much for the prompt reply. I'm sorry for the confusion- I have
a subform open to datasheet and stated list box. Anyway, I have it working
somewhat. I have two fields "Fluid" and "FluidListedBySundyne". I realize
it's not good to have two fields that are basically the same, but there are
some differences that the user will need to see. What I want to happen is
the datasheet subform to list all records whether it is from "Fluid" or
"FluidListedBySundyne" based on a LIKE entry from the combo box.

Example:
Enter "CAUSTIC" in combo box; returns records below
Fluid FluidBySundyne
12% Caustic BE Caustic

I'll repost the code I'm using.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] Like '*" & Me.CboFluid & "*'" & _
"OR qCompiledList.[FluidListedBySundyne] Like '*" & Me.CboFluid &
"*';"
Me.RecordSource = strSQL
End Sub

On the SQL for the record source of the subform, I've entered the name of
the combo box into the criteria row of the "Fluid" column and the
"FluidBy..." column.
Do I need the code below for the AfterUpdate of the subform? I removed it
and didn't see where it made a difference.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] Like '*" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I really appreciate your help, very frustrated at this point.
Thanks,
Pam

"Klatuu" wrote:

Your code does not do what you describe in your post. The code does not
reference a list box at all. What is appears to be doing is creating a rew
record source for your form and requerying the form. The After Update event
of the form is then trying to select a new current record based on the value
of a field in a sub form.

In your post you said you are tyring to populate a listbox filtered on a
value in a combo, so it is unclear what you are trying to do.

If you can post back with a description of what you want to do and how the
subform figures into this, perhaps we can give you some help.

"PHisaw" wrote:

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam

Glad to hear it.
Isn't it funny how some things that seem complex turn out to be simple and
some things that you think will be simple turn out to be complicated?

"PHisaw" wrote:

Klatuu,

Thanks so much for the reply and help. It's working perfectly!

Pam

"Klatuu" wrote:

Yes, you can do away with all the other code and try this (untested air code)
Private Sub CboFluid_AfterUpdate()
Dim strFilter As String

strFilter = "qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "'"
Me.SubFormControlName.Form.Filter = strFilter
Me.SubFormControlName.Form.FilterOn = True

End Sub

Note, where you see SubFormControlName is not necessarily the name of the
sub form. It is the name of the control on the form the sub form is attached
to. Be aware if there is a syntax problem in the above code, it will
probably be here. I sometimes get a little fuzzy on getting this right.

"PHisaw" wrote:

Klatuu,
Are you saying I need to remove all the code in the combo's AfterUpdate
event and replace it with something else? How do I create a Where clause
without the word Where? I think what you are telling me sounds easier than
what I have, but I'm not sure how to go about it. Can you help further?
Thanks,
Pam

"Klatuu" wrote:

Rather than resetting the Record Source of the subform, what you combo should
be doing (and it almost is with a little modification) is creating a Where
clause without the word where. Then setting the sub form's filter property
based on that Where clause. So basically, In the AFter Update event of the
Combo all you need to do is set the sub form's Fitler property and set the
Filter property to True. A requery will not be necessary, because applying
the filter will do that.

"PHisaw" wrote:

Klatuu,

Thank you so much for the prompt reply. I'm sorry for the confusion- I have
a subform open to datasheet and stated list box. Anyway, I have it working
somewhat. I have two fields "Fluid" and "FluidListedBySundyne". I realize
it's not good to have two fields that are basically the same, but there are
some differences that the user will need to see. What I want to happen is
the datasheet subform to list all records whether it is from "Fluid" or
"FluidListedBySundyne" based on a LIKE entry from the combo box.

Example:
Enter "CAUSTIC" in combo box; returns records below
Fluid FluidBySundyne
12% Caustic BE Caustic

I'll repost the code I'm using.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] Like '*" & Me.CboFluid & "*'" & _
"OR qCompiledList.[FluidListedBySundyne] Like '*" & Me.CboFluid &
"*';"
Me.RecordSource = strSQL
End Sub

On the SQL for the record source of the subform, I've entered the name of
the combo box into the criteria row of the "Fluid" column and the
"FluidBy..." column.
Do I need the code below for the AfterUpdate of the subform? I removed it
and didn't see where it made a difference.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] Like '*" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "*'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I really appreciate your help, very frustrated at this point.
Thanks,
Pam

"Klatuu" wrote:

Your code does not do what you describe in your post. The code does not
reference a list box at all. What is appears to be doing is creating a rew
record source for your form and requerying the form. The After Update event
of the form is then trying to select a new current record based on the value
of a field in a sub form.

In your post you said you are tyring to populate a listbox filtered on a
value in a combo, so it is unclear what you are trying to do.

If you can post back with a description of what you want to do and how the
subform figures into this, perhaps we can give you some help.

"PHisaw" wrote:

Hello,

I have an unbound combo box on a form that also has a list box. I want the
list box to populate with only those records that match selection from combo
box (a text field). I've done this before in another db and have copied and
pasted the code to this db, but can't get it to work. I've gotten errors and
have changed it around so much that I'm not sure where to go with it now. I
don't get the error messages but it doesn't return anything either.

Will someone please let me know what I'm doing wrong? I've included the
code from both below.

Private Sub CboFluid_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT qCompiledList.[SeparateSN],
qCompiledList.[OriginalCustomer], qCompiledList.[City],
qCompiledList.[State], qCompiledList.[Model], qCompiledList.[Fluid],
qCompiledList.[FluidListedBySundyne]" & _
"FROM qCompiledList " & _
"WHERE qCompiledList.[Fluid] = '" & Me.CboFluid & "'" & _
"Or qCompiledList.[FluidListedBySundyne] = '" & Me.CboFluid & "';"
Me.RecordSource = strSQL
End Sub

rivate Sub Form_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Fluid] = '" &
Str(Nz(Forms!Form1!fCompiledListSubform.Form!Fluid , 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help is greatly appreciated!
Thanks in advance,
Pam