Results 1 to 5 of 5
  1. #1
    Zstar91 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    2

    Angry MS Access - movement simulation

    Hello,



    i would like to ask for support with an ,,easy,, task. I have two different tables, where in one table there is a list of unique records (ProductsT) with an unique Field [LabelID].

    Then i have a second table which should record all movements (MovementT) of [LabelID] so there are many lines with [LabelID] and Unique is only the Time Entry.

    Now i need to connect those two tables with the fields [Actual location], [Last movement] -> TO (ProductsT) FROM (MovementT) [Destination location], [Date/Time Entry], WHERE [LabelID] - last of recordset:

    Once again:
    If new record in (MovementT) AND (MovementT).[LabelID] = (ProductsT).[LabelID] Then
    Copy (MovementT).[Destination location]-Previous Record To (MovementT).[Previous location]-New Record

    (MovementT).[Destination location] is new input

    Copy (MovementT).[Destination location] To (ProductsT).[Actual location]
    Update in both tables [Last movement] and [Date/Time Entry] by Date

    See my 2 screenshots, where i manualy filled the data as example - Target is to have a Form, where i scan(enter) LabelID - get all data from Products, fill in 1 field (Destination location) and after save(confirm) above should be done automatically.

    Many thanks in advance

    ProductsT
    Click image for larger version. 

Name:	ProductsT.png 
Views:	30 
Size:	29.8 KB 
ID:	45525
    MovementsT
    Click image for larger version. 

Name:	MovementT.png 
Views:	31 
Size:	20.4 KB 
ID:	45526
    Attached Thumbnails Attached Thumbnails Screenshot 2021-06-18 124352.png  
    Last edited by Zstar91; 06-18-2021 at 04:45 AM. Reason: additional explanation

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,613
    I'm wondering what the underlying business is. Many years ago there was a poster trying to move equipment from one location to another OR replace equipment with another from Inventory. All movements were recorded as transactions. Here is the link to the thread.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,171
    Methinks you'd have an easier and less risky job of it if you only had a Location field in movements. Then the last location is simply the record where Max of the Dates for the item that was moved, and you'd get that from a subquery. Don't use a subquery and you get all movements in chronological order when sorted by date.

    Trying to get field values from related records when those values are in another field is hard enough. Getting them in correctly is even harder and is something that I would avoid. Maybe the approach that you're wanting to use here is influenced by Excel-itis?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,361
    Looking at your post, I would say you should redesign your tables. You are duplicating data in different tables.

    Consider:
    Code:
    tblProducts
    ------------
    ProductID_PK         Autonumber
    ProjectNumber        Text
    ProjectName          Text
    SerialNumber         Text
    LabelID              Text  (Not sure what LabelID means - maybe needs a better name?)
    Remarks              Text
    
    
    tblMovements
    ------------
    MovementID_PK              Autonumber
    ProductID_FK               Long Integer    (Link to tblProducts)
    EmployeeID_FK              Long Integer    (Link to tblEmployees)       (CreatedBy)
    SourceLocationID_FK        Long Integer    (Link to tblLocations)       (SourceLocation)
    DestinationLocationID_FK   Long Integer    (Link to tblLocations)  (DestinationLocation)
    CreationDate               Date/Time
    
    
    tblEmployees
    ------------
    EmployeeID_PK       Autonumber
    FirstName           Text
    LastName            Text
    EmployeeNumber      Text
    
    
    tblLocations 
    -------------
    LocationID_PK        Autonumber
    Location             Text

    You would not try to enter/edit data using tables. You would have a main form (Projects) and a sub form (Movements).
    For the main and sub form record sources, I would use queries.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Zstar91 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    2
    Sorry for my late reply, I will try this option and let's see..

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

Similar Threads

  1. Mouse movement restricted in debug mode
    By Pietro_V in forum Access
    Replies: 5
    Last Post: 01-26-2021, 12:24 PM
  2. Watching movement between folders
    By Rendon115 in forum Programming
    Replies: 3
    Last Post: 06-22-2020, 06:11 PM
  3. Simulating mouse movement and clicking....
    By rpeare in forum Programming
    Replies: 10
    Last Post: 07-29-2018, 07:21 PM
  4. Replies: 2
    Last Post: 02-17-2012, 04:09 AM
  5. Replies: 1
    Last Post: 03-29-2009, 08:27 AM

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