iam working on the phone dircetoy system using ADO in vb6. I used serial number ,Name, phone number & City fiels. The program works fine but there is a little problem that the user can store the same name again in the application if it is already in the database.
IN serial number i use primary key. (Using Ms access) Is there any way to see that the user is alread enter.if yes then a user will see a message.
User already exits .!

rec.fields(0)=txtS_no.text
rec.fields(1)=txtname.text
rec.fields(2)=txtph.text

Your keys should be name and phone number, not serial number. First check name then check phone number (a person can have more than one phone number). If both true person already in list. If name then is this another number. If number then confirm duplicate number.

If you use checking through VB do the same for name also.
If you set primary key in access set the field's indexed property to indexed-no duplicates.

Thanks wizard-fred and KPKNNAN
As u mention that the Primary key should be name & phone number .How it is possible to make composit primary key in MS Access .If yes then plz let me know the way .

I fix the property of name (Indux no dublicate) but i use for Phono Memo & Memo does't have any Index property.Now what should i do ?
Waiting for reply

Well i apply Indexed yes(No dublicates) to the field "Name". I write a dublicate
name in the text box & press save button .VB give the following erroe message.

"The changes you requested to the table were not successful because they would create
dublicate values in the index,primary key or relationship.Change the data in the field or fields that contain dublicate data,remove the index,or reduce the index to permit
dublicate entries and try again."

Dim m As String
m = MsgBox("Do you want to save...", vbYesNo + vbDefaultButton2, "Save Confirmation")
If m = vbYes Then
rec.AddNew

rec.Fields(0) = txtS_no.Text
rec.Fields(1) = txtName.Text
rec.Fields(2) = txtCity.Text

rec.Update <------ error here

Now in Visual basic6 how can i handle the error .
Again thanks for the guidence

Change your Phone No. field to Number and as far as your error message goes try:

Dim m As String
m = MsgBox("Do you want to save...", vbYesNo + vbDefaultButton2, "Save Confirmation")
If m = vbYes Then
rec.AddNew

rec.Fields(0) = txtS_no.Text
rec.Fields(1) = txtName.Text
rec.Fields(2) = txtCity.Text

Else
msgbox"The Record Already Exists", ,"text for your blue msgbox title bar here"

End If

rec.Update <------ error here

M

Thanks every body the the good help. Well what about if i want to use Error Handling here to avoid dublicate values in the database.If it is possible then how can i apply Error Handing in the abouve code ?

This should work:

Dim m As String

On Error GoTo Error Handler

m = MsgBox("Do you want to save...", vbYesNo + vbDefaultButton2, "Save Confirmation")
If m = vbYes Then
rec.AddNew

rec.Fields(0) = txtS_no.Text
rec.Fields(1) = txtName.Text
rec.Fields(2) = txtCity.Text

ErrorHandler:

msgbox"The Record Already Exists", ,"text for your blue msgbox title bar here"

End If

rec.Update

M

You may find the End If needs to go before the ErrorHandler: in response 7. I can't remember.

M

My feelings on error handling. Use it only for true errors. If the conditions are defined and predictable then it is not an error.

Just mt opinion.

Well M it is greate & with this Eror handle it works fine But there is a little problem that i also use two fields more,the is
Phone no & Mobile number .If i enter data in both of it it works fine but if i leave enpty i.e Mobile number VB6 gives me an
error

Run Time Error -2147217887
" Error Occured"
rec.Fields(4) = txtMob.Text <----- Error

But if i use Memo for Mobile file then there is no error .
I use txt in mobile field because i need its property Index (No dublicate) but in Memo field this property is not available.
Please guide me how can i do if i want to use Memo i.e No dublicate .
Thanks againfor the kind help.

I use the above code as well as Error handling but all are use less .the problem is if there is dublicate record then the error occurs by error hadling but if i use a unique record putting in mt text boxes .Vb6 give a massges as i write in the code "Record saved" and then the error handler works.I do;'t know why .I also srtdy MS book for Vb6 and they refer that Error hanelder should be immidiate before End Sud.
I also use that technique but oops still for a unique recordset the error handeler works.
Plz guide me how can i fix it.How vb6
Error handeler should work only if there is dublicate record ?
Thanks for the guidence.

You have lost me a little in your explaination but keep the Number format for your Mobile Phone No field (numbers in general) but in the Field Properties where it says Required select No that way if the client has no Mobile No then it does not matter, if you leave it set to Yes an entry in this field is expected and an error will occur you do not enter one.

As wizard-fred says try not to use error handling for general purpose errors you know are going to happen, I usually try to use this only when an error pops up I did not expect and cannot sort out myself, either because of lazyness due to time restaints or mainly because I do not know how to fix it.

Regards

M

Well M ,again thanks for the reply. I have used Number data for Mob No and
keep the data Field as Number ,set its property

Required "No"
as well as
Indexed "Yes (No Dublicate)

in Ms Access 2000 but Oops it shows me the same error posted above in "Response Number 4" .
Normally we use Number field only for arithmatic calulation.
Text field is used for text as well as Numbers where calculation is not required.
Memo is used to store long Text as well as to store for big figure .

Here it is just a number (Mob No )so i change the data type as text as well as Number
but still as error message .

I also debug when i press the save button. The flow of control does't go to
else statement ,it skip it & stop at

rec.update

and show the error "see Response Number 4" .

Now i does't konw how to fix it if the user put aublicate number of Mob
using same name.Plz help if u can.

I think basically you have got into a mess with the DB, how many tables have you?, what are the fields you are using for Primary Keys in each table and are your tables connected as 1 to many relationships. Have you got the Foreign Keys in the related table set to Duplicates OK.

To do what I think you want to do which is to add a record providing it does not already exist is a relatively simple procedure. Here is the code I have used to add a record to a DB, it does not contain, as you can see any errors if the record already exists:

I am assuming you are using Data Control.

Private Sub cmdAddNew_Click()

'Move to the last record in the DB
datDVD.Recordset.MoveLast

'Add a new blank record to the DB
datDVD.Recordset.AddNew

'Copy the data from each text box into each field.
datDVD.Recordset.Fields("ID")= txtID.Text
datDVD.Recordset.Fields("Title") = txtTitle.Text
datDVD.Recordset.Fields("Author") = txtAuthor = txtAuthor.Text

'Update the new record, saves it as a record
datDVD.Recordset.Update

'Tell the user a record has been added
MsgBox "Record Added"

'Move to the last record.The one just entered
datDVD.Recordset.MoveLast

End Sub

M

I have only one table in my Database.
the fields are as under.

S_No Number <-----Primary Key
Name Text
Ph_no Text
Mob_no Text
Address Text
City Text

In serial No i.e S_no i use the property

Required: Yes
Index: Yous(No Dublicate)

Name
----

Required : yes
Allow Zero Length: No
Index : Yes (Dublicate Ok)

Ph_no
-----

Required: No
Allow Zero Length: Yes
Index: Yes(No Dublicate)

Mob_no
-----

Required: No
Allow Zero Length: Yes
Index: Yes(No Dublicate)

I also use the same property in Mobile Number also . But still i face the same problem.
I change the Ph_no data type as Number also but still same error as in Message 4.

I hope that now you can guide me inmuch better way.
Thanks

Ok, I am going to assume again that you are using Data Control to connect the VB6 form you have created to the DB.

Firstly add the following code to the Form Activate. This will depend on what you have named your text boxes and your data control I will use Customer for the data control.

'The following code is called whenever the 'form is activated. If it is not included no 'text will appear in your text boxes when 'you activate the form. Use Autonumber for 'txtS_No.Text and populate the DB before 'starting to code with a minimum of 1 'complete record.

'Move to the first record in DB
datCustomer.Recordset.MoveFirst

'Copy the record to the text boxes
txtS_No.Text=datCustomer.Recordset.Fields("S_No")
txtName.Text=datCustomer.Recordset.Fields("Name")
'and so on for all your fields, now if you run the program your text boxes should display text.

'put a button on your form and call it cmdAddNew

'work out the next S_No
Dim nextS_No as Integer

'Move to the last record
datCustomer.REcordset.MoveLast

'the nextS_No will = last + 1
NextS_No = Val(datCustomer.Recordset.Fields("S_No"))+1

'Add a blank new record
datCustomer.Recordset.AddNew

'Copy the data you enter into each field
datCustomer.Recordset.Fields("S_No")=NextS_No
datCustomer.Recordset.Fields("Name")=txtName.Text
'and so on for all your fields.

'Update the new record, saves it as a record
datDVD.Recordset.Update

'Tell the user a record has been added
MsgBox "Record Added"

'Move to the last record.The one just entered
datDVD.Recordset.MoveLast

NB.this will only work as long as the DB has at least one record. That is basically it, then it is up to you to add the code where necessary for anything else you want it to do like check for duplicate phone numbers.

M

Oh yes, before you can add a new record you will have to clear the text boxes

add a new button named clear and add the following code

txtS_No.Text = ""
etc etc for all your fields.

M

Just looked back at your first post and the above will not work, you say you are using ADO. If you use a data grid then the majority of what you want to do is all automatic, if you are getting duplication errors then your database is at fault.

M

Thanks M for the comprehensive reply. I also try the above but still no progress.
I study in one of the vb6 book & try to do it according to the following code. Thanks ,that now it is working fine but still iam unable to understand the login of if RST .EOF THen
What does't check each time? Plz review the code .It is working fine & now iam not facing any problem but need to understand how it works !
Thanks

Private Sub cmdSave_Click()

Dim RST As New ADODB.Recordset
Dim qry As String

Dim m As String
m = MsgBox("Do you want to save...", vbYesNo + vbDefaultButton2, "Save Confirmation")
If m = vbYes Then

Set RST = cn.Execute("select * from sample_tbl where Ph=" & (txtPh.Text))

If RST.EOF Then
Set RST = cn.Execute("select * from sample_tbl where Mob=" & (txtMob.Text))
If RST.EOF Then
rec.AddNew
rec.Fields(0) = txtS_no.Text
rec.Fields(1) = txtName.Text
rec.Fields(2) = txtPh.Text
rec.Fields(3) = txtMob.Text
rec.Fields(4) = txtCity.Text
rec.Update
MsgBox "Record have been Saveded...", vbInformation, "Message Box Window"