Trouble Linking Table's Columns

[chris9]

I have been trying to get two tables to be able to link and replace information. One of my tables just consists of two columns - one with an abbreviation and the second with the abbreviation all spelled out. I have another table that has several records and one column in the table contains these abbreviations as values. I would like to replace the columns in the second table with the abbreviations spelled out that are stated in the first table.

Here's what I'm really want to do but I'm not sure how to put this into a query(or another idea):

I want to match
[Form]![Form1]![Text0].MakeAbbr = Make.MakeAbbr
then replace
[Form]![Form1]![Text0].MakeAbbr
with
Make.MakeSpelledOut

Where [Form]![Form1]![Text0] and Make are my two tables

Thanks in advanced for all the help!

 

[psemianonymous]

The simplest way is to add a event procedure to Text0_LostFocus(). In the event, put the following:

Text0.Value = Nz(DLookup("[MakeSpelledOut]","[Make]","[MakeAbbr] = '" & Text0.Value & "'"))

This will do it "quick and dirty". For better results, use a combobox. With a combo-box, you can limit any entered values to items in the list, thus forcing your users to pick a valid entry. You also don't have to worry about manually updating the value--if you set it right, you can even type in/display one value (the abbreviation) and store the other value (the "spelled out" value) in the field.

 

[chris9]

I'm still having trouble with this. I can't create a combo box because the [Form]![Form1]![Text0] table is being created by importing a text file into a form I created. Do you know how I can put this DLookup into a Macro, then I could run the macro when the table is being created. I looked it up in the help and I couldn't find a good example of how to do this and I tried a couple things but nothing worked.

 

[psemianonymous]

I'm trying to get a handle of what you're asking for. You have:

-one Access table
-one table/data source that you have shown on the form, but doesn't exist otherwise or isn't reachable via normal means
-you want to replace everything in one column with another value, e.g. replace every "FUN" with "FUNdamentals", and every "CODE123" with "FIRE DRILL CODE".

I assumed you were entering data and wanted to update the data as it was entered into your database. No. You want a query which updates every item in the table. Don't use a form to do this, use a query. An update query will update every item in your table. If you want specifics, you're going to have to give me your table structures for both tables.

Once it is working, you can then run the query from a macro.

 

[chris9]

I have:
-one Access table called Makes containing two columns MakeAbbr and MakeName. Example: MakeAbbr=Chev and MakeName=Chevolet
-one table created from a form, where user enters the table name in to [Text0] and in [Text2] the text file to be imported. The user then clicks on the Create Table button that will create the [Text0] table.
-both tables are in the same database
-next on the form is a text box for another file name and then another button that will generate which I have created VB code to format and save the code into an xml document

So to answer your question when creating this imported file to a table that is when i want to also read in the MakeName into the MakeAbbr. I have a problem calling this [Form]![Form1]![Text0] table in a query.
I hope I've explained this a little better.
Thanks for helping.

 

[psemianonymous]

OK, here's how it can work.

Here's the code:

Dim strSQL as String
Dim tnm as String

'Get the table name
tnm = Text0.Value

strSQL = "UPDATE " & tnm & " INNER JOIN MAKES ON " & _
tnm & ".MakeAbbr = MAKES.MakeAbbr SET " & _
tnm & ".MakeAbbr = '" & MAKES.MakeName"

DoCmd.RunSQL strSQL

This should (hopefully) work. Put this code right after where you created the table, in the Create_Table_Click() event (or whatever your create table button is called).

One thing--make sure the &quot;new table's&quot; field name is correct. I just assumed it was called &quot;<newtable>.MakeAbbr&quot;

 

[chris9]

When I try this code, I'm getting a syntax error(missing operator) in query '2002.MakeAbbr = MAKES.MakeAbbr'.
2002 was the table that I created or that was in tnm.
Any suggestion to get this debugged.