Use a Custom Form Instead of MSGBOX 8

[MattGreer]

OK, the formatting in the msgbox is lame and annoying. I want to use a custom form with "OK" and "Cancel" buttons on it so I can make it all pretty.

I know how to open a form from within VBA, but how do I make it return a value depending on the button that I click?

It's just DoCmd.OpenForm [formname], right? What do I do after that? Or do I need a different command?

Thanks!!


Matt

 

[cranebill]

Quoted straight from help:

The OpenForm method carries out the OpenForm action in Visual Basic.

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

expression Required. An expression that returns one of the objects in the Applies To list.

FormName Required Variant. A string expression that's the valid name of a form in the current database. If you execute Visual Basic code containing the OpenForm method in a library database, Microsoft Access looks for the form with this name first in the library database, then in the current database.

View Optional AcFormView.

AcFormView can be one of these AcFormView constants.
acDesign
acFormDS
acFormPivotChart
acFormPivotTable
acNormal default. Opens the form in Form view.
acPreview
If you leave this argument blank, the default constant (acNormal) is assumed.


FilterName Optional Variant. A string expression that's the valid name of a query in the current database.

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

DataMode Optional AcFormOpenDataMode.

AcFormOpenDataMode can be one of these AcFormOpenDataMode constants.
acFormAdd
acFormEdit
acFormPropertySettings default
acFormReadOnly
If you leave this argument blank (the default constant, acFormPropertySettings, is assumed), Microsoft Access opens the form in the data mode set by the form's AllowEdits, AllowDeletions, AllowAdditions, and DataEntry properties.


WindowMode Optional AcWindowMode.

AcWindowMode can be one of these AcWindowMode constants.
acDialog
acHidden
acIcon
acWindowNormal default
If you leave this argument blank, the default constant
(acWindowNormal) is assumed.


OpenArgs Optional Variant. A string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module, such as the Open event procedure. The OpenArgs property can also be referred to in macros and expressions.

For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the openargs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.

This argument is available only in Visual Basic.

Remarks
For more information on how the action and its arguments work, see the action topic.

The maximum length of the wherecondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.

Example
The following example opens the Employees form in Form view and displays only records with King in the LastName field. The displayed records can be edited, and new records can be added.

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"


HTH
Bill

 

[MattGreer]

How does that return a value?

No offense, but did you read my question? I need it to RETURN A VALUE depending on whether the user clicks on OK or Cancel. All you did was post the (very long) help information on simply opening a form. Unless I missed something in there. Did I?


This form is opened from VBA instead of a message box.

The original code looks like this:

ntAnswer = MsgBox(strLine1 & strSkipLine & _
            strSpacer1 & strLine2 & strSkipLine & _
            strSpacer2 & strLine3, vbOKCancel, strLine4)

All that code is annoying and something else I have to contend with. Plus I can't format it. I just want to use a form to return my value.

How can I do that?

Thanks!!


Matt

 

[cranebill]

Oops forgot....

Each Command button will have its own command using the on click event in the event handler for each command button.

so you will have to have the button set some sort of variable for you to use.

For example:

Dim OKVar as Integer
OKVar = 1

Then you can use that in your other form


Bill

 

[MattGreer]

I guess I'll have to make it a global variable? I don't know of any other way to get information from one sub/function to another other than passing it in the parameters.

How would I set this up?

Option Compare Database
Option Explicit
Dim intReturnVal as Integer

Private Sub OpenMyForm()

DoCmd.OpenForm MyForm
'What stops the code from running after this previous
'statement is completed?

If intReturnVal = vbCancel Then Exit 'or whatever

'other code
'other code
End Sub

How do I 'freeze' the code after it opens the form, and make it wait for that form to be closed?

Thanks!!


Matt

 

[CajunCenturion]

Look into the Modal property of a form. Also, you don't need to use a global variable, you can make a variable a public property of a form and can then reference it with the fully qualified form name.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cgarts]

Perhaps you can just plunk a yes or no value (or whatever you need) into the receiving form's tag property?

I just tested a custom form to send values elsewhere, and it worked for me.

Private Sub BtnYes_Click()
Form_YourReceivingFormName.Tag = "yes"
End Sub

