Editing Form data before saving in a table

[dgmdvm]

I have a data entry form that allows the user to add gift idea items based on category, for example, clothing, toys etc. The user can enter the data in a single form field separated by commas. When I originally set this database up, I allowed comma delimited data to be saved in a single field which I have since learned is poor design, plus it is causing problems for me with some other features I want to add to the database. I have created VBA code to split up the strings into individual items and then store it in separate text boxes in another form. What I would like to do is run that code on the initial data entry so that when the record is saved to the table, the original strings are split and a single item is saved to each field of the table. I have no clue how to do that. Here is a portion of the code I used to split the strings and store each item in s separate text box. It uses the strsplit function. It loops through the comma delimited list and takes each individual item and places it in sequentially named text boxes.

Private Sub Form_Open(Cancel As Integer)

'code to parse the clothing field

Dim temp() As String

Dim y As Integer

Dim BS, BY As String

Me.Textbox35.SetFocus

strinput = Me.Textbox35.Text

temp() = strsplit(strinput)

For y = 0 To UBound(temp())

Me("Textbox" & y).SetFocus

Me("Textbox" & y).Text = temp(y)

Textbox35.Visible = False

Next

I'm not sure where to even start the code Could I leave the data entry fields unbound and bind the output textboxes to the underlying table? I was wondering if there is a way to avoid the whole step of putting the parsed data into textboxes and have it saved directly to the table?



thanks

[pbaldy]

In my view, the design still isn't right. The items should be records in a related table, not fields. More here:

http://r937.com/relational.html

its like the products part way down.

[dgmdvm]

So for my tables, I should have one table that lists the names of the individuals, (there is an ID number for each person -primary key), and a second related table that lists the gift items (Call it the Gift Items Table). Unlike the example you gave me the link to, I don't need to worry about gift quantities etc. I do have different types of gifts- clothing, toys, kitchen items, bedding; I assume these could be different fields of the Gift Items Table. Then, after I parse my entries in the original form, they can be appended as additional rows in the Gift Items Table? I'm still unsure of how to get the entries parsed and into the table, even if it is a separate related table.

Thanks.

[pbaldy]

Offhand, no, the different items shouldn't be fields. Think about it from a different perspective. Getting a new item, say pillows, shouldn't require design changes to the application. You might want to reread that link, or search on normalization.

To your question, within your loop you can append a record into the table, rather than populate a textbox. That could be done with an append query or the AddNew method of a recordset.

[dgmdvm]

Offhand, no, the different items shouldn't be fields. Think about it from a different perspective. Getting a new item, say pillows, shouldn't require design changes to the application. You might want to reread that link, or search on normalization.

I read the linked article on Normalization- easy to understand when you read it, hard to implement when looking at the database. My main table has 4 fields- ID number, family number, relationship (mother, father, daughter, son) and age. I have simplified my Gift Items Table- it has 4 fields ID number, Gift Item, Notes (descriptive information about clothing styles that a person might request) and clothing size. Look better?

I'll play around with the loop on my own and check back in if I get stuck.

Thanks for the help.

[ssanfu]

PMFJI,

Is it possible for you to post your dB? (Do a Compact & Repair, then Zip it)

Wouldn't a main form/sub form arraignment work?

[dgmdvm]

PMFJI,

Is it possible for you to post your dB? (Do a Compact & Repair, then Zip it)

Wouldn't a main form/sub form arraignment work?

That is my plan

Here is a zip file of the table portion of the database and the new forms that I am creating for it. So far, the append commands don't work and there are likely snippets of code that I have deleted from my original database. I also want to point out that my old working database copy works but has poor design- I am trying to learn the proper way to set the database up so I am redesigning it. If you want to see how it all works in total, I have attached the original database as well-it isn't well designed, but it works. The "old database is called "gift Tree data 2016. Open the Master list table and you can see the design problems.

Thanks

[dgmdvm]

I don't think my attachments made it. Here is a second try

[dgmdvm]

I have one additional question that has come up since I started my redesign with a form/subform. If I have a command button placed on the form to perform an action, I can't get the code to set focus on the textbox in the subform unless I actually place the command button in the subform. I've tried to use Gotocontrol and .setfocus with no success. Is the correct convention to put the command button in the subform or is there syntax that will allow me to start an action in the main form, perform an action in the subform, and hen post the entire record?

Thanks

[dgmdvm]

I've made progress in my design so the questions I asked above do not need answers.

Thanks

[ssanfu]

I looked at your dB and saw what I consider design flaws....

You had Shirts, Pants, Shoes as field names. What happens when you want to add Jacket or Tie or Dress?
You would have to redesign your tables, forms, queries and reports.
So I modified tables and forms......

I took out some spaces in names and renamed some controls. Access creates controls with names like "Text155" (very poor name ).
I added "Option Explicit" at the top of every module. (should be required IMHO)


Use these suggestions or not. Just wanted to provide an example of what you might do....

Good luck with your project...

[dgmdvm]

I looked at your dB and saw what I consider design flaws....

You had Shirts, Pants, Shoes as field names. What happens when you want to add Jacket or Tie or Dress?
You would have to redesign your tables, forms, queries and reports.
So I modified tables and forms......

I took out some spaces in names and renamed some controls. Access creates controls with names like "Text155" (very poor name ).
I added "Option Explicit" at the top of every module. (should be required IMHO)


Use these suggestions or not. Just wanted to provide an example of what you might do....

Good luck with your project...

Thanks for the response. I modified my design while I was waiting for your response and asked an additional question in another thread. Here is the URL for that thread https://www.accessforums.net/showthread.php?t=63447. You have already given me some input there. I just attached my redesign in that thread. Hope it looks better. I will incorporate the suggestions you made here later today. Again, thanks for all the help.