ADO insert record into table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 17 Mar 2005 23:20 GMT

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

Reply to this Message

JaRa - 17 Mar 2005 23:35 GMT

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

> Hi,
> I am a form with a Listbox and a command button. When an user clicks on
[quoted text clipped - 19 lines]
>
> Thanks

Reply to this Message