Discussion:
add to drop down list
(too old to reply)
Steve Goodrich
2010-06-07 21:27:28 UTC
Permalink
I have a table listing departments which I use to populate a drop down combo
box on my form - property set to limit to list

What is the best way for the user to add a new dept to this list without
going to the table?

Steve
XPS35
2010-06-08 09:38:34 UTC
Permalink
Post by Steve Goodrich
I have a table listing departments which I use to populate a drop down combo
box on my form - property set to limit to list
What is the best way for the user to add a new dept to this list without
going to the table?
Steve
You can use the not-in-list even. It looks something like:

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

If MsgBox("This dept is not in the list." & vbCrLf & vbCrLf & "Add it?",
vbYesNo, "Unknown dept") = vbYes Then
DoCmd.RunSQL "INSERT INTO Dept(Dept) VALUES('" & NewData & "')"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
--
Groeten,

Peter
http://access.xps350.com


--- news://freenews.netfront.net/ - complaints: ***@netfront.net ---
Steve Goodrich
2010-06-08 15:25:03 UTC
Permalink
Thanks for your reply,

I pasted the text into the not on list event but the 2 lines below were in
red and when I tried to add a new dept on my form I got a syntax error
message and it pointed back to the lines which were in red.

If MsgBox("This dept is not in the list." & vbCrLf & vbCrLf & "Add it?",
vbYesNo, "Unknown dept") = vbYes Then

have you any idea what the error could be, I have no experience with vba
many thanks
Steve
Post by XPS35
Post by Steve Goodrich
I have a table listing departments which I use to populate a drop down combo
box on my form - property set to limit to list
What is the best way for the user to add a new dept to this list without
going to the table?
Steve
Private Sub Dept_NotInList(NewData As String, Response As Integer)
If MsgBox("This dept is not in the list." & vbCrLf & vbCrLf & "Add it?",
vbYesNo, "Unknown dept") = vbYes Then
DoCmd.RunSQL "INSERT INTO Dept(Dept) VALUES('" & NewData & "')"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
--
Groeten,
Peter
http://access.xps350.com
XPS350
2010-06-08 15:36:35 UTC
Permalink
Post by Steve Goodrich
Thanks for your reply,
I pasted the text into the not on list event but the 2 lines below were in
red and when I tried to add a new dept on my form I got a syntax error
message and it pointed back to the lines which were in red.
If MsgBox("This dept is not in the list." & vbCrLf & vbCrLf & "Add it?",
vbYesNo, "Unknown dept") = vbYes Then
have you any idea what the error could be, I have no experience with vba
many thanks
Steve
Post by XPS35
Post by Steve Goodrich
I have a table listing departments which I use to populate a drop down combo
box on my form - property set to limit to list
What is the best way for the user to add a new dept to this list without
going to the table?
Steve
Private Sub Dept_NotInList(NewData As String, Response As Integer)
If MsgBox("This dept is not in the list." & vbCrLf & vbCrLf & "Add it?",
vbYesNo, "Unknown dept") = vbYes Then
   DoCmd.RunSQL "INSERT INTO Dept(Dept) VALUES('" & NewData & "')"
   Response = acDataErrAdded
Else
   Response = acDataErrContinue
End If
End Sub
--
Groeten,
Peter
http://access.xps350.com
The 2 lines (If ... Then) must be 1 line in the code. Long lines
like this do not fit in messages in this group.

Groeten,

Peter
http://access.xps350.com
Steve Goodrich
2010-06-08 22:04:23 UTC
Permalink
Many thanks, works perfectly now
Steve
Post by Steve Goodrich
Thanks for your reply,
I pasted the text into the not on list event but the 2 lines below were in
red and when I tried to add a new dept on my form I got a syntax error
message and it pointed back to the lines which were in red.
If MsgBox("This dept is not in the list." & vbCrLf & vbCrLf & "Add it?",
vbYesNo, "Unknown dept") = vbYes Then
have you any idea what the error could be, I have no experience with vba
many thanks
Steve
Post by XPS35
Post by Steve Goodrich
I have a table listing departments which I use to populate a drop down combo
box on my form - property set to limit to list
What is the best way for the user to add a new dept to this list without
going to the table?
Steve
Private Sub Dept_NotInList(NewData As String, Response As Integer)
If MsgBox("This dept is not in the list." & vbCrLf & vbCrLf & "Add it?",
vbYesNo, "Unknown dept") = vbYes Then
DoCmd.RunSQL "INSERT INTO Dept(Dept) VALUES('" & NewData & "')"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
--
Groeten,
Peter
http://access.xps350.com
The 2 lines (If ... Then) must be 1 line in the code. Long lines
like this do not fit in messages in this group.

Groeten,

Peter
http://access.xps350.com

Loading...