By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,066 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,066 IT Pros & Developers. It's quick & easy.

Prevent Duplication Of Records In Access With VB!

P: 27
I have 5 fields in my access table date,typeofexpenses,discription,amount,paymentmode . Now the problem is that i need to define a record by two fields-date and typeofexpenses. If the user tries to add a record which has both the date and typeofexpense field coinciding with a particular record in the database, then a msgbox should be displayed. Otherwise if only the date or only the typeofexpenses field is found coinciding, then the record should be added to the database. Pls Help Me Get A solution for this as i have been digging in this one for the past 1 week!!!!!!!
Please reply someone!!!!!!!!!!!!
Apr 23 '07 #1
Share this Question
Share on Google+
10 Replies


P: 47
I have 5 fields in my access table date,typeofexpenses,discription,amount,paymentmode . Now the problem is that i need to define a record by two fields-date and typeofexpenses. If the user tries to add a record which has both the date and typeofexpense field coinciding with a particular record in the database, then a msgbox should be displayed. Otherwise if only the date or only the typeofexpenses field is found coinciding, then the record should be added to the database. Pls Help Me Get A solution for this as i have been digging in this one for the past 1 week!!!!!!!
Please reply someone!!!!!!!!!!!!
hi
the fields r date,typeofexpenses,discription,amount,paymentmode .

Expand|Select|Wrap|Line Numbers
  1. Dim rsTemp As New ADODB.Recordset
  2.  Dim conTemp As New ADODB.Connection
  3.  
  4.  If rsTemp.State = adStateOpen Then rsTemp.Close
  5. ' if u using SQL Server then replace "# " by  " ' "
  6. ' I dont know the data type of typeofexpences i assume its char type
  7. 'if it is numeric the replace " ' " by "" 
  8.  rsTemp.Open "Select * from [TABLE NAME] where date=#" & format(txt1.Text,"dd/mmm/yyyy") & "# and typeofexpenses='" & txttypeofindex.Text & "'", conTemp, adOpenDynamic, adLockOptimistic
  9.  If rsTemp.RecordCount > 0 Then
  10.   MsgBox "The Record is Already Exist", vbOKOnly + vbInformation
  11.   Exit Sub
  12.  Else
  13.    rsTemp.AddNew
  14.    'code to add item
  15.    'code  to add item
  16.    rsTemp.Update
  17.  
  18.  End If
  19.  
try it
Apr 23 '07 #2

P: 27
thanx dude, it was really helpful
Apr 25 '07 #3

P: 27
Actually im having another recordset "Exp" for adding which i have defined it in a Module and a connection DB. So should i now replace the rsTemp and connTemp that u have specified by my Exp and DB. Moreover i have not Set my recordset as ADODB.recordset but simply New Recordset.

Please Explain!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Apr 25 '07 #4

hariharanmca
100+
P: 1,977
hi
the fields r date,typeofexpenses,discription,amount,paymentmode .

Expand|Select|Wrap|Line Numbers
  1. Dim rsTemp As New ADODB.Recordset
  2.  Dim conTemp As New ADODB.Connection
  3.  
  4.  If rsTemp.State = adStateOpen Then rsTemp.Close
  5. ' if u using SQL Server then replace "# " by  " ' "
  6. ' I dont know the data type of typeofexpences i assume its char type
  7. 'if it is numeric the replace " ' " by "" 
  8.  rsTemp.Open "Select * from [TABLE NAME] where date=#" & format(txt1.Text,"dd/mmm/yyyy") & "# and typeofexpenses='" & txttypeofindex.Text & "'", conTemp, adOpenDynamic, adLockOptimistic
  9.  If rsTemp.RecordCount > 0 Then
  10.   MsgBox "The Record is Already Exist", vbOKOnly + vbInformation
  11.   Exit Sub
  12.  Else
  13.    rsTemp.AddNew
  14.    'code to add item
  15.    'code  to add item
  16.    rsTemp.Update
  17.  
  18.  End If
  19.  
try it
Expand|Select|Wrap|Line Numbers
  1. rsTemp.Open "Select * from [TABLE NAME] where date=#" & format(txt1.Text,"dd/mmm/yyyy") & "# and typeofexpenses='" & txttypeofindex.Text & "'", conTemp, adOpenDynamic, adLockOptimistic
In this qry it is not good to pass date format as dd/MM/yyyy

i should be MM/dd/yyyy or yyyy/MM/dd
Apr 25 '07 #5

P: 27
Thanx I got it,U helped me a lot
this was my code

