Taking value from a control in a form and place that value in a field in another table.

[CJ_London]

You don't need to save it as it is automatically copied to the clipboard - see image, I don't know how to embed it - Neopa, please can you do the honours or point me in the right direction as to how to do it :)
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10569d166121476 3/image_2022-08-23_013240557.pn g[/IMGNOTHUMB]

[NeoPa]

Hi CJ :-)

When it's been attached it shows as a link by the post. Copy the URL using right-click | Copy Link. Edit your post and add [IMGNOTHUMB][/IMGNOTHUMB] around this link. If you edit one of your own where I've already done it for you you'll see an example of it in action.

For further options see BB Code. That's pretty old and the supported options change over time, but it'll give you a lot to choose from.

[MNewby]

Okay, obviously I need to join the TourT.ID = CADLogT.TourID, I assume as a one (TourT.ID) to many CADLogT.TourID, correct? Also, do I need to join the UnitAvailable field in both in order for the UnitAvailable field in both to match values after the record is saved in CADLogT?

[MNewby]

If I put this image in correctly, this will give you a visual depiction of my forms being used and their underlying tables.
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10574d166126259 2/snap-2022-08-22-17.58.46.png[/IMGNOTHUMB][IMGNOTHUMB]https://bytes.com/attachments/attachment/10575d166126259 2/snap-2022-08-22-18.00.22.png[/IMGNOTHUMB][IMGNOTHUMB]https://bytes.com/attachments/attachment/10576d166126259 2/snap-2022-08-23-09.40.58.jpg[/IMGNOTHUMB][IMGNOTHUMB]https://bytes.com/attachments/attachment/10577d166126259 2/snap-2022-08-23-09.45.34.jpg[/IMGNOTHUMB][IMGNOTHUMB]https://bytes.com/attachments/attachment/10578d166126259 2/snap-2022-08-23-09.46.29.jpg[/IMGNOTHUMB]

[NeoPa]

Hi Mark.

I'm still unclear what you're after here. If you have a one-to-many (1-M) connection between your tables, what is it that triggers changes to your[UnitAvailable] flags? Do they all have to change when any one does or can there be different values across the log records? If different then how do you determine which one should match between the log records and the [TourT] one?

What's going on here logically - in human terms - for you to want to do this type of update?

[MNewby]

Okay, we will fist talk about the subform CAD_LogDispF with its record source being CADLogT.

When the operator selects the Unit (TourID) in the TourID control, it of course placed that TourID value in that field. Then the operator tabs to the next control, ActionID and selects the appropriate action, i.e.

DP is Dispatched Primary which has a value of 1
DB is Dispatched as Backup which has a value of 2
A is Arrived which has a value of 3
etc. etc.

On the BEFORE UPDDATE event of the subform, I have the Select Case ladder, which depending on the action the operator selects, 1 or 2 or 3 or etc, the Case Ladder puts a 1 (for Unit Available) or 0 (for Unit Not Available) in the UnitAvailable field in the subform's record source CADLogT.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.EmployeeID = DLookup("EmployeeID", "LocalUserT")
    
    If IsNull([EntryDateTime]) Then
        [EntryDateTime] = Now()
    End If
    
    Select Case ActionID.Value
    
        Case Is = 1: UnitAvailable = 0
        Case Is = 2: UnitAvailable = 0
        Case Is = 3: UnitAvailable = 0
        Case Is = 4: UnitAvailable = 1
        Case Is = 6: UnitAvailable = 1
        Case Is = 7: UnitAvailable = 1
        Case Is = 8: UnitAvailable = 1
        Case Is = 10: UnitAvailable = 1
    
    End Select
            
End Sub

On the ON CURRENT of that same subform, I have the following subroutines.

Private Sub Form_Current()

    If ActionID = 1 Or 2 Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qry_CAD_UpdateTourUnitAvailByLog"
        DoCmd.OpenQuery "qry_CAD_UpdateDispTimeInTourT"
        DoCmd.SetWarnings True
    End If
    
    If ActionID = 3 Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qry_CAD_UpdateTourUnitAvailByLog"
        DoCmd.OpenQuery "qry_CAD_UpdateBeginTimeInTourT"
        DoCmd.SetWarnings True
    End If
    
    If ActionID = 4 Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qry_CAD_UpdateTourUnitAvailByLog"
        DoCmd.SetWarnings True
    End If
    
    If ActionID = 6 Or 7 Or 8 Or 10 Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qry_CAD_UpdateTourUnitAvailByLog"
        DoCmd.OpenQuery "qry_CAD_UpdateEndTimeInTourT"
        DoCmd.SetWarnings True
    End If
    
 End Sub

The UPDATE query that I have below will update the UnitAvailable field in the TourT table each time the record in the CADLogT is saved. This seems to work but probably not very efficiently as I think the way I have it, it looks at every record in the table. I was hoping that I could achieve the same thing without UPDATE queries.

