Find duplicate record and pass its values to the boxes for update

[hefaz]

Hello every one.
i am trying to catch duplicate values from a data. in the picture when i want to add new record, then if the record already exists then go to that record and fill all the boxes, then i will press the update button,but if not, then add new value. field is indexed.
Capture.JPG![Capture.JPG]
so how to code that? i need it very much.
here is the code:

Private Sub cmdAdd_Click()
If T6.Text = Data.Recordset.Fields("Name").Value Then
 T8.Text = Data.Recordset.Fields("Amount").Value
 T9.Text = Data.Recordset.Fields("Price").Value
 T10.Text = Data.Recordset.Fields("Bought").Value
 T11.Text = Data.Recordset.Fields("Date").Value
 Else
 MsgBox ("new record")
 ' what i need here to insert? to add a new record
End Sub

[strive4peace]

Recordset.AddNew
assign values
then
Recordset.Update

[hefaz]

but now it says, this is avoided because of associated object, what is that?

[strive4peace]

you probably need Data. before Recordset

Also, I apologize for suggesting an answer ... am new to posting here. I gave you a different answer but then realized you were asking from Visual Basic not Access and couldn't figure out how to delete my post so I did my best at giving you an answer that might work. Perhaps someone else will jump in with something better.

thanks

[hefaz]

no problem at all, thank you very much, however I tried data I hope someone will help me

[zmbd]

I don't usually handle VB stuff directly; however, from what I've seen S4P is on the right track.

I am guessing the data is in a database, so, what I would guess you should do is build an SQL based on the values entered. Use that SQL to establish a record-set object and check for the presence of a matching record. If found, then either use that Object to edit directly therein or use the Object to jump to that record.

You do not mention where your datasource is located (Access, Oracle, MYSQL, SQL Server, etc...); however, because most of my work is with Access, I do have a link to a basic (pun intended :-D ) method to open a record set in Access using VB - it is for Offic97; however, the method should still be valid: Visual Basic Example to Open a RecordSet Using DAO

Hopefully that points you in the right direction.

[strive4peace]

thanks, Z

so it would seem that hefaz needs to define the SQL to find a specific record, and assign a recordset variable.

Assume this would be something like:

  Dim ws As Workspace 'this may not be necessary
  Dim db As dao.Database
  Dim strSQL As String
  Dim rs As dao.Recordset
 
  'Create a default workspace Object
  'this may not be necessary
  'Set ws = ... 'not sure what this would be ... example is DBEngine.Workspaces(0) 'but this is what Access uses
 
  'Open a Database 
  'Set db = ws.OpenDatabase("PathAndFilenameOfDatabase")
  'might just need this:
  Set db = OpenDatabase("PathAndFilenameOfDatabase")
 
  'The SQL statement -- change Tablename to whatever it is
  strSQL = "SELECT T.*" _
    & " FROM [Tablename] as T " _
    & " WHERE T.Name = '" & T6.Text & "'"
 
   'Create a Dynaset Type Recordset
   Set rs = db.OpenRecordset(strSQL,dbOpenDynaset)
 
   with rs
      if .eof then 'no record found
         .AddNew
         !Name = T6.Text
      else 'record found
         .Edit
      end if
      !Amount = T8.text
      !Price = T9.Text
      !Bought = T10.Text
      !Date = T11.Text
      .Update
      .close 'close recordset
   end with 'rs
 
   set rs = nothing 'release recordset variable
   db.quit 'or maybe db.close
   set db = nothing 'release db variable
   'set ws = nothing 'if ws is needed

the thing this code does not to is ensure there are values in each control, which really should also be done before assigning them

hefaz , if you get it figured out, would you mind sharing what you did? thank you

[hefaz]

thank you both zmbd and dear strivepeace.
actually I don't know sql I am using access database, which doesn't have a code like that to define, I think I can't describe my problem, here I will try to explore it again.
in the first post I have a form (the picture) I want to enter some texts to the boxes, so if the record (name) which is denid duplicate in database, already exists, than give me a message and fill automatically all the boxes with that record for updating, and if it does not exist than create a new record. my English is weak, I hope you understood. thank you again.

