Modify record

  • Thread starter Thread starter insideout786
  • Start date Start date
I

insideout786

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 said:
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.




[email protected] said:
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!
Click to expand...
Click to expand...
 
Back
Top