UPDATE CADLogT LEFT JOIN TourT ON CADLogT.TourID = TourT.ID SET TourT.UnitAvailable = [CADLogT].[UnitAvailable];

Let's talk now about the table, TourT with ID as is Primary Key. It also has a field called UnitAvailable. This needs to toggle 1 or 0 to indicate whether that Unit (Officer) is available or not.

Now lets go back to the subform.

On the subform with its record source being CADLogT, if that record's UnitAvailable value is 1, I need the UnitAvailable value in the table TourT to change to 1. For example, if unit 704 is selected, and ActionID of DP is selected (value of 1) then the UnitAvailable field in the CADLogT is changed to a value of 0. I have the above subroutines that OpenQuery "qry_CAD_Update TourUnitAvailBy Log". It does seem to work but I believe it is not efficient or the correct way to get the UnitAvailable value to change.

[MNewby]

Maybe a simpler way for me to say it is this way.

If the ActionID value in the ActionID control on the subform named CAD_LogDispF is a 1 or 2,

AND

If the DispDateTime value in the DispDateTime control in the parent form CAD_CallDispSpl itF is empty,

THEN

the value in the DispDateTime field in the parent form CAD_CallDispSpl itF should equal the value in the EntryDateTime control on the subform CAD_LogDispF.

[NeoPa]

Hi Mark.

Would it be possible to go back to my last post and respond to the questions I ask there? You throw a great deal of extra information at me, but I see nothing there to give context or provide an understanding of why you have what appears to be an over-complicated structure or design. Just loads and loads of extra detail to trawl through before realising it provides nothing helpful. Nothing I could find anyway. Please consider your answers carefully. They could save a lot of time & effort.

[MNewby]

Okay, I will go back to the other post.

[MNewby]

The UnitAvailable flag in the subform's table changes based on the Select Case ladder in the subform's Before Update event. I showed that Case ladder in earlier post in this thread. Right now I am relaying on an Update Query to update the UnitAvailable field in the TourT table, which does not seem to be efficient.

Perhaps I am wrong but I do not believe I can link the TourT table and the CADLogT together and make that query the record source for the subform. I apologize if I am making this convoluted, I just don't understand.

[MNewby]

There would be several TourIDs in the log pertaining to the same incident ActivityT.ID. For instance the unit (TourID) is dispatched to the call (ActionID 1), the same unit arrives on scene (ActionID 3), the same unit returns available (ActionID 10), etc..... all on a separate record in the CADLogT. In each record in the CADLotT, there is a UnitAvailable field. It changes based on the Select Case ladder.

It is certainly possible for a second unit to be dispatched as backup, with the same procedures I described in the above paragraph. The backup unit of course has a different TourID than the primary unit.

I am not sure if that answers your question.

[MNewby]

If it would be easier for you to understand, I can zip the db and post it.... it has no live confidential data in it.

[NeoPa]

Hi Mark.

I'm sure it all makes sense to you, but when you use words to represent an idea then I have to recognise what those words pertain to. TourID makes sense to you. To me it's a word. When I have a clear understanding of what it refers to then I still have to translate that from TourID every time you use it when it comes to understanding the context. There are clues as to what you understand it to mean, but spread across various sentences.

Tell me if/when I go wrong.

1. A single record in your [ActivityT] table represents an incident that a small police agency has to deal with.
2. A single record in your [TourT] table represents a police unit. I assume a unit would be a patrolling group either in a vehicle or on foot.
3. The [CADLogT] table records represent incident actions that will always include one unit and an action. Each such action either ties up or frees up a unit, depending on [ActionID].
4. The [CADLotT] table is presumably simply a mis-spelling of [CADLogT].

Mark:
Perhaps I am wrong but I do not believe I can link the TourT table and the CADLogT together and make that query the record source for the subform.

From my understanding of your table structure that would be wrong. It's what I've suggested to simplify your process considerably.

Mark:
In each record in the CADLotT, there is a UnitAvailable field.

Why? What possible reason could there be for storing that information again when it is already determined from the [ActionID] value? Only the [TourT] table should contain a [UnitAvailable] Field.

NB. To take your database and reverse-engineer it in order to find what the questions ought to be is a very labour-intensive way to find out what you think you're trying to do. Generally not helpful to find what the question should be.

Hopefully that should be some help, but I'm flying almost blind so if we need to clarify further we'll see what we can do.

[MNewby]

All 4 are correct. The only reason I had a UnitAvailable field in subform CADLogT was so that I could do a Update query.... but as I mentioned before, that is not efficient I don't believe.

[MNewby]

Okay, Neo..... It finally sunk into my thick head. Linking those two tables did work. I removed the UnitAvailable field in the CADLogT. The Case ladder in the Before Update does change the value as I need. Wow.... thanks for being patient. I am sure I will have other questions. I will consider this post solved.