Another Not in List not working

[Afrosheen via AccessMonster.com]

Thanks for reading this.
Here is the code I'm using:

Private Sub Combo109_NotInList(NewData As String, Response As Integer)
Dim strMsg, nm1, stwhere As String
10 strMsg = "That value is not in the list. Are you sure you want to add
it?"
20 If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbYes Then
30 Response = acDataErrAdded
40 CurrentDb.Execute ("insert into tblsupervisor(Supervisor) select '" &
NewData & "'as exprl;")
nm1 = Me.[Supervisor]
stwhere = "nm1=supervisor"
MsgBox nm1
DoCmd.OpenForm "frmsupervisor", , , stwhere
50 Else
60 Response = acDataErrContinue
70 Me.Combo109.Undo
80 End If
end sub

Here's what it's doing. When I enter info in the combo box and it's not found
it does fire the not in list and adds the information to the table. What I
want it to do is to have it open a form and have it display the name I typed
in the combo box because there's other information I need to add. Before it
opens the form it asks for the parameter value of the nm1 string. After I
enter that then it will open the form with the parameter value. The msgbox
nm1 does not show the info in the combo box. It shows the previous name. The
field name is "Supervisor". For example:

I type Tom
The name before was George. The msgbox nm1 shows George. Not Tom.

I hope I've explained my self correctly.

Thanks for your help.

 

[JimBurke via AccessMonster.com]

What is Combo109? Is that name of the supervisor? If so, then what is the
field called supervisor? It seems like you have two different fields
referring to the same value. Is this form one that is bound to some other
table and has a relationship to the Supervisor table? And what exactly is the
puepose of tis form? Also, is it possible to have more than one supervisor
with the same name? That's something you may need to account for in the form
you're opening. And if you can have the same name repeated, how do you
distinguish between them? Sorry for so many questions, but I know nothing
about your application.

Let me see if I have this right. It sounds like you're trying to select a
supervisor name from the combobox. If they type in one that's not already
there, you want to add the new supervisor name that they've typed in, then
open the form to add additional info for that supervisor. Is that right?

