vba for realtions between tables

[wojnicztrust]

I found code to fill a table from form using button (blank field in a form) and it works for first field in form

Private Sub Save_Click()
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("name of table")
With rst
rst.AddNew
!name field in a table = Me![name field in a form]
rst.Update
.Close
End With
End Sub

but another field should put date in to an another table which is in relations with first table (one to many) and i don't know what code should be to this relations.
Please help!

 

[DavidAtWork]

I think you may need to post a bit more detail of how this second table relates to the first one. I understand that you're writing new records to table 1 but what is happening in table 2, are you updating records in table 2 that will link to table 1 or writing new records to table 2 as well.
David

 

[wojnicztrust]

David I done some database to let you know what I mean, also I done screen to help understand. Please check relations in database.

 

[jdraw]

I think you should review some videos/articles on Normalization and database design. Approaching database from a Form will lead to issues.

Take a top down view of your business and how database supports your "Business".

You may get some ideas from these data models
http://www.databaseanswers.org/data_...ders/index.htm
http://www.databaseanswers.org/data_...ucts/index.htm

and if you want to see the videos to show how to determine such a model, watch these free video tutorials

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. The example relates Customers, Orders and Items.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

 

[wojnicztrust]

jdraw if I you'd like to learn VBA from scratch I would have bought a Bible vba, but now I don't have much time to learn, I must do database asap and I looking help with concrete problem.
By the way you do not even opened my files before you wrote a post.

 

[jdraw]

I wrote my post after I saw David's post. If you think the videos I suggested relate to vba, you are very mistaken. Did you watch any of the videos? There are about table design, normalization and relationships.

I gave you the best advice I could based on your post and David's response. In my view you were working from a Form, not a designed database with tables and relationships. There was no info re business or business facts.

You can ignore my posts or anyone else's, that is always your choice.

I have been out most of the day and have not seen your database until now.

I see you have tables and relationships, but I have no idea of exactly what you are trying to do.

And I am not sure based on your latest post whether you want any help from me or not.

 

[wojnicztrust]

I saw first two links - doesn't work. I played third and fourth but trust me jdraw I don't have time to watch. When I will have all my database, then I can learn normalization.
Of course that I need your help, if you can...
About my problem:
I found the code on the internet which write from blank field in form, data to tables (aftrer I click on the "save" button) and it works, but this code take not into account that these tables are in relations - that is my big problem. If I reproduction this code to all of my fields in a form and click the "save" button, there are date in my tables without relations, but I need the code which take into account relations between tables.

 

[jdraw]

Tell us what you are trying to do. Are you using a bound form?
Can you give a sample? Data before and data after the event.

When I look at your code (post 1)

Private Sub Save_Click()
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("name of table")
With rst
rst.AddNew
!name field in a table = Me![name field in a form]
rst.Update
.Close
End With
End Sub

This is only processing one field.

You would have to have the other fields, after this line

!name field in a table = Me![name field in a form]
![field2 in table] =Me.[field2 on form]
![field3 in table] =Me.[field3 on form]

All field assignments before the Update

You should not have spaces in field and table names -- will lead to syntax errors.

 

[wojnicztrust]

There are four tables.
The code for all fields is

Private Sub Save_Click()
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Archives")
With rst
rst.AddNew
!Name = Me![Name]
rst.Update
.Close
End With
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Details_archive")
With rst
rst.AddNew
!Character_number = Me![Character_number]
rst.Update
.Close
End With
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Class_b")
With rst
rst.AddNew
!First_level = Me![First_level]
!Second_level = Me![Second_level]
rst.Update
.Close
End With
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Collection")
With rst
rst.AddNew
!Type_of_character = Me![Type_of_character]
rst.Update
.Close
End With
End Sub

But the tables are in relations (one to many). This code fill incorrectly date in to a tables.
jdraw please find file Database.accdb what I attached now. Open form, fill fields, click save, then open relations between tables and then compare it with the data filled in tables by form.
In this code there is something missing to link tables.

 

[jdraw]

What is your database about? What are you trying to do.

I saved your database. Opened the Form and keyed in some data and clicked the SAVE button.

The attached jpg shows the result.

You're going to have to tell us more about what you're trying to do, if we are going to offer assistance.

 

[wojnicztrust]

This database was an example, what I done to let you know what I mean.
But Ok I see that it is too hard to explain with this database, so I done new.

Now We have 3 tables: Animals, Birds, Dogs - with relations (one to many)
In this example I use only two tables Animals and Dogs.

Now I attached 4 files:
form.png - to show you how the form look
relations.png - to show you how relations between tables look
What is.png - to show you how tables look, after keyed some data in form and then click "Save"
what should be.png - to show you how tables should be look, after keyed some data in form and then click "Save"

In button "Save" I used the same code what before

 

[jdraw]

I'm sorry I do not understand what you are trying to do. The pictures are not helping me.

You may be working with Animals where Dogs and Birds are subtypes.
Here's a link for Entity type/subtype in ER Diagrams
http://it.toolbox.com/blogs/enterpri...diagrams-14255

I hope someone reading this thread understands WHAT you are trying to do, and will give you assistance.

Good luck with your project.

 

[Alansidman]

wojnicztrust

I have read all the threads here. I have viewed your sample data bases. I don't understand the business of your databases. Would you please explain as clearly and simply as possible what the business process is. What you hope to achieve. This will help us to help you as it is not really clear what you are trying to do. Why are you using an unbound form versus a bound form with bound subforms.

If you are unwilling to understand the concept of normalization, which is the main underlying concept of building a relational database management system, then how do you expect your database to be efficient? Your idea of learning it after you have built your database is similar to buying a car but without an ignition system and hoping someone will show you another way to start it.

Jack has offered you many valuable tips (in the form of videos that are not very long) to help you understand what RDBMS is all about. You claim that this is urgent and you don't have time to learn now. It has been at least two days that you have been posting to this site. I'm sure you could have squeezed a couple of hours in those days to look at these basic concepts.