Cascading Combo Box

S

Sharon

I have tried to use an option group and a combo box as shown in the approach
by Martin Green at www.fontstuff.com CascadingListDemo4. After days or work,
I cannot get it to work properly. I am not using a table for the
ReportNumbers, but am using a union query "qryAllReports" that list all of
the Report Numbers from 3 separate tables. I have gone around and around
until I am so confused I don't know where I have gone wrong. Can anyone help?


Private Sub grpReportName_AfterUpdate()
On Error Resume Next
Dim strReportType As String
Select Case grpReportName.Value
Case 1
strReportType = "ExpenseReport"
Case 2
strReportType = "TravelExpenseReport"
Case 3
strReportType = "Purchase Req."
Case 4
strReportType = "CheckRequest"

End Select
cboReportNumber.RowSource = "Select qryAllReports.ReportNumber " & _
"FROM qryAllReports " & _
"WHERE tblReportType.ReportName = '" & strReportType & "' " & _
"ORDER BY qryAllReports.ReportNumber;"
End Sub

Private Sub Form_Current()
On Error Resume Next
Dim strReportType As String
If IsNull(cboReportNumber.Value) Then
grpReportName.Value = Null
End If
' Synchronise ReportType combo with existing ReportNumber
strReportType = DLookup("[ReportName]", "qryAllReports",
"[ReportNumber]='" & cboReportNumber.Value & "'")
Select Case strReportType
Case "ExpenseReport"
grpReportName.Value = 1
Case "TravelExpenseReport"
grpReportName.Value = 2
Case "Purchase Requisition"
grpReportName.Value = 3
Case "CheckRequest"
grpReportName.Value = 4
End Select
' Synchronise ReportNumber combo with existing ReportNumber
cboReportNumber.RowSource = "SELECT qryAllReports.ReportNumber " & _
"FROM qryAllReports " & _
"WHERE tblReportType.ReportName = '" & strReportType & "' " & _
"ORDER BY [cboReportNumber];"
End Sub


Thanks in advance.
 
S

SteveS

Hi Sharon,

The problem is the query "qryAllReports". I think you have one field in the
query - the report name. In Martin Green's example, the table "tblAll" has two
fields.

For your code to work, the structure (and entries) for query "qryAllReports"
would have to look like

ReportName ReportNumber
---------------------------
"for case 1 (ExpenseReport)"

Educational 1
Company CC 1

"for case 2 (TravelExpenseReport)"

Meals 2
Lodging 2
Transportation 2

"for case 3 (Purchase Req.)"

Software 3
Hardware 3
Office Goods 3

etc.


Following Martin Green's example

ReportType ReportName
=======================================
Educational ExpenseReport
Company CC ExpenseReport

Meals TravelExpenseReport
Lodging TravelExpenseReport
Transportation TravelExpenseReport

Software Purchase Req.
Hardware Purchase Req.
Office Goods Purchase Req.

etc.




What is the SQL of the query "qryAllReports"?
When you open the query, what does the results look like? (just a few rows)


How many different types of Expense Reports do you have?
How many different types of TravelExpenseReport do you have?
etc.


Hope this makes sense

--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
I have tried to use an option group and a combo box as shown in the approach
by Martin Green at www.fontstuff.com CascadingListDemo4. After days or work,
I cannot get it to work properly. I am not using a table for the
ReportNumbers, but am using a union query "qryAllReports" that list all of
the Report Numbers from 3 separate tables. I have gone around and around
until I am so confused I don't know where I have gone wrong. Can anyone help?


Private Sub grpReportName_AfterUpdate()
On Error Resume Next
Dim strReportType As String
Select Case grpReportName.Value
Case 1
strReportType = "ExpenseReport"
Case 2
strReportType = "TravelExpenseReport"
Case 3
strReportType = "Purchase Req."
Case 4
strReportType = "CheckRequest"

End Select
cboReportNumber.RowSource = "Select qryAllReports.ReportNumber " & _
"FROM qryAllReports " & _
"WHERE tblReportType.ReportName = '" & strReportType & "' " & _
"ORDER BY qryAllReports.ReportNumber;"
End Sub

