UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> If/then From Multi-valued Field And Moving Logic To Vba, Access 2016    
 
   
momo2000
post Oct 19 2019, 03:17 PM
Post#1



Posts: 69
Joined: 6-November 13



BACKGROUND/PROBLEM:
Hello all. I’ve managed a database for merit badge counselors for boy scout troops for the past 6 years, and many of you have helped me with this project over the years, thanks. I’ve got a new feature I’m trying to add, so looking for some guidance.

Of the 100+ merit badges, there are a handful (about 15) that require that the adult merit badge counselor have specific certifications in order to qualify to teach that merit badge. For example, to teach the Art MB there is no requirement, but to teach the Climbing MB you must be “BSA Climb on Safely Training“ certified. Now I want to track and report on this, in a “Certification Notes” section on the forms (first) and reports (next).

Since this will be calculated from existing values, I know I don’t want to store this in the table. Also, since this “Certification Notes” section will be fairly complicated If/Then string concatenation (for the 0-15 of these that each adult may have), I don’t want to do it in Expression Builder, but would prefer to do this in VBA so I can structure my logic in a “readable/manageable” format.

QUESTIONS:
#1. I have used a Multi-Valued field for the Merit Badge field for each Adult (I know there are varied opinions on MVFs but it is where I am) and I am having trouble getting my selection criteria to work on the MVF, and I have tried the actual name and the index number, to no avail, so would like to understand how to make that work. It is currently in Expression Builder, but will need to move to VB once I get the syntax down.

#2. How can I put the calculations for the Certification Notes control into VBA instead of Expression Builder? Where would I put this, how would I trigger, maybe after I exit the Merit Badges control?

ATTACHMENT
I have attached a database with scaled down controls & fields, and with 3 records to demonstrate what I have tried, in the Certification Notes, but can’t get to work. In the example, the note for Climbing should show up in the Certification Notes, but doesn’t. If I can figure these two things out, I think I can string together the VBA code to make the text string for the Certification Notes control. Ideas or links to get me pointed in the right direction?

Attached File(s)
Attached File  MBC_test.zip ( 128.03K )Number of downloads: 5
 
Go to the top of the page
 
June7
post Oct 19 2019, 03:45 PM
Post#2



Posts: 971
Joined: 25-January 16



In case you don't already know, an MVF really just holds pointers to a related hidden table where there is a record for each item. This will require VBA that opens a recordset of MVF data, loops through recordset and builds string for display in textbox.

Why is PersonID a text instead of number field?

1. build this query object named AdultMerits
SELECT [Milton Adults].ID, [Milton Adults].PersonID, [Milton Adults].[Last-First], [Merit Badges].[MB Name]
FROM [Merit Badges] INNER JOIN [Milton Adults] ON [Merit Badges].ID = [Milton Adults].MBC_For.Value;

2. create VBA custom function that will build string - expand for other 14 merit badges - this uses merit badge names because easier to read than ID numbers but could be modified to use ID's instead and query would not need to include INNER JOIN on Merit Badges
This procedure is structured to be behind form or report. Would have to be duplicated behind both. Structuring to be more generic and placed in general module is complicated by including expiration date for each certification.
CODE
Function GetCerts()
Dim rs As DAO.Recordset
Dim str As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM AdultMerits WHERE PersonID='" & Me.PersonID & "'")
Do While Not rs.EOF
    Select Case rs(3)
        Case "Climbing"
            str = str & "Climbing Certification Expires on: " &  & Me.Text91 & ";"
    End Select
    rs.MoveNext
Loop
GetCerts = str
End Function

3. call function from textbox: =GetCerts()
This post has been edited by June7: Oct 19 2019, 04:35 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
momo2000
post Oct 19 2019, 06:29 PM
Post#3



Posts: 69
Joined: 6-November 13



Thanks June7. I kinda understand that about MVF, but don’t understand the specifics. PersonID is text because I have 3 external spreadsheets that I get monthly that I run queries against to refresh the [Milton Adults] table and the PersonID column in those tables is text. I haven’t had a problem, so stuck with it.
Your approach is really interesting, never would have come up with that. Mobile now, but will test and report back, thanks for the detailed approach!

Go to the top of the page
 
June7
post Oct 19 2019, 06:38 PM
Post#4



Posts: 971
Joined: 25-January 16



Instead of query object that query could be in VBA.

Set rs = CurrentDb.OpenRecordset("SELECT [Milton Adults].ID, PersonID, [Last-First], [MB Name] " & _
"FROM [Merit Badges] INNER JOIN [Milton Adults] ON [Merit Badges].ID = [Milton Adults].MBC_For.Value WHERE PersonID='" & Me.PersonID & "'")

