Close form, skips calling form focus

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" wrote:

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

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" wrote:

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

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

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.

--
Thanks for your help,
Chris


"Albert D.Kallal" wrote:

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

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 he


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

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?


--
Thanks for your help,
Chris


"Albert D.Kallal" wrote:

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 he


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

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!

--
Thanks for your help,
Chris


"Albert D.Kallal" wrote:

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 he


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

OK, I got it now. Work with it enough and THINK and it comes.

Thanks for all your help.
--

Chris


"Albert D.Kallal" wrote:

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 he


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

"Chris" wrote in message
...

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....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal