Modify record

I need to modify a record but before I do that I need to check and see
if the record exist in the table. If it does than I need to modify the
record and if it does not than basically add new record. When I try to
run the code below I get error message for line "x=DCount("*",......"
saying "Type mismatch" can someone please help me fix this code. Also,
if someone can help me finish the "if" statement, it would be really
appreciated.

I am new to access world and really need all the help I can get.


Function RecordTest()

Dim x as string

'CPAData is the table
'Date is a field in the record, Date/ Timei.e. 1/1/2007
'Cleaners is text field, i.e. "One Stop Cleaning"

x = DCount("*", "[CPAData]", "[Date]=#" & Me.[txtDate] & "#" And
[Cleaners] = """ & Me.txtCleaners & """)

if x = 0 then

'add new record at the end of the table

else

'modify the existing record

endif


end function


Thanks in advance for all your help!

Hi,

There is a problem with your dcount line... Try this one:

DCount("*", "[CPAData]", "[Date]=#" & Me.[txtDate] & "# And
[Cleaners] = '" & Me.txtCleaners & "'")

Inside your if, you want to use an insert statement if the record doesn't
exist, and an update statement if it does... like this:

insert into TABLENAME (FIELD1, FIELD2) values (VALUE1, VALUE2)

or

update TABLENAME set FIELD1 = VALUE1, FIELD2 = VALUE2 where IDFIELD = IDNUMBER

Good luck with it!!

Damian.




" wrote:

I need to modify a record but before I do that I need to check and see
if the record exist in the table. If it does than I need to modify the
record and if it does not than basically add new record. When I try to
run the code below I get error message for line "x=DCount("*",......"
saying "Type mismatch" can someone please help me fix this code. Also,
if someone can help me finish the "if" statement, it would be really
appreciated.

I am new to access world and really need all the help I can get.


Function RecordTest()

Dim x as string

'CPAData is the table
'Date is a field in the record, Date/ Timei.e. 1/1/2007
'Cleaners is text field, i.e. "One Stop Cleaning"

x = DCount("*", "[CPAData]", "[Date]=#" & Me.[txtDate] & "#" And
[Cleaners] = """ & Me.txtCleaners & """)

if x = 0 then

'add new record at the end of the table

else

'modify the existing record

endif


end function


Thanks in advance for all your help!

thanks man!

Damian S wrote:
Hi,

There is a problem with your dcount line... Try this one:

DCount("*", "[CPAData]", "[Date]=#" & Me.[txtDate] & "# And
[Cleaners] = '" & Me.txtCleaners & "'")

Inside your if, you want to use an insert statement if the record doesn't
exist, and an update statement if it does... like this:

insert into TABLENAME (FIELD1, FIELD2) values (VALUE1, VALUE2)

or

update TABLENAME set FIELD1 = VALUE1, FIELD2 = VALUE2 where IDFIELD = IDNUMBER

Good luck with it!!

Damian.




" wrote:

I need to modify a record but before I do that I need to check and see
if the record exist in the table. If it does than I need to modify the
record and if it does not than basically add new record. When I try to
run the code below I get error message for line "x=DCount("*",......"
saying "Type mismatch" can someone please help me fix this code. Also,
if someone can help me finish the "if" statement, it would be really
appreciated.

I am new to access world and really need all the help I can get.


Function RecordTest()

Dim x as string

'CPAData is the table
'Date is a field in the record, Date/ Timei.e. 1/1/2007
'Cleaners is text field, i.e. "One Stop Cleaning"

x = DCount("*", "[CPAData]", "[Date]=#" & Me.[txtDate] & "#" And
[Cleaners] = """ & Me.txtCleaners & """)

if x = 0 then

'add new record at the end of the table

else

'modify the existing record

endif


end function


Thanks in advance for all your help!