If you have a certification date field for each of these 15 badges, don't see any real benefit to textbox with concatenated info.

In normalized structure, adult/badge info (with or without certification) would be in a related dependent table with a record for each adult/badge pair (instead of MVF and hidden related table). Why do you not want to do this?

This post has been edited by June7: Oct 19 2019, 07:22 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
momo2000
post Oct 19 2019, 09:05 PM
Post#5



Posts: 69
Joined: 6-November 13



OK, digesting this and got it working with the query. QUESTION: What does the “3” represent in this line: Select Case rs(3)? Is that the index number of Climbing in the “hidden table” for the MVF? If yes, how do I see that hidden table?

As for your updated question, you are probably correct fully normalized is probably the correct way to approach this. I have so many forms/reports and custom VBA code build using the MVF that it will take some work to rearchitect. But I need to do it, just not sure I can do it right now. Additionally, some of the certs have expirations and some never expire, and then others just have a note that I want to add to this text box with concatenated info. But still I think the fully normalized is the right way. So chalk another up to “hey I’ll make it work fast and easy with a MVF, but then didn’t build for the long term and got stuck” problem 😉

Thanks, I can flesh out the full VBA solution with all of the 15 MBs over the next few days, and integrate into the real solution and report back. I never new you could call a query for a text field (each record), never would have crossed my mind!!!
Go to the top of the page
 
June7
post Oct 19 2019, 09:51 PM
Post#6



Posts: 971
Joined: 25-January 16



AFAIK, cannot see hidden MVF table.

The 3 is the recordset field index. Index begins with 0 so the fourth field has index 3. I used index because I could not get field name reference correct.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Oct 19 2019, 10:10 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,606
Joined: 19-June 07
From: SunnySandyEggo


Hi June.
QUOTE (June7)
AFAIK, cannot see hidden MVF table.
You might ask Colin about that. He may know...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Oct 20 2019, 03:29 AM
Post#8



Posts: 971
Joined: 25-January 16



Colin's article about system tables http://www.mendipdatasystems.co.UK/purpose...bles/4594446646
Indicates MVF tables are 'deep hidden' and cannot be viewed nor queried.
Can only query table that has the multi-value field.

This post has been edited by June7: Oct 20 2019, 03:36 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
momo2000
post Oct 20 2019, 08:25 AM
Post#9



Posts: 69
Joined: 6-November 13



OK, spent some more time this AM understand what is going on and think I got my head around it. No need to see the hidden table for the MVF, so drop that.

Another question, you said: "This procedure is structured to be behind form or report. Would have to be duplicated behind both. Structuring to be more generic and placed in general module is complicated by including expiration date for each certification." I do need this on a few different reports and hate to have 99% duplicate code blocks, so wondering why I can't reference the table.field name of [Milton Adults].Climbing_Certification_Date instead of the referencing the control name of Me.Text91 ? I have tried to change it and make it work, and of course it doesn't, but it seems like it should.
Go to the top of the page
 
June7
post Oct 20 2019, 02:52 PM
Post#10



Posts: 971
Joined: 25-January 16



Actually, general module code easier than I thought. Consider this revised procedure.
CODE
Function GetCerts(strID)
Dim rs As DAO.Recordset
Dim str As String
Set rs = CurrentDb.OpenRecordset("SELECT [Milton Adults].*, [MB Name] " & _
"FROM [Merit Badges] INNER JOIN [Milton Adults] ON [Merit Badges].ID = [Milton Adults].MBC_For.Value WHERE PersonID='" & strID & "'")
Do While Not rs.EOF
    Select Case rs![MB Name]
        Case "Climbing"
            str = str & "Climbing Certification Expires on: " & rs!Climbing_Certification_Date & ";"
    End Select
    rs.MoveNext
Loop
GetCerts = str
End Function

Expression in textbox: =GetCerts([PersonID])

This post has been edited by June7: Oct 20 2019, 02:53 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
momo2000
post Oct 20 2019, 03:34 PM
Post#11



Posts: 69
Joined: 6-November 13



OK, that is some serious witchcraft!!!!!! Particularity the two lines:
CODE
Set rs = CurrentDb.OpenRecordset("SELECT [Milton Adults].*, [MB Name] " & _
"FROM [Merit Badges] INNER JOIN [Milton Adults] ON [Merit Badges].ID = [Milton Adults].MBC_For.Value WHERE PersonID='" & strID & "'")

Never in a million years would have come up with that iconfused.gif
I've now added a few more MBs in my little prototype and it is working! Will work to integrate in to the full solution over the next few days after my day job. After that, I should be able to post a page of the key report that goes to each troop (with things obscured) where this will come into play really want to you to see that.

Thanks SO MUCH!!!

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2019 - 07:15 PM