One problem that I do see is this:
stwhere = "nm1=supervisor"
this should be
stwhere = "nm1=""" & supervisor & """"
I used double quotes because I'm not sure if you need to allow for single
quotes in the name - using the double quotes will allow for it. If you don;t
need to worry about quotes then use
stwhere = "nm1='" & supervisor & "'"


Also, for your Insert, for your purposes you should probably just use this
format:
"insert into tblsupervisor (Supervisor) Values(""" & NewData & """)"

You really only need to use a SELECT statement when you have a reason to
insert data based on a SELECT statement - in your case you're inserting a
given value. It still works either way I guess.

Thanks for reading this.
Here is the code I'm using:

Private Sub Combo109_NotInList(NewData As String, Response As Integer)
Dim strMsg, nm1, stwhere As String
10 strMsg = "That value is not in the list. Are you sure you want to add
it?"
20 If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbYes Then
30 Response = acDataErrAdded
40 CurrentDb.Execute ("insert into tblsupervisor(Supervisor) select '" &
NewData & "'as exprl;")
nm1 = Me.[Supervisor]
stwhere = "nm1=supervisor"
MsgBox nm1
DoCmd.OpenForm "frmsupervisor", , , stwhere
50 Else
60 Response = acDataErrContinue
70 Me.Combo109.Undo
80 End If
end sub

Here's what it's doing. When I enter info in the combo box and it's not found
it does fire the not in list and adds the information to the table. What I
want it to do is to have it open a form and have it display the name I typed
in the combo box because there's other information I need to add. Before it
opens the form it asks for the parameter value of the nm1 string. After I
enter that then it will open the form with the parameter value. The msgbox
nm1 does not show the info in the combo box. It shows the previous name. The
field name is "Supervisor". For example:

I type Tom
The name before was George. The msgbox nm1 shows George. Not Tom.

I hope I've explained my self correctly.

Thanks for your help.

 

[Jaek]

Afrosheen:
Maybe the problem is that you're pulling the value for nm1 from the control
Me.[Supervisor], which hasn't actually been updated at that point (when the
not in list event is triggered). I'm guessing the control is maybe 'dirty'
but not updated... I haven't used the not in list event much, but it looks
like the NewData parameter is capturing the new value in the control,
correct? How about using that instead of going out to the form to get nm1?

 

[Afrosheen via AccessMonster.com]

Sorry Fellas, neither solution worked.
I keep getting syntax errors.
The combo109 is from the table called tblsupervisor. The control source is
called [supervisor]. It is being stored in the tblmain called Supervisor.

Some times there are new Supervisors to add so that's where "not in list"
comes in. You enter the name and if it doesn't find it in the tblSupervisor
table then it asks you if you want to add it to the list. I've used it before
and it's worked. The problem comes in to play because when there is a new
supervisor there are two more fields to add information in. They are called:
[emp] and [pos]. That's where I need the form to pop up, or become visible
and have the entered supervisor name show up then add the additional
information.

Thanks for the help. I hope I've explained it better. I'll keep trying.

Afrosheen:
Maybe the problem is that you're pulling the value for nm1 from the control
Me.[Supervisor], which hasn't actually been updated at that point (when the
not in list event is triggered). I'm guessing the control is maybe 'dirty'
but not updated... I haven't used the not in list event much, but it looks
like the NewData parameter is capturing the new value in the control,
correct? How about using that instead of going out to the form to get nm1?

Thanks for reading this.
Here is the code I'm using:

[quoted text clipped - 32 lines]

Thanks for your help.

 

[John W. Vinson]

The combo109 is from the table called tblsupervisor. The control source is
called [supervisor]. It is being stored in the tblmain called Supervisor.

I'm pretty sure that the NotInList event applies to FORMS, not to tables.

Combo boxes in tables are really, really limited. See
http://www.mvps.org/access/lookupfields.htm for a critique of this misfeature.

 

[Afrosheen via AccessMonster.com]

Thanks for your reply. I am using forms when I want the Not in List to fire.
It is on a combo box list. The combo109 is on a form and I'm just using the
table called tblSupervisor as the control source.

Thanks for your help.

BTW, Thanks for helping me with displaying the other information within the
table from a previous post.

The combo109 is from the table called tblsupervisor. The control source is
called [supervisor]. It is being stored in the tblmain called Supervisor.

I'm pretty sure that the NotInList event applies to FORMS, not to tables.

Combo boxes in tables are really, really limited. See
http://www.mvps.org/access/lookupfields.htm for a critique of this misfeature.

 

[Jaek]

Afrosheen -
Can you post the code where you tried using the NewData variable instead of
referring to the control on the form? Might be a learning experience for me.

Sorry Fellas, neither solution worked.
I keep getting syntax errors.
The combo109 is from the table called tblsupervisor. The control source is
called [supervisor]. It is being stored in the tblmain called Supervisor.

Some times there are new Supervisors to add so that's where "not in list"
comes in. You enter the name and if it doesn't find it in the tblSupervisor
table then it asks you if you want to add it to the list. I've used it before
and it's worked. The problem comes in to play because when there is a new
supervisor there are two more fields to add information in. They are called:
[emp] and [pos]. That's where I need the form to pop up, or become visible
and have the entered supervisor name show up then add the additional
information.

Thanks for the help. I hope I've explained it better. I'll keep trying.

Afrosheen:
Maybe the problem is that you're pulling the value for nm1 from the control
Me.[Supervisor], which hasn't actually been updated at that point (when the
not in list event is triggered). I'm guessing the control is maybe 'dirty'
but not updated... I haven't used the not in list event much, but it looks
like the NewData parameter is capturing the new value in the control,
correct? How about using that instead of going out to the form to get nm1?

Thanks for reading this.
Here is the code I'm using:

[quoted text clipped - 32 lines]

Thanks for your help.

 

[David H]

Why are you adding the value here using an insert? Why not open the form and
capture the data there?

Also, I always put the response statements at the end of the code blocks -
basically do what I need to do and then set the response.

I've added someother inline comments as well.

Thanks for reading this.
Here is the code I'm using:

Private Sub Combo109_NotInList(NewData As String, Response As Integer)
Dim strMsg, nm1, stwhere As String
10 strMsg = "That value is not in the list. Are you sure you want to add
it?"
20 If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbYes Then
30 Response = acDataErrAdded
40 CurrentDb.Execute ("insert into tblsupervisor(Supervisor) select '" &
NewData & "'as exprl;")
nm1 = Me.[Supervisor]
stwhere = "nm1=supervisor"

Don't you mean [stwhere = "nm1 =" & "'" & supervisor & "'"] ?

MsgBox nm1
DoCmd.OpenForm "frmsupervisor", , , stwhere

Does the form open as modal? If not the rest of the code will execute.

 

[Afrosheen via AccessMonster.com]

Good Morning or afternoon depending where you're at.

The code I was trying to work didn't. This is the code that did work.

Private Sub Combo109_NotInList(NewData As String, Response As Integer)
Dim strMsg, stwhere As String

strMsg = "'" & NewData & "' is not in the list. Are you sure you want
to add it?"
10 ' strMsg = "That value is not in the list. Are you sure you want to
add it?"
20 If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbYes Then
30 Response = acDataErrAdded
40 CurrentDb.Execute ("insert into tblsupervisor(Supervisor) select '" &
NewData & "'as exprl;")
'Me.frmSupervisor.Visible = True
DoCmd.OpenForm "frmsupervisor", , , "Supervisor = " & Chr(34) &
NewData & Chr(34)
50 Else
60 Response = acDataErrContinue
70 Me.Combo109.Undo
80 End If
End Sub

Opening up the form with the docmd works. What I'd like it to do is to unhide
it as a subform on the main form. I need more help with that.

Afrosheen -
Can you post the code where you tried using the NewData variable instead of
referring to the control on the form? Might be a learning experience for me.

Sorry Fellas, neither solution worked.
I keep getting syntax errors.

[quoted text clipped - 25 lines]

 

[John W. Vinson]

Thanks for your reply. I am using forms when I want the Not in List to fire.
It is on a combo box list. The combo109 is on a form and I'm just using the
table called tblSupervisor as the control source.

Sorry... misinterpreted the "combo is from the table" line.

Please post your actual NotInList event VBA code. You won't be able to use the
first-level trick of just adding the supervisor name to a table, but it's not
at all hard to open a Dialog form to add whatever other information is needed.

 

[John W. Vinson]

Good Morning or afternoon depending where you're at.

The code I was trying to work didn't. This is the code that did work.

Private Sub Combo109_NotInList(NewData As String, Response As Integer)
Dim strMsg, stwhere As String

Yuck. Rename Combo109 to cboSupervisor or SOMETHING meaningful! You'll be glad
you did when you need to look at your code six months from now.

strMsg = "'" & NewData & "' is not in the list. Are you sure you want
to add it?"
10 ' strMsg = "That value is not in the list. Are you sure you want to
add it?"
20 If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbYes Then
30 Response = acDataErrAdded
40 CurrentDb.Execute ("insert into tblsupervisor(Supervisor) select '" &
NewData & "'as exprl;")
'Me.frmSupervisor.Visible = True

Delete the Execute and Visible lines above.

DoCmd.OpenForm "frmsupervisor", , , "Supervisor = " & Chr(34) &
NewData & Chr(34)

Change the line above to

DoCmd.OpenForm "frmsupervisor", , , "Supervisor = " & Chr(34) &
NewData & Chr(34), , acDialog, NewData

This will open the form in "Dialog" mode, stopping execution of your code
until the user enters the needed supervisor data and closes the form.

In frmSupervisor's Open event put

Me!txtSupervisorName.DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)

to prefill the supervisor name textbox (obviously use your own control name).

50 Else
60 Response = acDataErrContinue
70 Me.Combo109.Undo
80 End If
End Sub

Opening up the form with the docmd works. What I'd like it to do is to unhide
it as a subform on the main form. I need more help with that.

A Subform would really not be appropriate here - for one thing you can't
"open" a Subform, it's a control already on the form.

 

[Afrosheen via AccessMonster.com]

Thanks for the help again. I'm sure glad this board is availible. It works
the way I wanted it to. John and I have been going around and around for
three almost four days now and I appreciate his patients with me. It just
gets frustrating.

BTW John, I also have a post for dlookup() in the que. I won't need it now.

Thanks again to everyone who's offered suggestions.

Good Morning or afternoon depending where you're at.

The code I was trying to work didn't. This is the code that did work.

Private Sub Combo109_NotInList(NewData As String, Response As Integer)
Dim strMsg, stwhere As String

Yuck. Rename Combo109 to cboSupervisor or SOMETHING meaningful! You'll be glad
you did when you need to look at your code six months from now.

strMsg = "'" & NewData & "' is not in the list. Are you sure you want
to add it?"

[quoted text clipped - 5 lines]

NewData & "'as exprl;")
'Me.frmSupervisor.Visible = True

Delete the Execute and Visible lines above.

DoCmd.OpenForm "frmsupervisor", , , "Supervisor = " & Chr(34) &
NewData & Chr(34)

Change the line above to

DoCmd.OpenForm "frmsupervisor", , , "Supervisor = " & Chr(34) &
NewData & Chr(34), , acDialog, NewData

This will open the form in "Dialog" mode, stopping execution of your code
until the user enters the needed supervisor data and closes the form.

In frmSupervisor's Open event put

Me!txtSupervisorName.DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)

to prefill the supervisor name textbox (obviously use your own control name).

50 Else
60 Response = acDataErrContinue

[quoted text clipped - 4 lines]

Opening up the form with the docmd works. What I'd like it to do is to unhide
it as a subform on the main form. I need more help with that.

A Subform would really not be appropriate here - for one thing you can't
"open" a Subform, it's a control already on the form.