Results 1 to 8 of 8
  1. #1
    cantrellw is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4

    Updating a table with short text into certain cells based on other cells in table

    Hi all, I work for a company that has unit numbers such as "BB3465". Very often, we have a run of the same units. These all have a unique unit number but are grouped together in a run i.e. "BB3465-3470". I have created a database to lookup the run schedule and allows me to sort through our schedule by start date/unit#/group, etc. I create a DXF file for the floorplans for each unit or run of units, which is placed in a shared network drive. I usually just paste the network location as a hyperlinked file path in my table and copy it for each unit in a run. So, when the run has 5 units it it, I copy the first cell and paste it into all the ones below within the same group. I'm very certain there is a faster way to do this, especially when there are 150 units in one run.




    I'm thinking I need a query to recognize the GroupRange column and make all of the File Location cells within that group range the same, I am just unsure on how to get it to recognize the different groups and then put the same text in each corresponding group and not just put the same text in all of the cells. If the unit does not belong to a group (single unit), then the GroupRange column is blank. I am able to append to the table a given value when the GroupRange column is not blank, but it fills in every run with the same text.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,554
    suggest provide examples of all the variants you have and what you want them to change to.

    your terminology is excel - so to clarify are you talking access or excel? - an excel table is different to an access one, access does not have cells or columns, it has fields

  3. #3
    cantrellw is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4
    Here is a screen shot:

    Click image for larger version. 

Name:	cnc_db_snip.JPG 
Views:	11 
Size:	98.5 KB 
ID:	42559

  4. #4
    cantrellw is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4
    This is in Access. However, a table in access has rows, columns, and cells. I'm not sure how else I could describe them...


    Here's what I'm talking about, I select series and typed in 4199. I have 129 rows of data in the queried table shown below. I would like to be able to paste a filepath, or note, into the top cell and have it link all of these together. BUT, if I put in B4328, it would not have the same file path or note as 4199-4327.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,013

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,554
    Think I will have to leave this here. I'm not getting the information I asked for. Perhaps someone else can make a suggestion. I would advise you provide your table and relationships structure since it would appear you want to be able to update a field in your series table. But no idea if you have such a table, and if you do whether there are duplicates for different jobs.

    You also appear to be using a split form - these have limited functionality and I suspect you will need to develop your own to do what you want

  7. #7
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    691
    use an update query:


    update [TABLENAME] set Filelocation = "C:\MyLocation" where Series = "MYSERIESCODE"

  8. #8
    cantrellw is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4
    I accomplished what i need to do thanks to orange's help link. I created another form to input the series and put in filepath and then it runs an update query to update the JobTbl table. My only problem now is that Access doesnt like to hyperlink these locations through the update query, which seems to be a known issue and I've been looking into how to fix this part, but its workable for now.

    EDIT:
    Fixed the hyperlink issue. For others' reference. I just had to have the update query update what i wanted twice for the hyperlink fields to be filled out correctly, if that makes sense.

    for example, my update to line in my query is: [Forms]![SeriesUpdateFrm]![Text2] & "#" & [Forms]![SeriesUpdateFrm]![Text2] into a hyperlinked field on the table and it works as expected now.
    Last edited by cantrellw; 08-05-2020 at 02:01 PM. Reason: Fixed my own problem

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

Similar Threads

  1. Replies: 6
    Last Post: 09-14-2018, 01:47 PM
  2. Replies: 5
    Last Post: 09-06-2018, 11:37 PM
  3. Replies: 15
    Last Post: 12-11-2017, 07:29 PM
  4. Replies: 3
    Last Post: 12-30-2015, 05:24 AM
  5. Form To Table - To Many Cells
    By LOUM in forum Forms
    Replies: 3
    Last Post: 05-03-2012, 02:21 PM

Tags for this Thread

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 - Senior Forums