Switch Search Criteria in Combo Box

G

Guest

I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
 
S

strive4peace

Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
 
G

Guest

Hi Crystal,

Thank you! I am going to attemp this now. FYI, The [PO No] field is my
primary index. It is an Autonumber - Long Integer. Don't know if this changes
anything. Thanks.

Howard

strive4peace said:
Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
 
G

Guest

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






strive4peace said:
Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
 
S

strive4peace

Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:

Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Crystal,

In answer to your question, yes, the PO No is a unique field and is the
primary key. The property code is not. Can I assume I should have two combo
boxes, one to look up by PO No, and the other to look up by Property Name? If
so, I assume I'm going to use the "visible" attribute to invoke one or the
other. If this is what you had in mind, please let me know. Thanks for all
the help. It's very much appreciated.

Howard

strive4peace said:
Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:

Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:

I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Crystal,

Okay...I'm 98% there. This is working almost perfect, and so simple. With
two combo boxes and using the visible attribute, only a few lines of code and
it works great. One more hopefully small issue and it's perfect. Since the PO
No's are unique, they work perfect. You select a PO No from the drop down
list and it's displayed on the form. Not quite so perfect with the Property
Name combo box. There are duplicate Property Names as any property can have
numerous PO's. When the drop down list appears, the Properties are sorted
alphabetically with the unique PO's displayed besides the names as should be.
The property names will be duplicated as again any property may have multiple
PO's. Lets say a property named Yorkdale Mall has 5 PO's. Therefore in the
dropdown list, it will show 5 lines of Yorkdale Mall, each with a unique PO
number beside it. So far so good. Let's say the first Yorkdale Mall entry has
PO number 1024. No matter which line for Yorkdale I click on, it always
selects the first PO 1024, the first in the list for Yorkdale. The same
happens fo all other properties, it always selects the first PO for that
particular property. Do you know a way I can fix this so it selects the
actual PO I click on? Let me know. Thanks again!

Howard


strive4peace said:
Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:

Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:

I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

Hi Howard,

You're welcome ;)

It is not really a matter of how you "should" do it, but
seperating the combos does make life easier! It is also
very easy to add additional comboboxes for searching using
other data and sorting.

Rather than turning one or the other on or off, why not let
them both be visible all the time? I put combos to find and
filter in my form headers.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

In answer to your question, yes, the PO No is a unique field and is the
primary key. The property code is not. Can I assume I should have two combo
boxes, one to look up by PO No, and the other to look up by Property Name? If
so, I assume I'm going to use the "visible" attribute to invoke one or the
other. If this is what you had in mind, please let me know. Thanks for all
the help. It's very much appreciated.

Howard

:

Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:



Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:


I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

Hi Howard,

Perhaps you want PropertyName to be a FILTER and not a FIND.

Instead of applying the filter to your form, you could apply
it to the list in the combobox to find by POnumber.

the after update event of your PropertyName combo to filter
could be something like this:

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

Dim strSQL As String

strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder "

if not isnull(me.FilterPropertyName) then
strSQL = strSQL _
& " WHERE PropertyName = '" _
& me.FilterPropertyName & "' "
end if

strSQL = strSQL & " ORDER BY [PO No];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus
Me.SearchForPO.DropDown

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

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Okay...I'm 98% there. This is working almost perfect, and so simple. With
two combo boxes and using the visible attribute, only a few lines of code and
it works great. One more hopefully small issue and it's perfect. Since the PO
No's are unique, they work perfect. You select a PO No from the drop down
list and it's displayed on the form. Not quite so perfect with the Property
Name combo box. There are duplicate Property Names as any property can have
numerous PO's. When the drop down list appears, the Properties are sorted
alphabetically with the unique PO's displayed besides the names as should be.
The property names will be duplicated as again any property may have multiple
PO's. Lets say a property named Yorkdale Mall has 5 PO's. Therefore in the
dropdown list, it will show 5 lines of Yorkdale Mall, each with a unique PO
number beside it. So far so good. Let's say the first Yorkdale Mall entry has
PO number 1024. No matter which line for Yorkdale I click on, it always
selects the first PO 1024, the first in the list for Yorkdale. The same
happens fo all other properties, it always selects the first PO for that
particular property. Do you know a way I can fix this so it selects the
actual PO I click on? Let me know. Thanks again!

