A form to find datasets and change some data?

[Declen]

Greetings.



Is it possible to make a form that would allow you to find several datasets via drop-down-boxes and automatically change some data in them?

I have a table with just two columns: unique names and yes/no checkboxes. However, there are many names, and I would need to set several checkboxes at once in various places. So scrolling and doing this manually gets tedious.

Thus I would like to make a form where I would find a name I need in a drop-down-box and it would automatically set the corresponding checkbox to yes. Better yet, if the form had several such boxes and did the check for all the names in them at once. The first part is already done, so, what interests me is that autochecking part.How could it be done?

[June7]

Most anything can be done with enough code. Really need more info. Provide sample of your data. If you want to provide db for analysis, follow instructions at bottom of my post.

"if the form had several such boxes and did the check for all the names in them at once" - I am not really understanding this. Several checkboxes? All what names where?

[ArviLaanemets]

The simplest way (on fly, so you have to check the exact syntax):

A button runs a procedure, which:
1. Asks for list of names - user enters a string like <"Name1","Name2", ...,"NameX">, which is then saved to variable (e.g. strNameList);
2. Saves to another variable (e.g. strQuery) a querystring like
strQuery = "UPDATE YourTable SET YourCheckBox = True WHERE YourName IN (strNameList)"
3. Runs the strQuery

[orange]

Declen,

It would be helpful to you and readers if you could provide more contextual information.
What is the underlying "business issue/opportunity"? Names are not all that unique, so potential duplication is a possible side effect of your proposed approach. I say possible because we do not know the details. If these names are clients or patients..., then the criticality of any changes could be an issue. More info please -- even an example or two.

Paraphrasing what June said --- anything can be done--just a matter of SMOP. (small matter of programming.)

[Declen]

Thank you for your response.

June7, Orange,

There aren't really many details worth discussing. It's not a business application. it's just a small database for my personal use, It already works as intended, however, there is a mid-step I have to do manually in order for it to work. So, I'd like to automatize it.

Now the details:
There is a video game franchise called Doodle God. In there one combines two "elements" to make a new "element". And I've made a hint-tool to show what new elements can be made now based on which elements are already made.
It's very simple: the main table contains the recipes and has four columns: ingredient 1, ingredient 2, product, episode (a chapter in which the recipe is available). The second table contains the list of all possible elements and has two columns: element names and checkboxes to mark them as made/discovered. And there are a few almost identical queries, one for each episode. Each yields a list of products from recipes where both ingredients are already discovered and the product is not, and the recipe is available in the respective episode.
As I said, it works as intended, but I have to mark the elements as discovered in the second table manually.

I'll attach the database to my reply.

[Declen]

The simplest way (on fly, so you have to check the exact syntax):

A button runs a procedure, which:
1. Asks for list of names - user enters a string like <"Name1","Name2", ...,"NameX">, which is then saved to variable (e.g. strNameList);
2. Saves to another variable (e.g. strQuery) a querystring like
strQuery = "UPDATE YourTable SET YourCheckBox = True WHERE YourName IN (strNameList)"
3. Runs the strQuery

Do I understand correctly, in your solution a user would have to type the names in manually?

[June7]

The constructed name list would require apostrophes around each string element. The quote marks will error in SQL constructed in VBA. Also, variable must be concatenated, not embedded between quote marks.

'Name1','Name2','Name3'

CurrentDb.Execute "UPDATE YourTable SET YourCheckbox = True WHERE YourName IN (" & strNameList & ")"

Teaching and expecting users to correctly enter a string array of names into a textbox is road to insanity. Use a multi-select listbox and VBA loops through selected names to build string. A form could be designed to emulate listbox functionality that allows arbitrary selection of non-consecutive records but I've never done that. If you can apply filtering to data to select records, then code to update that dataset is relatively simple.

Looking at your db now. In English, Recepies is a misspelling of Recipes.

There is only one checkbox (yes/no) field and it is in Elements table. I am not understanding what condition requires setting this field to True. What constitutes "Done"? What are the rules?

[Declen]

Looking at your db now. In English, Recepies is a misspelling of Recipes.

Yes

There is only one checkbox (yes/no) field and it is in Elements table. I am not understanding what condition requires setting this field to True. What constitutes "Done"? What are the rules?

As I've explained, I start the query. It yields elements I can find. I play the game; find new elements, then find them in Elements table. Manually set checkbox to True, so they are marked as found/discovered (this constitutes "Done"). Then I actualize the query, so it excludes newly found elements and includes new elements to find from the recipes that have newly found elements as ingredients.
What I'd like to have is to find elements via drop-down-box rather than by scrolling trough the table, and than set the checkboxes for those elements to True.

[June7]

Sorry, missed post 5. Wikipedia helped me understand the game. A pair of elements combine to make another element. And that new element can actually be paired to create even another element. Like an assembly line or manufacturing type database.

Water + Fire = Alcohol

Alcohol + Grass = Absinthe

You have a form that has 6 comboboxes to allow for 6 elements to be selected. So you want to search Recepies for pairs of these 6 elements?

