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

[IMGNOTHUMB]https://bytes.com/attachments/attachment/8525d1447076834/capture.jpg[/IMGNOTHUMB]

[strive4peace]

Recordset.AddNe w
assign values
then
Recordset.Updat e

[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(Afghanis tan)
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?)(hahah a 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!