Results 1 to 11 of 11
  1. #1
    WilsonsW is offline Novice
    Windows Vista Access 2000
    Join Date
    Mar 2011
    Posts
    10

    Renaming fields

    Hi, I am building a program to process many very similar datasets. The program uses many queries to perform many functions to analyze and clean the datasets. All datasets are in the exact same format, but the field names vary. Since the queries are based on pre-determined field names, this is a problem. I need to be able to run a query using the field 'toolbox' even when the field is named 'tools'. Changing the field name in design view of the table is not an option. Is there a way to set the queries to run based on a variable that the user defines - say, with a combo box to select the name of the 'toolbox' field for each dataset?

    Thanks!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    yes, but you have to do it in code.

    basically what you do here is write a small sub or function behind a form button or something. You concatenate into the sql statement, the listbox items that are highlighted, or one field value if it's a combo. It's simply a task of creating a querydef in vba instead of using the query builder.

  3. #3
    WilsonsW is offline Novice
    Windows Vista Access 2000
    Join Date
    Mar 2011
    Posts
    10
    Thanks Adam

    Forgive me but I'm new to writing code. Could you provide a rough example of code that could do this? To make the example simple, say I need to create a new field called NewField1 and update it so that it contains the exact information that the field "tools" contains (right now I would do this using a query with the field 'toolbox'

    Thanks so much for you help

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    wait a minute. you want to dynamically CREATE fields?? I don't understand.

    Give a little more explanation pls. What's the deal?? Simply seeing one of your table field's data with another field name attached to it?? What else is there other than that??

    If that's simply the case, just query the field and give an alias name using the 'AS' keyword.

  5. #5
    WilsonsW is offline Novice
    Windows Vista Access 2000
    Join Date
    Mar 2011
    Posts
    10
    Creating a field was just an example of something I might need to do. That part isn't really the issue. The issue is performing functions that require field names. For example, if I need to update everything in a field (this is just an example), I would need to tell the query what field to update. However, the field name might be any number of things. The user will know what field it is, but will need to tell the query that information. I was thinking I could use a combo box that shows all of the fields in the table, and the user will just select the field that the action should be carried out on.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    When Adam asked for a "little more explanation" I'm sure he was asking you to describe what your overall task(s) was? What environment? A sample of the data to be processed? Your approach to the process? Who would actually do/use whatever process was designed/developed? How many times would this process be required to run?

    If it is a "data cleanup", it could seem to be a one-time effort? If you have to run data cleanups repeatedly, then maybe there is some flaw in the big picture.

    At this point no one is thinking about whether or not combo boxes are appropriate.

    Help us help you by giving us some context of your issue.

  7. #7
    WilsonsW is offline Novice
    Windows Vista Access 2000
    Join Date
    Mar 2011
    Posts
    10
    All of the data is coming from outside sources. This program is a way to take information from these sources and check it for errors. The error checks are straightforward and do everything I need them to do. I have built all of the queries I need to do everything that needs to be done. The only issue remaining to be resolved is the field name issue. Because the datasets is coming from a vast number of outside sources, the formatting of field names is vastly different. The data is the same, but the field names are not. There are too many field names to change manually, and it is important to send the datasets back out with the same field names they came in with. It is obvious to anyone running the program what the field names mean. So the person running the program will know that 'tools' is really the same thing as 'toolbox', but obviously the queries do not know this. It is true, there might be a flaw somewhere in the big picture of this, but I feel like I am so close that it would be a huge pain to rethink the whole thing.

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by WilsonsW View Post
    Creating a field was just an example of something I might need to do. That part isn't really the issue. The issue is performing functions that require field names. For example, if I need to update everything in a field (this is just an example), I would need to tell the query what field to update. However, the field name might be any number of things. The user will know what field it is, but will need to tell the query that information. I was thinking I could use a combo box that shows all of the fields in the table, and the user will just select the field that the action should be carried out on.
    Got it. Of course you can do that. For instance, here is a short portion of a random record function that I wrote long ago:

    Code:
    Function RndRecord(fldInput As Variant, _
                       tblInput As String)
    
    On Error GoTo Cleanup
    
    Dim db As DAO.Database
      Set db = CurrentDb
        Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT " & fldInput & " FROM " & tblInput, _
                                      dbOpenDynaset)
    
    'some code
    
    var = rs.GetRows(rs.RecordCount)
    
    Randomize
      
    'array manipulations
    
    RndRecord = var(0, 0)
        
    End Function '//LL
    So basically, the two inputs are table name and field name. Is that what you're looking for??

    If so, and you always want to run an update query, you could write:

    Code:
    currentdb.execute _
    "UPDATE " & me.combobox & " SET " & _
    "[" & me.combobox2 & "] = YourUpdateValueHere " & _
    "OptionalWHEREclauseHere"
    combobox = tablename highlighted
    combobox2 = fieldname highlighted

    Does that make sense?

  9. #9
    WilsonsW is offline Novice
    Windows Vista Access 2000
    Join Date
    Mar 2011
    Posts
    10
    Adam,

    This looks very helpful! I am a total novice with code so I am trying to figure out where this code needs to go, but hopefully I can figure it out. I am correct in assuming that the second bit of code should be substituted for ...

    CurrentDb.OpenRecordset("SELECT " & fldInput & " FROM " & tblInput, _ dbOpenDynaset)

    ... in the first bit?

    I apologize for my naivety in advance.

    Thanks again

  10. #10
    WilsonsW is offline Novice
    Windows Vista Access 2000
    Join Date
    Mar 2011
    Posts
    10
    I figured it out! Adam, you are a lifesaver. Thanks so much!!

  11. #11
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    OK. marked as solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Renaming Fields on Table or Query
    By jo15765 in forum Access
    Replies: 4
    Last Post: 12-09-2010, 01:47 PM
  2. Combine two fields, Null fields involved
    By yes sir in forum Access
    Replies: 9
    Last Post: 10-03-2010, 09:20 AM
  3. Edit a table in a query by renaming the query?
    By TheWolfster in forum Queries
    Replies: 2
    Last Post: 07-30-2010, 02:57 PM
  4. Replies: 5
    Last Post: 03-20-2010, 08:30 AM
  5. Replies: 3
    Last Post: 08-10-2009, 08:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums