[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Adding columns names from another table to lookup in another table

This is a follow up to my previous question.  The code below will grab the column names and put them in a table.  Question I have is can I do something similar where the table in a grid several of the fields are based on a lookup and when I enter the field in a grid the drop down would show the column names from the other table.  Is that possible.

sub getColumn()
dim rs1 as dao.recordset, rs2 as dao.recordset, j as integer

set rs1=currentdb.openrecordset("Table1")
set rs2=currentdb.openrecordset("Table2")

for j=0 to rs1.Fields.count-1
      rs2.addnew
      rs2![FieldName]=rs1.fields(j).name
      rs2.update
 next

rs1.close
rs2.close
set rs1=nothing
set rs2=nothing
end sub


0
keschuster
Asked:
keschuster
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
it will be a lot easier to set the lookup in the design view of the  table.

but before you do that, see this
http://www.mvps.org/access/lookupfields.htm
0
 
GRayLCommented:
The table whose field names you are changing has to be done thru its table definition, not the recordset

sub getColumn()
dim rs1 as dao.recordset, td2 as dao.tabledef, j as integer

set rs1=currentdb.openrecordset("Table1")
set td2=currentdb.tabledefs("Table2")

for j=0 to rs1.Fields.count-1
      td2.addnew
      td2.Fields(j).name=rs1.fields(j).name
      td2.update
 next

rs1.close
td2.close
set rs1=nothing
set td2=nothing
end sub

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I presume this is for some sort of "database mapping" application? If so, the only way to get at the Lookup columns for a database is through the Fields collection of a DAO.TableDef:

Function ShowFields(TableName As String) As String

  Dim tdf  As DAO.TableDef
  Dim dbs  As DAO.Database
  Dim fld As DAO.Field
 
  Dim i As Integer
 
  Set dbs = CurrentDb
  Set tdf = dbs.TableDefs(TableName)
 
  For Each fld In tdf.Fields
    Debug.Print fld.Name
    For i = 0 To fld.Properties.Count - 1
      'the RowSource property will only be available if the user has set the property
      If fld.Properties(i).Name = "RowSource" Then
        If Len(fld.Properties(i).Value) > 0 Then Debug.Print fld.Properties(i).Value        
      End If
    Next i
   
  Next fld
 
  Set fld = Nothing
  Set tdf = Nothing
  Set dbs = Nothing
 
End Function

This does nothing more than printout the .RowSource ... you could use Cap's code (above) to open a recordset and grab the FieldNames, or use the various VBA string functions to parse out the names.



0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
keschusterAuthor Commented:
LSMConsulting.  You are exactly correct.  I have "mapping" table that is loaded with the fields names of the "source"  I want column next to that to have a drop down of the taargets fields names so the user can "map" them.

Will the code you provided get me there?  My thought which I wasn't altogether happy with was to create a table that held the field names (which I can load dynamically with the code I already have) But I was trying to avoid carrying another table
0
 
keschusterAuthor Commented:
Additionally, I'm trying to avoid a Form to process this.  I'd like to stick with the grid view
0
 
GRayLCommented:
keschuster:  Did you read my post?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you say "grid view", do you mean a Form in Datasheet view? If you're trying to use a Table in Datasheet view then the short answer is "you can't do that" ... tables have no Event model nor VBA module. Tables are for storing data; Forms are for viewing and manipulating data (either via VBA or by allowing Access to do it for you), so use a Form if you need to make dynamic changes during data entry.

Still, I don't really follow you ... are you trying to build a routine that will BUILD a table, or one that will MAP existing tables?

If you're trying to build tables: It's a bad idea to use lookups on a table ... see Cap's link for info on why that is.
0
 
keschusterAuthor Commented:
I'm talking Datasheet view.  And I'm trying to populate fields in the DataSheet.  I think my approach will be to create a table with enough data in it (Column names) that I can use the Lookup to Query for what the drop down should display

Does that make sense?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<I'm talking Datasheet view>

Form or Table? They're two different things ...

Still, I'm not entirely sure what you're trying to achieve ... if you're trying to allow someone to build a table through your view, then you'd need to allow them to (a) select the Table they want to use and then (b) select the Columns they want to use from that table ... this would, typically, require a Combo and Multiselect Listbox (since you can, and quite often do, have multiple columns in a lookup). If you are mapping existing tables, then you'll need to store the Lookup fields in a different table, related back to your Fields table.

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now