Friends, I have a Database in Access, Table Name- Master . Structure is given below
Mno=Membership no in digits
Name=Member's Name
Fno=Family number in digits
Fname=name of the family
Faddress=Address of family
Fphone=Telephone of Family
The database contains several records where some members come under the same family.In other words,Suppose there are 10 members.2 members come under Fno 1,3 members come under Fno 2, 5 members come under Fno 3.
My problem is this
If I modify the family details of one member,that modification must be simultanously updated in the family details of all other members of that family
I tried the following code

If MRS.State = adStateOpen Then MRS.Close
MRS.Open "SELECT Fno,Fname,Faddress,Fphone FROM Master WHERE Fno = '" & Val(TxtFno.Text) & "'", conn, adOpenStatic, adLockOptimistic
  MRS.MoveFirst
 Do While Not MRS.EOF
   MRS("Fno") = TxtFno.Text
  MRS("Fname") = TxtFname.Text
  MRS("Faddress") = TxtFddress.Text
  MRS("Fphone") = TxtFphone.Text
  MRS.Update
  MRS.MoveNext
  Loop
MRS.Close

On running the following error message is shown
Key column information is insufficient or incorrect. Too many rows were affected by update.

Pleas help me out

Edited 3 Years Ago by Dani: Formatting fixed

MRS.Open "SELECT Fno,Fname,Faddress,Fphone FROM Master WHERE Fno = '" & Val(TxtFno.Text) & "'", conn, adOpenStatic, adLockOptimistic

here make sure that Fno field's type is not number if it is defined as Number then use this:

MRS.Open "SELECT Fno,Fname,Faddress,Fphone FROM Master WHERE Fno ="& Val(TxtFno.Text) , conn, adOpenStatic, adLockOptimistic

one thing , why you are using MRS("Fno") = TxtFno.Text it will give no sense because it always same as txtFno(as you retrieve data on the basis of TxtFno.Text).So remove this line .

so write as follow :

If MRS.State = adStateOpen Then MRS.Close
MRS.Open "SELECT Fno,Fname,Faddress,Fphone FROM Master WHERE Fno ="& Val(TxtFno.Text) , conn, adOpenStatic, adLockOptimistic
MRS.MoveFirst
Do While Not MRS.EOF
MRS("Fname") = TxtFname.Text
MRS("Faddress") = TxtFddress.Text
MRS("Fphone") = TxtFphone.Text
MRS.Update
MRS.MoveNext
Loop
MRS.Close

if this doesn't help you then feel free to ask and don't forget to put your table structure(field name with field type like number , text etc.) also put information about key field , if you have.

Friend, I tried as told. But the same error message.
Formerly the field type of Fno was text I changed it to Long and worked again But no result.
Now my table structure is as follows
Mno-text-Input in digits
Name -text
Fno-Long - input in digits
All other fields are text fields. I have no key field

(Actually I have very little knowledge about the Key field and all that. I am very new to VB.Even This much I studied from the net.Sorry Excuse my ignorance. Another thing when I tried to modify the filed type of Fno from text to Long It is not allowed ,So I removed the field and Added again.Is there any way to modify the field type)
Kindly help me Thanks in advance

So I removed the field and Added again.Is there any way to modify the field type

yes.right click on the table name and select design view and change Fno to Number. ok

and i am not getting what you wanna say so i recommend you to upload zip of your project here.

you can add file here by Files option from the menus of the editor.i'll check the code and try to point where error exist.

Edited 3 Years Ago by rishif2

To change the type of field I have tried as u told.But - Type,Size,Collating order - All these are disabled Then how can we modify it.

Sir I don't know much about Zip files. Any way I tried to compress folder containing all the files with RAR (something like that which my computer have).The folder was compressed with tight wrapping style But when I tried to up load an error message- The file type u are attempting to upload is not allowed.Can I upload all the files individually in its original form.It contains 13 files and one folder total 324 kb size

i don't know why you are getting error while uploading .rar files but try to upload zip files . if you don't know to create a zip file of files and folders then just follow these:

right click on the folder and click on send tonow select compressed(zipped) folder.

now zip file is ready and upload you zip file here. i'll check the code.and try to point out the error

Edited 3 Years Ago by rishif2

Hellow Sir,
I got it,I have uploaded the zip file.Thank U. The programme is a bit larger than what I placed before U. I mentioned only the relavant portion
U can use the UserId "va134450" and password "berty" Even more Userids are available if U open the database TblUser. I used some fictitious data , all fields are not filled, only for testing purpose. By designing this I am trying to study the different features of visual basics.I want your opinion
Thank U sir.
Another thing U have not mentioned about changing the data type using the Data manager The size,type etc are disabled when we try to modify

dear user,
i think you forgot to mention that in which form you are getting error .
it will easier for us to point out if you tell about a form in which are getting errors.

Sorry sir, I thought u may get it by reading my first posting.
In the Master menu. If I change some fields of a member ,his personal details as well as his family details, all must be updated in the Master table as well as in the Family Table. This I Succeeded. Ok. But there may be members beloging to the same family.So if I make any changes in the family details of one member that should be reflected in the family details of other members of that family in the Master table simultaneously

Dear rishif2, Not heard from you..May be busy OK.

Somebody else please help me..

It seems nobody can help me ?

Edited 3 Years Ago by mavtcr

This article has been dead for over six months. Start a new discussion instead.