Opening form based on combo box selection

[Sharon]

At first I was using the FormType in a List. I had it working correctly that
when I went into the tblReceipts and chose a ReportType (in the list) it
would automatically open that form so I could input the information into that
particular form. For instance, if I chose "Expense Report" it would open the
frmExpenseReport and I would go on my merry way. Then, I had to think too
hard and realized that I should have used ReportType in a separate table.
So, when I went back and created a tblReportType with a "ReportTypeID" and a
"ReportType" field and created a drop down box in the form with these two
columns (only ReportType is visible), and then tried to run the code I
created to open the forms, etc., it won't work. I have tried various things
such as changing the "Cancel as Integer" and using "dim ReportType as
integer", changing the "Me" to "tblReports.ReportType", changing the "Expense
Report" to the ReportTypeID instead of the ReportType, i.e. "1" instead of
"Expense Report" and I just can't get it to work right. I am tempted to go
back to the way I had it, but I don't think that is the proper way to design
a database? Any help is appreciated as I am now just going in circles and
probably messing other portions of the database up with my random trying of
different things. Thanks.


Private Sub cmbReportType_Exit(Cancel As Integer)

If tblReports.ReportType = "Expense Report" Then
DoCmd.OpenForm "frmExpenseReport", , , , , acNormal
ElseIf Me.ReportType = "Purchase Order Report" Then
DoCmd.OpenForm "frmPurchaseOrder", , , , , acNormal
ElseIf Me.ReportType = "Check Request Report" Then
DoCmd.OpenForm "frmCheckRequest", , , , , acNormal
ElseIf Me.ReportType = "Travel Expense Report" Then
DoCmd.OpenForm "frmTravelExpenseReport", , , , , acNormal
ElseIf Me.ReportType = "Reimbursement Check Report" Then
DoCmd.OpenForm "frmReimbursementCheck", , , , , acNormal
ElseIf Me.ReportType = "Trip Report" Then
DoCmd.OpenForm "frmTripReport", , , , , acNormal
End If

End Sub

 

[Chaim]

Sharon,

You're basing your selection on the the table not the combo box. According
to the name of the Exit event procedure, the name of your Combo Box is
'cmbReportType'.

Try changing 'tblReports' to 'Me!cmbReportType' and see what happens. I'm
assuming the text that is displayed in the combo box corresponds to
ReportType. I don't think you need to include ReportType in the conditions.
Final should look like:

IF Me!cmbReportType = "Expense Report" then
DoCmd.OpenForm "frmExpenseReport", , , , , acNormal
elseif ....

You might need to use me!cmbReportType.Text if this is not the bound column
of the combo box.

Good Luck!

 

[John Griffiths]

Are you trying to launch a form based on the user selecting an English
description?

IE
DoCmd.OpenForm Me!cmbReportType.Column(0), , , , , acNormal
supposing that
Me!cmbReportType.Column(1) = "Fill Out Expense Details"
Me!cmbReportType.Column(0) = "frmExpenseReport"


Had to reply as I couldn't be sure of the question - John

 

[Sharon]

If by "an English description" you mean type written out in words instead of
autonumber , by John I think you've got it!!! I think, if I am following
you correctly, the query is selecting the "English description" instead of
the AutoNumber, right? The message that I am getting is as follows "The
value you entered isn't valid for this field. For example, you may have
entered text in a numeric field or a number that is larger than the field
size setting permits" so that seems to be the case. You are saying that the
Column 0 is the Autonumber and Column 1 is the English description?

The way I have it set up is that Column 0 is the ReportTypeID (autonumber)
and the "ReportType" (text) is "Expense Report", "Purchase Order", "Check
Request", etc. So I need to "pull" the information from Column(1)?

I hope I am following this correctly. If not, I apologize, as I find myself
going around in circles. Sometimes, I think I know just enough to be
dangerous and not enough to do any good!!!! I appreciate your patience.

S

 

[Sharon]

I have tried the code below with and without the ".Text" but I keep getting
the message that you can see in the reply to John. I thought this might have
something to do with the Autonumber/ReportType query, but now I don't know.
Thanks for your help!

Private Sub cmbReportType_Exit(Cancel As Integer)


If Me!cmbReportType.Text = "Expense Report" Then
DoCmd.OpenForm "frmExpenseReport", , , , , acNormal
ElseIf Me.ReportType.Text = "Purchase Order Report" Then
DoCmd.OpenForm "frmPurchaseOrder", , , , , acNormal
ElseIf Me.ReportType.Text = "Check Request Report" Then
DoCmd.OpenForm "frmCheckRequest", , , , , acNormal
ElseIf Me.ReportType.Text = "Travel Expense Report" Then
DoCmd.OpenForm "frmTravelExpenseReport", , , , , acNormal
ElseIf Me.ReportType.Text = "Reimbursement Check Report" Then
DoCmd.OpenForm "frmReimbursementCheck", , , , , acNormal
End If

End Sub

 

[John Griffiths]

the message that you can see in the reply to John. I thought this might

have
<snip/>

FYI Not seen on server - John

 

[Sharon]

John, I wasn't sure what you mean by "Not shown on server" so I just posted
my response to you again. Sorry that I am not understanding what you mean.
Thanks.

Posting reply again.



If by "an English description" you mean type written out in words instead of
autonumber , by John I think you've got it!!! I think, if I am following
you correctly, the query is selecting the "English description" instead of
the AutoNumber, right? The message that I am getting is as follows "The
value you entered isn't valid for this field. For example, you may have
entered text in a numeric field or a number that is larger than the field
size setting permits" so that seems to be the case. You are saying that the
Column 0 is the Autonumber and Column 1 is the English description?

