Adding to Table from Drop Down Box

[LightByrd]

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

 

[LightByrd]

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

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

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

 

[Stefan Hoffmann]

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 <--

 

[Douglas J. Steele]

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

 

[LightByrd]

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

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

 

[LightByrd]

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

 

[Stefan Hoffmann]

hi,

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 <--