Coding Questions

G

Guest

I am currently trying to add this code to the after update of my combo box -
What I want the code to do is allow the user to use a name that is not listed
in the Reroute Contacts table - and then they can have the option to add the
name to the original table. I don't know if I am explaining this very well
or not. Here is the code that I have - when I try to run the code it stops
at the End Sub part. I am not sure what I am doing wrong. Any help would be
greatly appreciated.

Private Sub cboRerouteContacts_AfterUpdate()
Dim intAnswer As Integer
intAnswer = MsgBox("The Reroute Contact " & Chr(34) & strNewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNoCancel, "Reroute Contacts")
Dim strSQL As String
If Statement Then
If vbYes Then
' Allow entry and add new item to the list
strSQL = "INSERT INTO Reroute Contacts ([Reroute Contacts]) " & _
"VALUES ('" & strNewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
blnNewListItem = True
MsgBox "The new contact has been added to the list." _
, vbInformation, "Reroute Contacts"
If vbNo Then
'Allow entry but do not add item to list
'No action necessary
If vbCancel Then
'Do not allow entry
MsgBox "Please choose a contact from the list.", _
vbExclamation, "Reroute Contacts"
Me.cboRerouteContacts.Undo
Me.cboRerouteContacts.Dropdown
Cancel = True
End If
End If
End If

End Sub
 
A

Albert D. Kallal

You need to use the comb box not in list event, NOT the after update
event...

So,


You can set the limit to yes = to yes.

The, for the no in list event, you can use the following code:


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

Dim strSql As String
If MsgBox(NewData & " not in list, add?", _
vbYesNo + vbQuestion) = vbYes Then
strSql = "insert into tblStudents (name) values('" & NewData & "')"
CurrentDb.Execute strSql
Response = acDataErrAdded
End If

End Sub

Note I used a table name of Students, and field name of Sname. So, just
change the table name, and the field to whatever you used.

Setting the Response as above will force ms-access to re-load the comb box,
and also tells ms-access that you just
add a new entry..and thus the current comb box entry is to be allowed...

so, the "trick" in making this whole thing work is

Response = acDataErrAdded
 
G

Guest

When I type something other then what is listed in the combo box, it lets it
go in there but it does not show up in the table that I am adding it to. The
way that I have the table set up is it is based on a history table. I have a
command button that once the button is pushed a macro run appending that
record to a different table and then a delete query is ran to delete it out
of that table.
 
A

Albert D. Kallal

You need to set the combo box limit to list = yes.

The above will ONLY allow a user to enter values from the list, if they type
something new, then the
not in list event will fire....

Your original question is not 100% clear, but I am working on the assuming
that if the user types something in that is NOT in the current list, then
they are given a prompt/chance to enter this new value....
 
G

Guest

Ok, I have put the following code in the Not in List section but when i type
in something that is not in the list it is stating that the name is not on
the list and making me choose one from the list - I want them to have the
option to add the item or to be able to just use this contact for 1 time only

Private Sub cboRRContacts_NotInList(NewData As String, Response As Integer)
Dim strSql As String
If MsgBox(NewData & " not in list, add?", _
vbYesNo + vbQuestion) = vbYes Then
strSql = "insert into tblRRContacts (name) values('" & NewData & "')"
CurrentDb.Execute strSql
Response = acDataErrAdded
End If

End Sub
 
G

Guest

Ok ignore what I just sent - It is giving me the option to add but what if I
just want to have this contact used as a 1 time thing and not to be added to
the list?
 
A

Albert D. Kallal

Ok ignore what I just sent - It is giving me the option to add but what if
I
just want to have this contact used as a 1 time thing and not to be added
to
the list?

ah..yes...that is why my spider sense stated that you are not 100% clear
here....

Of course, if you use not in list..and add it to that "list", then it has
been added,
and thus will be available in the future for all new selections.

So, the given code solution is ONLY for the above case..and not what you
now (more clearly) explained.

It is not clear what type of choices you will now give the user
(opps...made a mistake...don't add)
(yes....I want to add)
(allow, but don't add to the list).

It gets a little messy. However, if you just ask yes if the user wants to
add (and ignore the other possibility outcomes), then use the after
update event, and simply search the
table for the value.... (this means we remove the limit to list..and
the not in list code).

just use in after update..

Dim strSql As String


If DCount("name", "tblRRContacts ", _
"name = '" & Me.Combo26 & "'") = 0 Then
If MsgBox("this value is not in the list...add it?", _
vbQuestion + vbYesNo) = vbYes Then
strSql = "insert into tblRRContacts (name) values('" & _
Me.Combo26 & "')"
CurrentDb.Execute strSql
Me.Combo26.Requery
End If
End If
 

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

Top