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

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


"XPS35" <(E-Mail Removed)> wrote in message
news:hul32p$2cco$(E-Mail Removed)...
> Steve Goodrich wrote:
>
>> 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 Removed) ---

On 8 jun, 17:25, "Steve Goodrich" <stevegoodr...@btinternet.com>
wrote:
> 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
>
> "XPS35" <xps...@gmail.com> wrote in message
>
> news:hul32p$2cco$(E-Mail Removed)...
>
> > Steve Goodrich wrote:
>
> >> 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: n...@netfront.net ---

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

Many thanks, works perfectly now
Steve
"XPS350" <(E-Mail Removed)> wrote in message
news:71e03706-2a3d-47ce-a2ab-(E-Mail Removed)...
On 8 jun, 17:25, "Steve Goodrich" <stevegoodr...@btinternet.com>
wrote:
> 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
>
> "XPS35" <xps...@gmail.com> wrote in message
>
> news:hul32p$2cco$(E-Mail Removed)...
>
> > Steve Goodrich wrote:
>
> >> 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: n...@netfront.net ---

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