Private Sub Form_Current()
On Error Resume Next
Dim strReportType As String
If IsNull(cboReportNumber.Value) Then
grpReportName.Value = Null
End If
' Synchronise ReportType combo with existing ReportNumber
strReportType = DLookup("[ReportName]", "qryAllReports",
"[ReportNumber]='" & cboReportNumber.Value & "'")
Select Case strReportType
Case "ExpenseReport"
grpReportName.Value = 1
Case "TravelExpenseReport"
grpReportName.Value = 2
Case "Purchase Requisition"
grpReportName.Value = 3
Case "CheckRequest"
grpReportName.Value = 4
End Select
' Synchronise ReportNumber combo with existing ReportNumber
cboReportNumber.RowSource = "SELECT qryAllReports.ReportNumber " & _
"FROM qryAllReports " & _
"WHERE tblReportType.ReportName = '" & strReportType & "' " & _
"ORDER BY [cboReportNumber];"
End Sub


Thanks in advance.
Click to expand...
 
S

Sharon

I appreciate your response to my question, without the respondees I would be
lost! :)

Because I have three different tables, and wanted only one field for all
report numbers, I used a Union query to create this query. I am not very
experienced with these types of queries so am not sure how to add more
columns as you suggested. This is the SQL code that I used to make the
qryAllReports.

SELECT tblExpenseReport.ExpenseReportNumber as ReportNumber
FROM tblExpenseReport

UNION SELECT tblTravelExpenseReport.TravelExpenseReportNumber as ReportNumber
FROM tblTravelExpenseReport

UNION SELECT tblCheckRequest.CheckRequestNum as ReportNumber
FROM tblCheckRequest

UNION SELECT tblPurchaseOrder.PurchaseOrderNum as ReportNumber
FROM tblPurchaseOrder;

I thought a union query was appropriate for my needs here, but should I be
doing it a different way?


--
S


SteveS said:
Hi Sharon,

The problem is the query "qryAllReports". I think you have one field in the
query - the report name. In Martin Green's example, the table "tblAll" has two
fields.

For your code to work, the structure (and entries) for query "qryAllReports"
would have to look like

ReportName ReportNumber
---------------------------
"for case 1 (ExpenseReport)"

Educational 1
Company CC 1

"for case 2 (TravelExpenseReport)"

Meals 2
Lodging 2
Transportation 2

"for case 3 (Purchase Req.)"

Software 3
Hardware 3
Office Goods 3

etc.


Following Martin Green's example

ReportType ReportName
=======================================
Educational ExpenseReport
Company CC ExpenseReport

Meals TravelExpenseReport
Lodging TravelExpenseReport
Transportation TravelExpenseReport

Software Purchase Req.
Hardware Purchase Req.
Office Goods Purchase Req.

etc.




What is the SQL of the query "qryAllReports"?
When you open the query, what does the results look like? (just a few rows)


How many different types of Expense Reports do you have?
How many different types of TravelExpenseReport do you have?
etc.


Hope this makes sense

--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
I have tried to use an option group and a combo box as shown in the approach
by Martin Green at www.fontstuff.com CascadingListDemo4. After days or work,
I cannot get it to work properly. I am not using a table for the
ReportNumbers, but am using a union query "qryAllReports" that list all of
the Report Numbers from 3 separate tables. I have gone around and around
until I am so confused I don't know where I have gone wrong. Can anyone help?


Private Sub grpReportName_AfterUpdate()
On Error Resume Next
Dim strReportType As String
Select Case grpReportName.Value
Case 1
strReportType = "ExpenseReport"
Case 2
strReportType = "TravelExpenseReport"
Case 3
strReportType = "Purchase Req."
Case 4
strReportType = "CheckRequest"

End Select
cboReportNumber.RowSource = "Select qryAllReports.ReportNumber " & _
"FROM qryAllReports " & _
"WHERE tblReportType.ReportName = '" & strReportType & "' " & _
"ORDER BY qryAllReports.ReportNumber;"
End Sub

