Prev: Email subform contents from main form
Next: Issue creating sql authentication odbc system dsn
From: LightByrd on 9 Mar 2010 16:34
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




From: bhicks11 via AccessMonster.com on 9 Mar 2010 17:21
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

From: LightByrd on 10 Mar 2010 16:55
"bhicks11 via AccessMonster.com" <u44327(a)uwe> wrote in message
news:a4c510d8966f9(a)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


From: Stefan Hoffmann on 11 Mar 2010 03:44
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 <--

From: Douglas J. Steele on 11 Mar 2010 09:14
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(a)noway.invalid.com> wrote in message
news:OV7H7xJwKHA.812(a)TK2MSFTNGP06.phx.gbl...
> "bhicks11 via AccessMonster.com" <u44327(a)uwe> wrote in message
> news:a4c510d8966f9(a)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
>


 |  Next  |  Last
Pages: 1 2
Prev: Email subform contents from main form
Next: Issue creating sql authentication odbc system dsn