If Then Else Statement

J

Jeff

Q1: Below is the code for a pop-up where the user inputs
an ID to open an update form to a specific record in the
underlying table. How do I create an error pop-up for
invalid ID input where the ID is not listed on the table?
I tried an "If Then Else" statement as detailed below, but
it didn't work.

Original Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

stLinkCriteria = "[CUST ID]=" & Me![Text3]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindCustID"

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub

If Then Else Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

If stLinkCriteria = "[STUDY ID]=" & Me![Text3] Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindStudyID"
ElseIf stLinkCriteria = "[STUDY ID]<>" & Me![Text3]
Then
DoCmd.Restore
DoCmd.Close acForm, "FindStudyID"
End If
End If

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub
Q2: I was able to set the input mask to !00000000.0;0;" "
to insure the correct input type, although I am not crazy
about the error message for invalid value input as follows:

"The value you entered isn't appropriate for the input
mask !00000000.0;0;" " specified for this field."

Can I change this to a custom message reading
simply "Invalid Format"?

Thank you in advance for your assistance!!!
Jeff
 
J

JP

Well, first thing is you gotta go read up on Visual Basic because the way
you tried to do this shows that you don't really understand what's going on
in the code that the wizard tries to generate.

stLinkCriteria is simply a string that holds the criteria that will be used
to filter the form when the form is open. There is no sense in testing
what's in the string (which is what you did with your If-Then-Else because
it's just going to contain the following string of characters. It does not,
in any way, tell you whether or not there is a record out there that meets
that criteria. Think of it this way: if you were going to look for all the
objects that are blue, then stLinkCriteria would contain the word "blue".
It wouldn't tell you if there are any blue objects in your population, it
just tells you that you intend to search for blue objects.

What you should probably do is set up a DCOUNT using your criteria to see if
there are any records that meet your criteria. If so, then open the
CustsDetailsTableInputFormUPDATE using the criteria as your filter. If not,
then do whatever else it is that you wanted to do.

As for your second question, use the ON ERROR event to trap that particular
error and give your own error message.

There are a number of good Access programming books out there from
publishers like Sybex, Wrox, SAMS, Que, Microsoft, etc. They'll have titles
that says something like Access Programming or Access Development (make sure
it's a book on programming/development and not a general book on how to
create databases, build queries, etc). Get one and read it.

Jeff said:
Q1: Below is the code for a pop-up where the user inputs
an ID to open an update form to a specific record in the
underlying table. How do I create an error pop-up for
invalid ID input where the ID is not listed on the table?
I tried an "If Then Else" statement as detailed below, but
it didn't work.

Original Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

stLinkCriteria = "[CUST ID]=" & Me![Text3]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindCustID"

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub

If Then Else Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

If stLinkCriteria = "[STUDY ID]=" & Me![Text3] Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindStudyID"
ElseIf stLinkCriteria = "[STUDY ID]<>" & Me![Text3]
Then
DoCmd.Restore
DoCmd.Close acForm, "FindStudyID"
End If
End If

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub
Q2: I was able to set the input mask to !00000000.0;0;" "
to insure the correct input type, although I am not crazy
about the error message for invalid value input as follows:

"The value you entered isn't appropriate for the input
mask !00000000.0;0;" " specified for this field."

Can I change this to a custom message reading
simply "Invalid Format"?

Thank you in advance for your assistance!!!
Jeff
Click to expand...
 
J

Jeff

JP,
Thank you for your candor. I certainly do not profess to
understand the code, and I have been looking for a good
programing book. I was referred to the publisher Que by a
friend, so it looks like that's going to be my choice. I
have been doing my best to get this db running and as user
friendly as possible. I've gotten a lot of assistance
from this site and have learned much.

I understand, now, what the stLinkCriteria code is doing.
I read the MS VB Help pertaining to the DCount Function,
but I don't know how to go about setting up the DCOUNT.

Jeff

-----Original Message-----
Well, first thing is you gotta go read up on Visual Basic because the way
you tried to do this shows that you don't really understand what's going on
in the code that the wizard tries to generate.

stLinkCriteria is simply a string that holds the criteria that will be used
to filter the form when the form is open. There is no sense in testing
what's in the string (which is what you did with your If- Then-Else because
it's just going to contain the following string of characters. It does not,
in any way, tell you whether or not there is a record out there that meets
that criteria. Think of it this way: if you were going to look for all the
objects that are blue, then stLinkCriteria would contain the word "blue".
It wouldn't tell you if there are any blue objects in your population, it
just tells you that you intend to search for blue objects.