Private Sub Form_Current()
On Error Resume Next
Dim strReportType As String
If IsNull(cboReportNumber.Value) Then
grpReportName.Value = Null
End If
' Synchronise ReportType combo with existing ReportNumber
strReportType = DLookup("[ReportName]", "qryAllReports",
"[ReportNumber]='" & cboReportNumber.Value & "'")
Select Case strReportType
Case "ExpenseReport"
grpReportName.Value = 1
Case "TravelExpenseReport"
grpReportName.Value = 2
Case "Purchase Requisition"
grpReportName.Value = 3
Case "CheckRequest"
grpReportName.Value = 4
End Select
' Synchronise ReportNumber combo with existing ReportNumber
cboReportNumber.RowSource = "SELECT qryAllReports.ReportNumber " & _
"FROM qryAllReports " & _
"WHERE tblReportType.ReportName = '" & strReportType & "' " & _
"ORDER BY [cboReportNumber];"
End Sub


Thanks in advance.
Click to expand...
Click to expand...
 
S

SteveS

I thought a union query was appropriate for my needs here, but should I be
doing it a different way?
Click to expand...

It depends on What you are trying to do. I know *how* you have tried, but I am
confused on *what* you are trying to do.



Steve S.
 
S

Sharon

This is an expense database with the main table being tblReceipts. Every
receipt is recorded with the date, amount, etc. Each receipt can either be
categorized as an Expense, Travel Expense, Check Request, Purchase
Requisition, depending on the MethodofPayment. For example, if a purchase
was made on my credit card, I would need to be reimbursed so an expense
report is created. If the purchase was charged to the company for services a
check request needs to be created and if the purchase was for something
tangible like supplies, a purchase requisition is created and lastly if the
expense was related to business travel, a travel expense report is created.

I have two combo boxes:
1. ReportType:
a) Expense Report;
b) Travel Expense Report;
c) Check Request;
d) Purchase Requisition;

These reports begin with prefixes EXP, TEX, CRQ, PRQ

Then I have a combo box that is the union query
2. ReportNumber; EXP000100; TEX000100, CRQ000100, PRQ000100, etc.

Instead of having four different combo boxes with all EXPs, TEXs, CRQs and
PRQs separately, I created the union query which lists all of the report
numbers in one field.

CRQ000100
CRQ000200
EXP000100
EXP000200
EXP000300
EXP000400
EXP000500
PQR000100
PQR000200
TEX000100
TEX000200

What I wanted to happen is when I chose the type of report in one combo box,
the second combo box (cboReportNumber) would only show the reports related to
that type of report. For example: If I chose Expense Report, then the
second combo box would only show EXP000100; EXP000200; EXP000300; EXP000400;
EXP000500

I hope this gives you a better idea of what I am trying to accomplish.

Thanks for you help.




2.
 
S

SteveS

Hi Sharon,

OK, one more question.

Why do you have three tables? What data do the tables hold? (Field names?)
Do you have to have three tables?

Oops, that's three questions.... <grin>
 
S

SteveS

Sometimes the light finally comes on......

Try this:

Delete (or comment out) the Form_Current() sub



Change the grpReportNumber_AfterUpdate() sub to:

'**************
Private Sub grpReportNumber_AfterUpdate()
Dim strSQL As String

'clear the combo box
Me.cboReportNumber = Null

Select Case grpReportNumber
Case 1
strSQL = "SELECT tblExpenseReport.ExpenseReportNumber " & _
"FROM tblExpenseReport " & _
"Order By ExpenseReportNumber; "
Case 2
strSQL = "SELECT
tblTravelExpenseReport.TravelExpenseReportNumber " & _
"FROM tblTravelExpenseReport " & _
"Order By TravelExpenseReportNumber;"
Case 3
strSQL = "SELECT tblCheckRequest.CheckRequestNum " & _
"FROM tblCheckRequest " & _
"Order By CheckRequestNum;"
Case 4
strSQL = "SELECT tblPurchaseOrder.PurchaseOrderNum " & _
" FROM tblPurchaseOrder " & _
"Order By PurchaseOrderNum;"
End Select

Me.cboReportNumber.RowSource = strSQL
Me.cboReportNumber.SetFocus
Me.cboReportNumber.Dropdown
End Sub
'***********


I would suggest using a list box instead of a combo box. You can see more
report numbers and you can set up the listbox doubleclick event to open the
report.

Will this work for you??
 
