Track has:
1. Order Number
2. Name
3. A Type (Instrument, Controller, Sound Clip)
4. A Preset
5. 1 or more Effects
Preset has:
1. Name
2. VST (the program that generates the sound, or sample collection name.)
3. Brand (The creator/seller of the VST)
Note: It is important to know the VST and Brand to find the Preset as there 1,000's of presets and any Preset name may also exist in another VST. i.e. "Grand Piano" may be a preset in nearly every VST.
VST has:
1. Name
2. Version
Brand has:
1. Name
2. Alias (The company name isn't always the name associated with the VST. Or, a Brand has multiple trademark names for categories of VST's.)
Effects have:
1. A Preset
2. Order (The order in which effects are applied to a track are important. i.e. Distortion may applied before Reverb on a Guitar track. Switching the order would change the final outcome.
Here is an actual datasheet containing all the info I am trying to collect. You can see the different components and especially how a track may have multiple effects. The track Preset name has the VST above it. The VST has an "I" to the left indicating it is an instrument type VST. The Effects are marked E!, E2, etc to indicate they are effects and what the correct order should be.
I think it's the conceptual issue that I am struggling with. Are you trying to digitise a finished song to show a time graph with all the instrumentation and voices separately - so that the PC could actually play the song if it had the instrument capability.
What's the atomic level for this? Is it a single bar of the song? Are effects applied to individual bars, instrument by instrument.
I don't see how you can have an effect such as reverb (I've no idea what all the effects might be), without identifying the individual instruments that use that effect and the places in the song that the effect is applied.
If you aren't scoring the song as such, but just describing the production method in broad terms it's quite a different thing.
I think it's the conceptual issue that I am struggling with. Are you trying to digitise a finished song to show a time graph with all the instrumentation and voices separately - so that the PC could actually play the song if it had the instrument capability.
What's the atomic level for this? Is it a single bar of the song? Are effects applied to individual bars, instrument by instrument.
I don't see how you can have an effect such as reverb (I've no idea what all the effects might be), without identifying the individual instruments that use that effect and the places in the song that the effect is applied.
If you aren't scoring the song as such, but just describing the production method in broad terms it's quite a different thing.
My goal is to store the settings only. I need to know the presets and the track information. I can back up the midi events digitally. I've experienced loss of information in the past where I don't know the preset for a track, or all the presets for a song. Having this documentation helps me recover lost information very easily. (I back up my project files occasionally, but most of the information loss is due to an update in a VST. Having a record of my settings makes it easy to recover.)
This database is a project I just want to build. I used to build databases in the past and did not think this would be so difficult. It has proven to be hard to get the table structure right. My biggest hurdle has been assigning the preset. There are 1,000's and I need to break them down by Brand and VST to be able to find any given preset. There are also different types of VST's which create different type of Tracks. The type relationship is where I am running into trouble, I think.
If you see my earlier post of a spreadsheet with song information it shows exactly what data I am collecting.
I added forms to add the supporting data and created a Splash screen. The supporting data forms still need plenty of work, but they were sufficient to enter a great deal of supporting data.
I thought a few days away from frmSong might give me some new perspective.
My theory now is to combine the Effect Presets and the Instrument Presets. Add a field to identify the Preset Type, and use it to filter the possible presets. (Effects presets only show up in the sfrmTrackEffects preset options and are prevented from showing up in the sfrmTrackList form.)
I am having the following issues:
1. sfrmTrackList
I am unable to change or enter a new record due to the VST entry. (cboVST control)
2. sfrmTrackEffects,sfrmSongEffects
I get an error message regarding a control name. I am unable to find any discrepancies in the field names.
If you have the time, I would appreciate you looking at the database attached and see if you can help. I understand if I have worn out my welcome. You have been very generous with your time and expertise.
however tblPresets is a table that combines (holds) all Preset records AND has a Preset Type table associated to it - which currently allows Instrument and Effect Preset types (you have some other Preset Types too). Does this meet the requirements for types of Presets? If not describe precisely what you need that is not met by the type of Preset table. The next part of the requirement for this is how to use it to filter the presets that might be selected when trying record the "effectUsed" against a track. Looking at your frmSong where you can select a Preset type (from the combo) can be used to then apply a filter to the PresetID combo available below it - check out how to construct cascading combo boxes.
On the frmSong, you have a subform: sfrmSongEffects - however the master / child linking fields are not defined:
A little discussion here: your frmSong is fundamentally based upon the tblTracks (remembering that Tracks are of type Song (as a master track) and other Tracks). sfrmSongEffects (as opposed to the sfrmTrackEffects) will only apply to Tracks of Type "song". The visible property of the sfrm can be changed to display or not depending upon the presence of Track Type "Song". However the sfrmTrackEfrects will show the same trackeffect as sfrmSongEffects for a song track because both use the same links (trackID) - not the parentTrackID as yuo currenlty have it in the query - which means you cannot make the correct link.)
The control in sfrmTrackEffects tbxCurrentTrack is to be assigned the trackID from the track list which is also a subform.
If there is no tracklist records for the song then there is no value to assign. Use an If statement to check if it is a new record.
Code:
If Me.NewRecord Then
' nothing
Else
[Forms]![frmSong]![sfrmTrackEffects].[Form]![tbxCurrentTrack] = Forms![frmSong].Form![sfrmTrackList]![tbxTrack_ID]
End If
Re Point 1
unable to change or enter a new record due to the VST entry. (cboVST control)
Before going further: the sfrmTrackList relies upon 6 tables to show data and maintain - this is not usual. Focus on what really is to be maintained here - the list of tracks associated to a song (the parentTrackID) and the effects that apply to those tracks. It is inappropriate in this form to select a Brand, and for that to then cascade changes to the VST, the Preset and Preset type. Brand is a read-only/display only item on this form. Similarly for VST. If you simply make Brand and VST read-only text boxes then you won't have the conflict when trying to update them via the combo.
The set of 4 combo boxes you have displayed in that form all have a text box overlay, and are not bound to a control source.
Do understand that each text box overlay can be removed as the combo box can show the text you required and allow the ID to be stored - you are almost there - except for binding them (however see no need for combos for Brand, VST and Preset Type (except if you use them as cascading filters - see above notes).
The below eg where the control source is now set, with your current settings for format, show the Preset name (as the displayed element from the query used to populate the combo, and the Preset_ID is hidden).
I DO STILL recommend you use the simpler data structure as previously expoused. KISS principle - and improved normalisation (conceptualisation of Song and Tracks as separate entities: Song has 1 Master Track and many Other tracks.) Better now than later.
however tblPresets is a table that combines (holds) all Preset records AND has a Preset Type table associated to it - which currently allows Instrument and Effect Preset types (you have some other Preset Types too). Does this meet the requirements for types of Presets? If not describe precisely what you need that is not met by the type of Preset table. The next part of the requirement for this is how to use it to filter the presets that might be selected when trying record the "effectUsed" against a track. Looking at your frmSong where you can select a Preset type (from the combo) can be used to then apply a filter to the PresetID combo available below it - check out how to construct cascading combo boxes.
On the frmSong, you have a subform: sfrmSongEffects - however the master / child linking fields are not defined:
A little discussion here: your frmSong is fundamentally based upon the tblTracks (remembering that Tracks are of type Song (as a master track) and other Tracks). sfrmSongEffects (as opposed to the sfrmTrackEffects) will only apply to Tracks of Type "song". The visible property of the sfrm can be changed to display or not depending upon the presence of Track Type "Song". However the sfrmTrackEfrects will show the same trackeffect as sfrmSongEffects for a song track because both use the same links (trackID) - not the parentTrackID as yuo currenlty have it in the query - which means you cannot make the correct link.)
Re point 2:
Whenever I try to open the frmSong it fails on the line:
The control in sfrmTrackEffects tbxCurrentTrack is to be assigned the trackID from the track list which is also a subform.
If there is no tracklist records for the song then there is no value to assign. Use an If statement to check if it is a new record.
Code:
If Me.NewRecord Then
' nothing
Else
[Forms]![frmSong]![sfrmTrackEffects].[Form]![tbxCurrentTrack] = Forms![frmSong].Form![sfrmTrackList]![tbxTrack_ID]
End If
Re Point 1
Before going further: the sfrmTrackList relies upon 6 tables to show data and maintain - this is not usual. Focus on what really is to be maintained here - the list of tracks associated to a song (the parentTrackID) and the effects that apply to those tracks. It is inappropriate in this form to select a Brand, and for that to then cascade changes to the VST, the Preset and Preset type. Brand is a read-only/display only item on this form. Similarly for VST. If you simply make Brand and VST read-only text boxes then you won't have the conflict when trying to update them via the combo.
The set of 4 combo boxes you have displayed in that form all have a text box overlay, and are not bound to a control source.
Do understand that each text box overlay can be removed as the combo box can show the text you required and allow the ID to be stored - you are almost there - except for binding them (however see no need for combos for Brand, VST and Preset Type (except if you use them as cascading filters - see above notes).
The below eg where the control source is now set, with your current settings for format, show the Preset name (as the displayed element from the query used to populate the combo, and the Preset_ID is hidden).
I DO STILL recommend you use the simpler data structure as previously expoused. KISS principle - and improved normalisation (conceptualisation of Song and Tracks as separate entities: Song has 1 Master Track and many Other tracks.) Better now than later.
tblPresets is a table that combines (holds) all Preset records AND has a Preset Type table associated to it - which currently allows Instrument and Effect Preset types (you have some other Preset Types too). Does this meet the requirements for types of Presets?
Yes. I can filter out "Effects" presets for sfrmTrackDetails and exclude any presets that are not "Effects" for sfrmTrackEffects and sfrmSongEffects.
Before going further: the sfrmTrackList relies upon 6 tables to show data and maintain - this is not usual. Focus on what really is to be maintained here - the list of tracks associated to a song (the parentTrackID) and the effects that apply to those tracks. It is inappropriate in this form to select a Brand, and for that to then cascade changes to the VST, the Preset and Preset type. Brand is a read-only/display only item on this form. Similarly for VST. If you simply make Brand and VST read-only text boxes then you won't have the conflict when trying to update them via the combo.
I only need the details from tblTrack displayed here, but each track includes at least one Preset. (The join table tblTrackPreset takes care of this.) Knowing or displaying just the Preset isn't sufficient. There are hundreds and Preset Names can be duplicated among different VST's. I need the Brand, VST, and PType (preset type) to help drill down to the proper Preset. I also need to display this supporting information so I can visually see I have assigned the correct Preset to the Track.
I've tried working directly with the comboboxes, but on continuous forms, a change to any combobox is reflected in all the displayed records. Copying the name to an unbound text box hides the display changes in the inactive records.
You are right about this section, it is where trouble is coming into play. If I didn't need to display the VST name on the form I could make everything work, but that is not a solution to the task. I need to bring in this support data to help find Presets and to show I have the correct Preset selected.
The control in sfrmTrackEffects tbxCurrentTrack is to be assigned the trackID from the track list which is also a subform.
If there is no tracklist records for the song then there is no value to assign. Use an If statement to check if it is a new record.
Code:
If Me.NewRecord Then
' nothing
Else
[Forms]![frmSong]![sfrmTrackEffects].[Form]![tbxCurrentTrack] = Forms![frmSong].Form![sfrmTrackList]![tbxTrack_ID]
End If
This is correct. I have done little work on these subforms yet. sfrmSongEffects can have a parent/child connection based on the Track_ParentID. sfrmTrackEffects will be manually synced to the current record in the sfrmTrackDetails subform. (This will display only the effects being applied to the current Track_ID.)
I added forms to add the supporting data and created a Splash screen. The supporting data forms still need plenty of work, but they were sufficient to enter a great deal of supporting data.
I thought a few days away from frmSong might give me some new perspective.
My theory now is to combine the Effect Presets and the Instrument Presets. Add a field to identify the Preset Type, and use it to filter the possible presets. (Effects presets only show up in the sfrmTrackEffects preset options and are prevented from showing up in the sfrmTrackList form.)
I am having the following issues:
1. sfrmTrackList
I am unable to change or enter a new record due to the VST entry. (cboVST control)
2. sfrmTrackEffects,sfrmSongEffects
I get an error message regarding a control name. I am unable to find any discrepancies in the field names.
If you have the time, I would appreciate you looking at the database attached and see if you can help. I understand if I have worn out my welcome. You have been very generous with your time and expertise.
Unfortunately, it still presents the same problem. The form is trying to update records that should be read only and, in the end, creates a record with duplicate PKs that prevent a record from being saved or changed.
I'm thinking I just connect sfrmTrackDetails with frmSong for Track details. (No VST or Brand information.) I then write code to query all the Preset details and fill out unbound fields manually. I would have New and Save command buttons to implement the changes to the tblTrackPreset table.
I can't figure out why I can't use the FK's from the tblBrand, and tblVST tables to filter the Preset list without Access needing to save the record. Why is Access trying to update the support tables?
Unfortunately, it still presents the same problem. The form is trying to update records that should be read only and, in the end, creates a record with duplicate PKs that prevent a record from being saved or changed.
I'm thinking I just connect sfrmTrackDetails with frmSong for Track details. (No VST or Brand information.) I then write code to query all the Preset details and fill out unbound fields manually. I would have New and Save command buttons to implement the changes to the tblTrackPreset table.
I can't figure out why I can't use the FK's from the tblBrand, and tblVST tables to filter the Preset list without Access needing to save the record. Why is Access trying to update the support tables?
Unfortunately, it still presents the same problem. The form is trying to update records that should be read only and, in the end, creates a record with duplicate PKs that prevent a record from being saved or changed.
I'm thinking I just connect sfrmTrackDetails with frmSong for Track details. (No VST or Brand information.) I then write code to query all the Preset details and fill out unbound fields manually. I would have New and Save command buttons to implement the changes to the tblTrackPreset table.
I can't figure out why I can't use the FK's from the tblBrand, and tblVST tables to filter the Preset list without Access needing to save the record. Why is Access trying to update the support tables?
The attached, based on the earlier version provided, allows:
creation of song records and the tracks for the song (using a subform) and displaying the effects used for the track (on a sub-sub form)
tracks to be assigned effects, where the preset can be selected based on filters for preset type and VST (which is filtered by Brand if needed).
open frmsong to check
Hope it helps.
Suggest that the thread being > #72 posts has addressed the original requirement/problem - it is now a matter of building the app and sorting issues if/as needed with the app.
The attached, based on the earlier version provided, allows:
creation of song records and the tracks for the song (using a subform) and displaying the effects used for the track (on a sub-sub form)
tracks to be assigned effects, where the preset can be selected based on filters for preset type and VST (which is filtered by Brand if needed).
open frmsong to check
Hope it helps.
Suggest that the thread being > #72 posts has addressed the original requirement/problem - it is now a matter of building the app and sorting issues if/as needed with the app.
This look really good except for assigning the tblPType_Name in the sfrmPreset. I tried to change it to a combobox and add tblPType_ID to the subform's query. Access would not allow me to use a combobox to make a selection. I think I know a way around this. (Add an unbound combobox under the existing tblPType_Name text box and copy the name from the combobox to the text box.
The reason why the preset type is not independently set in sfrmPreset (which lists the presets used in a track) is because the Preset Type is determined / belongs to the Preset - it is not, according to the data model, dependent on the use of the preset as an effect within a track. Choosing a specific Preset carries with it the Preset Type. Each record in the Preset table has a specific Preset Type. Implies you need to maintain the Presets with all potential PresetType variants.
If Preset Type is to be set independently of the Preset when a Preset is selected as an effect on a track, then that attribute /field needs to be moved to the EffectUsed table (it is not dependent upon the PresetID, instead it depends upon the EffectUsedID - this is part of the analysis / discovery that is normalisation).
Your proposal, to change the PresetType in the sfrmPreset (in the current data structure) means:
1. changing the Preset Type of the Preset - which affects all other EffectUsed records using that Preset. They will all now be assigneed that Preset Type - which I am sure you do not want
2. OR, if when you select a Preset Type for the selected Preset and find it does not exist, add a new Preset record with the Preset Name and Preset Type, and VST ID, and then in the EffectUsed pick up that new PresetID to change it from the existing EffectUsedID, or use it as the new EffectUsedID if adding an EffectUsed. Not as simple as just changing the PresetType.
If it is appropriate (para 2), it would be a much simpler solution to move the field to EffectUsed, as then the proposed combo selection can be made without affecting other records. Interested to hear which way you go.
The reason why the preset type is not independently set in sfrmPreset (which lists the presets used in a track) is because the Preset Type is determined / belongs to the Preset - it is not, according to the data model, dependent on the use of the preset as an effect within a track. Choosing a specific Preset carries with it the Preset Type. Each record in the Preset table has a specific Preset Type. Implies you need to maintain the Presets with all potential PresetType variants.
If Preset Type is to be set independently of the Preset when a Preset is selected as an effect on a track, then that attribute /field needs to be moved to the EffectUsed table (it is not dependent upon the PresetID, instead it depends upon the EffectUsedID - this is part of the analysis / discovery that is normalisation).
Your proposal, to change the PresetType in the sfrmPreset (in the current data structure) means:
1. changing the Preset Type of the Preset - which affects all other EffectUsed records using that Preset. They will all now be assigneed that Preset Type - which I am sure you do not want
2. OR, if when you select a Preset Type for the selected Preset and find it does not exist, add a new Preset record with the Preset Name and Preset Type, and VST ID, and then in the EffectUsed pick up that new PresetID to change it from the existing EffectUsedID, or use it as the new EffectUsedID if adding an EffectUsed. Not as simple as just changing the PresetType.
If it is appropriate (para 2), it would be a much simpler solution to move the field to EffectUsed, as then the proposed combo selection can be made without affecting other records. Interested to hear which way you go.
Thank you for that clear explanation. I see what you mean. I am sure my problems with the forms are due to my table structures. If I normalize the data properly, the forms will just be bull work.
My biggest problem is that I need the brand and VST details for each track, but they are dependent only on the preset. The track and the preset must be in the same table. The Brand and VST are needed to find the correct preset (as a preset name is not unique among various VST's. Several VST's have a "Piano" preset.
@mike60smart
I've tried to add in Effects form. I'm working sfrmSongEffects. I want to include a NotInList procedure to the cboPreset control. The form does what I want but gets stuck in this endless loop of adding the new Preset. (The NotInList keeps getting triggered because the list is not being updated. I've tried to update the list for the control, but I'm not getting to the list before the NotInList is being triggered.
Would you please take a look at the sfrmSongEffects and recommend a strategy? (I can duplicate it in the sfrmTrackEffects.)
