add to drop down list

[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

 

[XPS35]

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: (e-mail address removed) ---

 

[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

 

[XPS350]

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

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]

Many thanks, works perfectly now
Steve

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

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