UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA For Append, Access 2016    
 
   
KristaC
post Mar 5 2018, 12:52 PM
Post#1



Posts: 82
Joined: 5-February 15



Hey guys!

This is a 2-part question...

1. I want three tables to append to one new table (allinventory) via an afterupdate event after someone makes a new record in each of the three forms (original songs, versions of songs, and song branches) -- does the code here below work to do that? I need to stack or union these tables for tons and tons of reports for my users, and I'm thinking if there is a table that programatically "stacks" them it will save me lots of effort.
Allinventory has the fields: allinventoryID (which is an autonumber), trackingsheetNEW_id, trkgfilename, versionID, versionfilename, branchID, branchfilename

2. I want to append the new table (allinventory) with two fields from each table. I want the ID from each table, which is a number field, and a text field called "songfilename". I think I figured out the VBA as below to append the ID, but I don't know how to insert the text field as well. I don't even know where to add it!!

Here is my thinking so far:

I would make three modules (...is this the right way...??) and then call the module in the AfterUpdate event of the songfilename field in each of my three forms:

MODULE 1
Sub StackOrigs()

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = currentdb
For Each tdf In db.TableDefs
' ignore system and temporary tables

If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "test") Then
'append to my new hidden table
StrSQL = "INSERT INTO " & "kc_allinventory.allinventoryID " & " " & _
"SELECT kc_trackingsheet.trackingsheetNEW_id " & _
"FROM " & tdf.Name & " ;"
DoCmd.RunSQL StrSQL
End If
Next
Set tdf = Nothing
Set db = Nothing
End Sub

MODULE 1
Sub StackVersions()

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = currentdb
For Each tdf In db.TableDefs
' ignore system and temporary tables

If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "test") Then
'append to my new hidden table
StrSQL = "INSERT INTO " & "kc_allinventory.allinventoryID " & " " & _
"SELECT kc_versions.versionID " & _
"FROM " & tdf.Name & " ;"
DoCmd.RunSQL StrSQL
End If
Next
Set tdf = Nothing
Set db = Nothing
End Sub

MODULE 1
Sub StackBranches()

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = currentdb
For Each tdf In db.TableDefs
' ignore system and temporary tables

If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "test") Then
'append to my new hidden table
StrSQL = "INSERT INTO " & "kc_allinventory.allinventoryID " & " " & _
"SELECT kc_branches.branchID " & _
"FROM " & tdf.Name & " ;"
DoCmd.RunSQL StrSQL
End If
Next
Set tdf = Nothing
Set db = Nothing
End Sub
Go to the top of the page
 
GroverParkGeorge
post Mar 5 2018, 01:41 PM
Post#2


UA Admin
Posts: 33,506
Joined: 20-June 02
From: Newcastle, WA


Perhaps you can do this. However, it's the long way around the block and doesn't take advantage of one of the great strengths of Access.

I would recommend that you do this with subforms, not with code.

Let's start with a review of table design. I think you are trying to duplicate the song file name in two places? If so, that's not an appropriate design. If not, I'm not sure why you don't just bind the appropriate table to the form and not worry about copying fields in VBA.

Here's a review of the basics of table design. Invest some time in learning more about that.

For an example of how to handle related data in a main form/sub form design, download and study this demo database. Note that although it does use code, none of it is required to append fields to multiple tables.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
KristaC
post Mar 5 2018, 01:50 PM
Post#3



Posts: 82
Joined: 5-February 15



Thanks for your post, Grover. However, the schema regards IP and the business logic requires three separate tables for songs, versions and branches (and each have a different filename). While "songs" are the original IP and versions and branches are actually in a many relationship to the original, versions and branches are also their own inventory asset. All inventory includes all -- and the fields in all three tables are vastly different. I need a way to append the allinventory table with at least the ID of each row in each table and that row's respective filename field from each table.
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 08:23 AM
Post#4


UA Admin
Posts: 33,506
Joined: 20-June 02
From: Newcastle, WA


Thanks. Unfortunately that doesn't explain the situation in a way that I can grasp.

For example: "... the schema regards IP ... " I assume you mean that the database is intended to track Intellectual Property? Or does IP represent a different concept?

Also: "...business logic requires three separate tables for songs, versions and branches (and each have a different filename)" So, that's a total of three tables? or nine tables? And where does the concept of "a different filename" come into play?

I would expect fields in different tables to be different. That's one reason we have different tables, but I'm not sure how that impacts this situation. Moreover, in any properly designed Relational Database, there is almost never a valid reason to store the same data in more than one field. I.e. "... and that row's respective filename field from each table". In other words, once you have established a relationship between two tables by inserting a foreign key from the parent table into the child table, ALL of the fields in both tables are available via a query and there is no reason to copy the same data field (e.g. "filename") into another table.

In short, unless there is something radically different here that is not yet clear, you really do need a basic one-to-many, or perhaps many-to-many table schema, which are best supported in Access with a main form/sub form interface design.

I do think it would help to describe--in business terms, not database terms--what you are tracking here. Starting with the definition of IP, original, versions, and branches. While I think I have a pretty good idea, it'll help to confirm what is going on and why.

Thank you.

George Hepworth

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
zaxbat
post Mar 6 2018, 08:53 AM
Post#5



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Not clear....do you say that any time anybody makes a single update to any of the 3 tables that you want to run a loop through all recs of every one of the 3 tables and append all data into the allinventory table? Your all inventory table will become very large ....is it a log file...maybe if you are using it as a log file this makes some sense...otherwise I question what you are trying to do.


In the afterupdate event you could just take that single updated record and append it into the allinventory table. That might make some sense.....but not so sure. I think you would want to see if the record already exists in the allinventory table then append it if not but update it if it does already exist. If you want to avoid duplicates then you will need to have unique index for each record.

I would like to see the field layout for the allinventory table.

Now, what is the text field that you also want to include? This should not be difficult at all, but it does depend on if it is a simple litteral or if you need to assemble the text as a unique string from multiple sources.

Here is an afterthought......instead of appending to the allinventory table each time somebody updates.....you could delete it and run a full merge of all 3 tables into a fresh new allinventory table on a command button click instead. This is what I would do to keep the whole operation cleaner.
This post has been edited by zaxbat: Mar 6 2018, 09:30 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
nuclear_nick
post Mar 6 2018, 09:56 AM
Post#6



Posts: 1,586
Joined: 5-February 06
From: Ohio, USA


I don't see this explicitly mentioned yet...

But IMO, if you have to, in a relational database, create a new record programmatically in a table (or in this case 3 tables) for every new record in another table, then you've probably made an error somewhere in your relationship/table design.

Just an example why this is bad. Are you also going to program something in the other three tables that if something is deleted, it's also going to keep things clean? No 'orphan' records? That could mean a lot more programming and testing, when it could be something fixed in your initial design.

Good luck, and happy coding!

(Okay, I see it mentioned by GPG... I just skimmed... sorry. But theory still holds.)
This post has been edited by nuclear_nick: Mar 6 2018, 09:57 AM

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 04:32 AM