Is an element considered "Done" if it is used at least once in a pair? Your data does not support that. Elements that have not been used in any pair are checked. And it can't be Done just because element is in PR column - Air is checked but it is not in PR.

So, I still don't understand rules for checking Done.

Philosopher's Stone element has apostrophe - apostrophe in data can cause issue when building SQL in VBA. I am running queries in your db using DCount() expression and that is also bugging on the apostrophe. Suggest removing 's.

[Declen]

A pair of elements combine to make another element. And that new element can actually be paired to create even another element. Like an assembly line or manufacturing type database.

Water + Fire = Alcohol

Alcohol + Grass = Absinthe

Yes, that's correct.

You have a form that has 6 comboboxes to allow for 6 elements to be selected. So you want to search Recepies for pairs of these 6 elements?

No. I want to select up to 6 elements from Elements table and set them all as Done. (BTW, if it was a commercial project, I've wouldn't dare to do anything as reckless)

Is an element considered "Done" if it is used at least once in a pair? Your data does not support that. Elements that have not been used in any pair are checked. And it can't be Done just because element is in PR column - Air is checked but it is not in PR.

No, An element is considered "Done" if it's made/discovered and available for further combinations. Air and other three elements are checked because they are given from the start and we can build combinations from them right away. So, well you call the "ToDo" query, it will yield six other elements, that can be made combining those four. One of them is Alcohol. So, in the game I combine Fire and Water to get Alcohol. Now Alcohol is discovered and I need to check it as "Done". When I do it and actualize the query, Alcohol will disappear from the results and be replaced with a new element - Vodka. For Vodka cam be made by combining newly discovered Alcohol with Water, thus having Alcohol checked as "Done" makes this recipe available.

Philosopher's Stone element has apostrophe - apostrophe in data can cause issue when building SQL in VBA. I am running queries in your db using DCount() expression and that is also bugging on the apostrophe. Suggest removing 's.

Thank you for bringing this up. I'll check whether it currently works.
PS.: I've checked it out and it works fine at least for now: the query doesn't seem to mind. It yields both Philosopher's Stone as a product and products of any recipes that have Philosopher's Stone as an ingredient.
But if it will constitute problems with implementing of what I want - I'll change it. Also, must confess, that I have no clue about VBA. I only know SQL.

[June7]

No. I want to select up to 6 elements from Elements table and set them all as Done.

Okay, that's fairly clear-cut requirement. Several ways to code this.

This could probably be done entirely with a parameterized query object (I avoid parameterized queries). It could look like:

UPDATE Elements SET Elements.Done = True
WHERE (((Elements.El)=Nz([Forms]![Set Elements to discovered]![Kombinationsfeld0],"") Or (Elements.El)=Nz([Forms]![Set Elements to discovered]![Kombinationsfeld10],"") Or (Elements.El)=Nz([Forms]![Set Elements to discovered]![Kombinationsfeld12],"") Or (Elements.El)=Nz([Forms]![Set Elements to discovered]![Kombinationsfeld14],"") Or (Elements.El)=Nz([Forms]![Set Elements to discovered]![Kombinationsfeld16],"") Or (Elements.El)=Nz([Forms]![Set Elements to discovered]![Kombinationsfeld18],"")));

And then could code macro or VBA to call parameterized query object (I don't use macros).

Options in VBA not using parameterized query.

Have VBA for each combobox AfterUpdate event calling a Sub that runs SQL Update. Example showing one combobox, change references as necessary for the others:

Private Sub Kombinationsfeld0_AfterUpdate()
SetDone Me.Kombinationsfeld0
End Sub

Sub SetDone(strE)
CurrentDb.Execute "UPDATE Elements SET Done=True WHERE El='" & Replace(strE, "'", "''") & "'"
End Sub

Another approach is to have function running the SQL:

Function SetDone(strE)
CurrentDb.Execute "UPDATE Elements SET Done=True WHERE El='" & Replace(strE, "'", "''") & "'"
End Function

Now in the combobox AfterUpdate property: =SetDone([Kombinationsfeld0])
Again, change combobox reference as necessary.

Note in the VBA use of Replace() function to deal with possible apostrophe.

[Declen]

Options in VBA not using parameterized query.

Have VBA for each combobox AfterUpdate event calling a Sub that runs SQL Update. Example showing one combobox, change references as necessary for the others:

Private Sub Kombinationsfeld0_AfterUpdate()
SetDone Me.Kombinationsfeld0
End Sub

Sub SetDone(strE)
CurrentDb.Execute "UPDATE Elements SET Done=True WHERE El='" & Replace(strE, "'", "''") & "'"
End Sub

Tried out this solution. It works.
Thank you very much!

Could you explain the syntax in the WHERE statement? Specifically, what '" in El='" stands for?

[June7]

The apostrophe is a delimiter character. This defines a text string. SQL requires parameters for text fields to be enclosed in apostrophe or quote. These characters must be in pairs when used for this purpose. This is why apostrophes in data is an issue when building SQL string in VBA. In the VBA construction I used apostrophe as delimiter. To force treatment as literal text, it would be doubled - this is called escaping special character. Instead of Replace function could have done: WHERE El=""" & strE & """"

Date/time field requires # character to define a date/time input value.