Results 1 to 6 of 6
  1. #1
    pulsarman605 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    3

    Angry HELP!!! Update Record from Another Record

    Hi,

    I have a simple table for a device database to keep track of phones.

    I want to be able to add a new phone(row) and copy data from an existing row to the new row.

    It is an SQL backend, I have managed to do this on SQL, but for the life of me cannot work out how to pull this across to access.

    I have created a form that the user can select the previous device, and put the details of the new device. But that is where I am stalled.

    This is the SQL statement I used.

    update Devices


    set ModelID = newdata.ModelID,
    stateID = newdata.StateID,
    CustomerID = newdata.CustomerID,
    DevTypeID = newdata.DevTypeID,
    PlansID = newdata.PlansID,
    InUse = newdata.InUse,
    Notes = newdata.Notes
    from
    (
    select
    modelID,StateID,CustomerID,DevTypeID,PlansID,InUse ,Notes
    from
    Devices
    where
    IMEI = 123456 (This will come from the FORM)
    ) newdata
    where IMEI = 1234567 (this will come from the FORM)

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am a little confused... You say you want to add a NEW phone, but then say you want to copy an existing phone record data into the new phone record, but then want to put the details of the new phone into the record?

    You have a form with controls for the phone details. (Not sure if the controls are bound or unbound.)
    You just need to execute an append query to add the new phone to the table.

    If the controls are unbound, you might use something like (**air code**):
    Code:
    Dim sSQL as String
    
    sSQL = "INSERT INTO Devices (modelID, StateID, CustomerID, DevTypeID, PlansID,InUse  , Notes) 
    sSQL = sSQL & " VALUES ('" & me.ModelID & "', '" & Me.StateID & "', " & Me.CustomerID & ", " & DevTypeID 
    sSQL = sSQL & ", '" & Me.PlansID & "', " & Me.InUse & ", '" & Me.Notes & "');"
    '  Debug.Print sSQL
    Currentdb.Execute sSQL, dbfailonerror
    I'm not sure which fields are what type, so some of the delimiters may be wrong.

  3. #3
    pulsarman605 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    3
    Thanks ssanfu

    Sorry if I was a little unclear. Ideally.... this is what I am trying to acheive.

    Someone has bought a new phone. I want to add that phone to my DB but copy some details from old phone, then remove those details from the old phone.

    My field are

    ID, IMEI, Serial, Notes, SimID, PhnumID, StaffID.

    The ID fields are the ones I want to copy from the "old' device to the "new" device and then null out.

    I was tying to make ONE form that they could choose the OLD IMEI in one section, then in the new phone section type new IMEI,Serial,Notes.

    Then click a button and it would do the reset in the background.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Does this apply to:
    1) An existing customer that has a phone they bought from you is now buying a new phone?
    1a) What if the phone is a different phone type? (ie has a LG, buying an Apple)

    2) A new customer that owns a phone, but is buying a phone from you?

    ID, IMEI, Serial, Notes, SimID, PhnumID, StaffID.
    These fields are different than the SQL you provided...

    Would you post your dB?
    Make a copy, delete all but a few records, change sensitive data, do a "Compact and Repair", then Zip it.

  5. #5
    pulsarman605 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    3
    Cheers again.

    Sorry yes the field are the same as previous.... sorry just didn't specify ALL of them ;-)

    My database is based in "devices" rather than customers/staff. I have over 40 "devices" I monitor in my company and I am trying to keep track of all of them.

    1. Yes, in theory that is correct. I would have an employee with a current Phone, and he is getting a new phone.
    1a. Different Type would be fine as I have a table for device "types.

    2. Is not really applicable.

    I am not sure I can copy the database as working as my tables are actually linked to SQL. So you would get nothing in them??

    I have attached a copy of my SQL Database Diagram.Click image for larger version. 

Name:	PhdbaseDiagram.png 
Views:	10 
Size:	29.9 KB 
ID:	34377

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have SQLSE on my development computer, so I could do testing.

    What about the controls on the forms? Are the controls bound or unbound?

    Curious..... I don't see any date fields. I would expect to see at least one in table DEVICES........





    Having all PK fields named "ID" is not a good practice. I would change the names of the PK fields to the name or part of the name of the table.
    Examples:
    TABLE Name
    PK Field Name
    Customers CustomerID_PK
    Sims SimID_PK
    Devices DeviceID_PK

    FK FIELD Names
    CustomerID_FK
    SimID_FK
    DeviceID_FK

    I use "ID" in my PK fields to indicate they are an Autonumber type. And, of course, the suffix "PK". And I change the suffix to FK for the foreign key field.

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

Similar Threads

  1. Replies: 16
    Last Post: 01-18-2016, 07:25 PM
  2. Replies: 3
    Last Post: 10-04-2015, 10:17 AM
  3. Replies: 4
    Last Post: 02-18-2015, 11:28 AM
  4. Replies: 1
    Last Post: 05-26-2014, 12:39 PM
  5. Replies: 4
    Last Post: 12-14-2012, 06:33 PM

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