Append Query and Auto Populate

[Divine]

Hi,

I have a tbl that gets populated from an append query and a form that is crated from this table. there is one field that gets populated from the append query called departments, the department names are long and we have abbreviated versions of the department names that I would like to use.



In the table I have two columns “department” (for long name) and “updated department” (for abbreviated department name) When I use the append query to update the department (long name) I want the abbreviated department column to populate.

for example if department column reads "Drilling and Workover" from the append query, I want updated department column to slefpopulate to read "D&WO"
I want the updated department field to automatically fill with the abbreviated name. How do I do this?

[June7]

Why are you using an append query anyway? Why are you duplicating the department name into the destination table? Should just save the primary key (abbreviated name?) from the departments table.

What is the source of the data for the append? Why can't you just include the abbreviated column? Post the query for analysis.

[Divine]

I'm using anappend query because the data comes from an external source and the externalsource is not willing to change their data for me. I append there info to mytable and then I want to change it to an abbreviated format.

[June7]

Got it, had to read the original thread again.

Can run an UPDATE query after the append.

[Divine]

ok let me try that

[Divine]

Sorry man but Ihave never done this before. I'm not sure how to write the expression thatchanges multiple department names to an abbreviated form using an update query.The update query appears to be changing all the department names to onedepartment. in the criteria I tried "if Drilling and Workover" then "D&WO" butthat didnt work. I'm lost here man. I'm sure its something easy.

[June7]

Sorry, I thought you already had the abb names in a "Departments" table and want to add them to another table where data is imported into with long names. If you don't already have the abb names available for reference then you cannot do this. No way can Access know that "Drilling and Workover" should be abbreviated as D&WO. Can't read minds.

[Divine]

Well I was ableto do the below code on the form I'm working in and it kind of worked except it wants to populate everyrecord with the same abbreviation when I click on the field [text62] I want itto populate automatically not on click and be specific to each record. Any thoughts?

Private Sub text62_click()
If [Dept] = "Southern Area Oil Drilling Department" Then
[Text62] = "SAODD"
ElseIf [Dept] = "Drilling &Workover Services Department" Then
[Text62] = "D&WSD"
ElseIf [Dept] = "Exploration Drilling Department" Then
[Text62] = "EDD"
End If
End Sub

[Bob Fitz]

Perhaps you could use your code in the After Update event of [Dept]

[June7]

That isn't going to work for records that already exist.

Are these the only names you will ever have to be concerned with - only 3 names? If you want to do a mass update of many records at one time, one method is an UPDATE sql action. In VBA I would try:

CurrentDb.Execute "UPDATE tablename SET AbbName = Switch([Dept]='Southern Area Oil Drilling Department', 'SAODD', [Dept]='Drilling & Workover Services Department', 'D&WSD', [Dept]='Exploration Drilling Department', 'EDD')"