[strive4peace]

hi hefaz,

you're welcome

you are using Access? Well, then we can help you better! You posted this in the Visual Basic forum.

The easiest way to change information in a table using a form is to bind it to the RecordSource.

Go to the design view of the form.

Turn on the Property Sheet if it is not showing by pressing Alt-Enter.

the Property Sheet shows you more information about what is selected.

Click where the rulers intersect in the upper left to select the form object.

On the property sheet, click on the Data tab and then in the RecordSource property. Then click on the drop-down arrow and select the table you are using.

Once you have identified the table, you can then bind each of the controls.

Click on the first control.

On the data tab, click in the ControlSource property, then drop the list and choose the appropriate field.

Do this for each control.

Once you get that far, post back and we can help you more.

Your form looks very nice. What is your native language?

[strive4peace]

hi hefaz,

it occurs to me that I perhaps got so excited when I saw Access that I misinterpreted what you meant. If you would be kind enough to ZIP your project and a sample database, we can look at it and attempt to give you better help.

[hefaz]

thank you man, my native language is Pashto(Afghanistan)
well, I think I again missed that, sorry for that, I am using access database with visual basic the form is created in visual basic 6.0 ( now I am really confused, how to describe my problem?)(hahaha we know that gathering requirements is hard step in software engineering.).
Dear s4p. I am just using access as back-end my front end is visual basic, just make a form for me, that can catch a duplicate record and pass its all values to the textboxes in the form, if the duplicate record doesn't exist than create that record as a new record. thanks for all the patience friends.

[zmbd]

Hefaz,
S4P's post should point you in the correct direction.

You need to make the connection to the database:
Visual Basic Concepts> Visual Studio 6.0> Create a Connection to a Microsoft Access Database File

There is a link there to take you to a tutorial for interacting with the database engine. It's not the best; however, it should provide the basics.

+ Access SQL is a tad different than your standard flavor if SQL; however, it will be something you have to master to be able to complete your project.

[hefaz]

thanks zmbd,
every thing is ok with the database all the links are created, data is also connected, I just want to catch duplicate record and pass its value to boxes,(BANG, I AM CONFUSED, YOU ALSO GOT CONFUSED.).

[strive4peace]

Hi hefaz,

firstly, Name is NOT a good unique identifier -- there must be something better. Use an AutoNumber in the database structure to uniquely identify records. Also, since your database is for medications, I am assuming there will be a CustomerNumber or a PatientNumber that would also be unique.

If you need further help, we need to see your form and a sample database. Could you zip them and attach them? Or post them and link us to them?

Thank you

warm regards,
crystal

~ have an awesome day ~

[zmbd]

K,
+ Building on S4P's information: Tokens and key words to avoid:
Problem names and reserved words in Access
Your best bet is to always use a modified version.
So instead of [Name] for a field it is advisable to Use [FName] or [First_Name] etc....

+ Look at lines 16 to 18 of S4P code.
This sets up the query when executed in line 21 to return a record set that either contains nothing, or contains the record meeting the values in your form.

+ Lines 23 thru 36 handle the find-and-edit of the matching record or creates a new record with the entered values.

Personally, I would have looked at the record count as I've found the End of File flag to not always be reliable; however the count will either return 1 or 0 if there are any records or no records respectively.

In any case, the EOF should work for most cases.

+ As S4P rightly points out, the code needs to be modified to handle null/no-entry values in your form. I add to this that the code also needs to be setup for error trapping too.

At this point, I would disagree with needing to see your database file in and of itself; however, if you will list the table names and fields along these lines:

table1_name
[field1_name] as primary key as long (or autonumber)
[field2_name] as text(20)
[field3_name] as foreign key to table... as long
etc...

for each of your tables that would help.


Please understand, many of us cannot or will not d/l attachments. This has absolutely nothing to do with who you are... In my case, such d/l are prohibited by my ITSec staff. For many others, it is part of "safe computing/best practices" not to d/l such files. A practical example as to why can be found here http://bytes.com/topic/access/answer...l-ms-products. this unfortunate Member opened a file from a trusted source and was having all sorts of issues!

