Assign a numerical value to a response and calculate a score

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.
 
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.
Click to expand...

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.
 

Attachments

  • tbl_Features.png
    tbl_Features.png
    35.3 KB · Views: 145
  • tbl_Records.png
    tbl_Records.png
    68.9 KB · Views: 155
  • tbl_RecordsFeatures.png
    tbl_RecordsFeatures.png
    19.5 KB · Views: 105
  • relationships.png
    relationships.png
    25.7 KB · Views: 160
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
 
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).
Click to expand...

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".
 
Do you have a record entered in the main form?

Can you post a sample of your database?
 
plog said:
Do you have a record entered in the main form?

Can you post a sample of your database?
Click to expand...

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

Attachments

Honestly, I'm not the best with forms.

Can you post this as a new thread in the Form section?
 
"You can't add or change a record because the associated record is required in the table 'tbl_records'"
Click to expand...

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.
 
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.
 
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
 
Last edited:
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
Click to expand...
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()
Click to expand...
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
Click to expand...
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 :(
 
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
Click to expand...
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!
 
you have beautiful eyes!
 
Oh, wow! I'd never been thanked before! :cool:
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!!
 
You must log in or register to reply here.

Similar threads

T
Display text in calculated query field based on multiple checkboxes
Replies
4
Views
288
timbits35
T
O
Solved Unbound Form, Two Subforms: Checkbox Selection for Product-Hotel Association
Replies
7
Views
580
Odeh Naber
O
A
Numerical ID vs Alphanumerical ID: practical advice needed on best way forward from where I am
2
Replies
23
Views
1,165
Cotswold
Cotswold
S
Should my code embrace more of the "Single Responsibility Principle"?
Replies
5
Views
419
SparklySpartan
S
A
Solved VBA compact-and-repair of a closed (corrupted) database from a new, blank Access file
2
Replies
24
Views
1,022
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom