Toggle form between two querys / search fields

G

Guest

I have a main form which displays a purchase order entry. The form is bound
to a query which is sorted by Customer Name. This works fine. The user
sometimes doesn't know the customer for a particular invoice and wants to
search by PO Number. My approach is to have two command (toggle) buttons to
switch the search "mode". If they click on the "Search by PO" command button
the following will happen:
1) The form will now be bound to a different query, sorted by PO number
2) The "Customer Name Search" combo box will disappear
3) The "PO Number Serach" combo box will re-appear
4) The "Search By PO" command (toggle) button will disapear
5) The "Search By Customer Name" command (toggle) button will appear
I think I know how to make the search fields and command buttons disappear
and re-appear usng the "visible" attribute, but how do I change which query
the form is bound to? Is this even the right approach for this? Any help
would be appreciated. Thanks so much!
 
G

Guest

You can do this with one combo box and one command button. Some example code
for each is below. What happens is the combo is initially set up to search
by Activity. The command button knows what to do based on the combo box's
bound column property. When you click the command button, it changes the
properties of the combo to use the other row source and changes the caption
of the command button.

The combo uses the caption of the command button to know how to do its search.

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION FROM CISAttributeTable;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY FROM CISAttributeTable;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
End Sub
 
G

Guest

This didn't quite work. I'm getting errors with the DAO.Recordset
declaration. I don't think it recognizes the DAO. Also, what is
CISAttributeTable? Should I be replacing that with my table name or is that
some reserved word? Sorry, I'm still a lttle green with VB. Thanks!

Howard

Klatuu said:
You can do this with one combo box and one command button. Some example code
for each is below. What happens is the combo is initially set up to search
by Activity. The command button knows what to do based on the combo box's
bound column property. When you click the command button, it changes the
properties of the combo to use the other row source and changes the caption
of the command button.

The combo uses the caption of the command button to know how to do its search.

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION FROM CISAttributeTable;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY FROM CISAttributeTable;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
End Sub


hfreedman said:
I have a main form which displays a purchase order entry. The form is bound
to a query which is sorted by Customer Name. This works fine. The user
sometimes doesn't know the customer for a particular invoice and wants to
search by PO Number. My approach is to have two command (toggle) buttons to
switch the search "mode". If they click on the "Search by PO" command button
the following will happen:
1) The form will now be bound to a different query, sorted by PO number
2) The "Customer Name Search" combo box will disappear
3) The "PO Number Serach" combo box will re-appear
4) The "Search By PO" command (toggle) button will disapear
5) The "Search By Customer Name" command (toggle) button will appear
I think I know how to make the search fields and command buttons disappear
and re-appear usng the "visible" attribute, but how do I change which query
the form is bound to? Is this even the right approach for this? Any help
would be appreciated. Thanks so much!
Click to expand...
Click to expand...
 
G

Guest

CISAttributeTable is a table in my database. You will need to change that to
whatever table you use.
I don't know what version of Access you are on, so there are two things you
can do. One is to set a reference to DAO in you VBA references. That would
be in the VB Editor, Tools, References.

The other would be to remove the DAO reference. That would be changing this
line
Dim rst As DAO.Recordset
to
Dim rst AsRecordset

hfreedman said:
This didn't quite work. I'm getting errors with the DAO.Recordset
declaration. I don't think it recognizes the DAO. Also, what is
CISAttributeTable? Should I be replacing that with my table name or is that
some reserved word? Sorry, I'm still a lttle green with VB. Thanks!

Howard

Klatuu said:
You can do this with one combo box and one command button. Some example code
for each is below. What happens is the combo is initially set up to search
by Activity. The command button knows what to do based on the combo box's
bound column property. When you click the command button, it changes the
properties of the combo to use the other row source and changes the caption
of the command button.

The combo uses the caption of the command button to know how to do its search.

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION FROM CISAttributeTable;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY FROM CISAttributeTable;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
End Sub


hfreedman said:
I have a main form which displays a purchase order entry. The form is bound
to a query which is sorted by Customer Name. This works fine. The user
sometimes doesn't know the customer for a particular invoice and wants to
search by PO Number. My approach is to have two command (toggle) buttons to
switch the search "mode". If they click on the "Search by PO" command button
the following will happen:
1) The form will now be bound to a different query, sorted by PO number
2) The "Customer Name Search" combo box will disappear
3) The "PO Number Serach" combo box will re-appear
4) The "Search By PO" command (toggle) button will disapear
5) The "Search By Customer Name" command (toggle) button will appear
I think I know how to make the search fields and command buttons disappear
and re-appear usng the "visible" attribute, but how do I change which query
the form is bound to? Is this even the right approach for this? Any help
would be appreciated. Thanks so much!
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

Switch Search Criteria in Combo Box 12
Pass information from form to QUERY CRITERIA 1
Using a Listbox's RowSource in a Dlookup 0
coding between subforms 5
toggle button nightmare 3
Filter or reload recordset? 4
select subform records to add to table and report 3
Access Microsoft access database(creating a search engine form) 0

Top