Struggling with finding an answer to a simple question

[BJZeak]

Expecting the issue is my failure to clearly describe the question

MS Access has provided in many of its wizards the ability to selectively pick from a list on the left and add to a list on the right

Was looking to implement something like this for a project without reinventing the wheel ... wondering if there is a simple way this can be achieved; Form Template, Function, or Other?

Thanks in advance for any insight/suggestions you can provide

Looking to use something like this selection process to help users build/manage cross/association links between items in a list

Example: item 1 -> item 17 -> item 29 (Items 1, 17, and 29 are all associated but have no way to be programmatically linked hence the need for building an Item Link Manager)

all items may have different reference details so the expectation is a manual link manager should provide fewer errors with the linking process

link details would simplify the distribution of raw data

 

[Andrzejek]

Let's say you have 3 lists:

[pre]
Car Color Millage[blue]
1 Ford [/blue] ... ...
2 Chevy 16 Red [blue]29 50000[/blue]
3 Toyota [blue]17 White[/blue] 30 60000
4 Mazda 18 Black 31 70000
5 Citroen 19 Green 32 80000
... ... ...
[/pre]
You can have 3 list boxes where you can select one item from each list.
I would have another table:

[pre]
tblSelections
ID Table
[red]1[/red] Car
[red]2[/red] Color
[red]3[/red] Millage
[/pre]
And your selection(s) would be in yet another table:
[pre]
tblChoices
ID Grp Sel Item
5 ... ... ...
6 1 [red]1[/red] 1
7 1 [red]2[/red] 17
8 1 [red]3[/red] 29
9 2 ...
...
[/pre]
so you can see the Group 1 has a selection of 1 (car: Ford) 2 (color: 17) and 3 (Millage 29)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I’m assuming this is something like creating meals from various ala carte food/beverage items.

It’s first important to get the tables set up correctly. I would have a table of food items with at least a FoodID and FoodName. Then a table of Meals with MealID and MealName. The third table is a junction table that combines a MealID with one or more FoodIDs. This would have one record per Meal per Food Item.

Forms would have a meal to select on the left and a continuous subform of the junction table. This wouldn’t be much different from the Northwind Orders and Order Details.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BJZeak]

ok so these responses indicate my question is still unclear ... at a high level, think of a flat file that isn't normalized with embedded looped data ... this data comes from XML files generated from online forms not under our control ... currently, we import the flat files then attempt to use the data with overly complicated queries ... What I envision is a dynamic field extractor that uses a field map to allow pushing the data into normalized tables automatically ... have done cross conversions in code but they are an absolute maintenance nightmare ... a dynamic Field Map/Cross could easily add/update data with minimal code and shouldn't require any changes if data changes are made

Finding a simple way to Build/Manage this Cross is the current task at hand ... Note: looped data within a file will also automatically create multiple normalized records

Currently attempting to use two side-by-side subforms that list input fields to a list of potential normalized fields ... started by making a table that identifies all Access tables and their fields ... this is my items table ... then have a table linker that just identifies Input to Output table Matches ... the final step is now to cross-connect the Input Fields to the Output Fields within these tables.

 

[Andrzejek]

It looks to me Duane and I missed your point completely. :-(

If you present a part of your XML file with the example of the data that represents your issue, and the outcome (tables with the data from your XML file), we may have a better picture of what you are after.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[BJZeak]

No problem ... my fault ... my question was more in line of "Is there some way in Access to simulate the Form Wizard dialog box?" without having to do the coding shown below ... the example given below is just for Tables, ultimately, I want to take this idea to the field level using the same technique ... an input table like app could have fields that are spread across a number of other tables which normalize the input table data ... the purpose of building this manager is to simplify the data processing such that the resulting FIELD MAP can be processed using a dynamic in/out routine.

Currently, we might receive an XML flat file that might look something like

table app with fields; a, b, c, d, e1, e2, e3, f, g1, g2 ... the intent is to match/manage a field map using a Wizard style Dialog Box such that:
fields; a, b, c, d, & f populate a record in table X with corresponding fields az, bz, cz, dz & fz ... XML field names may not always be identical to the table field names
fields; e1, e2, & e3 populate 3 records in table Y with corresponding fields of az & ez
fields; g1 & g2 populate 2 records in table Z with corresponding fields of az & gz

Step 1 Create Table Reference: app references tables X, Y, Z
Step 2 Create Field Reference: app references fields a,b,c,d,f in X, a & e in Y and a & g in Z

High-level example of the Form Wizard process I am attempting to simulate: take any DB with any number of tables and create a simple form that has a list control tied to a list of tables (built from tabledef), then has 2 combo boxes that show the same list of tables minus the one selected ... then provide the 4; >, >>, <, <<, buttons that quickly allow moving tables of interest across left to right ... what I have done is create a temp table called zTbl with an additional field STA that is used to populate these two combo boxes using 2 queries

STA = 0 is the selected Table which won't show in the combo boxes
STA = 1 is the Left Combo Box
STA = 2 is the Right Combo Box

> changes 1 -> 2
< changes 2 -> 1
>> changes all 1 -> 2
<< changes all 2 -> 1

Option Compare Database
Option Explicit

Private Sub Form_Load()
 clrT
 Me.Form.Refresh
End Sub

Sub clrT() ' clears the Combo boxes on load or change of listbox
 Dim sql As String 
 DoCmd.SetWarnings False
 sql = "delete * from zTbl ;"
 DoCmd.RunSQL sql
 DoCmd.SetWarnings True
End Sub

Private Sub cbT_Change() ' load T1 Combo Box
 Dim sql As String
 Dim rs As DAO.Recordset
 clrT
 DoCmd.SetWarnings False
 sql = "insert into zTbl select * FROM Tbl ;"
 DoCmd.RunSQL sql
 DoCmd.SetWarnings True
 sql = "select * from zTbl where [tbl] = '" & Me.cbT.Value & "'"
 Set rs = CurrentDb.OpenRecordset(sql)
 If rs.RecordCount > 0 Then
  rs.MoveFirst
  rs.Edit
  rs!sta = 0
  rs.Update
 End If
 Me.Form.Refresh
End Sub

Private Sub cmA1_Click() ' >>
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("ztbl")
 If rs.RecordCount > 0 Then
  rs.MoveFirst
  While Not rs.EOF
   If rs!sta = 1 Then
    rs.Edit
    rs!sta = 2
    rs.Update
   End If
   rs.MoveNext
  Wend
 End If
 Me.Form.Refresh
End Sub

Private Sub cmA2_Click() ' <<
 Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("ztbl")
 If rs.RecordCount > 0 Then
  rs.MoveFirst
  While Not rs.EOF
   If rs!sta = 2 Then
    rs.Edit
    rs!sta = 1
    rs.Update
   End If
   rs.MoveNext
  Wend
 End If
 Me.Form.Refresh

End Sub

Private Sub cmS2_Click() '<
 S2
End Sub

Sub S2() '<
Dim sql As String
 Dim rs As DAO.Recordset
 sql = "select * from zTbl where [tbl] = '" & Me.lsT2.Value & "'"
 Set rs = CurrentDb.OpenRecordset(sql)
 If rs.RecordCount > 0 Then
  rs.MoveFirst
  rs.Edit
  rs!sta = 1
  rs.Update
 End If
 Me.Form.Refresh
End Sub

Private Sub cmS1_Click() '>
 S1
End Sub

Sub S1() '>
Dim sql As String
 Dim rs As DAO.Recordset
 sql = "select * from zTbl where [tbl] = '" & Me.lsT1.Value & "'"
 Set rs = CurrentDb.OpenRecordset(sql)
 If rs.RecordCount > 0 Then
  rs.MoveFirst
  rs.Edit
  rs!sta = 2
  rs.Update
 End If
 Me.Form.Refresh
End Sub

Private Sub lsT1_DblClick(Cancel As Integer) '>
 S1
End Sub

Private Sub lsT2_DblClick(Cancel As Integer) '<
 S2
End Sub