Opening a Form with If then statement

  • Thread starter Thread starter jeff gilstrap
  • Start date Start date
J

jeff gilstrap

I am trying to use an "If " statement to open up either a
customer form or supplier form when I click on a button
from a summary form. The summary form is populated using
a union query from a supplier table and customer table.
If the "customer number" (which the union query is using
for either a customer number or supplier number) it is a
number value I would like to open a customer form. If it
is text value I would like to open a supplier form. How
can I use the if statement to specify the format of
the "customer number" to determine which form should be
opened? The code I need help on to open the form is as
follows. Thanks, Jeff G.

Private Sub MoreInfo_Click()
On Error GoTo Err_MoreInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String
******this is what I could use help on *************
If [Customer Number] is a number format Then

stDocName = "Customers"

stLinkCriteria = "[Customer Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else [Customer Number] is a text format

stDocName = "Suppliers"

stLinkCriteria = "[Supplier Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_MoreInfo_Click:
Exit Sub

Err_MoreInfo_Click:
MsgBox Err.Description
Resume Exit_MoreInfo_Click

End Sub
 
Here is a "sneaky" way to do this without using an If..Then setup. This code
tests the string in Customer Number to see if it's "numeric" (does not
contain nonnumeric characters in places where a number should not have
nonnumeric characters). If IsNumeric returns True (which is the value -1 in
ACCESS), the resulting value for intType is 2; if False (value of 0),
intType is 1. The Choose function chooses the appropriate value to use based
on if the value of intType is 1 or 2 (if 1, Choose returns the first value
[second argument], if 2, the second value [third argument]).


Private Sub MoreInfo_Click()
On Error GoTo Err_MoreInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intType As Integer

intType = Abs(IsNumeric(Me.[Customer Number])) + 1
strDocName = Choose(intType, "Suppliers", "Customers")
stLinkCriteria = Choose(intType,"[Supplier Number]", _
[Customer Number]") & "='" & _
Me.[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_MoreInfo_Click:
Exit Sub

Err_MoreInfo_Click:
MsgBox Err.Description
Resume Exit_MoreInfo_Click

End Sub

--
But, if you want to use the If...Then structure, then this would be the
subroutine's code:

Private Sub MoreInfo_Click()
On Error GoTo Err_MoreInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String
******this is what I could use help on *************
If IsNumeric(Me.[Customer Number]) = True Then

stDocName = "Customers"

stLinkCriteria = "[Customer Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

stDocName = "Suppliers"

stLinkCriteria = "[Supplier Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_MoreInfo_Click:
Exit Sub

Err_MoreInfo_Click:
MsgBox Err.Description
Resume Exit_MoreInfo_Click

End Sub


--

Ken Snell
<MS ACCESS MVP>



jeff gilstrap said:
I am trying to use an "If " statement to open up either a
customer form or supplier form when I click on a button
from a summary form. The summary form is populated using
a union query from a supplier table and customer table.
If the "customer number" (which the union query is using
for either a customer number or supplier number) it is a
number value I would like to open a customer form. If it
is text value I would like to open a supplier form. How
can I use the if statement to specify the format of
the "customer number" to determine which form should be
opened? The code I need help on to open the form is as
follows. Thanks, Jeff G.

Private Sub MoreInfo_Click()
On Error GoTo Err_MoreInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String
******this is what I could use help on *************
If [Customer Number] is a number format Then

stDocName = "Customers"

stLinkCriteria = "[Customer Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else [Customer Number] is a text format

stDocName = "Suppliers"

stLinkCriteria = "[Supplier Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_MoreInfo_Click:
Exit Sub

Err_MoreInfo_Click:
MsgBox Err.Description
Resume Exit_MoreInfo_Click

End Sub
Click to expand...
 
Excellent! I will try the choose function out. Thank
you Jeff G
-----Original Message-----
Here is a "sneaky" way to do this without using an If..Then setup. This code
tests the string in Customer Number to see if it's "numeric" (does not
contain nonnumeric characters in places where a number should not have
nonnumeric characters). If IsNumeric returns True (which is the value -1 in
ACCESS), the resulting value for intType is 2; if False (value of 0),
intType is 1. The Choose function chooses the appropriate value to use based
on if the value of intType is 1 or 2 (if 1, Choose returns the first value
[second argument], if 2, the second value [third argument]).


Private Sub MoreInfo_Click()
On Error GoTo Err_MoreInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intType As Integer

intType = Abs(IsNumeric(Me.[Customer Number])) + 1
strDocName = Choose(intType, "Suppliers", "Customers")
stLinkCriteria = Choose(intType,"[Supplier Number]", _
[Customer Number]") & "='" & _
Me.[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_MoreInfo_Click:
Exit Sub

Err_MoreInfo_Click:
MsgBox Err.Description
Resume Exit_MoreInfo_Click

End Sub

--
But, if you want to use the If...Then structure, then this would be the
subroutine's code:

Private Sub MoreInfo_Click()
On Error GoTo Err_MoreInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String
******this is what I could use help on *************
If IsNumeric(Me.[Customer Number]) = True Then

stDocName = "Customers"

stLinkCriteria = "[Customer Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

stDocName = "Suppliers"

stLinkCriteria = "[Supplier Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_MoreInfo_Click:
Exit Sub

Err_MoreInfo_Click:
MsgBox Err.Description
Resume Exit_MoreInfo_Click

End Sub


--

Ken Snell
<MS ACCESS MVP>



I am trying to use an "If " statement to open up either a
customer form or supplier form when I click on a button
from a summary form. The summary form is populated using
a union query from a supplier table and customer table.
If the "customer number" (which the union query is using
for either a customer number or supplier number) it is a
number value I would like to open a customer form. If it
is text value I would like to open a supplier form. How
can I use the if statement to specify the format of
the "customer number" to determine which form should be
opened? The code I need help on to open the form is as
follows. Thanks, Jeff G.

Private Sub MoreInfo_Click()
On Error GoTo Err_MoreInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String
******this is what I could use help on *************
If [Customer Number] is a number format Then

stDocName = "Customers"

stLinkCriteria = "[Customer Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else [Customer Number] is a text format

stDocName = "Suppliers"

stLinkCriteria = "[Supplier Number]=" & "'" & Me!
[Customer Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_MoreInfo_Click:
Exit Sub

Err_MoreInfo_Click:
MsgBox Err.Description
Resume Exit_MoreInfo_Click

End Sub
Click to expand...


.
Click to expand...
 
Back
Top