What you should probably do is set up a DCOUNT using your criteria to see if
there are any records that meet your criteria. If so, then open the
CustsDetailsTableInputFormUPDATE using the criteria as your filter. If not,
then do whatever else it is that you wanted to do.

As for your second question, use the ON ERROR event to trap that particular
error and give your own error message.

There are a number of good Access programming books out there from
publishers like Sybex, Wrox, SAMS, Que, Microsoft, etc. They'll have titles
that says something like Access Programming or Access Development (make sure
it's a book on programming/development and not a general book on how to
create databases, build queries, etc). Get one and read it.

Q1: Below is the code for a pop-up where the user inputs
an ID to open an update form to a specific record in the
underlying table. How do I create an error pop-up for
invalid ID input where the ID is not listed on the table?
I tried an "If Then Else" statement as detailed below, but
it didn't work.

Original Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

stLinkCriteria = "[CUST ID]=" & Me![Text3]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindCustID"

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub

If Then Else Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

If stLinkCriteria = "[STUDY ID]=" & Me![Text3] Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindStudyID"
ElseIf stLinkCriteria = "[STUDY ID]<>" & Me![Text3]
Then
DoCmd.Restore
DoCmd.Close acForm, "FindStudyID"
End If
End If

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub
Q2: I was able to set the input mask to ! 00000000.0;0;" "
to insure the correct input type, although I am not crazy
about the error message for invalid value input as follows:

"The value you entered isn't appropriate for the input
mask !00000000.0;0;" " specified for this field."

Can I change this to a custom message reading
simply "Invalid Format"?

Thank you in advance for your assistance!!!
Jeff
Click to expand...


.
Click to expand...
 
J

JP

DCount counts the number of occurrences of a field or value in a table.

count = DCOUNT(field/expression, table/recordset, criteria)

Assume that your table is "Customers" and you appear to have a field in the
table called [Cust ID]. In that case, you could write something like this:

count = DCOUNT("[Cust ID]", "Customers", "[CUST ID]=" & Me![Text3])

That says, count the number of Cust IDs you find in the table Customers
where [Cust ID] = <whatever value is in the field Text3 of your form>

Type the quotes as they appear above.


Jeff said:
JP,
Thank you for your candor. I certainly do not profess to
understand the code, and I have been looking for a good
programing book. I was referred to the publisher Que by a
friend, so it looks like that's going to be my choice. I
have been doing my best to get this db running and as user
friendly as possible. I've gotten a lot of assistance
from this site and have learned much.

I understand, now, what the stLinkCriteria code is doing.
I read the MS VB Help pertaining to the DCount Function,
but I don't know how to go about setting up the DCOUNT.

Jeff

-----Original Message-----
Well, first thing is you gotta go read up on Visual Basic because the way
you tried to do this shows that you don't really understand what's going on
in the code that the wizard tries to generate.

stLinkCriteria is simply a string that holds the criteria that will be used
to filter the form when the form is open. There is no sense in testing
what's in the string (which is what you did with your If- Then-Else because
it's just going to contain the following string of characters. It does not,
in any way, tell you whether or not there is a record out there that meets
that criteria. Think of it this way: if you were going to look for all the
objects that are blue, then stLinkCriteria would contain the word "blue".
It wouldn't tell you if there are any blue objects in your population, it
just tells you that you intend to search for blue objects.

What you should probably do is set up a DCOUNT using your criteria to see if
there are any records that meet your criteria. If so, then open the
CustsDetailsTableInputFormUPDATE using the criteria as your filter. If not,
then do whatever else it is that you wanted to do.

As for your second question, use the ON ERROR event to trap that particular
error and give your own error message.

There are a number of good Access programming books out there from
publishers like Sybex, Wrox, SAMS, Que, Microsoft, etc. They'll have titles
that says something like Access Programming or Access Development (make sure
it's a book on programming/development and not a general book on how to
create databases, build queries, etc). Get one and read it.

Q1: Below is the code for a pop-up where the user inputs
an ID to open an update form to a specific record in the
underlying table. How do I create an error pop-up for
invalid ID input where the ID is not listed on the table?
I tried an "If Then Else" statement as detailed below, but
it didn't work.

Original Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

stLinkCriteria = "[CUST ID]=" & Me![Text3]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindCustID"

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub

If Then Else Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

If stLinkCriteria = "[STUDY ID]=" & Me![Text3] Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindStudyID"
ElseIf stLinkCriteria = "[STUDY ID]<>" & Me![Text3]
Then
DoCmd.Restore
DoCmd.Close acForm, "FindStudyID"
End If
End If

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub
Q2: I was able to set the input mask to ! 00000000.0;0;" "
to insure the correct input type, although I am not crazy
about the error message for invalid value input as follows:

"The value you entered isn't appropriate for the input
mask !00000000.0;0;" " specified for this field."

Can I change this to a custom message reading
simply "Invalid Format"?

Thank you in advance for your assistance!!!
Jeff
Click to expand...


.
Click to expand...
Click to expand...
 
J

Jeff

JP,
I understood what DCount did, but it didn't dawn on me how
to use it in my code until your reply. Duh!!! It works
like a charm, at this point, and I thank you for all of
your help.

Jeff
-----Original Message-----
DCount counts the number of occurrences of a field or value in a table.

count = DCOUNT(field/expression, table/recordset, criteria)

Assume that your table is "Customers" and you appear to have a field in the
table called [Cust ID]. In that case, you could write something like this:

count = DCOUNT("[Cust ID]", "Customers", "[CUST ID]=" & Me![Text3])

That says, count the number of Cust IDs you find in the table Customers
where [Cust ID] = <whatever value is in the field Text3 of your form>

Type the quotes as they appear above.


JP,
Thank you for your candor. I certainly do not profess to
understand the code, and I have been looking for a good
programing book. I was referred to the publisher Que by a
friend, so it looks like that's going to be my choice. I
have been doing my best to get this db running and as user
friendly as possible. I've gotten a lot of assistance
from this site and have learned much.

I understand, now, what the stLinkCriteria code is doing.
I read the MS VB Help pertaining to the DCount Function,
but I don't know how to go about setting up the DCOUNT.

Jeff

-----Original Message-----
Well, first thing is you gotta go read up on Visual
Click to expand...
Basic
because the way
you tried to do this shows that you don't really understand what's going on
in the code that the wizard tries to generate.

stLinkCriteria is simply a string that holds the
Click to expand...
criteria
that will be used
to filter the form when the form is open. There is no sense in testing
what's in the string (which is what you did with your
Click to expand...
If-
Then-Else because
it's just going to contain the following string of characters. It does not,
in any way, tell you whether or not there is a record
Click to expand...
out
there that meets
that criteria. Think of it this way: if you were
Click to expand...
going
to look for all the
objects that are blue, then stLinkCriteria would
Click to expand...
contain
the word "blue".
It wouldn't tell you if there are any blue objects in your population, it
just tells you that you intend to search for blue objects.

What you should probably do is set up a DCOUNT using
Click to expand...
your
criteria to see if
there are any records that meet your criteria. If so, then open the
CustsDetailsTableInputFormUPDATE using the criteria as your filter. If not,
then do whatever else it is that you wanted to do.

As for your second question, use the ON ERROR event to trap that particular
error and give your own error message.

There are a number of good Access programming books out there from
publishers like Sybex, Wrox, SAMS, Que, Microsoft, etc. They'll have titles
that says something like Access Programming or Access Development (make sure
it's a book on programming/development and not a
Click to expand...
general
book on how to
create databases, build queries, etc). Get one and
Click to expand...
read
it.
Q1: Below is the code for a pop-up where the user inputs
an ID to open an update form to a specific record in the
underlying table. How do I create an error pop-up for
invalid ID input where the ID is not listed on the table?
I tried an "If Then Else" statement as detailed
Click to expand...
below,
but
it didn't work.

Original Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

stLinkCriteria = "[CUST ID]=" & Me![Text3]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindCustID"

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub

If Then Else Code:
Private Sub FindCustID2_Click()
On Error GoTo Err_FindCustID2_Click

Dim stDocName1 As String
Dim stLinkCriteria As String

stDocName = "CustDetailsTableInputFormUPDATE"

If stLinkCriteria = "[STUDY ID]=" & Me![Text3] Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
DoCmd.Close acForm, "Switchboard"
DoCmd.Close acForm, "FindStudyID"
ElseIf stLinkCriteria = "[STUDY ID]<>" & Me! [Text3]
Then
DoCmd.Restore
DoCmd.Close acForm, "FindStudyID"
End If
End If

Exit_FindCustID2_Click:
Exit Sub

Err_FindCustID2_Click:
MsgBox Err.Description

End Sub
Q2: I was able to set the input mask to ! 00000000.0;0;" "
to insure the correct input type, although I am not crazy
about the error message for invalid value input as follows:

"The value you entered isn't appropriate for the input
mask !00000000.0;0;" " specified for this field."

Can I change this to a custom message reading
simply "Invalid Format"?

Thank you in advance for your assistance!!!
Jeff



.
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

Data Type Mismatch in Criteria Expression 1
Opening form using 2 criteria 9
how to close a form when using Access. 3
Search Form 2
Access 7
Open Form and get record. 4
setting query criteria from a form (I don't know what to call thi 1
Syntax error in query expression 7

Top