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

[MNewby]

Let me first explain the application, and state early on that I am an amateur at VBA. I am creating an Access database that will function similar to Computer Aided Dispatch for small police agencies. I have a table called TourT with a PK of ID.

I have a form named CAD_UnitSetup with its record source as the above table, TourT.

I have another form called CAD_CallDispSpl itF with its record source is ActivityT. In the ActivityT table, I have a field named TourID.

There is a subform to the above parent form CAD_CallDispSpl itF named CAD_Log_DispF. The subform's record source is CADLogT. In CADLogT, there is a field called TourID. Through a combo box on the subform CAD_Log_DispF the operator will select the appropriate Unit (TourID) and place that value in the TourID field in the subform's record source table CADLogT.

Also in the CADLogT, there is a number field called ActionID. The Operator will select the appropriate action from a combo box and place that value in the ActionID field. The actions are similar to 1 is for Dispatched as Primary, 2 is Dispatched as Backup, 3 is Arrived, etc.

I created the following Select Case subroutine in the BeforeUpdate event of the subform.

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

This places the value of 1 or 0 in the UnitAvailable field of the CADLogT. 1 is for Unit is Available, and 2 is Unit Not Available, (I chose to use a number value rather than Yes/No for other reasons).

When the operator selects the appropriate Action on the subform, it places a 1 or 0 in the UnitAvailable field. This simply means that for that particular record, for UnitAvailable is either 1 or 0 based on the selection of the ActionID control. This works fine but it does not achieve my ultimate goal.

My ultimate goal is to have the UnitAvailable field in the first table I described above, (TourT), toggle 1 or 0 to match the 1 or 0 in the latest record in the Log for the related TourID.

Any help would be appreciated, and please remember, I am a beginner to VBA.

Mark

[NeoPa]

Hi Mark.

Welcome to Bytes.com.

Let me start by saying that, as a newbie, you show remarkable forethought and attention to detail in your question. It's a fair amount to take in, but it's all there - and expressed pretty well too.

Now, if I understand you correctly, you want to update a matching value (UnitAvailable) in your table [TourT] whenever you make a change to that in one of your matching (on [ID]) [ActivityT] records.

For me, the easiest way to do this is to make the RecordSource of the Form into a Query that links the two tables on the [ID] field and includes the [TourT].[UnitAvailable] Field on that Form. You would need an AfterUpdate() Event Procedure for the [ActivityT].[UnitAvailable] Control to trigger the other value being updated, but that seems more straightforward than doing a side-along update of an unrelated table.

Just quickly, let's illustrate a different way of handling your Select Case statement :

Select Case Me.ActionID.Value
    Case 1 To 3
        Me.UnitAvailable = 0
    Case Else
        Me.UnitAvailable = 1
End Select

[CJ_London]

alternative to the case statement would be

unitAvailable = Abs(ActionID>=4)

[NeoPa]

There's no question that would work, but would it pass the test of being easier to maintain? I think most experts (and I certainly include you in that list) tend to advise against such code on the bases that :

1. It's harder to maintain - especially for those who haven't written it.
2. It relies on the implementation of the Boolean handling within VBA.
   Not everyone is as familiar with that as you are ;-)

[CJ_London]

If the only options are as set out by the OP then personally think just as easy to maintain. But in the future if a value of 5 can be sent which requires a 0 or there are values above 10 then it would not be appropriate and you would then use a case statement along the lines you proposed.

I do think users should learn and understand boolean handling. Every comparison provides a boolean result, even the case statement - either an action is between 1 to 3, or it's not, there is no 'maybe'. But there is an 'otherwise' with the else (which is still true/false)

This is moving off topic for this thread but I've lost track of the number of times I've seen OP's with code that uses AND's and OR's without brackets and don't understand why they get a wrong result because they don't understand boolean logic. I would also add that I extensively comment my code so it provides a 'story' so future developers can follow the logic as it provides a 'why'

[NeoPa]

I'm almost tempted to split this into a new thread as you raise some important issues. Maybe if I have some idle time somewhen.

I will limit myself to agreeing and sympathising about many developers' struggles to work well with Booleans - even though that's a more pure issue and doesn't result from their understanding (or lack thereof) of the underlying handling of it within VBA.

[MNewby]

Thank you for the response. I am trying. Regarding

"For me, the easiest way to do this is to make the RecordSource of the Form into a Query that links the two tables on the [ID] field and includes the [TourT].[UnitAvailable] Field on that Form."

Are you suggesting making a query as the record source for the subform CAD_LogDispF. Then join the CADLogT.UnitAva ilable and the TourT.Unitavail able fields in that query?

Mark

[CJ_London]

I have to say I'm struggling to understand your relationships - any change you can post a screenshot?

[MNewby]

CJ, I don't see how I can insert of a screen capture. The window is asking for an http:// address.

[CJ_London]

Click on the advanced tab (bottom right of the response box)
then click on upload files. See attached :
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10567d166121048 7/image_2022-08-23_002125577.jp g[/IMGNOTHUMB]
If you are using something like the snipping tool, you should just be able to select it from the clipboard.

[NeoPa]

Mark:
Are you suggesting making a query as the record source for the subform CAD_LogDispF. Then join the CADLogT.UnitAva ilable and the TourT.Unitavail able fields in that query?

Difficult to say - as I don't think I understand what you're asking. What does "Join" mean in that sentence?

The first part is certainly correct. From there you would have a Control for the [ActivityT] version of [UnitAvailable] as well as for the [TourT] version, though that could be set as not visible if you chose. Now, whenever the first is updated then you detect that and, if & when any record is saved you also check whether or not this Control has been updated. If it has then, in the Form_AfterUpdat e() Event procedure, you update the second Control and save the record again. Thus updating the [TourT] version of the [UnitAvailable] value any time the [ActivityT] one is changed.

[MNewby]

I might not have called it correctly, when you said make a query as the subforms record source, I have to bring in both tables, the CADLogT and the TourT. Don't I have to join the two tables on the TourT.ID and the CADLogT.TourID?

[MNewby]

I am trying to figure out where the snipping tool saves the image.... do you know the default location?

[NeoPa]

Mark:
Don't I have to join the two tables on the TourT.ID and the CADLogT.TourID?

The two tables are JOINed within the query itself, yes. The Form though, treats them as a single, flat, set of records. Both the Fields (or the same-named Field from both Tables) exist side-by-side in the dataset returned for the Form to use. These two Fields will always have different names assigned automatically but you can assign your own if it helps you work with them more easily.

Mark:
I am trying to figure out where the snipping tool saves the image.... do you know the default location?

When you have the Snipping Tool open it allows you to save your images wherever you like. It brings up a standard Save-As window where you can select or see both the name and the folder.
Once you have the file saved you can attach it using the 'advanced' tab as suggested and upload it. Once it is an attachment then it will have an associated URL and will thus be able to be shown within your post using the [IMGNOTHUMB] (or even just [IMG]) tags.

[NeoPa]

Mark:
Don't I have to join the two tables on the TourT.ID and the CADLogT.TourID?

Did I misunderstand what you were asking about with my earlier answer? Bear in mind I have very little understanding of your table structure. I thought you needed [TourT].[ID]=[ActivityT].[ID], but if the other table is [CADLogT] rather than [ActivityT], and the related Field is [TourID] rather than [ID], then yes. That's what you'd need to do the JOIN on in your query ([TourT].[ID]=[CADLogT].[TourID]).