If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Modify record
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! |
#3
|
|||
|
|||
Modify record
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! |
Thread Tools | |
Display Modes | |
|
|