Hello....
In a victim services database, there is a field for the name of an attending 
police officer.
To keep the name format consistent, the drop down box is populated by a
table using an SQL statement in the rowsource property.
What I want to do is for the user to be able to add another name using the 
drop down box directly.
I have set *limit to list* to NO, and it will modify the record, but it does
not add the new name to the table itself.
How might I do this.  Perhaps some VB code in the afterupdate event?
Thanks

-- 
Regards,
Richard Harison

http://blogs.techrepublic.com.com/howdoi/?p=201

Bonnie
http://www.dataplus-svc.com

LightByrd wrote:
>Hello....
>In a victim services database, there is a field for the name of an attending 
>police officer.
>To keep the name format consistent, the drop down box is populated by a
>table using an SQL statement in the rowsource property.
>What I want to do is for the user to be able to add another name using the 
>drop down box directly.
>I have set *limit to list* to NO, and it will modify the record, but it does
>not add the new name to the table itself.
>How might I do this.  Perhaps some VB code in the afterupdate event?
>Thanks
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1

"bhicks11 via AccessMonster.com" <u44327@uwe> wrote in message 
news:a4c510d8966f9@uwe...
> http://blogs.techrepublic.com.com/howdoi/?p=201
>
> Bonnie
> http://www.dataplus-svc.com
>
> LightByrd wrote:
>>Hello....
>>In a victim services database, there is a field for the name of an 
>>attending
>>police officer.
>>To keep the name format consistent, the drop down box is populated by a
>>table using an SQL statement in the rowsource property.
>>What I want to do is for the user to be able to add another name using the
>>drop down box directly.
>>I have set *limit to list* to NO, and it will modify the record, but it 
>>does
>>not add the new name to the table itself.
>>How might I do this.  Perhaps some VB code in the afterupdate event?
>>Thanks
>>
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
>

Thank you , Bonnie,
But there is still a problem.
The code on the page to which you referred me has this line

Dim cnn As New ADODB.Connection

It then has me create  (in strSQL VARIABLE) an INSERT INTO SQL statement 
which updates the lookup table for the combination box
It executes that SQL statement using the line:

cnn.Execute strSQL

But I get an "user defined type not defined: error.
I thought that was what the DIM statement did

Any answers?
I am running Access XP (2002)
Thanks
-- 
Regards,
Richard Harison 

hi,

On 09.03.2010 22:34, LightByrd wrote:.
> How might I do this.  Perhaps some VB code in the afterupdate event?

   http://msdn.microsoft.com/en-us/library/bb243765.aspx

You may take also a look at this simple to use class module:

   http://www.mosstools.de/download/mossNewComboEntry.zip

The description in German:

 
http://www.mosstools.de/index.php?option=com_content&view=article&id=105&Itemid=85


mfG
--> stefan <--

Sounds as though you don't have a reference set to ADO (or else there's a 
problem with your References collection).

While in the VB Editor, select Tools | References from the menu bar.

Make sure you've got a Reference set to Microsoft ActiveX Data Objects 2.x 
Library. (Actually, if any of the selected References (the ones with check 
marks at the top of the list) have MISSING: in front of them, you do have a 
problem...)

However, there's no need to use ADO for what that sample is doing.

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

  'Allow user to save non-list items.

  Dim strSQL As String
  Dim bytUpdate As Byte

  On Error GoTo ErrHandler

  bytUpdate = MsgBox("Do you want to add " & _
   cboMetals.Value & " to the list?", _
   vbYesNo, "Non-list item!")

  If bytUpdate = vbYes Then
    strSQL = "INSERT INTO tblMetals(Metals) " & _
     "VALUES ('" & _
     NewData & _
     "')"

    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError

    Response = acDataErrAdded

  Else
    Response = acDataErrContinue
    Me!cboMetals.Undo
  End If

AllDone:
  Exit Sub

ErrHandler:
  MsgBox Err.Number & ": " & Err.Description, _
   vbOKOnly, "Error"
  Resume AllDone

End Sub


-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"LightByrd" <rh@noway.invalid.com> wrote in message 
news:OV7H7xJwKHA.812@TK2MSFTNGP06.phx.gbl...
> "bhicks11 via AccessMonster.com" <u44327@uwe> wrote in message 
> news:a4c510d8966f9@uwe...
>> http://blogs.techrepublic.com.com/howdoi/?p=201
>>
>> Bonnie
>> http://www.dataplus-svc.com
>>
>> LightByrd wrote:
>>>Hello....
>>>In a victim services database, there is a field for the name of an 
>>>attending
>>>police officer.
>>>To keep the name format consistent, the drop down box is populated by a
>>>table using an SQL statement in the rowsource property.
>>>What I want to do is for the user to be able to add another name using 
>>>the
>>>drop down box directly.
>>>I have set *limit to list* to NO, and it will modify the record, but it 
>>>does
>>>not add the new name to the table itself.
>>>How might I do this.  Perhaps some VB code in the afterupdate event?
>>>Thanks
>>>
>>
>> -- 
>> Message posted via AccessMonster.com
>> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
>>
>
> Thank you , Bonnie,
> But there is still a problem.
> The code on the page to which you referred me has this line
>
> Dim cnn As New ADODB.Connection
>
> It then has me create  (in strSQL VARIABLE) an INSERT INTO SQL statement 
> which updates the lookup table for the combination box
> It executes that SQL statement using the line:
>
> cnn.Execute strSQL
>
> But I get an "user defined type not defined: error.
> I thought that was what the DIM statement did
>
> Any answers?
> I am running Access XP (2002)
> Thanks
> -- 
> Regards,
> Richard Harison
> 