[strive4peace]

thanks, Z -- thought since he attached the image, he could attach a file too ... still, and always, learning. I would like to see his project to figure it out myself too :)

[hefaz]

ok guys I will just zip the database along with the form and buttons, Just give it a look.

[hefaz]

when I want to enter records to the boxes, then I click check button to check if the record already exists, which's indexed value(no duplicates)is the name field. if it exists than give me that message in the code (second pic)and give its values to the boxes than I click update button to update that record. and if not than create a new record,
well this works, but when I enter a records that is already In the database, than give me an error message (third pic) now correct that for me. I have included the zipped folder.
That would be your pleasure if you guys give me your email, so that I would be able to send the complete project.
(capture.jpg)

CheckCode.JPG

Error.jpg

[zmbd]

+ Usually we ask our membership not to use private emails for topics online unless the information is sensitive in nature and we involve the site administration.

+ The code posted in the image.
This is nothing like what we've suggested/advised and as it stands will not work as there is no check between the database and the form. You must use some method, even if you have to emulate the DLookup() (see tutorial here on how to do that in VB: How to Implement the DLookup Function in Visual Basic ), to look the value up first.

Thus, User enters data, Clicks on [Check] Button, Code runs the look up via either the emulated Domain function or as we have suggested via record-set, if the code finds the value then retrieve and populate the controls on the form and enables the [Update] button, if the code doesn't find the value then create a new record and save the form data... maybe provide a bit of user feed back to that effect.

[hefaz]

exactly zmbd.
your third paragraph is my problem, now is there any way to do what I want? or is that a nonsense idea of mine? please tell me if the code s4p wrote will work for this problem. or write your own and final idea, because I know I have created a headache for you guys, nothing else.

sorry I am new here and still don't understand the basics of the site. p i.e privacy etc.

wow, thanks for the nice suggestion, about dlookup, I will try it, I think that is my problem.

sorry I failed to do that, if someone create just a sample for me that would be better, otherwise I can't.

[zmbd]

+ Usually we do not write the entire code, instead, we try to help guide our membership to look at all of the options available and decide the best route for themselves.

The reasoning is two fold:
++ Primarily, because there are usually multiple methods to arrive at a solution and no one approach is a panacea
++ Because, when one finds the solution oneself the methods learned become more valuable in that one usually learns how to apply the tools in more situations.

Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime.

- Maimonides

+ The code S4P has provided is a framework that you will need to modify to fit your situation.

[hefaz]

yeah, that gave me really a good sense and felling, thank you from the advice. that is right, I will try hard to reach to my problem's solution, and will post the answer back here. thank you both for the contribution.
THOSE WHO STRUGGLE MAY FAIL, BUT THOSE WHO DON'T STRUGGLE HAVE ALREADY FAILED.

[zmbd]

hefaz,
Your thread has definitely highlighted a weakness in my understanding of VB6 as it relates to Access. To that end I started looking around for more information about how to handle Access in VB.

I've found the following tutorial, it mentions using ADO as the primary means of manipulating Access database objects; however, with the advent of the "ACEDAO" (currently Access 2007 and newer) - I think it advisable to learn how to handle the "DAO" methods described within the tutorial. Using the native engine language to handle data seems to be the best route. I use ADO methods when push/pull data to a SQL-Server or MYSQL because the ADO translates better to those servers.

In any case here's the link - keep in mind that this tutorial references older libraries (3.51) however the newest version is ACE 12.0 ((Database.Version Property (DAO) )) So some things such as user level security are no longer available: http://www.vb6.us/guides/vb6-database-guide

In any-case, this is the one I am staring with, I found three others that appear to be newer and I'll be looking thru too - but they don't appear to be for "the faint of heart." Thus, this one is just as a primer for me as I do not develop in VB, even though it references older material it may provide some useful insights for your project.

Best of Luck with your project.

[hefaz]

Thank you man. Rated you as best moderator with 5 stars.

[hefaz]

still working on the problem guys...

[zmbd]

hefaz,
Have you a revised code?
What further steps have you taken?

[hefaz]

no zmbd sir,
i removed that feauture form it. it was really disappointed.