requiring data in a field based on the data in another field

[Guest]

Can someone please help.

I am creating a database and am having some difficulty with one aspect of
it. Below you will find the code that I am using to ensure that a user
inputs the correct data into the "Staging" field and the RNCNumber field.
The contingency is that if the "Staging" field contains "Production" then the
user is required to enter data in the RNCNumber field.T What am I doing
wrong? If I try
to go to another record or a new record and the conditions are not met,
shouldn't the Msgbox data pop up. It doesn't. And the area around the
message box is red which means that I have an error somewhere. I have tried
everything, and I am sure it is something minor that I am missing. Please
Help!!!!! .



Private Sub Form_BeforeUpdate(Cancel As Integer)

If (Me.[Staging] = "Production" And IsNull(Me.[RFCNumber])) Then
MsgBox("'RFC #' Cannot Be Blank if Targeted Environment = Production",
[vbCritical] [vbOKOnly], ["Data Missing"]
Cancel = True
Me.[RFCNumber].SetFocus
End If


End Sub

 

[Guest]

Rob,

Use brackets [] to delimit form controls, quotes to delimit literal strings,
and parentheses around parameters passed to a function. Variables and
constants require no delimiters.

When using more than one vb constant as a parameter to a function or
statement, add them. Since you do not need to return a value representing
the user's button selection, you need to use only the MsgBox statement. Try:

MsgBox "'RFC #' Cannot Be Blank if Targeted Environment = Production",
vbCritical + vbOKOnly, "Data Missing"

If this doesn't work, it may be that the value of the Targeted Environment
control is a numeric code rather than the text "Production". Test for this
by inserting a MsgBox statement at the top of the code:

MsgBox "Targeted Environment = " & Me![YourTargetedEnvironmentControlName]

Hope that helps.
Sprinks

 

[Guest]

Sprinks:

Thanks. I don't quite understand what happened but I put an ! mark after
the Me and used you example for the message box and put it in front of the
code and it popped up right off. So I moved that same message to the area
that I wanted it in and modified it and it works. I am going to be taking
some VB courses this fall so I can understand the code better. I can create
some pretty sophisticated databases through the graphic interface but I don't
know the code.

Thanks for your help and the inspiration.

Rob,

Use brackets [] to delimit form controls, quotes to delimit literal strings,
and parentheses around parameters passed to a function. Variables and
constants require no delimiters.

When using more than one vb constant as a parameter to a function or
statement, add them. Since you do not need to return a value representing
the user's button selection, you need to use only the MsgBox statement. Try:

MsgBox "'RFC #' Cannot Be Blank if Targeted Environment = Production",
vbCritical + vbOKOnly, "Data Missing"

If this doesn't work, it may be that the value of the Targeted Environment
control is a numeric code rather than the text "Production". Test for this
by inserting a MsgBox statement at the top of the code:

MsgBox "Targeted Environment = " & Me![YourTargetedEnvironmentControlName]

Hope that helps.
Sprinks

Can someone please help.

I am creating a database and am having some difficulty with one aspect of
it. Below you will find the code that I am using to ensure that a user
inputs the correct data into the "Staging" field and the RNCNumber field.
The contingency is that if the "Staging" field contains "Production" then the
user is required to enter data in the RNCNumber field.T What am I doing
wrong? If I try
to go to another record or a new record and the conditions are not met,
shouldn't the Msgbox data pop up. It doesn't. And the area around the
message box is red which means that I have an error somewhere. I have tried
everything, and I am sure it is something minor that I am missing. Please
Help!!!!! .



Private Sub Form_BeforeUpdate(Cancel As Integer)

If (Me.[Staging] = "Production" And IsNull(Me.[RFCNumber])) Then
MsgBox("'RFC #' Cannot Be Blank if Targeted Environment = Production",
[vbCritical] [vbOKOnly], ["Data Missing"]
Cancel = True
Me.[RFCNumber].SetFocus
End If


End Sub

 

[Guest]

Rob,

My pleasure. btw, the ! (Bang) operator is used to delimit an object (in
this case, Me, which is a shortcut reference to the current form) from an
*element* of a collection, in this case, a form control, which is itself a
member of the form's Controls collection.

The dot operator (.) is used to separate an object from property, method or
collection of the object.

Me![MyComboBox]
Forms![MyForm]![MyComboBox]
Me.Controls (refers to the Controls collection of the current form)
Me.RecordSource
Forms![MyForm]![MyComboBox].Visible

As to understanding the code, see VBA Help on the MsgBox statement.

Sprinks

Sprinks:

Thanks. I don't quite understand what happened but I put an ! mark after
the Me and used you example for the message box and put it in front of the
code and it popped up right off. So I moved that same message to the area
that I wanted it in and modified it and it works. I am going to be taking
some VB courses this fall so I can understand the code better. I can create
some pretty sophisticated databases through the graphic interface but I don't
know the code.

Thanks for your help and the inspiration.

Rob,

Use brackets [] to delimit form controls, quotes to delimit literal strings,
and parentheses around parameters passed to a function. Variables and
constants require no delimiters.

When using more than one vb constant as a parameter to a function or
statement, add them. Since you do not need to return a value representing
the user's button selection, you need to use only the MsgBox statement. Try:

MsgBox "'RFC #' Cannot Be Blank if Targeted Environment = Production",
vbCritical + vbOKOnly, "Data Missing"

If this doesn't work, it may be that the value of the Targeted Environment
control is a numeric code rather than the text "Production". Test for this
by inserting a MsgBox statement at the top of the code:

MsgBox "Targeted Environment = " & Me![YourTargetedEnvironmentControlName]

Hope that helps.
Sprinks

Can someone please help.

I am creating a database and am having some difficulty with one aspect of
it. Below you will find the code that I am using to ensure that a user
inputs the correct data into the "Staging" field and the RNCNumber field.
The contingency is that if the "Staging" field contains "Production" then the
user is required to enter data in the RNCNumber field.T What am I doing
wrong? If I try
to go to another record or a new record and the conditions are not met,
shouldn't the Msgbox data pop up. It doesn't. And the area around the
message box is red which means that I have an error somewhere. I have tried
everything, and I am sure it is something minor that I am missing. Please
Help!!!!! .



Private Sub Form_BeforeUpdate(Cancel As Integer)

If (Me.[Staging] = "Production" And IsNull(Me.[RFCNumber])) Then
MsgBox("'RFC #' Cannot Be Blank if Targeted Environment = Production",
[vbCritical] [vbOKOnly], ["Data Missing"]
Cancel = True
Me.[RFCNumber].SetFocus
End If


End Sub