Expand|Select|Wrap|Line Numbers
  1. Dim rsExp As New ADODB.Recordset
  2.        Dim rsDB As New ADODB.Connection
  3.        Set rsExp = New Recordset
  4.        Set DB = New Connection
  5.        rsDB.CursorLocation = adUseClient
  6.        rsDB.Open "PROVIDER=Microsoft.jet.OLeDB.3.51;Data Source=" & App.Path & "\GasDb.mdb;"
  7.         If rsExp.State = adStateOpen Then rsExp.Close
  8.            rsExp.Open "Select * from [expenses] where cDate=#" & Format(CDtpick.Value, "mm/dd/yyyy") & "# and TypeOfExpenses='" & CExpType.Text & "'", rsDB, adOpenDynamic, adLockOptimistic
  9.              If rsExp.RecordCount > 0 Then
  10.                 MsgBox "The Record Already Exist, Please Check!", vbOKOnly + vbInformation
  11.                 Exit Sub
  12.              Else
  13. Exp.AddNew
  14.  
  15.        Exp.Fields("cDate") = UCase(CDtpick.Value)
  16.        Exp.Fields("TypeOfExpenses") = UCase(CExpType.Text)
  17.        Exp.Fields("Description") = UCase(CDescription.Text)
  18.        Exp.Fields("Amount") = UCase(CAmount.Text)
  19.        Exp.Fields("PaymentType") = UCase(CPayType.Text)
  20.  
  21.        Exp.Update
  22.  
  23.        FG.AddItem UCase(CDtpick.Value) & Chr(9) & UCase(CExpType.Text) & Chr(9) & _
  24.                   UCase(CDescription.Text) & Chr(9) & UCase(CAmount.Text) & Chr(9) & _
  25.                   UCase(CPayType.Text) & Chr(9)
  26.  
  27.        ClearControls
  28.        FG.Enabled = True
  29.        cmdModify.Enabled = False
  30.                     End If
the FG is my Flexgrid

and tell me if this is a professional way of dng things! If u have any suggestions to do this in a more systematic way pls do tell me!

Thnx in advance
Apr 26 '07 #6

P: 27
well with this code i am able to define a record by its date as well as the typeofExpenses. I am using the Access Database!

rsTemp.Open "Select * from [TABLE NAME] where date=#" & format(txt1.Text,"dd/mmm/yyyy") & "# and typeofexpenses='" & txttypeofindex.Text & "'", conTemp, adOpenDynamic, adLockOptimistic

so at this point i need another field "Amount" to define the record along with the date and typeofexpenses!
So how should i include this Amount field within the SQL query above????

Pls Help!!!!!!!!!!!
Apr 30 '07 #7

P: 27
i forgot to specify this-the amount field in the access table is of field type currency.kindly help??
Apr 30 '07 #8

Expert 5K+
P: 8,434
... where date=#" & format(txt1.Text,"dd/mmm/yyyy") & "# and ...
I think you'll find this will produce incorrect results, as SQL always expects U.S. date format. That is, month first.

Hm... come to think of it, does applying a date format to a string like this actually work? I'm surprised.
May 1 '07 #9

P: 27
hey, this works fine for me, I haven't seen any errors so far!

this was actually told by SanjuMtr above in this list(the second one in this list)!! pls do check the code he has given!
Can u pls tell me how to add the field i told u just in my previous post??

thnx!!!!!!!!!!!!!
May 1 '07 #10

Expert 5K+
P: 8,434
hey, this works fine for me, I haven't seen any errors so far!
I could be wrong, obviously. But I'm fairly certain that SQL always uses m/d/y format. If you use d/m/y format it might not produce any errors, but may match the wrong dates. More information is available here, but I'd recommend you simply double-check that the records you are finding definitely match the date you expect. And when you display them, use a format that can't be misinterpreted, like "01 Feb 2007". If you display "01/02/2007" there's always some question as to whether it's the 1st of Feb or the 2nd of Jan.

this was actually told by SanjuMtr above in this list(the second one in this list)!! pls do check the code he has given!
I don't like to disagree with other experts here, and in fact I don't consider myself expert in this particular area. But according to what I've been told by SQL experts in the Access forum, this is something that people very frequently get wrong. And that includes people who know what they're talking about. That's why it's mentioned in the Access forum's tutorials (see link above).

Anyway, like I said - don't take anyone's word for it - just test it and satisfy yourself that it is finding the correct record(s).

Can u pls tell me how to add the field i told u just in my previous post??
Um... let's see... ah! Since "*" returns all the fields, I assume you mean that you want to add the new field to your record selection criteria. How about this...
Expand|Select|Wrap|Line Numbers
  1. rsTemp.Open "Select * from [TABLE NAME] " & _
  2.     "WHERE date = #" & Format(Txt1.Text,"mm/dd/yyyy") & "# " & _
  3.     "AND typeofexpenses = '" & txttypeofindex.Text & "' " & _
  4.     "AND Amount = " & Format(TxtAmount.Text) _
  5.     , conTemp, adOpenDynamic, adLockOptimistic
Ahah! I just realised while editing the code, that you had "mmm" for the month. This probably produces the three-letter abbrevioation such as "Jan" or "Feb". I thought we were dealing with "mm", which would produce "01" or "02", respectively.

Sorry about that.
May 1 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.