Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2009
    Posts
    15

    Unanswered: Updating fields in another record/table, from current record/table

    Been a while, but I need some help with updating some fields in a record in a different table, from a record in the current table i am working in.

    The short version. I have a table "Master" that lists all of our equipment; unit number, unit description, current lcoation, arrival date, driver. The other table "LogTable" contains the trucking schedule of that equipment; unit number, unit description, going to, coming from, driver and scheduled transport date. I would like to "update" the records in Master when a transport is marked completed (with a combo box, using the after update event), sending it the current position, date and driver of the record I just marked completed. I have the following SQL statement worked out, but it updates every record I have in LogTable. Is there a way to only update the current record I am working with? Guess I am just looking for a little help with the wording of this statement.

    DoCmd****nSQL "UPDATE Master INNER JOIN LogTable On Master.UnitNumber = LogTable.UnitNumber Set Master.CurrentLocation = LogTable.From, Master.ArrivalDate = Date(), Master.Driver = LogTable.Driver"

    A little vague, I know. I can post more info if needed.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You need to add a WHERE clause. This would get a numeric value from the form:

    DoCmd****nSQL "UPDATE Master INNER JOIN LogTable On Master.UnitNumber = LogTable.UnitNumber Set Master.CurrentLocation = LogTable.From, Master.ArrivalDate = Date(), Master.Driver = LogTable.Driver WHERE KeyFieldName = " & Me.KeyFieldName
    Paul

  3. #3
    Join Date
    Jun 2009
    Posts
    15
    pbaldy! You helped me with a home brewing database about 4 years ago, still use it to this day! Funny to have your help again! Thanks!

    On to the keyfieldname, I am having trouble with referencing that. Do I need to reference it like WHERE Master.UnitNumber = " & Me.UnitNumber? And for the less informed, what to the quotes and ampersand mean?

    thanks again for your help. when I get back to the office tomorrow I will give this a try! This little database will really help our dispatcher.

    I will also need to write a statement to append this same info to another table to track each and every transport. Could you help me when the time comes?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't recall getting my free beer samples yet!!

    What you're trying to achieve is

    ...WHERE Master.UnitNumber = 123

    where the 123 is coming from the form (which is what I assume you want). To do that, you include the fixed portions of the string inside the quotes, and the form reference outside the quotes. The & is a concatenation operator, so it joins them together. Thus if the form control named UnitNumber contained 123, the finished SQL would look like the above. This may help with debugging the SQL:

    Debugging

    and this is a decent tutorial by JasonM on building SQL in VBA:

    Building SQL in VBA
    Last edited by pbaldy; 11-16-11 at 14:35. Reason: Fix spelling goof
    Paul

  5. #5
    Join Date
    Jun 2009
    Posts
    15
    We will have to work something out with the samples! The home brewing hobby has become something of a major project now, I am making a lot of beer!

    I have encountered a small problem that has popped up. I added that line to the SQL statement, but now I get a "Data type mismatch in criteria expression" error. I have double checked that both fields in both tables are set up exactly the same, for obvious reasons and I have tried just having one field at a time being updated, but am still hitting this wall. here is my statement in full. Do you see anything wrong? Any help would be greatly appreciated!

    DoCmd****nSQL "UPDATE Master INNER JOIN LogTable ON Master.UnitNumber = LogTable.UnitNumber Set Master.CurrentLocation = LogTable.From, Master.Driver = LogTable.Driver, Master.ArrivalDate = LogTable.SchDate WHERE Master.UnitNumber = " & Me.UnitNumber

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That would imply that UnitNumber is a text field. As addressed in the tutorial, that would require a different syntax. Try

    DoCmd****nSQL "UPDATE Master INNER JOIN LogTable ON Master.UnitNumber = LogTable.UnitNumber Set Master.CurrentLocation = LogTable.From, Master.Driver = LogTable.Driver, Master.ArrivalDate = LogTable.SchDate WHERE Master.UnitNumber = '" & Me.UnitNumber & "'"
    Paul

  7. #7
    Join Date
    Jun 2009
    Posts
    15
    Sorry about that. I forgot I had to change that field to a text field as some of our units have letters in the unit number. But, it seems to be working! I am running into one problem though, if you wouldn't mind. The main screen where the dispatcher can edit and create asset movements is shown in continuous form. When there is more than one movement of the same unit on the screen and the I hit "completed" which triggers the SQL code to run, it doesn't perform the update correctly - or at all. Instead of the popup window saying it is updating one record, it has the count (3, 4, 5...) of how many records for that unit number there are in the list. Is there a better way for me to only reference the specific record I am hitting 'complete' for? I though that the update call referencing that unit number would work great, but...

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't know your data. Is there another field that either by itself or in conjunction with unit number will uniquely identify that record?
    Paul

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pbaldy View Post
    I don't recall getting my free beer samples yet!!
    aaaargh deedums...
    will this help

    I would have included more but apparently dbForums takes boozing seriously.. you are only allowed ten per post
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    LOL! Right back at ya:

    Paul

  11. #11
    Join Date
    Jun 2009
    Posts
    15
    LOL - If you are ever in Wyoming, hit me up! I always have 3 different beers (something light, something brown and something dark) on tap! Plus a soda for the kid's.

    I have the unique ID number for each log entry, but it isn't associated with anything in the master asset list. Can that be used somehow?

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If it uniquely identifies the record you want to update, I don't see why not.
    Paul

  13. #13
    Join Date
    Jun 2009
    Posts
    15
    OK! I got the update part of this little mission working perfectly! Thank you pbaldy!

    Now I have the same issue with my insert statement. It is seeing all the other records with the same unit number and throwing an error. I need to find a way to do the same thing as the update and only pull the data from the current record, but trying the same WHERE clause as the UPDATE doesn't work. Anybody have any ideas?

    DoCmd****nSQL "INSERT INTO History ([UnitNumber], [Description], [MovedTo], [Arrived], [Driver]) SELECT [UnitNumber], [Description], [To], [SchDate], [Driver] WHERE LogTable.[ID] = forms![Schedule View]![ID]"

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You haven't concatenated the form reference in as we did on the other. What's the error, and what's the data type of the field in the criteria?
    Paul

  15. #15
    Join Date
    Jun 2009
    Posts
    15
    Eh, forgot about that step. I am using the ID field of LogTable, so the data type is autonumber (long integer). Without the WHERE clause, I get the popup saying it is updating the one table (click yes - these popups will go away once I'm done with this bug hunt), then the other popup saying it is appending the second table (click yes) then the next window says "Trucking Scheduler can't append all the records in the append query" 0 to null, didn't add one record, 0 records locked, 0 validation rules. Need to redo the WHERE clause i have and see what i get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •