Search Combo Box on Form

[Cy]

Here's what I have going on, let's see if anyone can help out.

I have a Customer form, that stores the usual information, name,
address, etc. It also has a flag field, to designate what type of
customer this is. Prospect, current, past, etc. On my main form, I
have a combo box, that allows the user to select the type of
customer. Thus, when they open customers, they can filter out to see
just specific types of customers, rather then a slew of them.

Once the form is open, I have a combo box, that shows the listing of
filtered customers. They can select the customer they wish to jump to
and it jumps to that customers record.

Here is the code behind the combo box:
Private Sub cboCustLookup_AfterUpdate()
' Find the record that matches the control.
On Error GoTo cboCustLookup_AfterUpdate_Error

Me.AllowEdits = True
Dim Rs As Object

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[propertyname] = '" & Me![cboCustLookup] & "'"
Me.Bookmark = Rs.Bookmark
Me.PropertyName.SetFocus
Me.AllowEdits = False

On Error GoTo 0
Exit Sub

cboCustLookup_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cboCustLookup_AfterUpdate of VBA Document Form_CUSTOMER"
End Sub

That all works great. Now the problem is that they wanted the ability
to view ALL customer types, rather then filtered, plus still have the
option of filtering out there list.

So, on the combo box, on the main menu, I added ALL to the list of
available customer types, then added this code:

Private Sub cmdOpenCustomers_Click()
On Error GoTo Err_cmdOpenCustomers_Click

Dim stDocName As String
Dim stlinkcriteria As String
Dim strCustomerStatus As String

stDocName = "CUSTOMER"
strCustomerStatus = "CustomerStatus=forms!mainmenu!
cboCustomerStatus"

If Me!cboCustomerStatus = "all" Then
DoCmd.OpenForm stDocName, , , stlinkcriteria
Else
DoCmd.OpenForm stDocName, , , strCustomerStatus
End If

Exit_cmdOpenCustomers_Click:
Exit Sub

Err_cmdOpenCustomers_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCustomers_Click

End Sub

Now that all works great. However, the problem becomes, that if they
select ALL on the main menu, but cboCustLookup doesn't work. So, I
removed the filtering for cboCustLookup, but now the problem is that
if they select say Prospect from the main menu and click
cmdOpenCustomer button, they get just prospects, however,
cboCustLookup shows all customers of all types.

I have been trying to figure out how I can filter the cboCustLookup,
if they select a type and not filter it if they select ALL.
Obviously, to no avail. So, I'm turning to the newsgroup to see if
anyone can help out. Any ideas? Suggestions?

Thanks,

Ron

 

[Jeanette Cunningham]

Ron,
You now have 2 separate combo boxes to select the type of customer. I
suggest that you remove the combo box for customer type on the main form and
let users select the customer type from the customer form.

Jeanette Cunningham

Here's what I have going on, let's see if anyone can help out.

I have a Customer form, that stores the usual information, name,
address, etc. It also has a flag field, to designate what type of
customer this is. Prospect, current, past, etc. On my main form, I
have a combo box, that allows the user to select the type of
customer. Thus, when they open customers, they can filter out to see
just specific types of customers, rather then a slew of them.

Once the form is open, I have a combo box, that shows the listing of
filtered customers. They can select the customer they wish to jump to
and it jumps to that customers record.

Here is the code behind the combo box:
Private Sub cboCustLookup_AfterUpdate()
' Find the record that matches the control.
On Error GoTo cboCustLookup_AfterUpdate_Error

Me.AllowEdits = True
Dim Rs As Object

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[propertyname] = '" & Me![cboCustLookup] & "'"
Me.Bookmark = Rs.Bookmark
Me.PropertyName.SetFocus
Me.AllowEdits = False

On Error GoTo 0
Exit Sub

cboCustLookup_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cboCustLookup_AfterUpdate of VBA Document Form_CUSTOMER"
End Sub

That all works great. Now the problem is that they wanted the ability
to view ALL customer types, rather then filtered, plus still have the
option of filtering out there list.

So, on the combo box, on the main menu, I added ALL to the list of
available customer types, then added this code:

Private Sub cmdOpenCustomers_Click()
On Error GoTo Err_cmdOpenCustomers_Click

Dim stDocName As String
Dim stlinkcriteria As String
Dim strCustomerStatus As String

stDocName = "CUSTOMER"
strCustomerStatus = "CustomerStatus=forms!mainmenu!
cboCustomerStatus"

If Me!cboCustomerStatus = "all" Then
DoCmd.OpenForm stDocName, , , stlinkcriteria
Else
DoCmd.OpenForm stDocName, , , strCustomerStatus
End If

Exit_cmdOpenCustomers_Click:
Exit Sub

Err_cmdOpenCustomers_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCustomers_Click

End Sub

Now that all works great. However, the problem becomes, that if they
select ALL on the main menu, but cboCustLookup doesn't work. So, I
removed the filtering for cboCustLookup, but now the problem is that
if they select say Prospect from the main menu and click
cmdOpenCustomer button, they get just prospects, however,
cboCustLookup shows all customers of all types.

I have been trying to figure out how I can filter the cboCustLookup,
if they select a type and not filter it if they select ALL.
Obviously, to no avail. So, I'm turning to the newsgroup to see if
anyone can help out. Any ideas? Suggestions?

Thanks,

Ron

 

[Cy]

Not to sound stupid, but how would you suggest that, if the combo box
on the form is used for identifying the customer type for a particular
customer and saving it to that particular record?

Ron,
You now have 2 separate combo boxes to select the type of customer. I
suggest that you remove the combo box for customer type on the main form and
let users select the customer type from the customer form.

Jeanette Cunningham

Here's what I have going on, let's see if anyone can help out.

I have a Customer form, that stores the usual information, name,
address, etc. It also has a flag field, to designate what type of
customer this is. Prospect, current, past, etc. On my main form, I
have a combo box, that allows the user to select the type of
customer. Thus, when they open customers, they can filter out to see
just specific types of customers, rather then a slew of them.

Once the form is open, I have a combo box, that shows the listing of
filtered customers. They can select the customer they wish to jump to
and it jumps to that customers record.

Here is the code behind the combo box:
Private Sub cboCustLookup_AfterUpdate()
' Find the record that matches the control.
On Error GoTo cboCustLookup_AfterUpdate_Error

Me.AllowEdits = True
Dim Rs As Object

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[propertyname] = '" & Me![cboCustLookup] & "'"
Me.Bookmark = Rs.Bookmark
Me.PropertyName.SetFocus
Me.AllowEdits = False

On Error GoTo 0
Exit Sub

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cboCustLookup_AfterUpdate of VBA Document Form_CUSTOMER"
End Sub

That all works great. Now the problem is that they wanted the ability
to view ALL customer types, rather then filtered, plus still have the
option of filtering out there list.

So, on the combo box, on the main menu, I added ALL to the list of
available customer types, then added this code:

Private Sub cmdOpenCustomers_Click()
On Error GoTo Err_cmdOpenCustomers_Click

Dim stDocName As String
Dim stlinkcriteria As String
Dim strCustomerStatus As String

stDocName = "CUSTOMER"
strCustomerStatus = "CustomerStatus=forms!mainmenu!
cboCustomerStatus"

If Me!cboCustomerStatus = "all" Then
DoCmd.OpenForm stDocName, , , stlinkcriteria
Else
DoCmd.OpenForm stDocName, , , strCustomerStatus
End If

Exit_cmdOpenCustomers_Click:
Exit Sub

Err_cmdOpenCustomers_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCustomers_Click

End Sub

Now that all works great. However, the problem becomes, that if they
select ALL on the main menu, but cboCustLookup doesn't work. So, I
removed the filtering for cboCustLookup, but now the problem is that
if they select say Prospect from the main menu and click
cmdOpenCustomer button, they get just prospects, however,
cboCustLookup shows all customers of all types.

I have been trying to figure out how I can filter the cboCustLookup,
if they select a type and not filter it if they select ALL.
Obviously, to no avail. So, I'm turning to the newsgroup to see if
anyone can help out. Any ideas? Suggestions?

Ron

 

[Jeanette Cunningham]

I assumed the customer type was a combo used for searching for all the
customers of a particular type? Do you also need a combo that shows all the
current customers that are the particular type selected in the first combo?
Combos for searching need to be unbound. Combos to add/edit data need to be
bound to the field in the underlying table.
Assuming a one to many relationship between customers and customer type, a
main form for customer type with a subform for customer name, address etc
would be setup, is this how your form is set up?

Jeanette Cunningham

Not to sound stupid, but how would you suggest that, if the combo box
on the form is used for identifying the customer type for a particular
customer and saving it to that particular record?

Ron,
You now have 2 separate combo boxes to select the type of customer. I
suggest that you remove the combo box for customer type on the main form
and
let users select the customer type from the customer form.

Jeanette Cunningham

Here's what I have going on, let's see if anyone can help out.

I have a Customer form, that stores the usual information, name,
address, etc. It also has a flag field, to designate what type of
customer this is. Prospect, current, past, etc. On my main form, I
have a combo box, that allows the user to select the type of
customer. Thus, when they open customers, they can filter out to see
just specific types of customers, rather then a slew of them.

Once the form is open, I have a combo box, that shows the listing of
filtered customers. They can select the customer they wish to jump to
and it jumps to that customers record.

Here is the code behind the combo box:
Private Sub cboCustLookup_AfterUpdate()
' Find the record that matches the control.
On Error GoTo cboCustLookup_AfterUpdate_Error

Me.AllowEdits = True
Dim Rs As Object

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[propertyname] = '" & Me![cboCustLookup] & "'"
Me.Bookmark = Rs.Bookmark
Me.PropertyName.SetFocus
Me.AllowEdits = False

On Error GoTo 0
Exit Sub

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cboCustLookup_AfterUpdate of VBA Document Form_CUSTOMER"
End Sub

That all works great. Now the problem is that they wanted the ability
to view ALL customer types, rather then filtered, plus still have the
option of filtering out there list.

So, on the combo box, on the main menu, I added ALL to the list of
available customer types, then added this code:

Private Sub cmdOpenCustomers_Click()
On Error GoTo Err_cmdOpenCustomers_Click

Dim stDocName As String
Dim stlinkcriteria As String
Dim strCustomerStatus As String

stDocName = "CUSTOMER"
strCustomerStatus = "CustomerStatus=forms!mainmenu!
cboCustomerStatus"

If Me!cboCustomerStatus = "all" Then
DoCmd.OpenForm stDocName, , , stlinkcriteria
Else
DoCmd.OpenForm stDocName, , , strCustomerStatus
End If

Exit_cmdOpenCustomers_Click:
Exit Sub

Err_cmdOpenCustomers_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCustomers_Click

End Sub

Now that all works great. However, the problem becomes, that if they
select ALL on the main menu, but cboCustLookup doesn't work. So, I
removed the filtering for cboCustLookup, but now the problem is that
if they select say Prospect from the main menu and click
cmdOpenCustomer button, they get just prospects, however,
cboCustLookup shows all customers of all types.

I have been trying to figure out how I can filter the cboCustLookup,
if they select a type and not filter it if they select ALL.
Obviously, to no avail. So, I'm turning to the newsgroup to see if
anyone can help out. Any ideas? Suggestions?

Ron

 

[Jeanette Cunningham]

I usually do this sort of thing using a search form, I find that it makes
the coding a lot simpler.
To do it with a search form, you open a search form that has a combo for
Customer Type and a combo for Customer Name.
After user makes their selection, they press the OK button which opens up
the data entry/edit form to the customer record they chose on the search
form.
When they close the customer edit form they can return to the main menu and
do a new search.
The search form can also have a separate button if they wish to add a new
customer. On the form to add a new customer, you can have a bound combo
where they can choose the Customer type.

Jeanette Cunningham

I assumed the customer type was a combo used for searching for all the
customers of a particular type? Do you also need a combo that shows all the
current customers that are the particular type selected in the first combo?
Combos for searching need to be unbound. Combos to add/edit data need to
be bound to the field in the underlying table.
Assuming a one to many relationship between customers and customer type, a
main form for customer type with a subform for customer name, address etc
would be setup, is this how your form is set up?

Jeanette Cunningham

Not to sound stupid, but how would you suggest that, if the combo box
on the form is used for identifying the customer type for a particular
customer and saving it to that particular record?

Ron,
You now have 2 separate combo boxes to select the type of customer. I
suggest that you remove the combo box for customer type on the main form
and
let users select the customer type from the customer form.

Jeanette Cunningham



Here's what I have going on, let's see if anyone can help out.

I have a Customer form, that stores the usual information, name,
address, etc. It also has a flag field, to designate what type of
customer this is. Prospect, current, past, etc. On my main form, I
have a combo box, that allows the user to select the type of
customer. Thus, when they open customers, they can filter out to see
just specific types of customers, rather then a slew of them.

Once the form is open, I have a combo box, that shows the listing of
filtered customers. They can select the customer they wish to jump to
and it jumps to that customers record.

Here is the code behind the combo box:
Private Sub cboCustLookup_AfterUpdate()
' Find the record that matches the control.
On Error GoTo cboCustLookup_AfterUpdate_Error

Me.AllowEdits = True
Dim Rs As Object

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[propertyname] = '" & Me![cboCustLookup] & "'"
Me.Bookmark = Rs.Bookmark
Me.PropertyName.SetFocus
Me.AllowEdits = False

On Error GoTo 0
Exit Sub

cboCustLookup_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cboCustLookup_AfterUpdate of VBA Document Form_CUSTOMER"
End Sub

That all works great. Now the problem is that they wanted the ability
to view ALL customer types, rather then filtered, plus still have the
option of filtering out there list.

So, on the combo box, on the main menu, I added ALL to the list of
available customer types, then added this code:

Private Sub cmdOpenCustomers_Click()
On Error GoTo Err_cmdOpenCustomers_Click

Dim stDocName As String
Dim stlinkcriteria As String
Dim strCustomerStatus As String

stDocName = "CUSTOMER"
strCustomerStatus = "CustomerStatus=forms!mainmenu!
cboCustomerStatus"

If Me!cboCustomerStatus = "all" Then
DoCmd.OpenForm stDocName, , , stlinkcriteria
Else
DoCmd.OpenForm stDocName, , , strCustomerStatus
End If

Exit_cmdOpenCustomers_Click:
Exit Sub

Err_cmdOpenCustomers_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCustomers_Click

End Sub

Now that all works great. However, the problem becomes, that if they
select ALL on the main menu, but cboCustLookup doesn't work. So, I
removed the filtering for cboCustLookup, but now the problem is that
if they select say Prospect from the main menu and click
cmdOpenCustomer button, they get just prospects, however,
cboCustLookup shows all customers of all types.

I have been trying to figure out how I can filter the cboCustLookup,
if they select a type and not filter it if they select ALL.
Obviously, to no avail. So, I'm turning to the newsgroup to see if
anyone can help out. Any ideas? Suggestions?

Thanks,

Ron