Howard


:

Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:



Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:


I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Crystal,

Leave them both visible...What a great idea!!! Thanks so much!

strive4peace said:
Hi Howard,

You're welcome ;)

It is not really a matter of how you "should" do it, but
seperating the combos does make life easier! It is also
very easy to add additional comboboxes for searching using
other data and sorting.

Rather than turning one or the other on or off, why not let
them both be visible all the time? I put combos to find and
filter in my form headers.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

In answer to your question, yes, the PO No is a unique field and is the
primary key. The property code is not. Can I assume I should have two combo
boxes, one to look up by PO No, and the other to look up by Property Name? If
so, I assume I'm going to use the "visible" attribute to invoke one or the
other. If this is what you had in mind, please let me know. Thanks for all
the help. It's very much appreciated.

Howard

:

Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:



Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:


I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Crystal,

I don't quite understand this, but I'll give it a try, and play with it.
Thanks again!

strive4peace said:
Hi Howard,

Perhaps you want PropertyName to be a FILTER and not a FIND.

Instead of applying the filter to your form, you could apply
it to the list in the combobox to find by POnumber.

the after update event of your PropertyName combo to filter
could be something like this:

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

Dim strSQL As String

strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder "

if not isnull(me.FilterPropertyName) then
strSQL = strSQL _
& " WHERE PropertyName = '" _
& me.FilterPropertyName & "' "
end if

strSQL = strSQL & " ORDER BY [PO No];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus
Me.SearchForPO.DropDown

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

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Okay...I'm 98% there. This is working almost perfect, and so simple. With
two combo boxes and using the visible attribute, only a few lines of code and
it works great. One more hopefully small issue and it's perfect. Since the PO
No's are unique, they work perfect. You select a PO No from the drop down
list and it's displayed on the form. Not quite so perfect with the Property
Name combo box. There are duplicate Property Names as any property can have
numerous PO's. When the drop down list appears, the Properties are sorted
alphabetically with the unique PO's displayed besides the names as should be.
The property names will be duplicated as again any property may have multiple
PO's. Lets say a property named Yorkdale Mall has 5 PO's. Therefore in the
dropdown list, it will show 5 lines of Yorkdale Mall, each with a unique PO
number beside it. So far so good. Let's say the first Yorkdale Mall entry has
PO number 1024. No matter which line for Yorkdale I click on, it always
selects the first PO 1024, the first in the list for Yorkdale. The same
happens fo all other properties, it always selects the first PO for that
particular property. Do you know a way I can fix this so it selects the
actual PO I click on? Let me know. Thanks again!

Howard


:

Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:

Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:



Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:


I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

You're welcome, Howard ;)

To differentiate find and filter from controls for data
(even though they are in the header), I usually make the
background color Light Yellow for Find combos and Light
Green or Blue for Filter combos.

Even though you do not have light (faded) colors to pick
from on the color list, here is what I do:

1. pick a fill color from the icon drop-down

2. then click on the builder button (...) for BackColor
(Format tab of the property sheet)

3. click on the "Define Custom Colors >>" button

4. drag the triangle up the slider for a lighter shade

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Leave them both visible...What a great idea!!! Thanks so much!

:

Hi Howard,

You're welcome ;)

It is not really a matter of how you "should" do it, but
seperating the combos does make life easier! It is also
very easy to add additional comboboxes for searching using
other data and sorting.

Rather than turning one or the other on or off, why not let
them both be visible all the time? I put combos to find and
filter in my form headers.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

In answer to your question, yes, the PO No is a unique field and is the
primary key. The property code is not. Can I assume I should have two combo
boxes, one to look up by PO No, and the other to look up by Property Name? If
so, I assume I'm going to use the "visible" attribute to invoke one or the
other. If this is what you had in mind, please let me know. Thanks for all
the help. It's very much appreciated.

