I have a database that tracks many records that can be from the same person. I have a form that I can use to modify the name of that person for any reason. I was trying to create a loop method so that when I click on the apply button it will change all the records in the table that match that first and last name. Here is what I have but it is not working.

Private Sub cmdApply_Click()

Dim MyDB As Database, MyTable As Recordset, GO As Integer

Set MyDB = CurrentDb()
Set MyTable = MyDB.OpenRecordset("Offerings")



Do While GO = 1
If MyTable![First] <> [Forms]![EditMembers]![txtFirst] Then
GO = 1
Else
GO = 0
End If
MyTable.Edit
MyTable![First] = [Forms]![EditMembers]![txtFirst]
MyTable.Update
MyTable.Close
MyDB.Close
Loop


End Sub

Can anyone help me?

Thank you.

You should not be doing it like this. You are working much harder than you need to. Good database design states that you should store each piece of data (I'm going to shout this next part) AS FEW TIMES AS POSSIBLE. THE "RIGHT" NUMBER OF TIMES FOR MOST DATA IS ONCE. (end shout)

You should split the table up into at least two tables, one will contain the data relating to each customer, the others will contain other data. Each table should store data about exactly one entity. Examine the Northwind database to get an idea of how you should be doing it.

Edited by: fkegley on Wed Sep 21 14:29:03 EDT 2005.

What is the northwind database and where can I find this?

Thank you for the shout...

I think this will be a good oppertunity for me to lear how to do this.

Robby

You could try working with record sets and updating the name field that way. For example use a select statement to select all the records in the table with the persons name that you want to change. That will be your RS. Then just update the field to the new name. This is if you absolutly can not separate the table into 2 different data sets. B/c fkegley is correct about what you should do with your data.

The Northwind database comes as part of the Access package. It is an example db from Microsoft to show almost all of the functions of access. It will probably be in your Templates folder.

I would suggest you look at the following posts:

Normalizing a Database
A Little Treatise on Normalization
General Database Design Topics and Articles

On my machine, Windows XP Pro, Office 2003, this is the path (yours may be slightly different, but not radically so):

C:\Program Files\Microsoft Office\OFFICE11\SAMPLES

So in my case I have a members table which has all the members info including their first and last names in separate feilds. I then have an offerings table which store each offering that a member has done.

How would I do it without storing the first and last name in the offering table?

Would I use the member ID number from the Members table in some way?

Thanks.

Yes, you would put the Member ID in the offerings table. Then a query to join the two tables on MemberID would let you intelligently match up the records in each table, so that you could get a report that showed the data from both tables, for instance.

Would the member ID be different than the PK. As of now I have an ID in both tables that are PKs. How would I identify the member ID with the first and last name of the memeber?

Thanks

Yes, the member ID in the offerings table would be different than the PK in the offerings table. The MemberID in the offerings table is there so you can match a record in the offerings table to a record in the member table. The PK in the offerings table is there to make each row different.

Tell me this---does the offerings table have the memberID in each row? If it does, then your troubles are over. All you have to do is build a relationship between the two tables.

Here is what the offering table contains at this point. But any changes can be made failry easily since it is not in use yet. One thing though is if I change from having the first and last name on the offerings table to just a separate ID number, since I am now using that first and last name to query offerings from particular people will I be able to modify the query to match the IDs instead?

ID - Auto # - PK
First - Text
Last - Text
OffDate - Date/Time
General - Currency
Vision Fund - Curr
Tithes - Curr
Other - Curr
Total - Curr

Since I am listing feilds I may as well list the feilds in my Members table too:

ID - Auto# - PK
First - Text
Last - Text
Phone - Text
Address - Text
Email - Text

Thanks for the help. For you this is probably childs play but I am still learning and I lear by doing...


Robby