command button to open form selected conditonally?

G

Guest

How do I get a command button to open a particular form chosen according to a
control value?

For example, let's say I have a form frmOrders on which there is a control
cboCurrency.

I want a button cmbReceipts on frmOrders to open one of three possible forms
- frmReceiptsEUR, frmReceiptsGBP, or frmReceiptsUSD - according to whether
the value selected in cboLanguage is "EUR" or "GBP" or "USD". How do I code
the On Click event of cmbOpenDetails to do that?

The code generated by the wizard simply to open a single form frmReceipts is
as follows, and I can't figure out how to modify it:

Private Sub cmbReceipts_Click()
On Error GoTo Err_cmbReceipts_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReceipts"

stLinkCriteria = "[tblReceipts.OrderID]=" & Me![txtOrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbReceipts_Click:
Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Description
Resume Exit_cmbReceipts_Click

End Sub
 
G

Guest

Like this:

Private Sub cmbReceipts_Click()
On Error GoTo Err_cmbReceipts_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case Me.cboLanguage
Case "EUR"
stDocName = "frmReceiptsEUR"
Case "GBP"
stDocName = "frmReceiptsGBPR"
Case "USD"
stDocName = "frmReceiptsUSD"
Case Else
MsgBox "No language selected.", vbExclamation, "Invalid Operation"

Exit Sub
End Select

stLinkCriteria = "[tblReceipts.OrderID]=" & Me![txtOrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbReceipts_Click:
Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Description
Resume Exit_cmbReceipts_Click

End Sub

Ken Sheridan
Stafford, England
 
G

Guest

Like this:

Private Sub cmbReceipts_Click()
On Error GoTo Err_cmbReceipts_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case Me.cboLanguage
Case "EUR"
stDocName = "frmReceiptsEUR"
Case "GBP"
stDocName = "frmReceiptsGBPR"
Case "USD"
stDocName = "frmReceiptsUSD"
Case Else
MsgBox "No language selected.", vbExclamation, "Invalid Operation"

Exit Sub
End Select

stLinkCriteria = "[tblReceipts.OrderID]=" & Me![txtOrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbReceipts_Click:
Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Description
Resume Exit_cmbReceipts_Click

End Sub

Ken Sheridan
Stafford, England
 
G

Guest

Private Sub cmbReceipts_Click()

On Error GoTo Err_cmbReceipts_Click

strFormName = "frmReceipts" & Me.cboLanguage
DoCmd.OpenForm "frmReceipts" & Me.cboLanguage, , ,
"[tblReceipts.OrderID]=" & Me![txtOrderID]

Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Number & " - " & err.Description

End Sub

(I really dislike how MS writes VBA)
 
G

Guest

Thanks very much for this Dave, but I can't quite get it to work.

The code I have now (with the command button name corrected to cboCurrency)
is:

Private Sub cmbReceipts_Click()

On Error GoTo Err_cmbReceipts_Click
Dim strFormName As String

strFormName = "frmReceipts" & Me.cboCurrency
DoCmd.OpenForm "frmReceipts" & Me.cboCurrency, , ,
"[tblReceipts.OrderID]=" & Me![txtOrderID]

Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Number & " - " & Err.Description

End Sub



First time round strFormName came up as an undefined variable, which is why
I tried inserting Dim strFormName As String.

But then I got "Syntax error" message for
DoCmd.OpenForm "frmReceipts" & Me.cboCurrency, , ,

That line and the next line both show up red in the VBA window - does that
mean there's a syntax error for both of them?

Sorry to come back at you with all this, but your solution seems so elegant
and neat that I'd really like to make it work, and, as you can see, my VBA
skills are pretty rudimentary.

Thanks
David

Klatuu said:
Private Sub cmbReceipts_Click()

On Error GoTo Err_cmbReceipts_Click

strFormName = "frmReceipts" & Me.cboLanguage
DoCmd.OpenForm "frmReceipts" & Me.cboLanguage, , ,
"[tblReceipts.OrderID]=" & Me![txtOrderID]

Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Number & " - " & err.Description

End Sub

(I really dislike how MS writes VBA)
--
Dave Hargis, Microsoft Access MVP


David Newmarch said:
How do I get a command button to open a particular form chosen according to a
control value?

For example, let's say I have a form frmOrders on which there is a control
cboCurrency.

I want a button cmbReceipts on frmOrders to open one of three possible forms
- frmReceiptsEUR, frmReceiptsGBP, or frmReceiptsUSD - according to whether
the value selected in cboLanguage is "EUR" or "GBP" or "USD". How do I code
the On Click event of cmbOpenDetails to do that?

The code generated by the wizard simply to open a single form frmReceipts is
as follows, and I can't figure out how to modify it:

Private Sub cmbReceipts_Click()
On Error GoTo Err_cmbReceipts_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReceipts"

stLinkCriteria = "[tblReceipts.OrderID]=" & Me![txtOrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbReceipts_Click:
Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Description
Resume Exit_cmbReceipts_Click

End Sub
Click to expand...
Click to expand...
 
G

Guest

The two lines are actually one line. That is why you are getting a syntax
error.
The only other issue is to ensure cboCurrency actually has a value. Also,
you don't really need the strFormName. If I left it there, I apologize.
IMHO, there is no need to take the extra time to load a variable then
immediately use it.

So, I have added the check for cboCurrency having a value and removed the
strFormName variable:

Private Sub cmbReceipts_Click()

On Error GoTo Err_cmbReceipts_Click

If IsNull(Me.cboCurrency) Then
MsgBox "No Currency Selected", vbExclamation
Else
DoCmd.OpenForm "frmReceipts" & Me.cboCurrency, , ,
"[tblReceipts.OrderID]=" & Me![txtOrderID]
End If

Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Number & " - " & Err.Description

End Sub

--
Dave Hargis, Microsoft Access MVP


David Newmarch said:
Thanks very much for this Dave, but I can't quite get it to work.

The code I have now (with the command button name corrected to cboCurrency)
is:

Private Sub cmbReceipts_Click()

On Error GoTo Err_cmbReceipts_Click
Dim strFormName As String

strFormName = "frmReceipts" & Me.cboCurrency
DoCmd.OpenForm "frmReceipts" & Me.cboCurrency, , ,
"[tblReceipts.OrderID]=" & Me![txtOrderID]

Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Number & " - " & Err.Description

End Sub



First time round strFormName came up as an undefined variable, which is why
I tried inserting Dim strFormName As String.

But then I got "Syntax error" message for
DoCmd.OpenForm "frmReceipts" & Me.cboCurrency, , ,

That line and the next line both show up red in the VBA window - does that
mean there's a syntax error for both of them?

Sorry to come back at you with all this, but your solution seems so elegant
and neat that I'd really like to make it work, and, as you can see, my VBA
skills are pretty rudimentary.

Thanks
David

Klatuu said:
Private Sub cmbReceipts_Click()

On Error GoTo Err_cmbReceipts_Click

strFormName = "frmReceipts" & Me.cboLanguage
DoCmd.OpenForm "frmReceipts" & Me.cboLanguage, , ,
"[tblReceipts.OrderID]=" & Me![txtOrderID]

Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Number & " - " & err.Description

End Sub

(I really dislike how MS writes VBA)
--
Dave Hargis, Microsoft Access MVP


David Newmarch said:
How do I get a command button to open a particular form chosen according to a
control value?

For example, let's say I have a form frmOrders on which there is a control
cboCurrency.

I want a button cmbReceipts on frmOrders to open one of three possible forms
- frmReceiptsEUR, frmReceiptsGBP, or frmReceiptsUSD - according to whether
the value selected in cboLanguage is "EUR" or "GBP" or "USD". How do I code
the On Click event of cmbOpenDetails to do that?

The code generated by the wizard simply to open a single form frmReceipts is
as follows, and I can't figure out how to modify it:

Private Sub cmbReceipts_Click()
On Error GoTo Err_cmbReceipts_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReceipts"

stLinkCriteria = "[tblReceipts.OrderID]=" & Me![txtOrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbReceipts_Click:
Exit Sub

Err_cmbReceipts_Click:
MsgBox Err.Description
Resume Exit_cmbReceipts_Click

End Sub
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

filter a control when form opened by command button? 2
Access Open Form with WhereCondition 6
Command button on one form opens a new form to same subject 2
Main Menu Command Button 1
Form Button to Open another Form 7
Button done with wizard seems to default to form view when I need datasheet view ... ? 4
Command Button not working on form - HELP!!! 3
Open form with Command button Error 3

Top