Not changing the values during UPDATE 1

[chris9]

When I use this code I do not get an error, but it doesn't appear that the MakeName is the tnm table. I really need to get the MakeName somewhere into the tnm table either in it's own column or replace the MakeAbbr if the tnm.MakeAbbr = Makes.MakeAbbr.

Hopefully I've this is clear to understand and I greatly appreciate anyhelp.

Dim strSQL As String
Dim tnm As String


'Get the table name
tnm = [Forms]![Form1]!Text0.Value

strSQL = "UPDATE " & tnm & " AS A INNER JOIN Makes AS B ON A.MakeAbbr=B.MakeAbbr SET A.MakeAbbr = B.MakeName"

DoCmd.RunSQL strSQL

 

[scriverb]

I don't see anything at all wrong with your update query. I setup my own tables with data that would work and the update occurred as expected. My first inclination is that you are not matching on your inner join as suspected. I suggest that you should to create a new Select query with the same inner join and look at the A.MakeAbbr, B.MakeAbbr B.MakeName fields to see first of all do you have matching records as you expect and second is it possible that B.MakeName is the same as A.MakeAbbr which would make the Update seem like it is not working.

Do this and then get back with the results.

Bob Scriver

 

[chris9]

When I DoCmd.RunSQL strSQL am I supposed to be able to see the results in the update in the [Forms]![Form1]!Text0.Value
table? I get a message back that it's updating 2775 records out of the 3303, so it must be doing something but I can't tell. I trying to the the value of MakeAbbr and it's not returning the update.
I can't get the new query to work either because it won't read in the table name I created on the form.

 

[scriverb]

Copy your file and rename it to it's name -Copy so we have something to go back to after we do some diagnostic work on the file and query.

Create a new field in the table called Updated_MakeAbbr and make sure it is the same data type and size as the MakeAbbr field. Now run the SQL below:

tnm = [Forms]![Form1]!Text0.Value

strSQL = "UPDATE " & tnm & " AS A INNER JOIN Makes AS B ON A.MakeAbbr=B.MakeAbbr SET A.Updated_MakeAbbr = B.MakeName"

DoCmd.RunSQL strSQL

Now take a look at this new field and see what the data in that field looks like. Compare it to the field MakeAbbr in the same table. Is it the same?

Let me know.
Bob Scriver

 

[chris9]

This statement puts the correct values into the Update_MakeAbbr!

DoCmd.RunSQL "UPDATE " & tnm & " AS A INNER JOIN Makes AS B ON A.MakeAbbr=B.MakeAbbr SET A.Update_MakeAbbr = B.MakeName"

However this statement is not changing the value of A.MakeAbbr

DoCmd.RunSQL "UPDATE " & tnm & " AS A INNER JOIN Makes AS B ON A.MakeAbbr=B.MakeAbbr SET A.MakeAbbr = B.MakeName"

I know what the problem is but could still use some help solving it. I am importing a text field and the line breaks are automatically being placed in because of a macro that I designed. So Make.Abbr is only 4 characters long and my MakeName is longer so it's cutting off the all remaining characters after the first 4. So I need to find out how I can still break the imported text field where I want and still be able to update it and the width change or it would be find to add this column to the table but I want to be able to do it automatically because I want other users to be able to just click the form buttons.

Thank you so far for all the help it's really helped.

 

[chris9]

Thanks Bob for all the help.

I figured out how to add the column and everything is working very good.

DoCmd.RunSQL "ALTER TABLE " & tnm & " ADD COLUMN MakeName TEXT"


Another question a little different, is it possible to not have the alert box come up telling you that you are updating ___ row(s)?

 

[scriverb]

Sure surround your query calls with these statements:
docmd.setwarnings false
query calls
docmd.setwarnings true

They will run without notifications. Bob Scriver

 

[scriverb]

Chris9, I still want to help you solve the other problem with the Update SQL. What exactly is in these linebreaks from the imported data. I understanding when you are truncating the first four digits. Give me an example and we can probably use a Mid$ function to select out the data from that particular line of data. Bob Scriver

 

[chris9]

Here is the statement that is creating my table:

DoCmd.TransferText acImportFixed, "Price Symbol Import Specification", Forms!Form1!Text0, Forms!Form1!Text2, False, ""


The "Price Symbol Import Specification" was created when I manually imported one of the txt files and set all the field widths and names to fit the content that will be in all the future txt files. As you can probably guess Forms!Form1!Text0 is my new table name and Forms!Form1!Text2 is the txt file being imported.
I don't know if this is well enough explained or not so let me know if you have any questions.

 

[scriverb]

So, you importred this text file and in doing so you have some Line Breaks in your new datafile? Why don't you remove the line breaks before you perform the import process and clean up the data this way. This can be done in Word or similar software. Bob Scriver

 

[chris9]

These aren't traditional line breaks. If you go to file/get external data/import/ select a text file
then select fixed width and next
you will see how the line breaks are actually created. They are really fields breaks so I miss lead you with the terms that I used.

 

[scriverb]

When you import the text data and you get to the screen with the field break lines follow the instructions above them and you will see that you can move, create new ones, and delete any that you want deleted. You should be able to modify the fields to suite your needs at this point. Bob Scriver