After Update Event, Populate Text Box Based on Contents of a Textbox and Dropdown List

[larry11]

Hello, Good morning!

I am hoping someone can please help me with an after Update problem I am having within a form in an Access 2016 database.

I have a textbox (TextboxA1) that I would like to populate based on criteria firstly within a textbox and secondly in a dropdown list, but only if both criteria are met.

Data Source 1. (Text Box)
Within another text box there are various coma separated values, for example;

Wool Jumpers, Cotton T Shirts, Trousers,

Data Source 2. (Drop Down List)

List of 4 options;


1. (Blank - Empty cell, No Contents)
2. Small
3. Medium
4. Large


Examples of a Query

If "Wool Jumpers" is contained anywhere in Source 1. AND Source 2. is "Small" - Show "Wool Jumpers Small" in the TextboxA1

If "Wool Jumpers" is contained anywhere in Source 1. AND Source 2. is "Large" - Show Wool Jumpers Large" in the TextboxA1

If "Wool Jumpers" is Contained anywhere in Source 1. And Source 2. is Blank - Empty Cell, No Contents - Show nothing (remain blank) in TextboxA1

If "Wool Jumpers" is NOT contained anywhere in Source1. AND Source 2. is "Large" - Empty Cell, No Contents - Show nothing (remain blank) in TextboxA1. (Both criteria must be met to show a result)

If the criteria is met and shows but then either of the sources are changed to not satisfy the requirements then the TextboxA1 is again blank.

I hope I have explained this clearly.

May I also thank you in advance for any assistance you can offer.

many thanks

[orange]

Tell us about the business processes involved, so we have some context for your issue/question.
What are your tables and relationships ? Please post a jpg of your relationships window.

[larry11]

Hello

Thank you for your response;

To try and answer your questions,

The database is built using only 1 table, I am using it to send emails that address potential clients queries regarding the products we offer and their availability

Within the mail merge there are "Rules" that make the email more relevant to the clients questions, and as the merge fields are used to trigger the "Rules" I need to have slightly different merge field contents, for example "Wool Jumpers, Large" This would add the paragraph in the mail merge Rules that gives details about Large wool jumpers as opposed to for example "Small Wool Jumpers"

I have been trying to get it to work and have the following which almost works! except that if it has updated with "Large" selected from source 2 and then I change it to small TextboxA1 does not change but stay showing the original "Wool Jumpers; Large" - although if you delete the "Wool Jumpers" form Source 1 it doe s show nothing (which is right and obviously triggered by the "Else """ part of the code.

Private Sub Data_Source_1_AfterUpdate()
If Me.Data_Source1 Like "*Wool Jumpers*" Then
Me.TextboxA1 = "Wool Jumpers; " & Me.DataSource_2
Else
Me.TextboxA1 = ""
End If

End Sub

I hope this give you a better idea of what I am trying to do.

thank you again for taking the time to help.

[davegri]

You need the same update code to run when DataSource_2 is changed:

Private sub DataSource_2_AfterUpdate()
    Call Data_Source_1_AfterUpdate
End Sub