S

Sharon

Steve!
I did it!!!!! It took me a while, but once I understood where you were
going with this, I realized how easy it really was. I was trying too hard,
as usual, when it was really quite simple. Thank you so much for your help!!
 
S

Sharon

Now i have new problem.

When I make a selection in the group, it does just show that information,
i.e., Expense Report only shows EXP#s, Travel Expense Report only shows
TEX#s. However, when I select one on one record for receipt and then select
the proper Report Number it does fine, but when I go to the next receipt
record and change it to another EXP#, ALL of the records change to that same
number. I guess somehow I need to synchronize this for EACH Receipt record?
 
S

SteveS

In re-reading your posts on 7/11-12, you keep saying "Report" & "Report
Number". I thought you were picking a REPORT named "CRQ000100" using the
option group and combo box.

But it now sounds like you have a continuous form with a combo box to select
the "ReportNumber" - the report number being a way to categorize an entry
(record):
CRQ000100 would be a check request, account 100;
EXP000200 would be an expense, account 200

(is this called a ledger of accounts?? I don't know accounting.... but I
*did* date an accounting major in college...<g>... but that doesn't help now)


If you change the combo box and ALL of the records change to the same
number, then the combo box is unbound (not tied to a field in a table).

What is the CONTROL SOURCE of the combo box?
 
S

Sharon

I have confused even myself. That is why it took so long to respond back to
you. This is what I have right now:
This is the grpReportNumber (which is more of a ReportType I suppose) option
box:

Expense Report
Travel Expense Report
Check Request
Purchase Order Requisition

Then I have the combo box cboReportNumber
When I choose an option in the option box, i.e., Expense Report, then only
the EXP report numbers are showing. That is exactly what I want to happen.
But, now going to the next step. I want this to occur on a form frmReceipts.
I want to open the frmReceipts, input the information, select from the
option box, the report type, and then select from the cboReportNumber the
correct Report Number and save this information on this form. In other
words, when I scroll through the frmReceipt, I want the correct Report Number
to show in the cboReportNumber field. So you know where I am going with
this, the next step will be a command button to open the correct
corresponding Report.

For instance,

in frmReceipt I am on receipt number 60006, previously I had entered the
Report type, expense report, and chosen the correct cboReportNumber
(EXP002000) in that field. Then, I can choose the command button and open
the Expense Report EXP002000.

I am trying to take one step at a time. I have an end in mind, but am
having much trouble navigating the correct course!
 
S

SteveS

What is the record source (SQL) for "frmReceipts"?
So you are using the cboReportNumber to determine which records will be in
the recordset for a report? (or are headed that way..<g>??) Then the
cboReportNumber needs to be bound to a field in the form "frmReceipts"
recordsource.


If you want, send me a copy of your database so I can see where you are at.
Please remove any (and all) sensitive info... do a Compact and Repair, then
WinZip it.
 
S

Sharon

Thanks Steve! I knew in my head that is what I needed to do, but wasn't sure
how to go about it. But, guess what, I did figure it out and it is working
just like I expected. I think when I actually saw it in print, somehow a
bell went on, or is that ringing in my ears???? <lol> Now, I just have to
keep going. Do people out there actually have a problem with ever completing
a database? It seems I am constantly "revamping" it??? :)

Again, you have been so patient and I couldn't have done it without you.
 
S

SteveS

Excellent! I'm glad I could "bump" you in the right direction. <g>

As far as finishing a database, you would have to get an answer from the
MVP's. AFAIK, a database is never finished, its just in constant revision...

Good luck with your project
 
M

Marshall Barton

Sharon said:
Do people out there actually have a problem with ever completing
a database? It seems I am constantly "revamping" it??? :)
Click to expand...


Most developers will always find ways to improve on a
project. In my experience a project is "complete" when the
client stops paying for changes ;-)

If you are your own client, you're in for a long haul ;-)
 

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

Union Query and Combo Box Synchronization 1
Cascading Combo Boxes 1
Cascading Combo box 4
Cascading Combo Box - help needed please. 6
Combo Box - Case Selection 7
Cascading Combo boxes 2
Need help cascading 5 combo boxes 12
cascading combo boxes 1

Top