Assign a numerical value to a response and calculate a score (1 Viewer)

plog · Feb 12, 2018

No it would not be in that table. Further, that table should not have all the 'not_' values. That table is just a list of valid features.

To align records with features you need a junction table (also called associative entity https://en.wikipedia.org/wiki/Associative_entity). That table will contain 2 numeric fields, 1 to hold the NP_feature_id and one to hold the record_ID.

dafne · Feb 12, 2018

plog said:
No it would not be in that table. Further, that table should not have all the 'not_' values. That table is just a list of valid features.

To align records with features you need a junction table (also called associative entity https://en.wikipedia.org/wiki/Associative_entity). That table will contain 2 numeric fields, 1 to hold the NP_feature_id and one to hold the record_ID.

Thank you! I created a junction table, as you suggested. I'm uploading a few snapshots so you can tell me (if you're willing, of course!) if I did it right.

As for the subform: I never had to use one before, so my questions might seem silly. Here's what I did: I created two forms: my main form and another one, which is going to be embedded in my main form; my main form is a simple form; my subform is continuous. I just don't know how to set up my subform: which table is the Source of (what's going to be) my subform? What about my combo - what's the Control source? I'm guessing the Row Source is this query: SELECT tbl_Features.feature_ID, tbl_Features.feature_name FROM tbl_Features;

I think that I'm almost getting there (maybe). Thank you for your patience.

plog · Feb 12, 2018

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 · Feb 12, 2018

plog said:
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 · Feb 12, 2018

Do you have a record entered in the main form?

Can you post a sample of your database?

dafne · Feb 12, 2018

plog said:
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 · Feb 12, 2018

Honestly, I'm not the best with forms.

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

dafne · Feb 12, 2018

Will do. Thanks for everything!

The_Doc_Man · Feb 13, 2018

"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 · Feb 13, 2018

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 · Feb 13, 2018

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

Code:

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.

Code:

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 · Feb 13, 2018

Hi, arnelgp. Thanks!

arnelgp said:

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

Code:

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.

arnelgp said:
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.

arnelgp said:

then create the ShowResponse Function in your form.

Code:

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 · Feb 13, 2018

arnelgp said:

Code:

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 · Feb 13, 2018

you have beautiful eyes!

dafne · Feb 13, 2018

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!!

Assign a numerical value to a response and calculate a score (1 Viewer)

plog

Banishment Pending

dafne

Registered User.

Attachments

plog

Banishment Pending

dafne

Registered User.

plog

Banishment Pending

dafne

Registered User.

Attachments

plog

Banishment Pending

dafne

Registered User.

The_Doc_Man

Immoderate Moderator

dafne

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

dafne

Registered User.

dafne

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

dafne

Registered User.

Similar threads

Users who are viewing this thread