The way I have it set up is that Column 0 is the ReportTypeID (autonumber)
and the "ReportType" (text) is "Expense Report", "Purchase Order", "Check
Request", etc. So I need to "pull" the information from Column(1)?

I hope I am following this correctly. If not, I apologize, as I find myself
going around in circles. Sometimes, I think I know just enough to be
dangerous and not enough to do any good!!!! I appreciate your patience.

S


:

Click to show or hide original message or reply text.

Are you trying to launch a form based on the user selecting an English
description?

IE
DoCmd.OpenForm Me!cmbReportType.Column(0), , , , , acNormal
supposing that
Me!cmbReportType.Column(1) = "Fill Out Expense Details"
Me!cmbReportType.Column(0) = "frmExpenseReport"


Had to reply as I couldn't be sure of the question - John

Was this post helpful to you?
Reply Top





Sharon 7/15/2005 4:42 AM PST





I have tried the code below with and without the ".Text" but I keep getting
the message that you can see in the reply to John. I thought this might have
something to do with the Autonumber/ReportType query, but now I don't know.
Thanks for your help!

Private Sub cmbReportType_Exit(Cancel As Integer)


If Me!cmbReportType.Text = "Expense Report" Then
DoCmd.OpenForm "frmExpenseReport", , , , , acNormal
ElseIf Me.ReportType.Text = "Purchase Order Report" Then
DoCmd.OpenForm "frmPurchaseOrder", , , , , acNormal
ElseIf Me.ReportType.Text = "Check Request Report" Then
DoCmd.OpenForm "frmCheckRequest", , , , , acNormal
ElseIf Me.ReportType.Text = "Travel Expense Report" Then
DoCmd.OpenForm "frmTravelExpenseReport", , , , , acNormal
ElseIf Me.ReportType.Text = "Reimbursement Check Report" Then
DoCmd.OpenForm "frmReimbursementCheck", , , , , acNormal
End If

End Sub

--
S

 

[Sharon]

Never mind, I did it! I do appreciate all your help (and Chaim's too)!
After pointing me in the right direction, which I obviously only partially
understood, your responses forced me to think in "Access" to figure out my
mistakes and most of the time that is the most beneficial way to learn.
After coming back to it with a fresh look it only took me a minute to figure
out (Duh!) that I had the "column" on the wrong line.

Private Sub cmbReportType_Exit(Cancel As Integer)


If Me!cmbReportType.Column(1) = "Expense Report" Then
DoCmd.OpenForm "frmExpenseReport", , , , , acNormal
ElseIf Me.ReportType = "Purchase Order Report" Then
DoCmd.OpenForm "frmPurchaseOrder", , , , , acNormal
ElseIf Me.ReportType = "Check Request Report" Then
DoCmd.OpenForm "frmCheckRequest", , , , , acNormal
ElseIf Me.ReportType = "Travel Expense Report" Then
DoCmd.OpenForm "frmTravelExpenseReport", , , , , acNormal
ElseIf Me.ReportType = "Reimbursement Check Report" Then
DoCmd.OpenForm "frmReimbursementCheck", , , , , acNormal
ElseIf Me.ReportType = "Trip Report" Then
DoCmd.OpenForm "frmTripReport", , , , , acNormal
End If

End Sub

 

[Sharon]

I did it! After you pointed me in the right direction, and it didn't work
because I put the code in the wrong place, it forced me to think in "Access"
and I figured out what I was doing wrong. Thanks so much. I finally figured
out that I put your "column" instruction on the wrong line (Duh!). Thanks.


Private Sub cmbReportType_Exit(Cancel As Integer)


If Me!cmbReportType.Column(1) = "Expense Report" Then
DoCmd.OpenForm "frmExpenseReport", , , , , acNormal
ElseIf Me.ReportType = "Purchase Order Report" Then
DoCmd.OpenForm "frmPurchaseOrder", , , , , acNormal
ElseIf Me.ReportType = "Check Request Report" Then
DoCmd.OpenForm "frmCheckRequest", , , , , acNormal
ElseIf Me.ReportType = "Travel Expense Report" Then
DoCmd.OpenForm "frmTravelExpenseReport", , , , , acNormal
ElseIf Me.ReportType = "Reimbursement Check Report" Then
DoCmd.OpenForm "frmReimbursementCheck", , , , , acNormal
ElseIf Me.ReportType = "Trip Report" Then
DoCmd.OpenForm "frmTripReport", , , , , acNormal
End If

End Sub

 

[John Griffiths]

Hi

John, I wasn't sure what you mean by "Not shown on server" so I just

posted

* I was just trying to inform you that I couldn't see my
post as part of the thead in case anyone else wanted a re-post.

* If you change your table by adding a column for form name.

Table tblReportType
Column
+ ID
+ AutoNumber
+ Me!cmbReportType.Column(0)
Column
+ FormName
+ Text
+ Me!cmbReportType.Column(1) "frmExpenseReport", "frmPurchaseOrder"
Column
+ Description
+ Text
+ Me!cmbReportType.Column(2) "Expense Report" , "Purchase Order Report"

* You can lose the whole of the If...ElseIf structure and call it directly
by name.
Appliction.DoCmd.OpenForm Me!cmbReportType.Column(1), , , , , acNormal

* A few extra columns would allow greater control of lauching
forms depending on who the user is, time of day, anything you want.

'Column FormView LongInteger
'Column FilterName
'Column WhereCondition
'Column DataMode LongInteger
'Column WindowMode LongInteger
'Column OpenArgs Text

OpenForm(FormName, [View As AcFormView = acNormal], [FilterName],
[WhereCondition], [DataMode As AcFormOpenDataMode = acFormPropertySettings],
[WindowMode As AcWindowMode = acWindowNormal], [OpenArgs])

my response to you again. Sorry that I am not understanding what you mean.
Thanks.

<snip/>