Private Sub BtnNo_Click()
Form_YourReceivingFormName.Tag = "no"
End Sub

Best

C

 

[cgarts]

Private Sub BtnYes_Click()
Form_YourReceivingFormName.Tag = "yes" ' or whatever
End Sub

Private Sub BtnNo_Click()
Form_YourReceivingFormName.Tag = "no" ' or whatever
End Sub

Then your original form will have a return value that's easy to get a hold of. No global variables etc...

Best

C

 

[MattGreer]

OK, this is definitely progress. I have one form that, when you click the button, the warning box comes up and the code will set the .Tag value to vbOK or vbCancel of the original form.

Problem is, how do I pause the code, or how do I get the original form to complete the action (or ignore the action if the user presses cancel)?

What event would that go under? Form_GotFocus? or Form_Current?

I'll try both and see what happens. The only concern is that what is going to happen if the user presses "OK" there's things that are going to be deleted and replaced in the current record. I wouldn't want this to happen inadvertently. It appears that when a form opens the .tag property is blank, or null. I could test for that.

Thanks!!


Matt

 

[MattGreer]

OK, neither test works:

me.tag = ""

or

IsNull(me.tag)

It doesn't work with the fully qualified form format either, i.e. forms!frmMyForm.Tag...

What to do?

Thanks!!


Matt

 

[PHV]

MattGreer, are you sure the YesNoForm play with the Tag property of the Calling form ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[thecreator]

Am I missing something or do you just want a message box with a OK and Cancel button and then do something based on that ??

You can still use MSGBOX to do this ... the following is from the help text (I dropped Help and Context parts because I never use that)-

Dim Msg, Style, Title, Response
Msg = "Do you want to continue ?" ' Define message.
Style = vbOKCANCEL + vbCritical ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose OK.

<put your OK code here>

Else ' User chose Cancel.

<put your CANCEL code here>

End If

Have a look under MSGBOX FUNCTION in the help for options you can use with buttons and icons (a warning icon etc).

Hope this helps

 

[MattGreer]

Checking...

Yes, the .tag is getting passed from the Warning form to the main form.

Here's the code I have:

DoCmd.OpenForm "frmWarning" 'The code in the warning form sets the .Tag value to vbOK, or vbCancel

If Me.Tag = vbOK Then
    MsgBox "It's OK!"
ElseIf Me.Tag = vbCancel Then
    MsgBox "Darn, Canceled!"
Else
    MsgBox "oops!"
End If
Exit Sub

The problem is the first time through the code the .TAG is not null, and not blank, and I can't check it for a value until I've set it to a value. I get a type mismatch when I try to check it. IsNull(me.tag) doesn't work, and me.tag = "" doesn't work either.

I'm stuck.

Thanks!!


Matt

 

[MattGreer]

Never mind. The zero length string check does work. Don't know why it didn't before. meh

Thanks!!


Matt

 

[MattGreer]

thecreator:

No formatting options on the messagebox. No graphics, no colored fonts, no font style options, no bold, or underlined, or italics, etc. etc. etc.

Thanks!!


Matt

 

[MattGreer]

OK, so what I have now is:

Button on main form opens up warning form.

warning form sets the .tag property of the main form and then closes

What event should I stuff the remaining code in? Form_Current?

Thanks!!


Matt

 

[PHV]

Can't you set the Modal property of the warning form ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MattGreer]

I did, but the code that opens the warning form continues to run.

I put a message box to pop up after the warning form opens and every time, the msgbox comes up on top of the form.

Pop Up and Modal are set to Yes in the properties.

Thanks!!


Matt

 

[RoyVidar]

Hi!

[tt]DoCmd.OpenForm "frmWarning",,,,,acDialog[/tt]

Open the form in dialog mode, then the code in the calling form halts until the "warning" form is closed.

Seldom use the .Tag property for this myself, but often a text control on the calling form (hidden sometimes) or a form module public in the calling form, but that's preferences.

Roy-Vidar

 

[MattGreer]

THAT'S IT! SMOKIN!!!

RoyVidar is the greatest!!!


Thanks to all of you tho. I learn so much from these threads.

Man, I posted something over at Experts-Exchange and they didn't even figure it out. Coolness.

Thanks!!


Matt