Having problems with NotInList Event

G

Guest

I have a main form that is unbound with an unbound combo box. The users
would like to be able to add a New CTAName to the list in the combo box. I
have tried to use the NotInList event, but something is not working.

Here is what is happening when testing:
1. After typing in a new CTA Name "Alpha Omega" it comes up with a message
"Do you want to ADD this to the Database? yes or no. I click yes and it
gives this:

2. "This item is not listed in the dropdown box" then it

3. Brings up the dropdown box waiting for the user to choose a name already
listed. NOT what I want. I want to be able to add the new CTA NAme of
Alpha Omega and all of it's info along with it.

Here is my code:


Private Sub CTAName_Combo_AfterUpdate()

Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[CTAName] = '" & Me![CTAName_Combo] & "'"

If rs.NoMatch = True Then
MsgBox "Selected CTA Name not found!"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing

End Sub


Private Sub CTAName_Combo_NotInList(NewData As String, Response As Integer)

Dim db As Database
Dim rs As Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new CTA Name?") = vbNo Then
DoCmd.OpenForm "frmMyPopup", , , , acDialog, NewData
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdRecordsGoToNew
On Error Resume Next
' add initial data
With Me.RecordsetClone
.Fields("CTAName") = NewData
.Fields("IDNumber") = Now
.Fields("CTAAddress") = Now
.Fields("CTACity") = Now
.Fields("CTAState") = Now
.Fields("CTAZip") = Now
.Fields("CTAPhone") = Now
.Fields("CTAFax") = Now
.Fields("CTAEmail") = Now
.Fields("CTAContactName1") = Now
.Fields("CTAContactPhone1") = Now
.Fields("CTAContactFax1") = Now
.Fields("CTAContactEmail") = Now
End With
' save data to table
Me.Dirty = False
' requery the combo box to include new server in dropdown list
Me.CTAName_Combo.Undo
Response = acDataErrContinue
Response = acDataErrAdded
End If

End Sub


I would appreciate any help.

Thanks
 
G

Guest

melwester,
there maybe something wrong w/ ur insert query. to add a "new" row, u
have to use insert value query
e.g.
INSERT INTO Table1 ( test, testDesc )
VALUES ([combobox1],[str2]);
 
G

Guest

Comments are inline.

melwester said:
I have a main form that is unbound with an unbound combo box. The users
would like to be able to add a New CTAName to the list in the combo box. I
have tried to use the NotInList event, but something is not working.

Here is what is happening when testing:
1. After typing in a new CTA Name "Alpha Omega" it comes up with a message
"Do you want to ADD this to the Database? yes or no. I click yes and it
gives this:

2. "This item is not listed in the dropdown box" then it

3. Brings up the dropdown box waiting for the user to choose a name already
listed. NOT what I want. I want to be able to add the new CTA NAme of
Alpha Omega and all of it's info along with it.

Here is my code:
Click to expand...

Private Sub CTAName_Combo_NotInList(NewData As String, Response As Integer)

Dim db As Database
Dim rs As Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new CTA Name?") = vbNo Then
Click to expand...

Do you really want vbNo as the response here? According to this, and
guessing that the frmMyPopup is how you do the actual data addition, since
Response is set to acDataErrAdded which means "The Data Was Added To The
Underlying Record Source For This Combo Box", it seems that you probably want
to receive a vbYes.
DoCmd.OpenForm "frmMyPopup", , , , acDialog, NewData
Response = acDataErrAdded
Click to expand...

This Else branch is executed if the user clicked OK.
Else
DoCmd.RunCommand acCmdRecordsGoToNew
On Error Resume Next
' add initial data
With Me.RecordsetClone
Click to expand...

What are all of these uses of 'Now'? Now is a function that returns the
current time and date. These don't look or sound like Date/Time fields.
.Fields("CTAName") = NewData
.Fields("IDNumber") = Now
.Fields("CTAAddress") = Now
.Fields("CTACity") = Now
.Fields("CTAState") = Now
.Fields("CTAZip") = Now
.Fields("CTAPhone") = Now
.Fields("CTAFax") = Now
.Fields("CTAEmail") = Now
.Fields("CTAContactName1") = Now
.Fields("CTAContactPhone1") = Now
.Fields("CTAContactFax1") = Now
.Fields("CTAContactEmail") = Now
End With
' save data to table
Click to expand...

Why are you setting Me.Dirty to False? The goal of the NotInList is to add a
value to the Combo Box data source; Dirty relates to whether anything has
changed on the entire Form.
Me.Dirty = False
' requery the combo box to include new server in dropdown list
Click to expand...

Why the Undo? What is this supposed to be accomplishing?
Me.CTAName_Combo.Undo
Click to expand...

Which of the following do you want? You're setting Response to two values.
Only the last will take. Since you set it to acDataErrAdded in the other
branch also, you've claimed to have added data to the list whether the user
said to add it or not.
Response = acDataErrContinue
Response = acDataErrAdded
End If

End Sub


I would appreciate any help.

Thanks
Click to expand...

