Macros, VBA, Autopopulation of Data

[pdevito3]

I have a request that's probably a little of the box.



Right now, I have table 1 with (among others): ParentID (primary key), Parent Name, Daughter Count. I also have table 2 with (among others): DaughterID (primary key), ParentID, Daughter Name. Parent Name in table 1 might be W-1, daughter count might be 4, and daughter Name might be W-1A. Parent ID in table 2 is just a 1, 2, etc that has a relationship with parent ID from table 1.

I want to have a macro that automatically populates "x" number of daughters in daughter name based off of the parent's daughter count once a field in table 1 is updated. The resepective parent ID should also populate next to each item. For example: W-1 (parent ID = 1) can make 4 daughters. Once checked it should create four 1's in parent ID of table 2 and W-1A, W-1B, W-1C, and W-1D in each of the daughter name fields.

I have a macro that works and makes a new entry after each update, but I don't know how to change it to accomindate multiple entries based off of the field or add the appropriate alpha character. I wanted to get into VBA to try and modify the code, but the convert macro to VBA is greyed out in design mode for my macro. This is my current macro:

If Updated("Mated") Then | Create a Record In tblTable2 | SetField | Name: tblTable2.ParentID | Value: tblTable1.ParentID | SetField | Name: tblTable2.DaughterName | Value: tblTable1.ParentName |

Any help is greatly appreciated! Thanks

[June7]

Really should not save aggregate data, calculate when needed.

Creating a unique custom ID (the daughter name) requires VBA code. This has been discussed often in forum. Start with https://www.accessforums.net/access/...ers-34451.html

[pdevito3]

Well, the idea behind it is to elimnate manual entry. If my daughter count was 80, I don't want somebody to have to enter in a 80 values for something that could be automated. I'm thinking maybe a list form to select what I want to modify based off of now. That way it would be a one time thing and wouldn't be so reliant on the other data.


Any other ideas?

Thanks agian

[June7]

Not sure I understand. You want to automate creating a set of records that have an incrementing indentifier? The number of records created would be determined by some derived count? Derived from what?

[pdevito3]

I want someone to enter in the parent name and number of daughters it can make (4, 10, 50, it depends on the parent) and then have some way of automating the daughter creation in another table. Whether it be some kind of button in a form, something built into the table, or something else I don't know about, I want it to be automated.

Say I have a list of 20 parents, each with their own potential daughter count number. I want to press a button to populate the daughter table with the right parentID and the corresponding daughter name (w-1A, w-1b, etc.). Today, I made a list box with all of the parents I wanted to do this with, made a button, opened the vba code for the click event of that button, and I couldn't figure out the vba. I would think it would be a simple if clicked create a new record. Then use a for daughter count. I can't use the create a new record function unless it's a data macro in a table though, so I don't know how to approach it

[June7]

Something like:

For i = 1 to Me.textboxCount
CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) VALUES('" & Me.ParentID & i & "', #" & Date() & "#, " & Me.UserID & ")"
Next

The above shows an example of INSERT sql action for 3 field types - text, date, number.