how do i write it in access. my tab

[RosieGp]

how do i write it in access. my table is in access... and i'm using coldfusion (CFM) to call this query.

I tried the following but it does not work: the record is added to Database even if the ID exists.

<cfquery name="GetData" datasource="MainDB">
SELECT * FROM NewUsersTable
<cfif IsDefined("form.ID") and "form.ID" eq "NewUsersTable
.ID">

<cfquery name="updatedata" datasource="MainDB">
UPDATE newUsersTable 
SET UserAddress = #session.UserAddress#
</cfquery>

<cfelse>
<cfquery name="insertdata" datasource="MainDB">    INSERT INTO newUsersTable (NewUserTodayDate, UserID, UserFirstName, UserLastName, UserAddress , UserPhoneNumber, UserComments)
    VALUES
    ('#DATEFORMAT(Now(), "mm/dd/yyyy")#', '#form.UserID#', '#form.UserFirstName#', '#form.UserLastName#', '#form.UserAddress#', '#form.UserPhoneNumber#', '#form.serComments#') 
</cfquery>
</cfif>
</cfquery>

Any help is appreciated. Thanks.

 

[dhookom]

I think you first need to query the newUserstable to see if the UserID exists. Then use <cfIf> with the recordcount to determine if your query should be an insert or update.

Duane
Hook'D on Access
MS Access MVP