Very confusing. Have you run this through the Debugger to see if what you
think is happening is actually happening? I think you'll be surprised. I'm
somewhat mystified that this runs without complaining. Where does any of the
above code actually save anything to a table? Everything is unbound, so you
state, so there has to be an explicit SQL command executed someplace to save
this data.

Good Luck...
 
G

Guest

Check your Combo box to see if the Limit_to_List property is set to Yes. May
be the problem.

I do this 'add to list' action by simply presenting the raw table of values
for the client to update in a separate form. The client updates the table
and when they close I do a DoCmd.Requery and all is ok. My DropDowns also can
be double clicked to do the same.
 
G

Guest

I'm still new at this not sure what is correct and what isn't.
What are all of these uses of 'Now'? Now is a function that returns the
current time and date. These don't look or sound like Date/Time fields.
Click to expand...

OK I took out all the "Now's" I had copied this from someone elses and I
really didn't understand it anyways.

Do you really want vbNo as the response here? According to this, and
guessing that the frmMyPopup is how you do the actual data addition, since
Response is set to acDataErrAdded which means "The Data Was Added To The
Underlying Record Source For This Combo Box", it seems that you probably want
to receive a vbYes.
Click to expand...

This is in all the examples I seen so I just copied the vbNo. My
understanding If they click "No" then they (the users) had changed their mind
about adding a new one.
This Else branch is executed if the user clicked OK.


Why are you setting Me.Dirty to False? The goal of the NotInList is to add a
value to the Combo Box data source; Dirty relates to whether anything has
changed on the entire Form.
Click to expand...

The combo box has 16 columns that also need to be updated.
Why the Undo? What is this supposed to be accomplishing?


Which of the following do you want? You're setting Response to two values.
Only the last will take. Since you set it to acDataErrAdded in the other
branch also, you've claimed to have added data to the list whether the user
said to add it or not.
Click to expand...

I took out the "Response = acDataErr Continue". Like I said at the top I'm
new to this and not sure what I'm suppose to put in.
Very confusing. Have you run this through the Debugger to see if what you
think is happening is actually happening? I think you'll be surprised. I'm
somewhat mystified that this runs without complaining. Where does any of the
above code actually save anything to a table? Everything is unbound, so you
state, so there has to be an explicit SQL command executed someplace to save
this data.
Click to expand...
Yes - I did run it through the debugger and no errors showed. So I figured
all is fine.

I was told that in order to view the data by pulling up the info from a
combo box it had to be unbound. Now you're telling me I have to change
everything back to bound? Which is it?
 
G

Guest

Yes the "Limit_to_list" was set to Yes. When I tried to change it to No it
came up with an error about my column widths.

How do you "add to list" with a table?

tynerr said:
Check your Combo box to see if the Limit_to_List property is set to Yes. May
be the problem.

I do this 'add to list' action by simply presenting the raw table of values
for the client to update in a separate form. The client updates the table
and when they close I do a DoCmd.Requery and all is ok. My DropDowns also can
be double clicked to do the same.



melwester said:
I have a main form that is unbound with an unbound combo box. The users
would like to be able to add a New CTAName to the list in the combo box. I
have tried to use the NotInList event, but something is not working.

Here is what is happening when testing:
1. After typing in a new CTA Name "Alpha Omega" it comes up with a message
"Do you want to ADD this to the Database? yes or no. I click yes and it
gives this:

2. "This item is not listed in the dropdown box" then it

3. Brings up the dropdown box waiting for the user to choose a name already
listed. NOT what I want. I want to be able to add the new CTA NAme of
Alpha Omega and all of it's info along with it.

Here is my code:


Private Sub CTAName_Combo_AfterUpdate()

Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[CTAName] = '" & Me![CTAName_Combo] & "'"

If rs.NoMatch = True Then
MsgBox "Selected CTA Name not found!"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing

End Sub


Private Sub CTAName_Combo_NotInList(NewData As String, Response As Integer)

Dim db As Database
Dim rs As Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new CTA Name?") = vbNo Then
DoCmd.OpenForm "frmMyPopup", , , , acDialog, NewData
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdRecordsGoToNew
On Error Resume Next
' add initial data
With Me.RecordsetClone
.Fields("CTAName") = NewData
.Fields("IDNumber") = Now
.Fields("CTAAddress") = Now
.Fields("CTACity") = Now
.Fields("CTAState") = Now
.Fields("CTAZip") = Now
.Fields("CTAPhone") = Now
.Fields("CTAFax") = Now
.Fields("CTAEmail") = Now
.Fields("CTAContactName1") = Now
.Fields("CTAContactPhone1") = Now
.Fields("CTAContactFax1") = Now
.Fields("CTAContactEmail") = Now
End With
' save data to table
Me.Dirty = False
' requery the combo box to include new server in dropdown list
Me.CTAName_Combo.Undo
Response = acDataErrContinue
Response = acDataErrAdded
End If

End Sub


I would appreciate any help.

Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList Firing Problem 12
NotInList not firing ? 1
combobox, NotInList, update form 2
Need help with this NotInList code for a combo box 4
NotinList problem 7
NotInList Problem 3
NotInList Issue 1
Syntax (Missing Operator) Error 2

Top