Oprettelse af et tabeldesign i en formular

If you only want one comment for each user then you may as well have the comment field in the sam table as the users.

If you wantto add one or more comment for each user then I suggest you make a main form which shows one user at a time and the sub form which you use to add comments for each user.

Or you can make a continuous form which shows records from the comment table. Then alter the username column so that it is a combo which gets its data from the user table. You then add records by frist choose the name from th elist and then adding the comment.

if your tables are designed correctly then the form wizard will help you make the main form/Sub form method.

Du er velkommen til at sende din db (zippet) til spgeertz på hotmail!~)

I have also looked at your previous mail concenring this problem.


You first need to ask yourself how many comments are allowed for each user.If you answer only one then you do not need two tables, just ann extra field in your user table.

If you answer more than one then you must (noramally) have two tables. In this case you will normally have a main form showing one user and then a sub form which shows all comments for thta user.

If you want anything like what you suggest in your question

Bruger1 - kommentar1
Bruger2 - kommentar2
.
.


then make sure you have a relationship between the primary key (user name) in the brugere table and the foreign key (user name) in the comment table. Then create continous form using the form wizard. Choose the Comment table as the record source. Now you should have a form which has a combo where you can choose th euser and a comment  field.



You mention a button to write the data to the database. This is not necessary, in Access when you leave a record which you are editing the data will be wriiten automatically to the dB.

terry -> I need lots of comments for each user order by the date (and some other parameters I dont want to complicate this question with).

I have made a form that shows one user at the time (where I can add comments), but often it would be more usable if I could show all the users at once and make comments for them all and there after save all of the comments at once.

Showing all the comments aint a good idea, because it could be a quite big size formular if each users has about 10-100 comments each.

I need exatctly what I suggested - thats why I was putting this question at eksperten :-D

I have worked with php and mysql, so I know how to build a database, but Im not familiar with access.

spg -> Jeg har sendt en kopi af min database til dig - håber på at du ud over en løsning også kan forklare hvad du gør :-)

Lets say you have all users

User1
User2
User3
User4
..and so on

how can you add more than one comment for a single user?

in my other table I just make something like:
unique_id - date - User1 - comment_to_user1_1
unique_id - date - User2 - comment_to_user2_1
unique_id - date - User1 - comment_to_user1_2
unique_id - date - User1 - comment_to_user1_3
unique_id - date - User1 - comment_to_user1_4
unique_id - date - User2 - comment_to_user2_2
unique_id - date - User3 - comment_to_user3_1

then I can have all the comments I want to.

Then I suggest that you make the user column a a drop down as I mentioned 14/06-2006 15:33:45

If you have an address I can send a simple example

sure _ i would like that - but plz keep it real simple, so I can figure you programming out :-D

sirvival(snabelting)ofir.dk

sent

terry -> your example is close to what I want, but I want it to generate the list of all the users (and possible comments) by default (without having to choose all of them in a dropdownmenu/list). I also like if no comments are saved before I press a button - and maybe only comments wich are not blank will be saved in the table.

The list will show you  all users/comments which have been created. You cant show a list of users/comments if they arent created.


Look at the example you gave.


unique_id - date - User1 - comment_to_user1_1
unique_id - date - User2 - comment_to_user2_1
unique_id - date - User1 - comment_to_user1_2
unique_id - date - User1 - comment_to_user1_3
unique_id - date - User1 - comment_to_user1_4
unique_id - date - User2 - comment_to_user2_2
unique_id - date - User3 - comment_to_user3_1


These records exist and as you can see some users are shown more than once. How do you expect to show those which dont have comments?


unique_id - date - User4
unique_id - date - User5
unique_id - date - User6


But the records dont exist yet, so you dont have the information other than the user name.


Lets just say that you had a list of user who odidnt have any comments attached. This means that the data was only in the user table, none in the comment table.



User4
User5
User6

Now you want to enter 2 comments for User5, how do you expect to do this?

Hi terry - I have send you a copy of my database.

If you open the formular "opret_kommentar" you will see that its possible to make textfields and a 'save' button and do the way I talk about - writing in the textfield and only save the commentary when you press a button.

The formular "opret_kommentar_tabel" is my first simple try of makin a list of all the users and a commentary-textfield rigth to it - but if I make a save button, then I dont know how to save all the comments to the database.

Hvis andre vil hjælpe og byde ind kan de hente min hidtigie database her: www.bogstav.dk/test/access.zip

Hi alexander
I have looked at the dB you have sent me butit doesnt help me. The form "opret_kommentar" has only one user, and you want all. It also only allows youto add one comment at a time and you can only see one comment. Furthermore you have to choose the "elev" from a list and this is what you said you didnt want.

The formular "opret_kommentar_tabel" can only show each "elev" once, so I still dont see how you expect to add more than one comment for each "elev" like I asked 15/06-2006 10:22:49

The best idea I can give is something like the example I sent, other than that I cant help, I'm out of ideas.

Sorry!

I think we misunderstood each other.

I only want to add one comment for each user at the list (formular) - but my table (comments) should be able to have lots of comments for each users :-) but at the list in the formular I only want one comment for each user!

So the list in the formular should contain each users name (from the users table) and to the right for the name there should be a textfield for comments - and when I press a button it should add the username, the comment and the date for each user to the comments table except if the comments textfield for a user is blank.

there is a problem with having an unbound field for each detail line. The value you enter in an unbound field will have effect on all records.

Here is an idea.
You have a kommentar field in the elev table and it is this field you have on the form in the form opret_kommentar_tabel

now when you press the "gem" button you can either copy all records from the elev table in one go or you can loop through all records on the form if you need to do anything with the records before you insert them into the kommentar table.

You also need to empty the kommentar field when you first open the form. Her eis an exampel of how you loop through the records on the form.

Dim rs As Recordset

Set rs = Me.RecordsetClone

docmd.setwarnings false

Do Until rs.EOF

    rs.MoveNext
   

    Docmd.RunSQL ("INSERT into kommentar (Field list goes here) VALUES (values go here)")

Loop

docmd.setwarnings true


and to empty the comment field


docmd.setwarnings false
docmd.runsql ("UPDATE elev SET kommentar = ''")
docmd.setwarnings true


NOTE: None of this is tested.

little error in the loop

Do Until rs.EOF


    Docmd.RunSQL ("INSERT into kommentar (Field list goes here) VALUES (values go here)")

    rs.MoveNext
   

Loop

Hi Terry

First of all - really thanks you for trying helping me out here :-D

I discovered a "little" problem I hvae before I can try your code out - Somehow the textfield for the commentary I made to the right for the username give me trouble. All these commentary textfields are somehow connected (they got the same name) - when I go from one commentary textfield to another the content of the textfield became the same - if I write "hi mom" in one of these textfields and and moves tab to the next textfield then the content of all the textfields becam "hi mom" - do you know a solution for this problem?

I do probably just have to change a parameter or something - but how?

this is because the field is unbound (not connected to a field in a table). This is EXACTLY what  I mentioned 17/06-2006 16:58:56
"there is a problem with having an unbound field for each detail line. The value you enter in an unbound field will have effect on all records."

I'm off for the eveining will look again tomorrow

hehe yeah you are offcause right - but I really dont understand access, so  thats why I dont understand what you tried to explain me. But aint there not another way to do this? Cant I dynamic make these textfields - maybe with differents names to the textfields or ....?
There have to be a solution (I hope) - can I maybe make a container table wich can contain these infomations until I save it in the commentary table?

In a continuous form an unbound field always works like this, and also the properties for one unbound field apply for all records.

Try what I suggest. Have a kommentar field in your "elev" table. Use this as a temporary fieldf until they get copied to the kommentar table.

Please try it

I tried making a temp to the commentaries in my table elev (the user table) - and I made a textfield showing this container for the commentaries. It looks nice and will work for me - thanks :-D

But now my 'new' problems came.
I made my save-button and put at it a code similar the one you made for me:

Private Sub Kommandoknap6_Click()
   
    Dim rs As Recordset
   
    Set rs = Me.RecordsetClone
   
    DoCmd.SetWarnings False
   
    Do Until rs.EOF
   
        MsgBox ([elevtekst] + " - " + [Kommentartekst])
       
        rs.MoveNext
   
    Loop
   
    DoCmd.SetWarnings True

End Sub

I made a messagebox so I could see whats was happening - and it shows several messageboxes, but the text is the same similar to the post I chose through the formula.

Is my reference to the textfields wrong ([elevtekst] and [kommentartekst])?

make sure that the koometar field is selected as the control source fo rthe field (no longer unbound).

Igf you are still having problems the you can send me yourdB again and I'll see if I can see the problem.

thanks Alexander

No I think Im the one to say thanks :-)