Assign a numerical value to a response and calculate a score

[plog]

The source of the subform is tbl_RecordsFeatures.
The control source of the combo box will be feature_ID.
The row source is the query you posted.

The key to linking your form/subform is setting the Master/child relationship correctly (https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b).

The key to your combo box is to show the feature_name, but use the feature_ID - https://support.office.com/en-us/ar...up-field-44449cf7-ffdb-4a73-b980-495c0e66afdb

 

[dafne]

The key to linking your form/subform is setting the Master/child relationship correctly (https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b).

Thanks for the articles. I'm still getting a little error: something like "You can't add or change a record because the associated record is required in the table 'tbl_records'" (this is a free translation from French). Where is this coming from? My "master field" is record_ID from tbl_Records and my "child field" is record_ID from "tbl_RecordsFeatures".

 

[plog]

Do you have a record entered in the main form?

Can you post a sample of your database?

 

[dafne]

Do you have a record entered in the main form?

Can you post a sample of your database?

Yes! 2276 of them.
I'm uploading a sample DB with only 10 records. Thank you very much for your help!

 

[plog]

Honestly, I'm not the best with forms.

Can you post this as a new thread in the Form section?

 

[dafne]

Will do. Thanks for everything!

 

[The_Doc_Man]

"You can't add or change a record because the associated record is required in the table 'tbl_records'"

Dafne, I will explain this message for you. It is displayed when you have what is called "relational integrity" (RI) enabled on a relationship between two tables, and you tried to add "child" records before you defined a "parent" record. Think of this as the age-old debate about which came first (chicken or egg). Defining the relationship between two tables in parent/child relationship and then enabling RI tells Access to enforce whichever table you selected as the parent relationship - thus forever forcing you to have a chicken before you can lay that egg.

It occurs when you have parent/child relationships and attempt to create child records before saving the parent record. This MOST OFTEN occurs when you have one of two cases, and I'm betting on #1 from the context of your discussin.

#1 - you have a parent/child form to express visually what you have in your parent/child tables and have a way to implicitly or explicitly save the child record without saving the parent record.

#2 - you have a query that directly feeds a child table in some way. We have also had a few cases where a poorly written JOIN query tries to save parent and child simultaneously or in the wrong order (due to the LEFT/RIGHT choice of the join) - but that doesn't work because again, you have to have a parent before you can have a child. In theory, you SHOULD be able to do this through a JOIN but it is possible to botch the JOIN and make the query un-updatable.

 

[dafne]

Thanks. It’s probably number one, I guess.
I tried changing a few things, but something’s still missing.
Here’s what I have: three tables (one containing my records, another containing my features, and the junction table), a main form showing the record_ID and the text of the record and a continuous sub form containing a combo box with the list of features to choose from.
If anybody can help, it’d be very appreciated. It’d be a shame to have spent so many hours on this (I’m a novice) only to fail so close to the goal.

 

[arnelgp]

as plog explained, set up a table first.
this will be your scoring table

tblScore:

fieldName      fieldType
LowScore       Integer
HighScore      Integer
Response       short Text


sample data:


lowScore       highScore        Response
0              3                low salience
4              7                medium salience
8              999              high salience

for a test:

add an unbound textbox or label control on your form.
say its a label (lblResponse) and we use its caption to show the Response.
you can do it on each checkbox's AfterUpdate event.

say again for checbox1, on its After Update event:

=ShowResponse()

then create the ShowResponse Function in your form.

Public Function ShowResponse() As String
Dim score as integer
Dim thisResponse As String
score = Abs(Nz(Me.checbox1,0) + Nz(Me.checkbox2,0) + ...)
thisResponse = DLookUp("Response", "tblScore", _
    scores & " Between [lowScore] And [highScore]")
Me.lblResponse.Caption = thisResponse
ShowResponse=thisResponse
End Function

 

[dafne]

Hi, arnelgp. Thanks!

as plog explained, set up a table first.
this will be your scoring table

tblScore:

fieldName      fieldType
LowScore       Integer
HighScore      Integer
Response       short Text


sample data:


lowScore       highScore        Response
0              3                low salience
4              7                medium salience
8              999              high salience

OK, done that.

for a test:

add an unbound textbox or label control on your form.
say its a label (lblResponse) and we use its caption to show the Response.
you can do it on each checkbox's AfterUpdate event.

say again for checbox1, on its After Update event:

=ShowResponse()

So I tried to paste "=ShowResponse()" in each check box's AfterUpdate but it wouldn't let me. I wrote "ShowResponse" instead.

then create the ShowResponse Function in your form.

Public Function ShowResponse() As String
Dim score as integer
Dim thisResponse As String
score = Abs(Nz(Me.checbox1,0) + Nz(Me.checkbox2,0) + ...)
thisResponse = DLookUp("Response", "tblScore", _
    scores & " Between [lowScore] And [highScore]")
Me.lblResponse.Caption = thisResponse
ShowResponse=thisResponse
End Function

Done that. All I did was replace "checbox1" etc with the actual names of my check boxes, I used the same names as you did in your example for the other elements.

My lblResponse isn't showing anything

 

[dafne]

Public Function ShowResponse() As String
Dim score as integer
Dim thisResponse As String
score = Abs(Nz(Me.checbox1,0) + Nz(Me.checkbox2,0) + ...)
thisResponse = DLookUp("Response", "tblScore", _
    [B]scores[/B] & " Between [lowScore] And [highScore]")
Me.lblResponse.Caption = thisResponse
ShowResponse=thisResponse
End Function

OK, I think I got it to work (kind of). I tried clicking on one of the check boxes and the event fired. Maybe I'll try another kind of event because I don't want to go through every check box all over again. Also, I had to change "scores" into "score" in the code above.
Thanks a lot, arnelgp!!!! Definitely helped a lot!

 

[arnelgp]

you have beautiful eyes!

 

[dafne]

Oh, wow! I'd never been thanked before!
I wanted to save this salience score into my underlying table, so here's what I did (maybe it'll help someone else):

• used a text box (txt_salience) instead of a label (lblResponse) and linked it to a field in my table
• changed "lblResponse.Caption" to "txt_salience.Value" in the code function

Thanks again (everyone) for all the help! I'm saved!!