One form to be called from two differents places !!!

[mickeync]

Hi all,
I don't know how to handle this situation. Any suggestions really appreciated. I have one form name "CUSTOMER PROFILE", but being called from two differents places:

1) When user click "CUSTOMER PROFILE" from switchboard: It has minimum required fields such as: name, contact person, city, state (macroname: MIN_REQUIRED. I used BEFORE_UPDATE event to run this macro before closing the form and it wors fine!!! )

2) However I don't know how to handle when CUSTOMER PROFILE form being called from another form with certain condition. For example: on the STATUS FORM when CUSTOMER STATUS changed to COMPLETED. I need to go to CUSTOMER PROFILE then check to see if the rest of the form has been filled yet ??? If not then make them filled the rest of the CUSTOMER PROFILE form
It has differents requirement (more detail) such as addrsess, phone, zip code, tax id ... (I name this macro as a STATUS_REQUIRED)

In other word, how do I distinguish to run macroname "STATUS_REQUIRED" when calling from STATUS form OR run macro MIN_REQUIRED when calling from switchboard. Is it make sense to you at all ???

Thank you !!!

 

[fneily]

On the Customer Profile form, on the controls you want to hide and unhide, go to their TAG property on the property sheet and place some text such as "cansee"(without the " "'s).
On your switchboard, create an invisible label with a letter in it such as A.
Then on the Customer Profile On Open event put:

Dim Frm As Form
Dim I As Integer
Set Frm = Me
If Forms![Switchboard]![LabelName].Caption = "A" Then
For I = 0 To Frm.Count - 1
If InStr(Frm(I).Tag, "cansee&quot > 0 Then Frm(I).Visible = True
Next I
Else
For I = 0 To Frm.Count - 1
If InStr(Frm(I).Tag, "cansee&quot > 0 Then Frm(I).Visible = False
Next I
End If
End Sub

The If statements are on one line.

Neil

 

[dakota81]

Another option, create a global module with a variable that you can set to some value just before you open the form which will indicate where you're currently at. Then inside the form, check the value of the variable and customize the form based off that.

 

[MichaelRed]

or on the opening of the form, place a value in it's tag property. Using this approach you do need to clear the tag before exiting and a good practtice would be to open the form in modal mode (so it cannot be 'dismissed' w/o user actiivity.

You might also consider placing the special controls on a subform, and thus be able to hide / show just the single object rather than needitg to track and mainipulate individual controls.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mickeync]

Thanks all,
I am gonna try your code when I get to work on Monday

Mickey

 

[mickeync]

Hi Neil,
Thanks for your response. I've tried your code. However I wasn't quite sure what you mean by "create an invisible label with a letter in it .." You meant on switchboard tag property create a name like "A" ???? And where do I specify to open "CUSTOMER PROFILE" after the IF statement from your code ??? Bear with me, I am just a new kid of using VBA script. Thanks so much !!!

Hi dakota81 and michaelred,
I am also appreciated your ideas. It would help me a lot if you can write some coding so I can learn easier. Thanks a million guys.

 

[fneily]

Create a label box, using the Toolbox Label button, on your switchboard. Just a little one. When you create it, type B. Set it's visible property to No on it's property sheet. You seem to have a button on your switchboard named "Customer Profile" that, when clicked, opens the form. On this form (Customer Profile), on the OnOpen event, click in the box next to OnOpen and click the button with the three dots(build button). Click on Code Builder. VBA opens up and you paste the code. By the way, in the code, the first visible should = false, the second = true.

On the switchboard, on the button called "Customer Profile", on its' property sheet, on the OnClick event, in VBA type: Me![LabelName].Caption = "A"
Place this before the DoCmd.Openform statement.

On the OnClose event of the form Customer Profile type: Forms![Switchboard]![LabelName].Caption = "B"

Now when you click the button on the switchboard, it opens the Customer Profile form, it knows it's being opened by testing a field from the switchboard (the letter A) and hides the fields. When the Customer Profile closes, it resets the label to "B" so if any other form opens the Customer Profile form, the initial test will fail and all the contros' visible properties will be set to true.

 

[mickeync]

Hi Neil,
You're genious !!! It worked !!! I need one more favor please ... Now I was able to open CUSTOMER PROFILE from Switchboard as you instructed. However On CUSTOMER PROFILE macro I used BEFORE_UPDATE event to check the minimum required such as name, contact person, city, state before closing the form. It worked ok!!!

Now the hard part was: When the user change the status to COMPLETE on CUSTOMER STATUS Form, I need to go behind the scene to check the CUSTOMER PROFILE if the rest of the form has been entered yet ? It not then automatically open CUSTOMER PROFILE to force user key in the rest of the form such as address, phone, zip, tax id BEFORE THEY COULD CLOSE BOTH THE CUSTOMER PROFILE AND CUSTOMER STATUS AS WELL . Of course at this point the name, contact person, city, state has already been entered from the 1st place....

Thank you ... hope my question was not confused you ..

 

[fneily]

Before he leaves the Customer Profile form, on the AfterUpdate event of the Customer Profile form test to see if the fields are blank such as the following:

Dim strMessage As String
Dim intOptions As Integer
Dim bytChoice As Byte
Dim strTitle As String
If me![address] = null then
strMessage = "Are you sure you want to leave Address blank?"
intOptions = vbYesNo + vbQuestion
strTitle = "My Application"
bytChoice = MsgBox(strMessage, intOptions, strTitle)
If bytChoice = vbNo Then
[Address].SetFocus
exit sub
End If
End If
If me![phone] = null then
strMessage = "Are you sure you want to leave Phone blank?"
intOptions = vbYesNo + vbQuestion
strTitle = "My Application"
bytChoice = MsgBox(strMessage, intOptions, strTitle)
If bytChoice = vbNo Then
[phone].SetFocus
exit sub
End If
End If
If me![zip] = null then
strMessage = "Are you sure you want to leave Zip blank?"
intOptions = vbYesNo + vbQuestion
strTitle = "My Application"
bytChoice = MsgBox(strMessage, intOptions, strTitle)
If bytChoice = vbNo Then
[zip].SetFocus
Exit sub
End If
End If
If me![Tax] = null then
strMessage = "Are you sure you want to leave Tax blank?"
intOptions = vbYesNo + vbQuestion
strTitle = "My Application"
bytChoice = MsgBox(strMessage, intOptions, strTitle)
If bytChoice = vbNo Then
[Tax].SetFocus
Exit sub
end if
end if
End Sub

 

[fneily]

Oops. I meant on the BeforeUpdate event.

 

[mickeync]

Hi Neil,
Thanks for all your help. You're the best instructor !!!