save function value to specific row in a table

[coasterman]

I have a function of Allen Brownes http://allenbrowne.com/func-concat.html that creates a concatenated string from user entry in a sub sub form, that string describes (by means of the various tags) the key content of the main record and its sub forms. The concatenated string needs to be saved in the parent forms table to perform some search duties against the parent form.

Just by way of background the data being fed into the database is extremely diverse and with no easily defined structure, one record may be a simple contact record, another may be legal precedent another may be an internal business rule, there are countless other class of records but the key thing is all records need to be searchable in a single pass. After a number of false starts I had the idea of users adding keywords within the records subforms.. This way the final search can identify any potential records of interest regardless of the class of the record itself.

The form and field names below are fictional but may give a more understandable context

The tags are entered by a popup form which is linked to the sfrmAddressIssues which is two layers below the parent form.

Parent form =frmContacts
subform1 = sfrmAddresses
subform2 = sfrmAddressIssues
A command button on subform2 launches a final popup continuous form 'sfrmTags' (the FK is inherited from the sfrmAddressIssuesID) The function concatenate is working great and I have a memo field on the parent form whose control source is the function showing the tags all nicely together

As stated at the beginning I am now trying to find out how to plug the concatenated tags string from the function directly into the parent forms table (tblContacts) whenever the concatenate function is triggered.

The parent table PK is ContactID and the field which I am trying to populate with the tags is globalTags

I'm still learning about record sets but I suspect it's probably something I need for this task I just don't know how currently.

I should mention the popup form foreign key is the PK from Subform2 so again I guess I need to uncover the ContactID so that the code knows which row of the parent table to plug into, thinking aloud I guess I could set a tempvar to reference the ContactID on the parent form

Anyway there's my conundrum and as ever any help here would be greatly appreciated.

 

[Cronk]

If you are already displaying the concatenated child table field records on the parent form, why save the data in the parent table? Goes against normalization rules.

 

[coasterman]

Hi Cronk

Yes I do appreciate that this method is against best practice both in terms of normaiisation and in effect storing a calculated value.

The issue is my coding skills just aren't up to writing routines that will allow me to create a multi criteria search against to concatenate output and then presumably create a temp tabledef and SQL in vba to execute the search. I wish they did and I'm always working to improve my coding skillset, this however is just too complex an issue for me at present and with still with an awful lot to do to complete this project I have to keep in mind the delivery date to my boss.

Apologies therefore if my request goes against best practice but I have to be realistic about what I'm capable of

 

[static]

In your other thread you had this example :

Main record Countrytxt = 'England'
Sub record1 Citytxt = 'London' Buckingham palace Queen Parliament ethinic food centre
Sub record2 Citytxt = 'Coventry' and the CityTagtxt field might contain the words - Blitz Lady Godiva ethic food centre

So you seem to be saying that tags are entered into a table but then moved into a memo field for each main record?
Memo fields are best avoided for various reasons.

Leave the tags in the table, joined to the sub record by id.

England
    London
        Buckingham
        palace
        Queen
        Parliament
        ethnic
        food
        centre
    Coventry
        Blitz
        Lady
        Godiva
        ethnic
        food
        centre

That will allow you to use a query to count the number of 'hits' for each search. e.g.

search result for 'ethnic lady food'
Coventry      returned 3 matches
London        returned 2 matches

search result for 'queen food'
London        returned 2 matches
Coventry      returned 1 matches

search result for 'queen palace'
London        returned 2 matches

'split search string and generate the SQL query sorted by best match
For Each s In Split(SearchString, " ")
    If Len(ss) Then ss = ss + " or "
    ss = ss + "tag = '" + s + "'"
Next

SQL = "select count(1), subtable.subfield from " + _
    " tags inner join subtable on tags.subrecordID = subtable.subID where " + ss + _
    " group by subtable.subfield order by count(1) desc"

'Open the recordset and print results
Debug.Print "search result for '" + SearchString + "'":
With CurrentDb.OpenRecordset(SQL)
    Do Until .EOF
        Debug.Print .Fields(1), "returned"; .Fields(0); "matches"
        .MoveNext
    Loop
    .Close
End With
Debug.Print

 

[coasterman]

Hi and thanks for the responses

The tags are as you say stored in a table along with the tag PK and the FK for the subform2 record in view.

I'm using a qry including the tag table and the parent table (and the two tables in between) to uncover the parent PK 'ContactID' ,using the qry also allows me to ignore duplicate tags by the SELECT DISTINCT.

Now I have the ContactID for the parent record I can use that value in one of the arguments of the function so the Control Source of the memo field (txt field size will be exceeded in many cases) is now =ConcatRelated("Tag","qryGlobalTags","ContactID = " & [Forms]![frmContacts]![ContactIDtxt])

The Function is fired from the Form_Current event

That gets me to where I am so far…

The next phase is to incorporate another one of Allen Brownes functions which enables multi criteria searching against one field. I have already employed a slightly modified version of the code in an older version of the database where users were manually adding tags directly into a bound memo field on the parent form. The way I have set it up users can define up to 8 search words and it works great and users really like the ability to make the search as wide or as narrow based on the number of keywords they enter.

To incorporate the concatenate function with the multi criteria seach my best thought was to have the function send the evaluated result and save it to a field in the parent table each time it is triggered. I would then simply dispense with the unbound memo field and the new memo field would have it's control source set to the value now stored in the parent table - job done! I just don't know how to get the result of the concatenation function into the parent table.

I'm sure there are more elegant ways of providing the multicriteria search with the concatenated string from the first function and if ever work that out then I will change it but for now I need to focus on what is within reach, I'm at the limits of my knowledge in various aspects of the project and this is just one of them.

I know I will need to again uncover the correct ContactID to identify the correct row in the parent table and I guess that should simply be a case of referencing the ContactIDtxt control on the main form. Beyond that I'm stuck, and getting desperate :-(

 

[static]

So ConcatRelated generates a string from related records in your table/query qryGlobalTags.
Excellent.

You shouldn't need to concatenate anything. Generating long strings to search on is inefficient and less flexible than keeping everything separate.

Can you attach a copy of your database?

 

[coasterman]

As suggested I've uploaded a pared down version with just the forms and tables relevant to the original question.

In the right portion of the form that launches there are tow command buttons Basic search and Global

Currently they both launch the same search form (the global search is the one that isn't functional as it needs to point to a field in the parent table which as I have mentioned needs to be populated by the concatenate function output. I've left the old basic search in with it's memo field on the parent form so you can see how it works.

The tags are coming from the sub records accessed via the Address location and then the Issues form below that.

Once a couple of keywords are entered the list of result provides a cut down version of the form (I forgot to add a summary to each of the dummy records so the results may not make sense!) Once the user identifies a likely record of interest from the search results they double click in the row of the continuous form and that takes them to the main parent record.

Users want this same functionality but I need (for future development) the tags in a table form so that I can write queries for data mining based on any number scenarios, some identified , some yet to be realised I am sure!

Hope this is now a little more clear, I've been pretty rubbish in trying to explain this previously

Thansk again

 

[static]

Man, I need a bigger screen.

View attachment ib.accdb

see attached

List of tags is added as a subform and contact search is a popup.

I deleted a load of unnecessary stuff including your code.

 

[coasterman]

Please excuse the absence of reply, I managed to break a toe last week which had put me in a decidedly poor mood.

I concede I don't fully understand the example you kindly provided which is a problem of itself if I need to try to work out how to develop the idea and incorporate it myself into the final build. The not in list routine is something however I will be stealing and is going to be a great help

I will keep reviewing your example but with a now even closer deadline I was hoping any one passing by here could tell me if it is possible to modify the concat function so it inserts the concatenated string into the address issue table.

This will allow me to write any new SQL queries and then reuse the multi criteria form as I had when the tags were being entered into the bound field on the parent form and gets me past this present road block.

I’ve stripped down my original database but keeping the basic structure which hopefully will be much clearer now.

Thanks in advance

 

[static]

Ouch.

As far as I recall the concat function returned a string.
The easiest way to add data to a table is to set the value of a bound control on a form.

myboundcontrol = concatfunction(...)

It's your database and it's your choice if you take advice or not. But Access is a relational database. If your database isn't properly normalised the advice you'll receive will be to normalise it. If you ignore that advice you'll probably find further advice shortcoming. If for no other reason than non normalised data just creates further problems down the line.

None of my business, but if I was given a task to do something that I had no experience in with no training or support, I'd suggest my employer take a running jump or be grateful for whatever they receive when they receive it.

 

[coasterman]

Hi again,

I'm probably being dense here but as the control source of the existing unbound text box is the function itself how would I create a control and have it bound to a table and also pass the arguments to the function?

I thought the way to do that would be to insert the function result into the correct row of table at the end of the function code, I just don't know how to do it.

I don't suppose if you had a spare moment you could impose on you to modify the stripped down db I posted yesterday?

I should say I'm now only trying to get the concat result into the AddressIssueTbl rather than the parent table as I realise now I can uncover any results I need with that and some SQL.

 

[static]

You want the value to be updated whenever the tags change. So whichever form the tags are inputted/edited on, use an event to set the value of a bound control.

For example:

On form IssueAddressDetailsfrm
Remove the concat function from AddressTags and bind it to the field.

In sfrmTags1 add an on close event.

Private Sub Form_Close()
    If Me.FilterOn Then
        Dim strTags As String
        strTags = ConcatRelated("Tag", "tblTags", Me.Filter)
        Forms!IssueAddressDetailsfrm!AddressTags = strTags
    End If
End Sub

I used the filter set by IssueAddressDetailsfrm so disable user filters

Private Sub Form_Open(Cancel As Integer)
    'turn off filters so user can't change them
    Me.AllowFilters = False
End Sub

 

[coasterman]

@ Static. First signs are very promising - thank you so much, you have made my day!