ADO insert record into table

[Jeff]

Hi,
I am a form with a Listbox and a command button. When an user clicks on
the button an inputbox will appear to prompt for name of the new item to be
added into a table. Then it'll requery the listbox so it'll have the latest
entries. Here is the code for the cmdbtn:
Dim sRockName As String
Dim mySQL As String

sRockName = InputBox("New Rock ID", "Add Rock")
mySQL = "INSERT INTO ROCK (ROCKNAME)"
mySQL = mySQL + " VALUES ('" + sRockName + "')"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
ListRocks.Requery

The problem I am having is 1) when the warning is set to "on" the user will
get a prompt to confirm whether or not to add the new item. - it's kinda
annoying... 2) When I have the setting turn off.. the user won't see the
error when they enter a name that's already exist. Is it a way to get
around it?

Thanks

 

[JaRa]

Maybe this can help you. This code will also accept values with ' or "

- Raoul

dim cmdInsert as new ADODB.Command
dim cmdExists as new ADODB.Command
dim rs as ADODB.Recordset

cmdInsert.CommandText="INSERT INTO Rock (RockName) VALUES (?)"
cmdInsert.ActiveConnection=CurrentProject.Connection
cmdInsert.Parameters.Refresh
cmdExists.CommandText="SELECT Rockname FROM Rock WHERE RockName=?"
cmdExists.ActiveConnection=CurrentProject.Connection
cmdExists.Parameters.Refresh

sRockName = nz(InputBox("New Rock ID", "Add Rock"))
if sRockName<>"" then
cmdExists.Parameters(0)=sRockName
set rs=cmdExists.Execute
if rs.eof then
cmdInsert.Parameters(0)=sRockName
cmdInsert.Execute
ListRocks.Requery
else
' here you can put a message that the value was already entered
end if
rs.close
else
'here you can put a message when empty string was entered
end if
set rs=nothing
set cmdInsert=nothing
set cmdExists=nothing