Howard

:



Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:


Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:




Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:



I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

Hi Howard,

basically, what I mean is to use the AfterUpdate event of
"Find by PO" really find records... and to make the
AfterUpdate event of the PropertyName combo limit the list
for the find by Po Number combo.

Alternately, on the PropertyName combo, you could have
several PO's listed for each PropertyName (which would be
repeated). It would be be sorted by PropertyName and then
by PO. Its first* (hidden) column would be PO Number.

You can actually have something other than the first column
be the "bound" column for a combo, but I find it easier,
conceptually, to always use column 1 to bind it and repeat
that column elsewhere for display if it is not the way I
want the user to look things up.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

I don't quite understand this, but I'll give it a try, and play with it.
Thanks again!

:

Hi Howard,

Perhaps you want PropertyName to be a FILTER and not a FIND.

Instead of applying the filter to your form, you could apply
it to the list in the combobox to find by POnumber.

the after update event of your PropertyName combo to filter
could be something like this:

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

Dim strSQL As String

strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder "

if not isnull(me.FilterPropertyName) then
strSQL = strSQL _
& " WHERE PropertyName = '" _
& me.FilterPropertyName & "' "
end if

strSQL = strSQL & " ORDER BY [PO No];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus
Me.SearchForPO.DropDown

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

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Hi Crystal,

Okay...I'm 98% there. This is working almost perfect, and so simple. With
two combo boxes and using the visible attribute, only a few lines of code and
it works great. One more hopefully small issue and it's perfect. Since the PO
No's are unique, they work perfect. You select a PO No from the drop down
list and it's displayed on the form. Not quite so perfect with the Property
Name combo box. There are duplicate Property Names as any property can have
numerous PO's. When the drop down list appears, the Properties are sorted
alphabetically with the unique PO's displayed besides the names as should be.
The property names will be duplicated as again any property may have multiple
PO's. Lets say a property named Yorkdale Mall has 5 PO's. Therefore in the
dropdown list, it will show 5 lines of Yorkdale Mall, each with a unique PO
number beside it. So far so good. Let's say the first Yorkdale Mall entry has
PO number 1024. No matter which line for Yorkdale I click on, it always
selects the first PO 1024, the first in the list for Yorkdale. The same
happens fo all other properties, it always selects the first PO for that
particular property. Do you know a way I can fix this so it selects the
actual PO I click on? Let me know. Thanks again!

Howard


:



Hi Howard,

You're welcome ;) I would have been surprised if that code
WAS perfect as I didn't set up a form to test it, just
wanted to give you logic. I am glad you got it to work :)

Instead of changing one search combo, you may want to use
multiple combos (they don't have to be wide for the list to
be wide) -- this makes it very easy to add additional sort
options without changing combobox properties. Just set the
first column of each combo to the field with the primary key
and you can use the same code.

Is PO No the unique field in your record?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:


Hi Crystal,

Thanks for the code. It didn't quite work out-of-the box. I modified is as
follows. Currently, the first time in, the serach by Property Name works. The
switching to Search By PO No, work as well. However, when I switch back to
Search By Property Name, the columns in the drop down are screwed up and it
always return the first record in the list regardless of which is picked. The
Search By PO No always worked when switched to. Please have a look at my code
and offer any advise. Thanks! - Howard

Private Sub cmdSwitchSearch_Click()
Dim strSQL As String, mCaption As String
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

If Me.cmdSwitchSearch.Caption _
= "Search By PO No." Then

mCaption = "Search By Property Name"
Me.SearchForPOLabel.Caption = "PO No. Search: "

Me.SearchForPO.ColumnWidths = "2"";.25"";2"";2"
strSQL = strSQL & " [PO No], [Property Name]"

Else

mCaption = "Search By PO No."
Me.SearchForPOLabel.Caption = "Property Search: "

