asked on

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

Rey Obrero (Capricorn1)

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

GRayL

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keschuster

ASKER

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

keschuster

ASKER

Additionally, I'm trying to avoid a Form to process this. I'd like to stick with the grid view

GRayL

keschuster: Did you read my post?

Scott McDaniel (EE MVE )

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.

keschuster

ASKER

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?

Scott McDaniel (EE MVE )

<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.