Newbie! 52 tables from 1 Form

[javierjuarez]

I am a newbie to Access. I have many IT skills but have been exploring the likes of Access due to a request for help from a friend whom I handle her everyday IT network needs.

I was wondering if I can be sent in the right direction.

What I have is 52 tables (States) with information, tblIowa, tblIllionois, tblWisonsin, etc. . That's it! Somehow everything that was linked and part of this db has disappeared before I started there. I have tried to find answers in her backups, but not one tape has the "full" database. What I am asking for is help in creating a Form with a dropdown list that will choose a zip code or state and display the information from the tables. So if I wanted "Illinois" I would chose it from a dropdown and all information in that tblIllinois with be displayed.

I have read a lot of books in the last two weeks and have visited almost every help site that Google has given, but no luck. I have been able to create queries pretty well and forms that display information from any one table, but cannot figure out how build a choice list for all tbl"52states".

Thanks for any information or direction.

 

[Eupher]

Well, I suppose you could create one massive UNION query and use that as the row source for your combo, but...

Assuming all the state tables have identical structure, a better idea might be to create a single table comprised of a list of the states, relate this table to each of the state tables (via PK<->FK), and use the new table as the row source. With the relationships set up properly and the correct join in the SQL you could then pull whatever info you needed from the state tables.

An even better idea might be to simply create one table to hold ALL the state data - and then use an APPEND query to move the data from the individual state tables into the new master table. This could then be the row source for your combo... with the added advantage that this will be *much* simpler to deal with for future use.

Ken S.

 

[GKProgrammer]

I think I would create a combo with a Value list that lists all 52 states. The user would select one of the states and you can set the associated form's Recordsource and the controls ControlSource in the " On Change" event for the combo. As long as all the tables have the same structure and naming (which I assume they would) Something like this:

Private Sub cboStates_Change()
Me.RecordSource = "tbl" & cboStates
Me.ControlNameOnForm.ControlSource = "FieldNameInTable"
Me.ControlNameOnForm.ControlSource = "FieldNameInTable"
Me.ControlNameOnForm.ControlSource = "FieldNameInTable"
End Sub

Let me know how it goes.

Regards,
gkprogrammer

 

[TheAceMan1]

How are ya javierjuarez . . . . .

I have to agree with [blue]Eupher[/blue], particularly his 2nd option.

A new table with Autonumber [purple]PrimaryKey[/purple] and the fields of the State tables would work great for you (and you could get rid of those 52 tables and their impact on memory usage). [purple]From having to open specific forms to a simple lookup has got to be better.[/purple]

Your only big problem is [purple]appending the 52 state data into the new table.[/purple] it would be something like this:

[blue]   Dim tdf As DAO.TableDef, x As Integer, Cmp As String
   
   For Each tdf In CurrentDb.TableDefs
      For x = 1 To 52
         Cmp = Choose(x, "Alabama", "Alaska", "Arizona", "Arkansas", _
                         "California", "Caribbean", "Colorado", _
                         "Connecticut", "Deleware", "Florida", _
                         "Georgia", "Hawaii", "Idaho", "Illnois", _
                         "Indiana", "Iowa", "Kansas", "Kentucky", _
                         "Louisiana", "Maine", "MaryLand", _
                         "Massachusetts", "Michigan", "Minnesota", _
                         "Mississippi", "Missouri", "Montana", "Nebraska", _
                         "Nevada", "New Hampshire", "New Jersey", _
                         "New Mexico", "New York", "North Carolina", _
                         "North Dakota", "Ohio", "Oklahoma", "Oregon", _
                         "Pennsylvania", "Rhode Island", "South Carolina", _
                         "South Dakota", "Tennessee", "Texas", "Utah", _
                         "Vermont", "Virginia", "Washington", _
                         "Washington DC", "West Virginia", "Wisconsin", _
                         "Wyoming")
         If InStr(1, tdf.Name, Cmp) Then
            [green]'Append OldTable to NewTable
            'Delete Old Table[/green]
            Exit For
         End If
      Next[/blue]

See Ya! . . . . . .