Me.SearchForPO.ColumnWidths = ".25"";2"";2"";2"
strSQL = strSQL & " [Property Name], [PO No]"

End If

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

Me.SearchForPO.RowSource = strSQL
Me.SearchForPO.Requery
Me.SearchForPO.SetFocus

Me.cmdSwitchSearch.Caption = mCaption

End Sub



Private Sub SearchForPO_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Me.cmdSwitchSearch.Caption = "Search By PO No." Then
rs.FindFirst "[Property Name] = '" & Me![SearchForPO] & "'"
Else
rs.FindFirst "[PO No] = " & Str(Nz(Me![SearchForPO], 0))
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub






:




Hello Howard,

OnClick event of the SwitchSearch Command Button:

'~~~~~~~~~~~~~~~~~~~~~~~~~~`
dim strSQL as string, mCaption as string
strSQL = "SELECT [PO No], " _
& " [Property Name], " _
& " [Issue Date], " _
& " [Supplier Name] " _
& " FROM qryPurchaseOrder " _
& " ORDER BY "

if me.cmdSwitchSearch.caption _
= "Search By PO No" then

mCaption = "Search By Property Name"

me.combobox_controlname.ColumnWidths = 1;2;1;2
strSQL = strSQL & " [PO No], [Property Name]"

else

mCaption = "Search By PO No"

me.combobox_controlname.ColumnWidths = 0;3;1;2
strSQL = strSQL & " [Property Name] "

end if

strSQL = strSQL & ",[Issue Date], [Supplier Name];"

me.combobox_controlname.RowSource = strSQL
me.combobox_controlname.Requery
me.combobox_controlname.SetFocus

me.cmdSwitchSearch.caption = mCaption

'you may need to uncomment this line
'DoEvents

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

where cmdSwitchSearch is the controlname of your Search
command button

AfterUpdate event of the combobox:

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

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mPoNo as string
mPoNo = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "[Po No]= '" & mPoNo & "'"

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If

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

I am assuming that Po Number will always be filled out.
Ideally, the first column in each combo would be the unique
recordID (autonumber/long integer data type).

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


hfreedman wrote:



I'm sorry but I need to post my problem again. Thanks to Klatuu, I now
realize a simpler approach to resolve my issue but I still need help.
I have a form called frmPurchaseOrder which displays a single purchase order
record at a time using a query record source call qryPurchaseOrder. On the
form is a combo box called SearchForPO to search for and display a particular
purchase order record. The fields to display in the SearchForPO drop down
list are [PO No], [Property Name], [Issue Date]. and [Supplier Name]. I want
to be able to search two ways, by [Property Name] or by [PO No]. I have a
command button called SwitchSearch. If SearchForPO is currently set to search
by Property Name, when SwitchSearch is clicked, I want the following to
happen:
1) The SearchForPO ComboBox label will change from "PO No Search" to
"Property Name Search"
2) The SearchForPO ComboBox drop down list will display the records in [PO
No] order with the field order as [PO No], [Property Name], [Issue Date]. and
[Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By PO
No" to "Search By Property Name"
If SearchForPO is currently set to search by PO No, when SwitchSearch is
clicked, I want the following to happen:
1) The SearchForPO ComboBox label will change from "Property Name Search" to
"PO No Search"
2) The SearchForPO ComboBox drop down list will display the records in
[Property Name] order with the field order as [Property Name], [PO No],
[Issue Date]. and [Supplier Name]
3) The SwitchSearch Command Button Caption will change from "Search By
Property Name" back to "Search By PO No"
I hope this is clear. I need examples of statements to accomplish this or
just the exact code:) Any help will be appreciated. Thanks so much!
Howard
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Toggle form between two querys / search fields 3
Combo box choice checks a box in another database table via dlooku 3
Pass information from form to QUERY CRITERIA 1
Using a Listbox's RowSource in a Dlookup 0
Need combo box to refresh when moving to new record 2017 1
Need help coding nested/dynamic combo boxes 0
Help with Number sequence function 1
Purchase Order form questions 6

Top