Thank you Doug!
Worked like a charm!
I changed the MsgBox to
"Do you want to add " & NewData & " to the List?

As for ADO, I am so glad you showed me another way!  I don't really 
understand ADO and this program gets used by sites that are running 2002, 
2003, and 2007.  I was really afraid to add a reference that might be 
different in different versions.
Thanks again

-- 
Regards,
Richard Harison

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message 
news:e91odUSwKHA.2436@TK2MSFTNGP04.phx.gbl...
> Sounds as though you don't have a reference set to ADO (or else there's a 
> problem with your References collection).
>
> While in the VB Editor, select Tools | References from the menu bar.
>
> Make sure you've got a Reference set to Microsoft ActiveX Data Objects 2.x 
> Library. (Actually, if any of the selected References (the ones with check 
> marks at the top of the list) have MISSING: in front of them, you do have 
> a problem...)
>
> However, there's no need to use ADO for what that sample is doing.
>
> Private Sub cboMetals_NotInList(NewData As String, Response As Integer)
>
>  'Allow user to save non-list items.
>
>  Dim strSQL As String
>  Dim bytUpdate As Byte
>
>  On Error GoTo ErrHandler
>
>  bytUpdate = MsgBox("Do you want to add " & _
>   cboMetals.Value & " to the list?", _
>   vbYesNo, "Non-list item!")
>
>  If bytUpdate = vbYes Then
>    strSQL = "INSERT INTO tblMetals(Metals) " & _
>     "VALUES ('" & _
>     NewData & _
>     "')"
>
>    Debug.Print strSQL
>    CurrentDb.Execute strSQL, dbFailOnError
>
>    Response = acDataErrAdded
>
>  Else
>    Response = acDataErrContinue
>    Me!cboMetals.Undo
>  End If
>
> AllDone:
>  Exit Sub
>
> ErrHandler:
>  MsgBox Err.Number & ": " & Err.Description, _
>   vbOKOnly, "Error"
>  Resume AllDone
>
> End Sub
>
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "LightByrd" <rh@noway.invalid.com> wrote in message 
> news:OV7H7xJwKHA.812@TK2MSFTNGP06.phx.gbl...
>> "bhicks11 via AccessMonster.com" <u44327@uwe> wrote in message 
>> news:a4c510d8966f9@uwe...
>>> http://blogs.techrepublic.com.com/howdoi/?p=201
>>>
>>> Bonnie
>>> http://www.dataplus-svc.com
>>>
>>> LightByrd wrote:
>>>>Hello....
>>>>In a victim services database, there is a field for the name of an 
>>>>attending
>>>>police officer.
>>>>To keep the name format consistent, the drop down box is populated by a
>>>>table using an SQL statement in the rowsource property.
>>>>What I want to do is for the user to be able to add another name using 
>>>>the
>>>>drop down box directly.
>>>>I have set *limit to list* to NO, and it will modify the record, but it 
>>>>does
>>>>not add the new name to the table itself.
>>>>How might I do this.  Perhaps some VB code in the afterupdate event?
>>>>Thanks
>>>>
>>>
>>> -- 
>>> Message posted via AccessMonster.com
>>> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
>>>
>>
>> Thank you , Bonnie,
>> But there is still a problem.
>> The code on the page to which you referred me has this line
>>
>> Dim cnn As New ADODB.Connection
>>
>> It then has me create  (in strSQL VARIABLE) an INSERT INTO SQL statement 
>> which updates the lookup table for the combination box
>> It executes that SQL statement using the line:
>>
>> cnn.Execute strSQL
>>
>> But I get an "user defined type not defined: error.
>> I thought that was what the DIM statement did
>>
>> Any answers?
>> I am running Access XP (2002)
>> Thanks
>> -- 
>> Regards,
>> Richard Harison
>>
>
>

Thanks, Stefan!
Your link gave me some additional insight, tho my German is non-existent!
Thanks

-- 
Regards,
Richard Harison

"Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
news:OyeTbcPwKHA.3408@TK2MSFTNGP06.phx.gbl...
> hi,
>
> On 09.03.2010 22:34, LightByrd wrote:.
>> How might I do this.  Perhaps some VB code in the afterupdate event?
>
>   http://msdn.microsoft.com/en-us/library/bb243765.aspx
>
> You may take also a look at this simple to use class module:
>
>   http://www.mosstools.de/download/mossNewComboEntry.zip
>
> The description in German:
>
>
> http://www.mosstools.de/index.php?option=com_content&view=article&id=105&Itemid=85
>
>
> mfG
> --> stefan <--
>

hi,

On 11.03.2010 17:49, LightByrd wrote:
> Your link gave me some additional insight, tho my German is non-existent!
Quite easy, "Prost!" when you're having a drink, and "Danke" for saying 
thanks..


mfG
--> stefan <--