Close form, skips calling form focus

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

frmClient stays open always and visible (modal, not PopUp), remains open
during frmEmployee use;

frmEmployee called via button on frmClient; user closes when done (modal,
not PopUP), this remains visible during frmEmployeeSetup use, covering
frmClient;

frmEmployeeSetup called via button on frmEmployeeSetup, user closes when
done (modal, PopUP), frmEmployee is visible behind the popup.

Problem is: when closing frmEmployeeSetup frmEmployee closes or goes
stealth and the focus is on frmClient. I want the focus to be on frmEmployee
on closing frmEmployeeSetup.

I have tried stipping all code, setting focus, making visible all to no avail.

Can someone point the way to resolution? Please?
 
First, I have to assume this is a type-o



How can the button on the form call it's self? can I guess that you mean:

Why does frmEmployeeSetup need to be popup if it is model? If you make each
form model then the user has to traverse back the same way they came.
Problem is: when closing frmEmployeeSetup frmEmployee closes or goes
stealth and the focus is on frmClient.

The above is not normal behavior, and should not be happening.

My guess is that you got a popup setting on some form(s) that should not
have that setting. You ONLY need popup if you want that form to "stay" on
top, but USE other form. Since you want to return to the previous form, then
remove all popup settings, since as far as I can tell, you don't need them.

Do remember that calling code does NOT wait when you execute the
docmd.openform, so perhaps that is a issue. I would suggest simply removing
the popup status, since if it is model, then it has to stay on top of the
previous form anyway.

The other thing of course is that you should execute a disk write in most
cases RIGHT BEFORE you open a form. This not only saves data before leaving
a form and having a potential problem, but then things like the autonumber
id, and data is written, and thus when modified by other forms you don't
get that nasty "record has been modified by other users".


so, go:

me.Refresh
docmd.OpenForm "frmEmployeeSetup",,,"id = " & me!id

I put in the "were" clause, but I figure you might be loading the setup form
to the one record...

So, any reason why you don't just use model in all 3 cases?

And, don't confuse model, or popup forms with dialog forms...they are quite
different....

You can read about them here:
http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html
 
Thanks, Albert.

Your assumption re the typo is correct.

All three forms are now popup = no, modal = yes.

Still, on closing [frmEmployeeSetup], [frmEmployee] disappears.

You should be aware that everything works fine until I CLOSE
[frmEmployeeSetup]. after all other code executes. (I followed it through.)
I modified your suggested code to reflect being in the closing routine of
[frmEmployeeSetup], where the dissaperance happens. Also, you should know
that I wish the user to close the setup form and view the record of the
employee just setup.

What else can you suggest?
 
Good progress here!!

ok...
You should be aware that everything works fine until I CLOSE
[frmEmployeeSetup]. after all other code executes.

Hum, you caught my eye here! What code are you executing here, and when, and
at what point?


and
, you should know
that I wish the user to close the setup form and view the record of the
employee just setup.

I assume that you have written the employee record to disk in the employee
form BEFORE you launch the frmEmplyeesetup..right?
I also assume that you set the record for frmEmployeeSetup via the where
clause in the openform command...right?

In other words, is frmEmplyeesetup based on the SAME record that you want to
return to when you close, and go to frmEmployee?
 
Again, thanks.

A button on [frmEmployeeSetup] is how the user closes [frmEmployeeSetup]:
Me.Inactive = False ' sets the employee to active
Dim frm As Form
Set frm = Forms![frmEmployee]
frm.[cboSelectEmployee].Requery '[frmEmployee] combo box which contains
"EmployeeID" for all active employees.
frm.Requery
With frm.RecordsetClone
.FindFirst "EmployeeID=""" & Me.EmployeeID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
DoCmd.Close acForm, "frmEmployeeSetup"

Yes, the record exists when [frmEmployeeSetup] opens. But I don't use
"where", I use OpenArgs via a popup (from an MVP a while back) that uses
NotInList Event of a combo box to create and write the record before opening
[frmEmployeeSetup]. That combo box is all that's on that form; updating the
combo box passes open args to [frmEmployeeSetup].

And...yes, the record is setup, when I close [frmEmployeeSetup] and, yes,
that's the record I want to view.

I know this will work if I can solve why [frmEmployee] vanishes.
 
Ok, so, we are doing this from a not in list of a comb box.....

The problem we are having is that we got two forms, and a combo box ALL
BASED on the same table.

Further, when you do a re-query on the form, the combo box gets re-loaded,
but you are in the middle of a not in list event, and that will cause an
error.

Normally, there is little problem in launching a form to add a new record to
a combo box, but your combo box is used to navigate to new records, and also
add records, and the form that the combo box is on is also based on this
table (that 3 guys on the same table!!).

Normally, the not in list code to add a new value to the comb box is:


if MsgBox("Do you want to add this new client to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The open args passes the new data, and in the frmAddCleint, the on-load
event would go:


me!Lastname = me.OpenArgs

The above is actually ALL THE code you need to do this. Note the use of
acDialog to Halt THE CODE. If you don't use acdialog, then the code above
will continue to run, and NOT WAIT for the frmAddClent to finish. Note that
setting Response = acDataErrAdded does a re-load, and re-query of the combo
box.

However, the above is for the case when adding to a table for the combo box,
not the form we are actually on.

We need a way to re-load the form, and re-load the combo box. We CAN NOT do
that while in the combo box is in the middle of the not in list code. So,
the trick, or solution is to move the requery code to the AFTER UPDATE event
of the combo box

So, we going have to tell the form to requery, but we can't do it in the
middle of the not in list event (that also means you forms close event can't
do the requery and move to the correct record).

So, lets create a forms module level var


Option Compare Database
Option Explicit

Dim bolAdd As Boolean

Now, change our not in list code to:

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then
DoCmd.OpenForm "ContactsE", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If


Now, in our comb box after update event which moves to the new record, we
go:

' Find the record that matches the control.

If bolAdd = True Then
bolAdd = False
Me.Requery
End If

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Note the code to requery the form.

So, really, at the end of the day here, is that we can NOT requery the form
while in the middle of a not in list event. (and, it simply re-fires the not
in list code again and again). And, I betting you got code in the combo
AFTER UPDATE event anyway. So, the beauty of this code approach is that we
don't have to repeat the "after update" event code to "move" to the correct
record in your "setup" forms close event. So, we cleaned up things a lot
here, and got rid of a lot of code.

You can certainly modify the above not in list code to "add" the new record,
and launch form setup to that record if you wish.

Something like:

Dim rst As DAO.Recordset
Dim lngNewID As Long

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then

Set rst = CurrentDb.OpenRecordset("contacts")
rst.AddNew
rst!LastName = NewData
lngNewID = rst!ContactID
rst.Update
rst.Close
Set rst = Nothing

DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If

Note how in the above we don't open the form in add mode, and also do NOT
send the openargs to the record.
(this would also mean that we REMOVE any code in the setup form to set
things via open args DONT forget this...as this messed me up more then once)

So, you can see how this cleans up a ton of code.

The bottom line is that we can't re-query the combo box (or form) while we
are in the not in list code....
 
Wow. I think I followed some of that but don't test me.

However...the NotInList event code is completed by way of OnLoad event of
[frmEmployeeSetup]. Its coming in the OpenArgs from a form (not frmClient,
nor frmEmployee, nor frmEmployeeSetup) whose sole purpose is to "house" the
new employee ID assignment and pass the new ID to frmEmployeeSetup.

I must have confused you earlier. Sorry.

The code I provided earlier representing the close of frmEmployeeSetup is
the point at which frmEmployee vanishes (along with the proper closing of
frmEmployeeSetup), AND the new employee ID is in frmEmployee's combo box AND
the new record does exist.

All is good except frmEmployee vanishes along with frmEmployeeSetup proper
closing.

Thanks for helping me.
 
The code I provided earlier representing the close of frmEmployeeSetup is
the point at which frmEmployee vanishes

Yes, I understood that. And, that close code of frmEmployeeSetup does a
re-query of frmEmployee. If this is happening if you are STILL IN the not in
list code when you launched frmEmployeeSetup, then that why we got a
problem....

For a test to clear this up, put a button beside the combo box to "add new".
Now run your normal code, and you will likely find that things work.

(in other words, lets take out the not in list code, and simple run some
"test" code behind a button.

dim strText as string

strText = inputbox("what new value for comb box")

..... you SAME code goes here that adds the new record, and calls up form
frmEmployeeSetup.

Try the above...and eliminate the combo box from the equation, and see what
happens...

On the other hand, if you are NOT actually launcing frmEmployeeSetup from
the not in list code, then likely I am wrong. (however, either you are
halting the not in list code so you can ADD THE NEW record, or you are
letting the not in list code finish? (which is it?) However, if you let the
not in list code finish, then we have to assume the new record is already
added at this point. The after update code of that combo box will now but
the user will still be in frmEmployeeSetup at this point if you do not HALT
the code. (If you are not halting the code, I can't see how this will
work??).
However...the NotInList event code is completed by way of OnLoad event of
[frmEmployeeSetup].

Is it? Are you sure the not in list event is done? As I mentioned above, are
you telling me that frmEmployeeSetup is not opened in acdialog mode? If that
is the case, then the combo box code is NOT waiting, you added the new
record, and the combo box after update event code will now try to run. (ie:
frmEmployee will begin to try and move to the new record, and you are still
in frmEmployeeSetu?). Perhaps you don't have any code in the comb box after
update event then?
All is good except frmEmployee vanishes along with frmEmployeeSetup proper
closing.

It is possible I missing something else here, but then try placing a button
beside the combo box, and use that in place of the not in list event....
does it work?
 
You will probably view this as a big step, but I'm still lost.

I put a button and your code on the popup/not in list combo box form and
"forced" the ID. Fed frmEmployeeSetup via open args. Got the same results.
So . . .

I went to frmEmployeeSetup and commented out the OnLoadEvent and open args
and "forced" a valid (unused) code using an AddNew Command Button.

The behaviour was what I want. But, now what do I do to get
frmEmployeeSetup to test for a unique EmployeeID and add the record? I was
given that combo box/popup/not in list event stuff from an MVP a while back,
but I am not wedded to it.

I don't really care where EmployeeID is assigned or the record created as
long as its entered, saved, and returns to frmEmployee.

Thanks for your patience.
--
Thanks for your help,
Chris


Albert D.Kallal said:
The code I provided earlier representing the close of frmEmployeeSetup is
the point at which frmEmployee vanishes

Yes, I understood that. And, that close code of frmEmployeeSetup does a
re-query of frmEmployee. If this is happening if you are STILL IN the not in
list code when you launched frmEmployeeSetup, then that why we got a
problem....

For a test to clear this up, put a button beside the combo box to "add new".
Now run your normal code, and you will likely find that things work.

(in other words, lets take out the not in list code, and simple run some
"test" code behind a button.

dim strText as string

strText = inputbox("what new value for comb box")

..... you SAME code goes here that adds the new record, and calls up form
frmEmployeeSetup.

Try the above...and eliminate the combo box from the equation, and see what
happens...

On the other hand, if you are NOT actually launcing frmEmployeeSetup from
the not in list code, then likely I am wrong. (however, either you are
halting the not in list code so you can ADD THE NEW record, or you are
letting the not in list code finish? (which is it?) However, if you let the
not in list code finish, then we have to assume the new record is already
added at this point. The after update code of that combo box will now but
the user will still be in frmEmployeeSetup at this point if you do not HALT
the code. (If you are not halting the code, I can't see how this will
work??).
However...the NotInList event code is completed by way of OnLoad event of
[frmEmployeeSetup].

Is it? Are you sure the not in list event is done? As I mentioned above, are
you telling me that frmEmployeeSetup is not opened in acdialog mode? If that
is the case, then the combo box code is NOT waiting, you added the new
record, and the combo box after update event code will now try to run. (ie:
frmEmployee will begin to try and move to the new record, and you are still
in frmEmployeeSetu?). Perhaps you don't have any code in the comb box after
update event then?
All is good except frmEmployee vanishes along with frmEmployeeSetup proper
closing.

It is possible I missing something else here, but then try placing a button
beside the combo box, and use that in place of the not in list event....
does it work?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
 
But, now what do I do to get
frmEmployeeSetup to test for a unique EmployeeID and add the record?

Well, we need to get the combo box working for the above to work correctly.

Remember, in those examples I gave you, can REMOVE ALL OF the code you had
in the close event...you simply don't need it anymore...

So, lets just use the 2nd example where we create the new record in code

So, for the combo box we got:

Dim rst As DAO.Recordset
Dim lngNewID As Long

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then

Set rst = CurrentDb.OpenRecordset("contacts")
rst.AddNew
rst!LastName = NewData
lngNewID = rst!ContactID
rst.Update
rst.Close
Set rst = Nothing

DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If

You can post for me what you got for the above....

And, for the combo box after update event that moves to the correct record,
we got:


If bolAdd = True Then
bolAdd = False
Me.Requery
End If

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

You DO NOT need any more code in the frmEmployeeSetup

You DO NOT need to pass any parmaters to frmEmployeeSetup

You do NOT need any code in the close event of frmEmployeeSetup

So, in fact, you can simply modify the combo box code to add the new reocrd,
and grab the new id

And, you simply add the "bolAdd" condtion code in the combo box after udpate
event to requery the form if needed.....

Try the above, and post your code for the combo box not in list event, and
post the code you got for that combo box after udpate event...

In my example above code, I am obviously using a auto for the key id. It is
not clear if you are using a auotnumber, or in fact supply the contact id
your self...
 
Remember I am very much the amateur.

I followed last and made changes.

Does the bolAdd module name matter? It fails to compile, variable not
defined at "bolAdd=true".

Make sure I followed you on new code residence:

The following should be in the NotInListCode of [frmEmployeeSetupNewID] ?
(the form I had only alluded to earlier and not named for you; sole purpose
is house cbo and get new ID.)

"So, for the combo box we got:
Dim rst As DAO.Recordset
Dim lngNewID As Long
If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then
Set rst = CurrentDb.OpenRecordset("contacts")
rst.AddNew
rst!LastName = NewData
lngNewID = rst!ContactID
rst.Update
rst.Close
Set rst = Nothing
DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If"

The following should be in the AfterUpdate event of that same combo box? The
only other combo box for employee ID is on [frmEmployee] and has code in its
after update for selection of record to view. There is no combo box on
[frmEmployeeSetup] as that's a data entry form only and the ID was assigned
by [frmEmployeeSetupNewID]. At least it was a week ago and as I am presently
attempting to do.

" If bolAdd = True Then
bolAdd = False
Me.Requery
End If
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark"

I generally follow the logic, but lack the knowledge to see it completely.
Am I totally missing the boat here?
--
Thanks for your help,
Chris


Albert D.Kallal said:
But, now what do I do to get
frmEmployeeSetup to test for a unique EmployeeID and add the record?

Well, we need to get the combo box working for the above to work correctly.

Remember, in those examples I gave you, can REMOVE ALL OF the code you had
in the close event...you simply don't need it anymore...

So, lets just use the 2nd example where we create the new record in code

So, for the combo box we got:

Dim rst As DAO.Recordset
Dim lngNewID As Long

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then

Set rst = CurrentDb.OpenRecordset("contacts")
rst.AddNew
rst!LastName = NewData
lngNewID = rst!ContactID
rst.Update
rst.Close
Set rst = Nothing

DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If

You can post for me what you got for the above....

And, for the combo box after update event that moves to the correct record,
we got:


If bolAdd = True Then
bolAdd = False
Me.Requery
End If

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

You DO NOT need any more code in the frmEmployeeSetup

You DO NOT need to pass any parmaters to frmEmployeeSetup

You do NOT need any code in the close event of frmEmployeeSetup

So, in fact, you can simply modify the combo box code to add the new reocrd,
and grab the new id

And, you simply add the "bolAdd" condtion code in the combo box after udpate
event to requery the form if needed.....

Try the above, and post your code for the combo box not in list event, and
post the code you got for that combo box after udpate event...

In my example above code, I am obviously using a auto for the key id. It is
not clear if you are using a auotnumber, or in fact supply the contact id
your self...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
 
And I did create the module "modBolAdd".

Option Compare Database
Option Explicit

Dim bolAdd As Boolean





--
Thanks for your help,
Chris


Albert D.Kallal said:
But, now what do I do to get
frmEmployeeSetup to test for a unique EmployeeID and add the record?

Well, we need to get the combo box working for the above to work correctly.

Remember, in those examples I gave you, can REMOVE ALL OF the code you had
in the close event...you simply don't need it anymore...

So, lets just use the 2nd example where we create the new record in code

So, for the combo box we got:

Dim rst As DAO.Recordset
Dim lngNewID As Long

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then

Set rst = CurrentDb.OpenRecordset("contacts")
rst.AddNew
rst!LastName = NewData
lngNewID = rst!ContactID
rst.Update
rst.Close
Set rst = Nothing

DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If

You can post for me what you got for the above....

And, for the combo box after update event that moves to the correct record,
we got:


If bolAdd = True Then
bolAdd = False
Me.Requery
End If

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

You DO NOT need any more code in the frmEmployeeSetup

You DO NOT need to pass any parmaters to frmEmployeeSetup

You do NOT need any code in the close event of frmEmployeeSetup

So, in fact, you can simply modify the combo box code to add the new reocrd,
and grab the new id

And, you simply add the "bolAdd" condtion code in the combo box after udpate
event to requery the form if needed.....

Try the above, and post your code for the combo box not in list event, and
post the code you got for that combo box after udpate event...

In my example above code, I am obviously using a auto for the key id. It is
not clear if you are using a auotnumber, or in fact supply the contact id
your self...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
 
Does the bolAdd module name matter? It fails to compile, variable not
defined at "bolAdd=true".

You don't need to define a new module. Since you are running the after
update code in the combo box, then the obvious place to define this variable
is in that forms module.

So, simply define the bolAdd variable at the START of the forms module.
There is not need to create a whole new module to "just" allow us to define
that one variable. That variable is used by code in the ONE form, and thus
it makes sense to define that variable in that forms module.

The start of your forms module code should thus look like:

Option Compare Database
Option Explicit

Dim bolAdd As Boolean

If you were to scroll down further in the module, then you would start to
see your not in list code, and also the comb's after update code. So, it
kind makes sense to put (define) the variable in the same code module that
it is going to be used. Note that *could* create a whole new separate
module, but then that would imply a design where other forms, and other code
would need to use that variable bolAdd.

So, since we are using the variable in the code in the "form" module, then
we simply define this variable in that forms code module. It is kind of nice
feature that each new form you make also creates a "form" module for code
from that form. This natural grouping of code means that all code (and our
variables) is likely to be found in the one code module for that form.
 
I think I'm on the road to recovery and some (minor) comprehension.

EmployeeID is a text field and I think that is why it now fails at "
IngNewID = rst!EmployeeID". The user assigns the first three letters of the
last name followed by three numeric, incrementing the numeric portion by one.
Ex: SMI105, SMI106 and prior exist already so the next SMI would be
assigned 107 by the user, inputting SMI107, which isn't in the list.

I still appreciate you handholding.
 
EmployeeID is a text field and I think that is why it now fails at "
IngNewID = rst!EmployeeID".

Excellent observation on your part. My code assumed a "auto" id.

So, you got a situation where you combo box retuns a value like
SMI105...etc...

So, we need to change things a bit to reflect this

Dim rst As DAO.Recordset

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then
Set rst = CurrentDb.OpenRecordset("contacts")

' in the above, you would of course replace "contacts" with the actual name
of the table that you
' used

rst.AddNew
rst!EmployeeID = NewData
rst.Update
rst.Close
Set rst = Nothing

DoCmd.OpenForm "ContactsE", , , "EmployeeID = '" & NewData & "'", ,
acDialog

' in the above, you of course replace "ContactsE"with your frmEmployeeSetup
' note how I now have surrounded the text value with single quotes
' note how we don't need lngID anymore

Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If


Likely, your comb box "after update" code that worked before to "move" to
the record can remain as you had it.

We still need the

If bolAdd = True Then
bolAdd = False
Me.Requery
End If

...........your combo code to "move" to the reocrd would follow here:
 
There is a good record created.

However...
1. the new ID is not appearing in [EmployeeID] field on [frmEmployeeSetup]
(previously handled via open args) so none of the new employee entry data is
saved. There is a field on that form -- [EmployeeID].

2. the [frmEmployeeSetupNewID] form does not close at [frmEmployeeSetup]
opening. I cannot find where to place DoCmd close in the after update code.

3. the newly created record does not appear on [frmEmployee] -- that's why I
started this.

4. although the new record has only an ID, when I go to [frmEmployee] that
is no longer available as no requery of that combo box.

Where to now?
 
OK...I figured out to change [frmEmployeeSetup] to NOT DataEntry. So, I have
the record ok.

Still, I'm not bringing the new record up on [frmEmployee].

Please help!
 
OK...I figured out to change [frmEmployeeSetup] to NOT DataEntry. So, I
have
the record ok.

Still, I'm not bringing the new record up on [frmEmployee].

Probably the code to open the form is wrong

What does your open form command look like?

We are going to use a "where" clause of the open form to "send", or better
term "open"the form to the record we just created.

So, what does your open form command look like?

In fact, it probably a good idea for you to post both the comb not in list
code as you have, and also the comb box after update code....
 
Back
Top