Solved Paragraph to Individual Fields Possible? (1 Viewer)

[jessAccess]

I'm new to Access as of this weekend. I wanted to create a database of recipes that I could then export as recipe cards, always formatted the same way. I've accomplished that task, but only with typing everything into individual fields.

I'm wondering whether it is possible to paste data into a long text field and then use a macro to copy each paragraph into an individual field.

For example... this recipe ingredient data...

1/4 cup oat four
1/4 cup unsweetened cocoa powder
1/4 teaspoon sea salt
1/2 cup extra dark chocolate, chopped or Lily’s sugarfree (4 oz)*
2 tablespoons butter or coconut oil, melted
2/3 cup Swerve Sweetener Confectioners
3/4 cup Greek yogurt
2 large eggs
1 teaspoon vanilla extract
1/4 cup extra dark chocolate chips, optional
Pinch cocoa powder for dusting

...would be entered into individual fields...

ing1 = 1/4 cup oat four
ing2 = 1/4 cup unsweetened cocoa powder
etc, etc

Is this something that is possible with a macro? If anyone knows of any articles I could read or has any advise I would greatly appreciate it.

Thanks!

 

[June7]

Each item should be its own record, not multiple field entries of same record.

Most anything can be done with enough code. If you typed each item separated with a character that would not otherwise be used, code can parse the string and save each item to separate record/field.

 

[MajP]

Could you build an interface that allows you to drop that and parse into seperate records? Sure except for the last line. I can take the first numerics and put in to a field called amount, and the next word into a field called units.
1/4 Cup
1/2 Cup
Then put everything else into a field called ingredient
oat Flour
Unsweetened coco powder

Easy until i get to line
Pinch cocoa powder for dusting

That would have to be entered as
1 pinch cocoa powder for dusting.

 

[Pat Hartman]

One reason to not mush everything into a memo field is because separating ingredients, quantity, and UOM allows you to produce shopping lists and also do searches for recipes that contain specific ingredients. Have some cottage cheese that is about to expire? Find a place to use it.

 

[jessAccess]

So I have a table and each record in the table is a full recipe (ingredients, directions, yield, servings, etc, etc). I do not need to separate out qty, unit and ingredient because I already have calculated fields that pull that data from a single line like "1/4 cup oat flour"

The issue I'm trying to solve is that typically I find a recipe and have a list of ingredients. But to enter the data into the form, I'd have to either copy&paste or type each ingredient separately. I'd rather be able to copy and paste an entire set of ingredients into a text box on my form and then use a macro to save each line from the text box into an ingredient field for the record (ing1, ing2, ing3, etc).

I've done VBA in Excel, but it seems quite different in Access, or at least I can't record actions to figure out how it works. And since I've only just begun using Assess this weekend I'm at a bit of a loss for where to start with coding a Macro for this use.

Can anyone point me in the direction of some resources I could use to help me figure out how to make each line of a text box into individual fields with a macro?

 

[June7]

Understand that macros in Access are very different from what is called a macro in Excel. What Excel calls a macro is really VBA code. No, Access does not have a macro recorder.

I already indicated in post 2 what would be required to accomplish what you want. Below is a very simple example of parsing a string and doing something with each element. This assumes a string with elements separated by "|" (pipe) character.

Sub GetIngs(strS As String)
Dim aryS As Variant, x As Integer
aryS = Split(strS, "|")
For x = 0 To UBound(aryS)
    Debug.Print aryS(x)
Next
End Sub

Consistency of structure is critical in string manipulation. Your code to populate fields would certainly be more complex. Exactly how many fields are utilized? There is a limit of 255. What is the maximum number of ingredients for any recipe? A field for each ingredient is not a normalized database design.

 

[gemma-the-husky]

@op

The hard bit is you being able to specify EXACT:Y what the finished result needs to look like.

You can store all the data in tables, or in a single xml file, or anything else properly structured, so that you can impose a view on the structured data.
The table (or any other structure) could carry data informing the presentation routine regarding how it should be formatted - similar to word paragraph templates.

Going the other way, if you have an existing file with the recipe, you could read and parse the text, and process it into your template structure - what you probably don't easily have is a way of deciding how to treat the formatting of each row in the existing file. I assume there is something in the file that would hold the formatting information.

 

[MajP]

The issue I'm trying to solve is that typically I find a recipe and have a list of ingredients. But to enter the data into the form, I'd have to either copy&paste or type each ingredient separately. I'd rather be able to copy and paste an entire set of ingredients into a text box on my form and then use a macro to save each line from the text box into an ingredient field for the record (ing1, ing2, ing3, etc).

Assuming that these recipes are formatted in many different ways it might be hard to come up with a rule. You may get a 90% solution if you built an ingredient library. Then loop your library looking for an ingredient and write to your db. Then you also have a form to easily add to your library any ingredient not picked up. Over time you library will get better and better maybe getting a 99.9% solution. You may need a couple fields in your table so if they abbreviate it ppr or slt it knows to add salt and pepper instead. I would make it like a wizard where it shows you what is found and then you can confirm or deny.

I am busy today, but think I could come up with a good demo if this would make sense. I actually have done something similar.

 

[MajP]

Normally these are all seperate fields for ingredient, quantity, and unit of measuere. The concept of using a library may work to save to time.
You can paste the recipe in the box and hit the import ingredients. You can see it got all the ingredients (because I cheated and added to the library)
In real life you are going to be missing things from the library. So when you are done you pop open the library and add the missing ingredients. Then you can import again to make sure you have all you ingredients.
Now unless I add more logic you will get doubles but they will be next to each other.
So for example ham is a subset of hambone, and Potato is a subset of Russet Potato. So you need to delete. That can likely be fixed
but will require some logic.

 

[MajP]

I'm wondering whether it is possible to paste data into a long text field and then use a macro to copy each paragraph into an individual field.

Rereading you post seems to me like you store if line of ingredients as as record. This is not a good normalized design, see the template I provided. I did not build that, but that is pretty proper design. If you just wanted to break each paragraph and import (I am not recommending that) then that would be extremely easy.