You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
How do you run the test?
Aria said:I hope this isn't a dumb question and you don't mind me asking. I'm trying to
follow your instructions because I have a similar issue but... You put this
in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
How do you run the test?
If Me.[Nameof YourOptionButton] = Default Value Then
MsgBox "Your Message Here" ?
I'm sorry if this is so basic. I just want to make sure I understand this.
Tom Wickerath said:Hi Aria,
No need to apologize....the only dumb question is a question that is not
asked.
You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
No to both questions. You set the Visible property for the button and it's
associated label to No in form design view.
How do you run the test?
Try the following experiment in the Northwind.mdb sample database. I used
the copy that ships with Access 2003 to prepare these instructions:
1.) Open the Customers table in design view. Add a field named "Test"
(without the quotes). Set the Data Type to Number, and Field Size (lower
window) to Integer. Leave the default value set to zero.
2.) Create a new update query to provide an initial default value in the
Test field for the 91 Customer records. Dismiss the Add Tables dialog without
adding any tables. In query design view, click on View > SQL View. You should
see the word SELECT highlighted. Delete this default keyword. Copy the
following SQL statement (Ctrl C) and paste it into the SQL view (Ctrl V),
replacing the SELECT keyword:
UPDATE Customers SET Test = 0 WHERE Test Is Null
You can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run this update query.
3.) Open the Customers form in design view. Display your toolbox if it is
not already displayed (View | Toolbox). Make sure that the Wizards button in
the toolbox is enabled. Draw a rectangular area on the form large enough to
comfortably fit three option buttons.
Note: You don't really need a fourth hidden option button to accomplish this
goal, but adding one will help make it more obvious to you, or a future
developer that follows you, what the intended purpose was.
You should see a wizard that has you fill in the Label Names. Type in the
following:
Initial
One
Two
Three
Click Next. Accept the default choice: "Yes, the default is:" Initial
Click Next. Assign the values: 0, 1, 2, 3, respectively, to the Initial,
One, Two and Three options.
Click Next. Select the option: "Store the value in this field:", and pick
the new Test field.
Click Next. Select Option buttons and whatever style you want.
Click Next. On the last page of the wizard, for "What caption do you want
for the option group?", enter " Test Option Group" (without the quotes).
Click the Finish button.
4.) Display the Properties dialog, if it is not already displayed. You can
do this by clicking on View | Properties or, in Access 2002 and later, by
pressing the F4 button. Select the first option button and it's associated
label (you can press the Shift key to make multiple selections, or use your
left mouse button to "lasso" the two controls. You should see "Multiple
selection" in the blue title bar of the Properties dialog.
Select the Format tab. Change the Visible property from Yes to No.
Note: You can set this property for the controls one-at-a-time, instead of
using a multiple selection, if you wish. Double-clicking on the Yes value in
the Properties dialog should toggle it to No.
My recommendation is to change the font color for the label that reads
Initial to red. I do this for any controls on a form where I have
intentionally set the Visible property to No. This way, those controls stand
out to me the next time I open the form in design view.
5.) Select the Option Group frame, by clicking on the border. You should see
the words "Option Group:", along with the default name of this control, in
the blue title bar of the Properties dialog. Verify that you did not
accidently set the visible property for the frame itself to No. If so, reset
it to Yes. The default value for this control is shown on the Data tab. I see
"Frame56" as the default name; you will likely see a similar, but different,
name. We are going to give this Option Frame a more descriptive name. Select
the Other tab. Change the name to fraTest.
Note: The lowercase "fra" part is a standard naming convention for frames.
For a complete listing, see this site:
http://www.xoc.net/standards/rvbanc.asp#VB
6.) Select the form by clicking on the small box in the upper left corner,
where the two rulers meet (assuming View | Ruler is checked). You should see
the word "Form" in the blue title bar of the Properties dialog. Click on the
Event tab. Select the fourth event shown in the listing: "Before Update", by
clicking into the box. You should see a build button (a button with three
dots) to the right. Click on this Build button. If presented with dialog,
choose Code Builder. You should see the following, with your mouse cursor
blinking in this new, empty procedure:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Note: If you do not see the words "Option Explicit" as your second line of
code, then add this line manually. Additionally, configure your Visual Basic
Editor (VBE) to always add this line of code automatically, to all new code
modules. Here is a "gem tip" that discusses more:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
7.) Add an error-handler to your new procedure, because all procedures
should include error-handling:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
There is a very nice, free utility that allows you to add error-handling
with the click of a mouse button. This is known as MZTools:
http://www.mztools.com/v3/mztools3.aspx
8.) Add the following lines of code to your new procedure:
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
Click on the Save toolbar button. Then click on
Debug | Compile {ProjectName}
where {ProjectName} is the name of your VBA project. Hopefully, you will not
have any compile-time errors present. The completed procedure should look
like this:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
9.) Close the VBE. Open the form in normal mode, and give it a test drive.
For the first 91 records, you will not receive any prompt unless you add or
change some data in another control. The reason for this is that the form's
Before Update event procedure only fires when the form has been "dirtied"
(pencil symbol shown in record selector, if the record selector is visible.
Navigate to a new record. Add the key AAAAA. Type in a Company Name and a
Contact Name. Try to navigate back one record, or forward one record to a new
record. You should see a message that prevents you from doing this.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Aria said:I hope this isn't a dumb question and you don't mind me asking. I'm trying to
follow your instructions because I have a similar issue but... You put this
in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
How do you run the test?
If Me.[Nameof YourOptionButton] = Default Value Then
MsgBox "Your Message Here" ?
I'm sorry if this is so basic. I just want to make sure I understand this.
Aria said:Hi Tom,
Wow! *You* are the BEST! What an excellent explanation! Thank you so much
for taking the time to teach in such a patient manner. I have followed
your
instructions and received a compile error which says, "Expected:
identifier
or bracketed expression". It highlights the ending quote, comma and
underscore after
"...proceeding."
Here is how the code looks in my test:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before "
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
I'm sorry. I have already hijacked Chris's post (apologies to you Chris
but
I had searched for days looking for a situation that was similar to mine
and
I thought your question was perfect.) and didn't want to post back with
more
questions.
Thank you so much.
--
Aria W.
Tom Wickerath said:Hi Aria,
No need to apologize....the only dumb question is a question that is not
asked.
You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
No to both questions. You set the Visible property for the button and
it's
associated label to No in form design view.
How do you run the test?
Try the following experiment in the Northwind.mdb sample database. I used
the copy that ships with Access 2003 to prepare these instructions:
1.) Open the Customers table in design view. Add a field named "Test"
(without the quotes). Set the Data Type to Number, and Field Size (lower
window) to Integer. Leave the default value set to zero.
2.) Create a new update query to provide an initial default value in the
Test field for the 91 Customer records. Dismiss the Add Tables dialog
without
adding any tables. In query design view, click on View > SQL View. You
should
see the word SELECT highlighted. Delete this default keyword. Copy the
following SQL statement (Ctrl C) and paste it into the SQL view (Ctrl V),
replacing the SELECT keyword:
UPDATE Customers SET Test = 0 WHERE Test Is Null
You can then switch back to the more familiar design view, if you wish,
by
clicking on View > Design View. Run this update query.
3.) Open the Customers form in design view. Display your toolbox if it is
not already displayed (View | Toolbox). Make sure that the Wizards button
in
the toolbox is enabled. Draw a rectangular area on the form large enough
to
comfortably fit three option buttons.
Note: You don't really need a fourth hidden option button to accomplish
this
goal, but adding one will help make it more obvious to you, or a future
developer that follows you, what the intended purpose was.
You should see a wizard that has you fill in the Label Names. Type in the
following:
Initial
One
Two
Three
Click Next. Accept the default choice: "Yes, the default is:" Initial
Click Next. Assign the values: 0, 1, 2, 3, respectively, to the Initial,
One, Two and Three options.
Click Next. Select the option: "Store the value in this field:", and pick
the new Test field.
Click Next. Select Option buttons and whatever style you want.
Click Next. On the last page of the wizard, for "What caption do you want
for the option group?", enter " Test Option Group" (without the quotes).
Click the Finish button.
4.) Display the Properties dialog, if it is not already displayed. You
can
do this by clicking on View | Properties or, in Access 2002 and later, by
pressing the F4 button. Select the first option button and it's
associated
label (you can press the Shift key to make multiple selections, or use
your
left mouse button to "lasso" the two controls. You should see "Multiple
selection" in the blue title bar of the Properties dialog.
Select the Format tab. Change the Visible property from Yes to No.
Note: You can set this property for the controls one-at-a-time, instead
of
using a multiple selection, if you wish. Double-clicking on the Yes value
in
the Properties dialog should toggle it to No.
My recommendation is to change the font color for the label that reads
Initial to red. I do this for any controls on a form where I have
intentionally set the Visible property to No. This way, those controls
stand
out to me the next time I open the form in design view.
5.) Select the Option Group frame, by clicking on the border. You should
see
the words "Option Group:", along with the default name of this control,
in
the blue title bar of the Properties dialog. Verify that you did not
accidently set the visible property for the frame itself to No. If so,
reset
it to Yes. The default value for this control is shown on the Data tab. I
see
"Frame56" as the default name; you will likely see a similar, but
different,
name. We are going to give this Option Frame a more descriptive name.
Select
the Other tab. Change the name to fraTest.
Note: The lowercase "fra" part is a standard naming convention for
frames.
For a complete listing, see this site:
http://www.xoc.net/standards/rvbanc.asp#VB
6.) Select the form by clicking on the small box in the upper left
corner,
where the two rulers meet (assuming View | Ruler is checked). You should
see
the word "Form" in the blue title bar of the Properties dialog. Click on
the
Event tab. Select the fourth event shown in the listing: "Before
Update", by
clicking into the box. You should see a build button (a button with three
dots) to the right. Click on this Build button. If presented with dialog,
choose Code Builder. You should see the following, with your mouse cursor
blinking in this new, empty procedure:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Note: If you do not see the words "Option Explicit" as your second line
of
code, then add this line manually. Additionally, configure your Visual
Basic
Editor (VBE) to always add this line of code automatically, to all new
code
modules. Here is a "gem tip" that discusses more:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
7.) Add an error-handler to your new procedure, because all procedures
should include error-handling:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
There is a very nice, free utility that allows you to add error-handling
with the click of a mouse button. This is known as MZTools:
http://www.mztools.com/v3/mztools3.aspx
8.) Add the following lines of code to your new procedure:
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
Click on the Save toolbar button. Then click on
Debug | Compile {ProjectName}
where {ProjectName} is the name of your VBA project. Hopefully, you will
not
have any compile-time errors present. The completed procedure should look
like this:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
9.) Close the VBE. Open the form in normal mode, and give it a test
drive.
For the first 91 records, you will not receive any prompt unless you add
or
change some data in another control. The reason for this is that the
form's
Before Update event procedure only fires when the form has been "dirtied"
(pencil symbol shown in record selector, if the record selector is
visible.
Navigate to a new record. Add the key AAAAA. Type in a Company Name and a
Contact Name. Try to navigate back one record, or forward one record to a
new
record. You should see a message that prevents you from doing this.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Aria said:I hope this isn't a dumb question and you don't mind me asking. I'm
trying to
follow your instructions because I have a similar issue but... You put
this
in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
How do you run the test?
If Me.[Nameof YourOptionButton] = Default Value Then
MsgBox "Your Message Here" ?
I'm sorry if this is so basic. I just want to make sure I understand
this.
Pete D. said:Is it actully on two lines like you show. This was probally caused
accidently by the newreader wrapping the text. The _ at the end of the
line tells the compiler continue this line on next line.
Incase it wraps again this is a shortend version
proceeding", _
vbInformation, "Selection Required..."
MsgBox "You must select choices one, two or three before proceeding.", _
vbInformation , "Selection Required..."
Aria said:Hi Tom,
Wow! *You* are the BEST! What an excellent explanation! Thank you so much
for taking the time to teach in such a patient manner. I have followed
your
instructions and received a compile error which says, "Expected:
identifier
or bracketed expression". It highlights the ending quote, comma and
underscore after
"...proceeding."
Here is how the code looks in my test:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before "
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
I'm sorry. I have already hijacked Chris's post (apologies to you Chris
but
I had searched for days looking for a situation that was similar to mine
and
I thought your question was perfect.) and didn't want to post back with
more
questions.
Thank you so much.
--
Aria W.
Tom Wickerath said:Hi Aria,
No need to apologize....the only dumb question is a question that is not
asked.
You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
No to both questions. You set the Visible property for the button and
it's
associated label to No in form design view.
How do you run the test?
Try the following experiment in the Northwind.mdb sample database. I used
the copy that ships with Access 2003 to prepare these instructions:
1.) Open the Customers table in design view. Add a field named "Test"
(without the quotes). Set the Data Type to Number, and Field Size (lower
window) to Integer. Leave the default value set to zero.
2.) Create a new update query to provide an initial default value in the
Test field for the 91 Customer records. Dismiss the Add Tables dialog
without
adding any tables. In query design view, click on View > SQL View. You
should
see the word SELECT highlighted. Delete this default keyword. Copy the
following SQL statement (Ctrl C) and paste it into the SQL view (Ctrl V),
replacing the SELECT keyword:
UPDATE Customers SET Test = 0 WHERE Test Is Null
You can then switch back to the more familiar design view, if you wish,
by
clicking on View > Design View. Run this update query.
3.) Open the Customers form in design view. Display your toolbox if it is
not already displayed (View | Toolbox). Make sure that the Wizards button
in
the toolbox is enabled. Draw a rectangular area on the form large enough
to
comfortably fit three option buttons.
Note: You don't really need a fourth hidden option button to accomplish
this
goal, but adding one will help make it more obvious to you, or a future
developer that follows you, what the intended purpose was.
You should see a wizard that has you fill in the Label Names. Type in the
following:
Initial
One
Two
Three
Click Next. Accept the default choice: "Yes, the default is:" Initial
Click Next. Assign the values: 0, 1, 2, 3, respectively, to the Initial,
One, Two and Three options.
Click Next. Select the option: "Store the value in this field:", and pick
the new Test field.
Click Next. Select Option buttons and whatever style you want.
Click Next. On the last page of the wizard, for "What caption do you want
for the option group?", enter " Test Option Group" (without the quotes).
Click the Finish button.
4.) Display the Properties dialog, if it is not already displayed. You
can
do this by clicking on View | Properties or, in Access 2002 and later, by
pressing the F4 button. Select the first option button and it's
associated
label (you can press the Shift key to make multiple selections, or use
your
left mouse button to "lasso" the two controls. You should see "Multiple
selection" in the blue title bar of the Properties dialog.
Select the Format tab. Change the Visible property from Yes to No.
Note: You can set this property for the controls one-at-a-time, instead
of
using a multiple selection, if you wish. Double-clicking on the Yes value
in
the Properties dialog should toggle it to No.
My recommendation is to change the font color for the label that reads
Initial to red. I do this for any controls on a form where I have
intentionally set the Visible property to No. This way, those controls
stand
out to me the next time I open the form in design view.
5.) Select the Option Group frame, by clicking on the border. You should
see
the words "Option Group:", along with the default name of this control,
in
the blue title bar of the Properties dialog. Verify that you did not
accidently set the visible property for the frame itself to No. If so,
reset
it to Yes. The default value for this control is shown on the Data tab. I
see
"Frame56" as the default name; you will likely see a similar, but
different,
name. We are going to give this Option Frame a more descriptive name.
Select
the Other tab. Change the name to fraTest.
Note: The lowercase "fra" part is a standard naming convention for
frames.
For a complete listing, see this site:
http://www.xoc.net/standards/rvbanc.asp#VB
6.) Select the form by clicking on the small box in the upper left
corner,
where the two rulers meet (assuming View | Ruler is checked). You should
see
the word "Form" in the blue title bar of the Properties dialog. Click on
the
Event tab. Select the fourth event shown in the listing: "Before
Update", by
clicking into the box. You should see a build button (a button with three
dots) to the right. Click on this Build button. If presented with dialog,
choose Code Builder. You should see the following, with your mouse cursor
blinking in this new, empty procedure:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Note: If you do not see the words "Option Explicit" as your second line
of
code, then add this line manually. Additionally, configure your Visual
Basic
Editor (VBE) to always add this line of code automatically, to all new
code
modules. Here is a "gem tip" that discusses more:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
7.) Add an error-handler to your new procedure, because all procedures
should include error-handling:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
There is a very nice, free utility that allows you to add error-handling
with the click of a mouse button. This is known as MZTools:
http://www.mztools.com/v3/mztools3.aspx
8.) Add the following lines of code to your new procedure:
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
Click on the Save toolbar button. Then click on
Debug | Compile {ProjectName}
where {ProjectName} is the name of your VBA project. Hopefully, you will
not
have any compile-time errors present. The completed procedure should look
like this:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
9.) Close the VBE. Open the form in normal mode, and give it a test
drive.
For the first 91 records, you will not receive any prompt unless you add
or
change some data in another control. The reason for this is that the
form's
Before Update event procedure only fires when the form has been "dirtied"
(pencil symbol shown in record selector, if the record selector is
visible.
Navigate to a new record. Add the key AAAAA. Type in a Company Name and a
Contact Name. Try to navigate back one record, or forward one record to a
new
record. You should see a message that prevents you from doing this.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
I hope this isn't a dumb question and you don't mind me asking. I'm
trying to
follow your instructions because I have a similar issue but... You put
this
in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
How do you run the test?
If Me.[Nameof YourOptionButton] = Default Value Then
MsgBox "Your Message Here" ?
Aria said:Hi Pete,
Thanks for responding. Yes, it is actually on 2 lines. I changed it per
your
suggestion and I now receive the following compile error:
Invalid character
The entire line beginning with "Msgbox" is now red with the _ highlighted
in
blue.
Here is how it looks now:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
Ahhh...I see what you mean now about how the newsreader wraps the text,
but
it is actually on one line now. Open to any suggestions where I have gone
wrong. Thanks!
--
Aria W.
Pete D. said:Is it actully on two lines like you show. This was probally caused
accidently by the newreader wrapping the text. The _ at the end of the
line tells the compiler continue this line on next line.
Incase it wraps again this is a shortend version
proceeding", _
vbInformation, "Selection Required..."
MsgBox "You must select choices one, two or three before proceeding.", _
vbInformation , "Selection Required..."
Aria said:Hi Tom,
Wow! *You* are the BEST! What an excellent explanation! Thank you so
much
for taking the time to teach in such a patient manner. I have followed
your
instructions and received a compile error which says, "Expected:
identifier
or bracketed expression". It highlights the ending quote, comma and
underscore after
"...proceeding."
Here is how the code looks in my test:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before "
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
I'm sorry. I have already hijacked Chris's post (apologies to you Chris
but
I had searched for days looking for a situation that was similar to
mine
and
I thought your question was perfect.) and didn't want to post back with
more
questions.
Thank you so much.
--
Aria W.
:
Hi Aria,
No need to apologize....the only dumb question is a question that is
not
asked.
You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
No to both questions. You set the Visible property for the button and
it's
associated label to No in form design view.
How do you run the test?
Try the following experiment in the Northwind.mdb sample database. I
used
the copy that ships with Access 2003 to prepare these instructions:
1.) Open the Customers table in design view. Add a field named "Test"
(without the quotes). Set the Data Type to Number, and Field Size
(lower
window) to Integer. Leave the default value set to zero.
2.) Create a new update query to provide an initial default value in
the
Test field for the 91 Customer records. Dismiss the Add Tables dialog
without
adding any tables. In query design view, click on View > SQL View. You
should
see the word SELECT highlighted. Delete this default keyword. Copy
the
following SQL statement (Ctrl C) and paste it into the SQL view (Ctrl
V),
replacing the SELECT keyword:
UPDATE Customers SET Test = 0 WHERE Test Is Null
You can then switch back to the more familiar design view, if you
wish,
by
clicking on View > Design View. Run this update query.
3.) Open the Customers form in design view. Display your toolbox if it
is
not already displayed (View | Toolbox). Make sure that the Wizards
button
in
the toolbox is enabled. Draw a rectangular area on the form large
enough
to
comfortably fit three option buttons.
Note: You don't really need a fourth hidden option button to
accomplish
this
goal, but adding one will help make it more obvious to you, or a
future
developer that follows you, what the intended purpose was.
You should see a wizard that has you fill in the Label Names. Type in
the
following:
Initial
One
Two
Three
Click Next. Accept the default choice: "Yes, the default is:"
Initial
Click Next. Assign the values: 0, 1, 2, 3, respectively, to the
Initial,
One, Two and Three options.
Click Next. Select the option: "Store the value in this field:", and
pick
the new Test field.
Click Next. Select Option buttons and whatever style you want.
Click Next. On the last page of the wizard, for "What caption do you
want
for the option group?", enter " Test Option Group" (without the
quotes).
Click the Finish button.
4.) Display the Properties dialog, if it is not already displayed. You
can
do this by clicking on View | Properties or, in Access 2002 and later,
by
pressing the F4 button. Select the first option button and it's
associated
label (you can press the Shift key to make multiple selections, or use
your
left mouse button to "lasso" the two controls. You should see
"Multiple
selection" in the blue title bar of the Properties dialog.
Select the Format tab. Change the Visible property from Yes to No.
Note: You can set this property for the controls one-at-a-time,
instead
of
using a multiple selection, if you wish. Double-clicking on the Yes
value
in
the Properties dialog should toggle it to No.
My recommendation is to change the font color for the label that reads
Initial to red. I do this for any controls on a form where I have
intentionally set the Visible property to No. This way, those controls
stand
out to me the next time I open the form in design view.
5.) Select the Option Group frame, by clicking on the border. You
should
see
the words "Option Group:", along with the default name of this
control,
in
the blue title bar of the Properties dialog. Verify that you did not
accidently set the visible property for the frame itself to No. If so,
reset
it to Yes. The default value for this control is shown on the Data
tab. I
see
"Frame56" as the default name; you will likely see a similar, but
different,
name. We are going to give this Option Frame a more descriptive name.
Select
the Other tab. Change the name to fraTest.
Note: The lowercase "fra" part is a standard naming convention for
frames.
For a complete listing, see this site:
http://www.xoc.net/standards/rvbanc.asp#VB
6.) Select the form by clicking on the small box in the upper left
corner,
where the two rulers meet (assuming View | Ruler is checked). You
should
see
the word "Form" in the blue title bar of the Properties dialog. Click
on
the
Event tab. Select the fourth event shown in the listing: "Before
Update", by
clicking into the box. You should see a build button (a button with
three
dots) to the right. Click on this Build button. If presented with
dialog,
choose Code Builder. You should see the following, with your mouse
cursor
blinking in this new, empty procedure:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Note: If you do not see the words "Option Explicit" as your second
line
of
code, then add this line manually. Additionally, configure your Visual
Basic
Editor (VBE) to always add this line of code automatically, to all new
code
modules. Here is a "gem tip" that discusses more:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
7.) Add an error-handler to your new procedure, because all procedures
should include error-handling:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
There is a very nice, free utility that allows you to add
error-handling
with the click of a mouse button. This is known as MZTools:
http://www.mztools.com/v3/mztools3.aspx
8.) Add the following lines of code to your new procedure:
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
Click on the Save toolbar button. Then click on
Debug | Compile {ProjectName}
where {ProjectName} is the name of your VBA project. Hopefully, you
will
not
have any compile-time errors present. The completed procedure should
look
like this:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
9.) Close the VBE. Open the form in normal mode, and give it a test
drive.
For the first 91 records, you will not receive any prompt unless you
add
or
change some data in another control. The reason for this is that the
form's
Before Update event procedure only fires when the form has been
"dirtied"
(pencil symbol shown in record selector, if the record selector is
visible.
Navigate to a new record. Add the key AAAAA. Type in a Company Name
and a
Contact Name. Try to navigate back one record, or forward one record
to a
new
record. You should see a message that prevents you from doing this.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
I hope this isn't a dumb question and you don't mind me asking. I'm
trying to
follow your instructions because I have a similar issue but... You
put
this
in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
How do you run the test?
If Me.[Nameof YourOptionButton] = Default Value Then
MsgBox "Your Message Here" ?
Pete D. said:Place a space between coma and _
Then remove blank line between that one and next as compiler is looking at
next line not line after next.
Aria said:Hi Pete,
Thanks for responding. Yes, it is actually on 2 lines. I changed it per
your
suggestion and I now receive the following compile error:
Invalid character
The entire line beginning with "Msgbox" is now red with the _ highlighted
in
blue.
Here is how it looks now:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
Ahhh...I see what you mean now about how the newsreader wraps the text,
but
it is actually on one line now. Open to any suggestions where I have gone
wrong. Thanks!
--
Aria W.
Pete D. said:Is it actully on two lines like you show. This was probally caused
accidently by the newreader wrapping the text. The _ at the end of the
line tells the compiler continue this line on next line.
Incase it wraps again this is a shortend version
proceeding", _
vbInformation, "Selection Required..."
MsgBox "You must select choices one, two or three before proceeding.", _
vbInformation , "Selection Required..."
Hi Tom,
Wow! *You* are the BEST! What an excellent explanation! Thank you so
much
for taking the time to teach in such a patient manner. I have followed
your
instructions and received a compile error which says, "Expected:
identifier
or bracketed expression". It highlights the ending quote, comma and
underscore after
"...proceeding."
Here is how the code looks in my test:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before "
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
I'm sorry. I have already hijacked Chris's post (apologies to you
Chris
but
I had searched for days looking for a situation that was similar to
mine
and
I thought your question was perfect.) and didn't want to post back
with
more
questions.
Thank you so much.
--
Aria W.
:
Hi Aria,
No need to apologize....the only dumb question is a question that is
not
asked.
You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
No to both questions. You set the Visible property for the button and
it's
associated label to No in form design view.
How do you run the test?
Try the following experiment in the Northwind.mdb sample database. I
used
the copy that ships with Access 2003 to prepare these instructions:
1.) Open the Customers table in design view. Add a field named "Test"
(without the quotes). Set the Data Type to Number, and Field Size
(lower
window) to Integer. Leave the default value set to zero.
2.) Create a new update query to provide an initial default value in
the
Test field for the 91 Customer records. Dismiss the Add Tables dialog
without
adding any tables. In query design view, click on View > SQL View.
You
should
see the word SELECT highlighted. Delete this default keyword. Copy
the
following SQL statement (Ctrl C) and paste it into the SQL view (Ctrl
V),
replacing the SELECT keyword:
UPDATE Customers SET Test = 0 WHERE Test Is Null
You can then switch back to the more familiar design view, if you
wish,
by
clicking on View > Design View. Run this update query.
3.) Open the Customers form in design view. Display your toolbox if
it is
not already displayed (View | Toolbox). Make sure that the Wizards
button
in
the toolbox is enabled. Draw a rectangular area on the form large
enough
to
comfortably fit three option buttons.
Note: You don't really need a fourth hidden option button to
accomplish
this
goal, but adding one will help make it more obvious to you, or a
future
developer that follows you, what the intended purpose was.
You should see a wizard that has you fill in the Label Names. Type in
the
following:
Initial
One
Two
Three
Click Next. Accept the default choice: "Yes, the default is:"
Initial
Click Next. Assign the values: 0, 1, 2, 3, respectively, to the
Initial,
One, Two and Three options.
Click Next. Select the option: "Store the value in this field:", and
pick
the new Test field.
Click Next. Select Option buttons and whatever style you want.
Click Next. On the last page of the wizard, for "What caption do you
want
for the option group?", enter " Test Option Group" (without the
quotes).
Click the Finish button.
4.) Display the Properties dialog, if it is not already displayed.
You
can
do this by clicking on View | Properties or, in Access 2002 and
later, by
pressing the F4 button. Select the first option button and it's
associated
label (you can press the Shift key to make multiple selections, or
use
your
left mouse button to "lasso" the two controls. You should see
"Multiple
selection" in the blue title bar of the Properties dialog.
Select the Format tab. Change the Visible property from Yes to No.
Note: You can set this property for the controls one-at-a-time,
instead
of
using a multiple selection, if you wish. Double-clicking on the Yes
value
in
the Properties dialog should toggle it to No.
My recommendation is to change the font color for the label that
reads
Initial to red. I do this for any controls on a form where I have
intentionally set the Visible property to No. This way, those
controls
stand
out to me the next time I open the form in design view.
5.) Select the Option Group frame, by clicking on the border. You
should
see
the words "Option Group:", along with the default name of this
control,
in
the blue title bar of the Properties dialog. Verify that you did not
accidently set the visible property for the frame itself to No. If
so,
reset
it to Yes. The default value for this control is shown on the Data
tab. I
see
"Frame56" as the default name; you will likely see a similar, but
different,
name. We are going to give this Option Frame a more descriptive name.
Select
the Other tab. Change the name to fraTest.
Note: The lowercase "fra" part is a standard naming convention for
frames.
For a complete listing, see this site:
http://www.xoc.net/standards/rvbanc.asp#VB
6.) Select the form by clicking on the small box in the upper left
corner,
where the two rulers meet (assuming View | Ruler is checked). You
should
see
the word "Form" in the blue title bar of the Properties dialog. Click
on
the
Event tab. Select the fourth event shown in the listing: "Before
Update", by
clicking into the box. You should see a build button (a button with
three
dots) to the right. Click on this Build button. If presented with
dialog,
choose Code Builder. You should see the following, with your mouse
cursor
blinking in this new, empty procedure:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Note: If you do not see the words "Option Explicit" as your second
line
of
code, then add this line manually. Additionally, configure your
Visual
Basic
Editor (VBE) to always add this line of code automatically, to all
new
code
modules. Here is a "gem tip" that discusses more:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
7.) Add an error-handler to your new procedure, because all
procedures
should include error-handling:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
There is a very nice, free utility that allows you to add
error-handling
with the click of a mouse button. This is known as MZTools:
http://www.mztools.com/v3/mztools3.aspx
8.) Add the following lines of code to your new procedure:
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
Click on the Save toolbar button. Then click on
Debug | Compile {ProjectName}
where {ProjectName} is the name of your VBA project. Hopefully, you
will
not
have any compile-time errors present. The completed procedure should
look
like this:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation, "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
9.) Close the VBE. Open the form in normal mode, and give it a test
drive.
For the first 91 records, you will not receive any prompt unless you
add
or
change some data in another control. The reason for this is that the
form's
Before Update event procedure only fires when the form has been
"dirtied"
(pencil symbol shown in record selector, if the record selector is
visible.
Navigate to a new record. Add the key AAAAA. Type in a Company Name
and a
Contact Name. Try to navigate back one record, or forward one record
to a
new
record. You should see a message that prevents you from doing this.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
I hope this isn't a dumb question and you don't mind me asking. I'm
trying to
follow your instructions because I have a similar issue but... You
put
this
in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
How do you run the test?
If Me.[Nameof YourOptionButton] = Default Value Then
MsgBox "Your Message Here" ?
Pete D. said:I should of mentioned, for readablity and compiler standardization things
should be indented correctly. For instance
Private Sub SomeName
OnError gohere
Dim SomeOtherName as String
If true
do this
If anotherTrue
Do this
End If
end if
etc
Pete D. said:Place a space between coma and _
Then remove blank line between that one and next as compiler is looking at
next line not line after next.
Aria said:Hi Pete,
Thanks for responding. Yes, it is actually on 2 lines. I changed it per
your
suggestion and I now receive the following compile error:
Invalid character
The entire line beginning with "Msgbox" is now red with the _ highlighted
in
blue.
Here is how it looks now:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
Ahhh...I see what you mean now about how the newsreader wraps the text,
but
it is actually on one line now. Open to any suggestions where I have gone
wrong. Thanks!
--
Aria W.
:
Is it actully on two lines like you show. This was probally caused
accidently by the newreader wrapping the text. The _ at the end of the
line tells the compiler continue this line on next line.
Incase it wraps again this is a shortend version
proceeding", _
vbInformation, "Selection Required..."
MsgBox "You must select choices one, two or three before proceeding.", _
vbInformation , "Selection Required..."
Hi Tom,
Wow! *You* are the BEST! What an excellent explanation! Thank you so
much
for taking the time to teach in such a patient manner. I have followed
your
instructions and received a compile error which says, "Expected:
identifier
or bracketed expression". It highlights the ending quote, comma and
underscore after
"...proceeding."
Here is how the code looks in my test:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before "
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
I'm sorry. I have already hijacked Chris's post (apologies to you
Chris
but
I had searched for days looking for a situation that was similar to
mine
and
I thought your question was perfect.) and didn't want to post back
with
more
questions.
Thank you so much.
--
Aria W.
:
Hi Aria,
No need to apologize....the only dumb question is a question that is
not
asked.
You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
No to both questions. You set the Visible property for the button and
it's
associated label to No in form design view.
How do you run the test?
Try the following experiment in the Northwind.mdb sample database. I
used
the copy that ships with Access 2003 to prepare these instructions:
1.) Open the Customers table in design view. Add a field named "Test"
(without the quotes). Set the Data Type to Number, and Field Size
(lower
window) to Integer. Leave the default value set to zero.
2.) Create a new update query to provide an initial default value in
the
Test field for the 91 Customer records. Dismiss the Add Tables dialog
without
adding any tables. In query design view, click on View > SQL View.
You
should
see the word SELECT highlighted. Delete this default keyword. Copy
the
following SQL statement (Ctrl C) and paste it into the SQL view (Ctrl
V),
replacing the SELECT keyword:
UPDATE Customers SET Test = 0 WHERE Test Is Null
You can then switch back to the more familiar design view, if you
wish,
by
clicking on View > Design View. Run this update query.
3.) Open the Customers form in design view. Display your toolbox if
it is
not already displayed (View | Toolbox). Make sure that the Wizards
button
in
the toolbox is enabled. Draw a rectangular area on the form large
enough
to
comfortably fit three option buttons.
Note: You don't really need a fourth hidden option button to
accomplish
this
goal, but adding one will help make it more obvious to you, or a
future
developer that follows you, what the intended purpose was.
You should see a wizard that has you fill in the Label Names. Type in
the
following:
Initial
One
Two
Three
Click Next. Accept the default choice: "Yes, the default is:"
Initial
Click Next. Assign the values: 0, 1, 2, 3, respectively, to the
Initial,
One, Two and Three options.
Click Next. Select the option: "Store the value in this field:", and
pick
the new Test field.
Click Next. Select Option buttons and whatever style you want.
Click Next. On the last page of the wizard, for "What caption do you
want
for the option group?", enter " Test Option Group" (without the
quotes).
Click the Finish button.
4.) Display the Properties dialog, if it is not already displayed.
You
can
do this by clicking on View | Properties or, in Access 2002 and
later, by
pressing the F4 button. Select the first option button and it's
associated
label (you can press the Shift key to make multiple selections, or
use
your
left mouse button to "lasso" the two controls. You should see
"Multiple
selection" in the blue title bar of the Properties dialog.
Select the Format tab. Change the Visible property from Yes to No.
Note: You can set this property for the controls one-at-a-time,
instead
of
using a multiple selection, if you wish. Double-clicking on the Yes
value
in
the Properties dialog should toggle it to No.
My recommendation is to change the font color for the label that
reads
Initial to red. I do this for any controls on a form where I have
intentionally set the Visible property to No. This way, those
controls
stand
out to me the next time I open the form in design view.
5.) Select the Option Group frame, by clicking on the border. You
should
see
the words "Option Group:", along with the default name of this
control,
in
the blue title bar of the Properties dialog. Verify that you did not
accidently set the visible property for the frame itself to No. If
so,
reset
it to Yes. The default value for this control is shown on the Data
tab. I
see
"Frame56" as the default name; you will likely see a similar, but
different,
name. We are going to give this Option Frame a more descriptive name.
Select
the Other tab. Change the name to fraTest.
Note: The lowercase "fra" part is a standard naming convention for
frames.
For a complete listing, see this site:
http://www.xoc.net/standards/rvbanc.asp#VB
6.) Select the form by clicking on the small box in the upper left
corner,
where the two rulers meet (assuming View | Ruler is checked). You
should
see
the word "Form" in the blue title bar of the Properties dialog. Click
on
the
Event tab. Select the fourth event shown in the listing: "Before
Update", by
clicking into the box. You should see a build button (a button with
three
dots) to the right. Click on this Build button. If presented with
dialog,
choose Code Builder. You should see the following, with your mouse
cursor
blinking in this new, empty procedure:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Note: If you do not see the words "Option Explicit" as your second
line
of
Aria said:Pete,
Thank you so much for your help. That fixed the problem!
--
Aria W.
Pete D. said:I should of mentioned, for readablity and compiler standardization things
should be indented correctly. For instance
Private Sub SomeName
OnError gohere
Dim SomeOtherName as String
If true
do this
If anotherTrue
Do this
End If
end if
etc
Pete D. said:Place a space between coma and _
Then remove blank line between that one and next as compiler is looking
at
next line not line after next.
Hi Pete,
Thanks for responding. Yes, it is actually on 2 lines. I changed it
per
your
suggestion and I now receive the following compile error:
Invalid character
The entire line beginning with "Msgbox" is now red with the _
highlighted
in
blue.
Here is how it looks now:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
Ahhh...I see what you mean now about how the newsreader wraps the
text,
but
it is actually on one line now. Open to any suggestions where I have
gone
wrong. Thanks!
--
Aria W.
:
Is it actully on two lines like you show. This was probally caused
accidently by the newreader wrapping the text. The _ at the end of
the
line tells the compiler continue this line on next line.
Incase it wraps again this is a shortend version
proceeding", _
vbInformation, "Selection Required..."
MsgBox "You must select choices one, two or three before
proceeding.", _
vbInformation , "Selection Required..."
Hi Tom,
Wow! *You* are the BEST! What an excellent explanation! Thank you
so
much
for taking the time to teach in such a patient manner. I have
followed
your
instructions and received a compile error which says, "Expected:
identifier
or bracketed expression". It highlights the ending quote, comma and
underscore after
"...proceeding."
Here is how the code looks in my test:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
If Me.fraTest = 0 Then
MsgBox "You must select choices one, two or three before "
proceeding.",_
vbInformation , "Selection Required..."
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub
I'm sorry. I have already hijacked Chris's post (apologies to you
Chris
but
I had searched for days looking for a situation that was similar to
mine
and
I thought your question was perfect.) and didn't want to post back
with
more
questions.
Thank you so much.
--
Aria W.
:
Hi Aria,
No need to apologize....the only dumb question is a question that
is
not
asked.
You put this in the form's BeforeUpdate event as:
Me.[NameofYourOptionButton].Visible = No ?
Do you do the same for the label?
No to both questions. You set the Visible property for the button
and
it's
associated label to No in form design view.
How do you run the test?
Try the following experiment in the Northwind.mdb sample database.
I
used
the copy that ships with Access 2003 to prepare these
instructions:
1.) Open the Customers table in design view. Add a field named
"Test"
(without the quotes). Set the Data Type to Number, and Field Size
(lower
window) to Integer. Leave the default value set to zero.
2.) Create a new update query to provide an initial default value
in
the
Test field for the 91 Customer records. Dismiss the Add Tables
dialog
without
adding any tables. In query design view, click on View > SQL View.
You
should
see the word SELECT highlighted. Delete this default keyword.
Copy
the
following SQL statement (Ctrl C) and paste it into the SQL view
(Ctrl
V),
replacing the SELECT keyword:
UPDATE Customers SET Test = 0 WHERE Test Is Null
You can then switch back to the more familiar design view, if you
wish,
by
clicking on View > Design View. Run this update query.
3.) Open the Customers form in design view. Display your toolbox
if
it is
not already displayed (View | Toolbox). Make sure that the Wizards
button
in
the toolbox is enabled. Draw a rectangular area on the form large
enough
to
comfortably fit three option buttons.
Note: You don't really need a fourth hidden option button to
accomplish
this
goal, but adding one will help make it more obvious to you, or a
future
developer that follows you, what the intended purpose was.
You should see a wizard that has you fill in the Label Names. Type
in
the
following:
Initial
One
Two
Three
Click Next. Accept the default choice: "Yes, the default is:"
Initial
Click Next. Assign the values: 0, 1, 2, 3, respectively, to the
Initial,
One, Two and Three options.
Click Next. Select the option: "Store the value in this field:",
and
pick
the new Test field.
Click Next. Select Option buttons and whatever style you want.
Click Next. On the last page of the wizard, for "What caption do
you
want
for the option group?", enter " Test Option Group" (without the
quotes).
Click the Finish button.
4.) Display the Properties dialog, if it is not already displayed.
You
can
do this by clicking on View | Properties or, in Access 2002 and
later, by
pressing the F4 button. Select the first option button and it's
associated
label (you can press the Shift key to make multiple selections, or
use
your
left mouse button to "lasso" the two controls. You should see
"Multiple
selection" in the blue title bar of the Properties dialog.
Select the Format tab. Change the Visible property from Yes to No.
Note: You can set this property for the controls one-at-a-time,
instead
of
using a multiple selection, if you wish. Double-clicking on the
Yes
value
in
the Properties dialog should toggle it to No.
My recommendation is to change the font color for the label that
reads
Initial to red. I do this for any controls on a form where I have
intentionally set the Visible property to No. This way, those
controls
stand
out to me the next time I open the form in design view.
5.) Select the Option Group frame, by clicking on the border. You
should
see
the words "Option Group:", along with the default name of this
control,
in
the blue title bar of the Properties dialog. Verify that you did
not
accidently set the visible property for the frame itself to No. If
so,
reset
it to Yes. The default value for this control is shown on the Data
tab. I
see
"Frame56" as the default name; you will likely see a similar, but
different,
name. We are going to give this Option Frame a more descriptive
name.
Select
the Other tab. Change the name to fraTest.
Note: The lowercase "fra" part is a standard naming convention for
frames.
For a complete listing, see this site:
http://www.xoc.net/standards/rvbanc.asp#VB
6.) Select the form by clicking on the small box in the upper left
corner,
where the two rulers meet (assuming View | Ruler is checked). You
should
see
the word "Form" in the blue title bar of the Properties dialog.
Click
on
the
Event tab. Select the fourth event shown in the listing: "Before
Update", by
clicking into the box. You should see a build button (a button
with
three
dots) to the right. Click on this Build button. If presented with
dialog,
choose Code Builder. You should see the following, with your mouse
cursor
blinking in this new, empty procedure:
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Note: If you do not see the words "Option Explicit" as your second
line
of
The article on "Why a split database is so slow, in "really simple language"
was funny!
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.