Opening & Closing Forms through VBA (1 Viewer)

[accvbalearner]

I am working on the data entry forms for a database. I'm confusing myself trying to ask the right question(s) so here goes...

I have three forms, Form1, Form2 & Form3. All are for entering data into tables.

Form1 and Form2 have a Facility Name Field ComboBox
Form3 is for adding New Facilities

The code below is in Form1 and Form2 to determine if the Facility entered by the user is in the list, if it isn't in the list then the user can choose to open Form3.

Form3 will let them enter a New Facility Name.

Now I think this is the right question:
How do I tell the cursor to return to the Facility name Field in the Form that caused Form3 to be opened in the first place?

If it were just one form causing the entry I think a DoCmd.OpenForm would work on the On Close Event of Form3. But it could be either Form1 or Form2 that caused Form3 to be opened.

Thanks in advance for any help you can provide.


Private Sub Facility_NotInList(NewData As String, Response As Integer)
Dim intReply As Integer
intReply = MsgBox("The Facility '" & NewData & "' is not in the list. Would you like to add?", vbYesNo)

If intReply = vbYes Then
' -- Open F12-NewFacilityEntry form in data entry mode, passing the new data as
' -- an OpenArg
DoCmd.OpenForm "F12-NewFacilityEntry", , , , acFormAdd, acDialog, NewData

'-- Record added, so cancel Access's default processing
Response = acDataErrAdded
Else
MsgBox "Please Select an item in the list."

'-- We have handled the error, so tell Access not to put up
'-- it's own default error
Response = acDataErrContinue
End If
End Sub

 

[John Big Booty]

You could use the OpenArgs portion of the OpenForm method to pass the name of the calling form. You could then use this in the procedure you are using to close the form to set focus back to the form that called.

 

[mdlueck]

How do I tell the cursor to return to the Facility name Field in the Form that caused Form3 to be opened in the first place?

A couple of helpful posts for you...

Relocate Previously Selected Record in a Multiple Items Form
http://www.access-programmers.co.uk/forums/showthread.php?p=1178436#post1178436

But it could be either Form1 or Form2 that caused Form3 to be opened.

You could accomplish this a number of ways.

Perhaps easiest would be to use a global variable declared on a VBA module. Have that variable contain the ID of the record currently being considered.

When a form opens, it checks that variable, if <> 0, then attempt to locate the specified record.

 

[accvbalearner]

Thanks for the quick reply! What would the syntax for that look like? Would I have to put it in Form1 and Form2's code to capture the Form name or would it have to be a Public Function or something so that Form3 can get it?

 

[accvbalearner]

Reply from mdlueck,

I followed the link and kind of see where you are going with it, but am not for sure that it will work. I have included the ID for the table in the UI for all of the forms, but the field that starts the whole process is part of a record that hasn't been entered into the table yet. The ID isn't in the table until the form has been filled out correctly, so I can't refer to it to go back to a certain record I don't think.

Form3 isn't a subform to Form1 or Form2 either, it is it's own form. In the Form1 and Form2 null check on the BeforeUpdate event, I have the cursor return to a particular field using Me.WONbr.SetFocus

I would think that in the code I placed in the original thread I could somehow use your idea of capturing the FormName and sticking it somewhere while Form3 is open, then when the user clicks the X or a Close Button, it would pull and the FormName and go back to the Facility Name Field in Form1 or Form2 using Me.Form1.Facility.SetFocus

I hope I got the point across, it sounds good in my head, but doesn't look good on paper!

 

[accvbalearner]

I have figured out how to capture and keep the form name by creating a module called PubVarFormName like this:

Option Compare Database
Option Explicit
Public fmName As String

I have also verified that fmName is working as follows:

Private Sub Form_Close()

MsgBox ("Form Name = " & fmName)

Me!fmName.Area.SetFocus

End Sub

The message box is showing the correct name of the form, but the command to return the focus to the original form isn't working, any ideas why?

Thanks in advance for the help!

 

[jonathanchye]

Try :

Forms!frmName.Setfocus (this should set focus to frmName)

If you want to set focus to a control on that form try :

Forms!frmName.Form.ControlName.Setfocus

 

[accvbalearner]

Thanks for the idea, but I can't make it work. Every time I run it, I get the error "Microsoft Access can't find the form 'fmName' referred to in a macro expression or Visual Basic code."

I have set up a message box to show the form name (i.e. fmName) when it leaves Form1 and when it leaves Form3, the name is correct. When I type the name of Form1 into the statement it works every time, but the cursor ends up in the field following the one I want it to. This leads me to believe that I have set the wrong data type in the Module. I have tried As String and As Variant so far, not sure what other type to use.

I have tried the following variations and none have worked so far:
(Area is the name of the field in the form that I want the curser to go back to.)


Me.fmName.Area.Setfocus
Forms.fmName.Area.Setfocus
Forms!fmName.Area.Setfocus
Forms(fmName).Form.Area.Setfocus


Any help is much appreciated!!!

 

[jonathanchye]

Ah....fmName is a global variable in that case you must do it differently. I don't have my work PC infront of me at the moment but try something like this :


Declare fmName as Form instead of String in the global module. You then have to initialise your global variable. Use the form's OnLoad event with this code :

Set fmName = Screen.ActiveForm


To set focus to a control in fmName called "Area" try :

Forms!fmName!Area.Setfocus

 

[accvbalearner]

Jonathanchye,

Thanks for the reply, but I'm not able to make that work. I keep getting Mismatch errors now, but that is something new, so maybe it is progress. Let me give you a brief rundown of what I want to happen and maybe you can help me more. I have three forms, Form1, Form2 & Form3. Form3 can be opened by either Form1 or Form2. After a user has entered data in Form3 the cursor will return to the Area field in whichever Form caused Form3 to open when Form3 is closed by clicking the X.

Here are all of the codes that I am using to make this happen:

Module Code:

Option Compare Database
Option Explicit
Public fmName As String

--------------------------------------
Form1 Not in List Code:

Private Sub Area_NotInList(NewData As String, Response As Integer)
Dim intReply As Integer
intReply = MsgBox("The Area '" & NewData & "' is not in the list. Would you like to add?", vbYesNo)

If intReply = vbYes Then
' -- Open F13-NewAreaEntry form in data entry mode, passing the new data as
' -- an OpenArg
Set fmName = ("[" & Me.Form.Name & "]")

MsgBox ("Form Name = " & fmName)

DoCmd.OpenForm "F13-NewAreaEntry", , , , acFormAdd, acDialog, NewData

'-- Record added, so cancel Access's default processing
Response = acDataErrAdded
Else
MsgBox "Please Select an item in the list."

'-- We have handled the error, so tell Access not to put up
'-- it's own default error
Response = acDataErrContinue
End If
End Sub

--------------------------------------------------
Form3 Go Back to FormX Code:

Private Sub Form_Close()
'This will close the form and return to the original form in the Area Field.

MsgBox ("Form Name = " & fmName)

Forms!fmName!Area.SetFocus

End Sub

 

[jonathanchye]

Hmm got 2 ideas but first try this :

In your procedure Private Sub Area_NotInList(NewData As String, Response As Integer)
Change :

Set fmName = ("[" & Me.Form.Name & "]")

Change this to :

Set fmName = Screen.ActiveForm (remember to change declaration of fmName in your global module to Public fmName As Form)

You need to do this just before opening Form3 so fmName would be set to the correct form.

Hopefully this should fix the type mismatch issue.

 

[accvbalearner]

Jonathanchye,

Still have the mismatch in the message box for not in list sub of Form1 and Form3, so I Rem'd them out, now I get the same old message when closing Form3 (i.e. "Microsoft Access can't find the form 'fmName' referred to in a macro expression or Visual Basic code.)

Any other ideas? It seems to me that while fmName = Form1 when I have it set to a String or Variant isn't getting passed through to the Forms! statement as a valid Form Name, because when I use:

Forms!Form1.Area.Setfocus or Forms![Form1].Area.Setfocus or Forms("Form1").Form.Area.Setfocus

It works just fine.

Thanks for the help and quick replies! :banghead:

 

[jonathanchye]

Well fmName is definitely being saved somehow if you can display it in a message box...

Few things to check... are you sure you have declared fmName in a module?

Change the declaration of fmName back to String.

To save the form's name to fmName try :

Dim currentFormName as Form
Set currentFormName As Screen.ActiveForm
fmName = currentFormName.Name

(You need to make sure this is done just before Form 3 is opened)

then in Form 3's Close event try :

Forms(fmName).Area.Setfocus

 

[accvbalearner]

Jonathachye,

It Worked!!! Thanks so much for your help! I only have one teeny tiny issue, and that is when it returns to the originating form, it doesn't go to the Area field, it goes to the next field in the Form. I believe that is because of the code Response = acDataErrAdded but I'm not 100% sure. Otherwise it works Great!

For anyone else suffering through the same issue and browsing this VERY HELPFUL FORUM, here is the code that will return a User to the previous form opened when closing a form. I am using this on an Access 2007 Form for a user to add another item to a combo box list when it isn't already in the list. Once they add the new item and click the 'X' in the top it will return them to original form with the new value to the combo list added.

I am using -------- lines to separate the different code used.

Insert a Standard Module declaring a Global Variable as follows:

Option Compare Database
Option Explicit

Public fmName As String

----------------------------------------------------------------
Below is the code to determine what to do if the item is not in the list.

Private Sub Area_NotInList(NewData As String, Response As Integer)
Dim intReply As Integer
intReply = MsgBox("The Item '" & NewData & "' is not in the list. Would you like to add?", vbYesNo)

If intReply = vbYes Then
'This sets the form name so that the new form opening knows which form to go back to.
Dim currentFormName As Form
Set currentFormName = Screen.ActiveForm

fmName = currentFormName.Name
'Remove the ' to test the code with a Message Box
'MsgBox ("Form Name = " & fmName)

' -- Open the new form in data entry mode, passing the new data as
' -- an OpenArg - Place your form name in between the "" after OpenForm
DoCmd.OpenForm "YourFormName", , , , acFormAdd, acDialog, NewData

'-- Record added, so cancel Access's default processing
Response = acDataErrAdded
Else
MsgBox "Please Select an item in the list."

'-- We have handled the error, so tell Access not to put up
'-- it's own default error
Response = acDataErrContinue
End If
End Sub

---------------------------------------------------------------------
The code below is for returning back to the original form that started the add process

Private Sub Form_Close()
'This will close the form and return to the original form

'Remove the ' to test the code with a Message Box
'MsgBox ("Form Name = " & fmName)

Forms(fmName).[TheNameoftheFieldToGoBackTo].SetFocus

End Sub
---------------------------------------------------------------------


I hope this will help someone out in the future, if so, please send a thank you back to Users Jonathanchye, John Big Booty and mdlueck!