problem in editing records with visual basic

[davidvhp]

Hello,

In my access database I have 2 tables, where one is T_user where I store the username, password and qualityid (foreign key from the second table). The second table is T_quality,which has many fields, whose type is "yes/no" .
I have created a form where the user register his username and password and after this go to a second form where they are several unbound checkboxes, which have the name of the fields of the T_quality. I have already written some code in visual basic that stores the values from the checkboxes in the T_quality, but I am having trouble in getting the id of these set of values to record on the field qualityID in the table T_user.


Does anyone knows how can I get through visual basic the id from the record that the user has just created?
Maybe I am not managing this the right way. Maybe it is better to use bound checkboxes instead of unbound checkboxes.
If anyone can help me, I would be very thankful.

[orange]

What field identifies the records in T_Quality? How is that field defined?

[davidvhp]

The field that identifies t_quality is qualityID and it's type is autonumber!

[orange]

I'm not following exactly. You want to edit records. So these records already exist.

It would seem that if you wanted to do something with an existing record , you would use a bound form to display the record, then some buttons or independent checkboxes that could be clicked or checked to cause some event driven code to manipulate the record.

It sounds that you have a Usertable to identify users and assign an Id.
Perhaps in your Table T_quality you would have the UserId identifying which user "approved/assigned the quality of this item"?

I would not suggest placing the quality id in the user table; that seems you would be repeating all user info in the table. The user id alone will identify the user.

Could you describe what it is that these tables and processes represent?

[davidvhp]

I am developing a platform where a user register himself and describes him self through qualities. So when the users enters on the program, he must register a username and a password, and on the next form he must check the checkboxes that correspond to the qualities, that he thinks he has.
But besides this, the user must also give the qualities that he appreciates on other people. So that the program can make a match between users, using the qualities that a user has and the qualities that a user want.
I am on the first phase, where I am editing the forms so that it is possible that database stores this qualities corresponding to the user.
And since the table T_quality would be associated not only with table user, but also with another table(where I store the qualities that the user likes), I have made the association between T_quality and T_user throuhg the foreing key qualityID.
So I have T_quality(qualidadeID, gentleman,etc..) and T_user(username,password,qualidadeID). Each user has a set of qualities.
And I have already developed the code to record the information that the user puts, when registered, but I am having trouble in getting the qualityID corresponding to the set of qualities the user has choosen and record that qualityID on T_user.

Are you following now?

Thank you for the help

[orange]

Take a look at existing models/facts. See if they apply. Perhaps you will find something useful or options that you could use/adapt.

http://www.databaseanswers.org/data_...ites/facts.htm
http://www.databaseanswers.org/data_...ites/index.htm

You might consider tables

User ----->HasQualities
|
|
+-------->SeeksQualities

Where User identifies User details/password and ID
HasQualities has FK UserID
SeeksQualities has FK UserId

[orange]

You could have a form (mock up attached) with a txtbox to identify the User/Person Involved
and a Combo to show all the Qualities available. You could update the Qualities to be shown by removing anything already picked by this user.

The User/PersonID would be on the form (could be hidden). The User Selects
the quality he/she has and Clicks a button to Save a record.

They go through the list until they have added all the qualities that apply.

The code behind the button would be something like this (untested)

'---------------------------------------------------------------------------------------
' Procedure : btnHasQuality_Click
' Author :
' Date : 28-11-2011
' Purpose : To populate PersonHasQualitiesTable with
' the id of the Person and the id of the Quality involved.
'Unique index on PersonId & QualityId to prevent duplicates.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Private Sub btnHasQuality_Click()
On Error GoTo btnHasQuality_Click_Error

On Error GoTo Err_btnHasQuality_Click

Dim SQL As String
SQL = "Insert Into PersonHasQualitiesTable(PersonId,QualityId) " _
& "VALUES (" & Me.txboxUser & "," & Me.cboQuality & ")"
Debug.Print SQL
CurrentDb.Execute SQL, dbFailOnError

Exit_btnHasQuality_Click:
Exit Sub

Err_btnHasQuality_Click:
MsgBox Err.Description
Resume Exit_btnHasQuality_Click

On Error GoTo 0
Exit Sub

btnHasQuality_Click_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure btnHasQuality_Click of VBA Document Form_frmPersonHasQuality"

End Sub

You could have a similar form to populate the PersonSeeksQualities table.
Similar procedure, just pointing to a different table.
You could even have a different button for Add SeeksQualities Record. You would enable one button or the other depending on which option the user selected before opening this form.

This set up would involve 4 tables
User (UserId, Username, Address, Phone, email...)
Qualities(QualityID, qualityName, other things specific to quality...)
UserHasQualities(Id,FKUserId,FKQualityId, ..perhaps Date..)
UserSeeksQualities(Id,FKUserId,FKQualityId, ..perhaps Ddate)

In UserHasQualities and UserSeeksQualities, there would be a unique compound index on FKUserId + FKQualityId to prevenmt duplicate records.
Just a few ideas for consideration.

Good luck.

[davidvhp]

Thank you orange! It really helped me your approach to the problem. And it seems to be a better way to develop it.
But I would really prefer to use chckboxes, because I think it is more "functionally" to the user. Do you think it is possible to use them instead of comboboxes, as you propose on your solution?

[orange]

I think I was updating/editing my post when you posted.
Sure you could have a list of checkboxes with each checkbox representing a quality.
Before you Save the record, you would have a process to interrogate each checkbox, perhaps even confirm the choice, then add a record for that quality that was selected for that User.

I think for proper relational database techniques you would store 1 record per quality per user.

John Doe likes Movies
John Doe likes Swimming or whatever... each in a separate record.

Would you include strong dislikes as well in the qualities?
John Doe detests ballet for example. Just curious.

[davidvhp]

Yes. I have a table that is T_qualities_dislike, exactly for the qualities that the user deslikes .
What I don´t know how to do it, is to store the informations of the checkboxes in the table T_quality_user (which has the qualityid per userid) .

[orange]

Have you set up you qualities table?
I did some quick search and found these, that just add to the confusion.

http://bmindful.com/forum/thread/311...find-your-purp

Types of Music -- http://en.wikipedia.org/wiki/List_of_music_styles

another http://www.buzzle.com/articles/list-...qualities.html

still more http://wiki.answers.com/Q/What_are_g...st_on_a_resume

http://www.gurusoftware.com/GuruNet/...al/Factors.htm

[orange]

I have attached a database in 2000 format. It has 3 tables, a query and 3 forms. There are 4 quality records; 3 users.
You will see how the tables are related in the Relationships diagram.

I have prepopulated some Qualities for 2 users.

You can use form frmQualitiesLikes-ForInitialSelectionOnly to make selections for Sam (user 3).

There are some descriptions in the attached DavidQuality.txt that may be helpful.

Good luck.

[davidvhp]

Hello orange. sorry for the late reply, but was working on another computer.
Thank you very much for all the help,ideas and information you gave me. Last night I abandoned the idea of using checkbox. It would be very complex for the user to edit the data that had benn already recorded.
So want I have done was a form with 2 listbox, where the first one has all the qualities possible for the user and second one lists the quality that the user has. and between them I have putted 2 buttons to add a quality or remove a quality.
To make this process more efficiente I get the values to the 2nd listbox through a query that selects the values od quality for the current user!
Do you think that is a good solution ?

[orange]

Did you look at the database and text file I sent?

Any solution that meets the requirements will be good. The better and best solutions are often subjective choices. Some will have a slightly better ease of use factor; others may reduce the number of keystrokes or mouse clicks to get something done; often it is a form layout or presentation that is more intuitive to the user; sometimes it's the solution that simplifies maintenance, or facilitates changes.

[davidvhp]

Hello,

To simplify my question imagine that my database only has 3 tables. A T_tool(toolID, toolname), T_service(serviceID, servicename) and T_tool_name (serviceID,toolID).

Now I want to display all the tools that a service has. So I have made a form with a text box serviceID and a listbox, where the row source is a query that selects from the table T_tool_name all the the toolIDs for the serviceID that is displayed in the textbox.
My problem, is that this work for the serviceID=1 only. If I select the th serviceID=2 it still appears the tools from the serviceID=1 .

Can anyone help me please?

Thank you