Driving input from one form to update another

la1814

Registered User.
Local time
Today, 08:37
Joined
Dec 27, 2013
Messages
34
[SOLVED] Driving input from one form to update another

Good morning. I am having an issue with this code that I cannot figure out.
I am attempting to update one form based on inputs to another.
1. AircraftDatasheetForm (Form not open, being updated)
2. Flight (Form open and being changed, driving the update to AircraftDatasheetForm)
I have three fields in the Flight form that are driving the update.
1. Airborne (denotes aircraft is airborne)
2. Fifteen (denotes the aircraft is 15 min out from landing)
3. OnDeck (denotes the time the aircraft has landed)
I have one field in the AircraftDatasheetForm I want to update based on Airborne, Fifteen and OnDeck fields from the Flight Form.
1. FlightStatus

Both forms have one thing in common, the ‘side’ number of the aircraft that is being flown.
When the Airborne, Fifteen or OnDeck field is checked yes I want the following to occur:
1. Airborne (puts a number 1 in the FlightStatus field on the AircraftDatasheetForm)
2. Fifteen (puts a number 2 in the FlightStatus field on the AircraftDatasheetForm)
3. Fifteen (puts a number 3 in the FlightStatus field on the AircraftDatasheetForm)
If the Airborne was checked ‘Yes’ and subsequently checked ‘No’ I wanted to revert the FlightStatus back to 3 (meaning OnDeck)

Note, during the course of the day the same aircraft will fly multiple times, thus will have several records in the Flight form, however it only has one line in the AircraftDatasheetForm (defining itself). The update in FlightStatus will be driven off the latest Airborne/Fifteen/OnDeck entry.

The below code seems to work, however if I test it with multiple changes it eventually does not complete the update. I also noticed a conditional format I have in a field labeled ‘Side’ in the AircraftDatasheetForm based on the FlightStatus field does not automatically update after this AfterUpdate code is completed. Is there some type of code I need to put into this to drive something in the AircraftDatasheetForm to trigger the update of that form, subsequently the conditional format as well and so that if someone has the AircraftDatasheetForm open they will immediately see the change in FlightStatus?

Also note, I tied a few ‘And’s in the code in the event someone unchecks some type of combination of the Airborne/Fifteen/OnDeck it will give me the most logical result.
Most thanks in advance for your help.

Code:
 [SIZE=3][FONT=Calibri]    If Airborne And Fifteen And Side = Forms![AircraftDatasheetForm]![Side] Then[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]        Forms![AircraftDatasheetForm]![FlightStatus] = 2[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]        Else[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]        If Airborne And OnDeck And Side = Forms![AircraftDatasheetForm]![Side] Then[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]            Forms![AircraftDatasheetForm]![FlightStatus] = 3[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]            Else[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]                Forms![AircraftDatasheetForm]![FlightStatus] = 1[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]        End If[/FONT][/SIZE]
 
Last edited:
Your post is too complicated for me, I had to use Ctrl + F to follow it, and even then I'm not getting it.

>>> AircraftDatasheetForm (Form not open, being updated) <<<

You can't update the form if it's closed.

You could have it look in another form when it opened, and update its self.

But you also say about "others" using it?

I think you should have the info in a table.

Don't know really as I'm not getting the question.
 
AircraftDatasheetForm (Form not open, being updated)
First of all you can't update a form that is not open, not even when it's in design view. However, even though the form is open you don't still update the form, you update the table that is bound to the form.

So with all those out of the way, you need to update the underlying table either via a query or using a recordset. If you want to transfer values from one form to another that's a different case.

You need to very briefly and clearly explain what it is you're trying to do.
 
I think that transferring values from one form to the other would help my cause here. This way I could use a conditional format to change the background color of a Field. I have researched numerous ways and have yet to find the answer...Update Query, Joins, Code...

I will try to simplify this.

Two tables:
Aircraft (Parent)...lists all the aircraft by SideNumber
AirPlan (Child)...lists what times the SideNumber flies throughout the day

I have an 'Airborne' Yes/No Field in the AirPlan. When Airborne is checked 'Yes' on the AirPlan Table I want the Aircraft 'Form' to have a conditional format that colors the background color of the aircrafts 'SideNumber' to blue (signifying it is airborne with a quick glance at the datasheet form). Seems like it would be simple but for the life of me I am at a roadblock. Each form will be open by different users...so I will have to also trigger a refresh of some sort so the change applies immediately.

I hope this simplified my issue. Again, I appreciate any help and thank you much for your time.
 
So how's the form setup? Do you have a main form for Aircraft and a subform for Airplan?
Or if they are setup as two separate forms, have you at the least included the Airborne field in the Airplan form via Sidenumber?
 
vbInet,

I have each form separate/different tabs, in a Navigation Form.

[Aircraft]![Sidenumber] provides the lookup to populate [AirPlan]![Sidenumber]...this is the only relationship I have between the two tables. [AirPlan]![Airborne] is only on the [AirPlan] Form.

(note: I tried putting [Airborne] on the Aircraft Table and join the two forms with a query but ran into issues since I want the AirPlan (Child Form) the primary form where the change is made. Seems there is a one way check valve here in that the Parent Form can only drive the change??...or am I incorrect?)

What I tried to explain earlier and failed was this...(this way is more along the lines of updating the underlying table)

I had an event (code) set up under [AirPlan]![Airborne] that populated a field in [Aircraft]![FlightStatus] with a '1' if the aircraft were airborne (both forms open...which I embedded a DoCmd to open the closed [Aircraft] Form). I then used that to write an expression/conditional format to turn [Aircraft]![SideNumber] blue.

I am sure you have noticed the novice here. If anything I have learned a lot from my trial and error over the last couple days. Thanks again for any assistance and guidance to this newbie.
 
I'm a bit confused by your last post. Can you upload a sample db? Just include only the relevant forms and some (fictitious) test data.
 
VbaInet,

I have attached a sample db. Please note, the db will be used by several people at the same time to update info. My hopes are to have a conditional format on the Aircraft!Side that colors it blue when the AirPlan!Airborne is checked yes. AirPlan!Airborne will also drive other things in the Aircraft Datasheet Form at a later date.

Ideally I want the user to update the 'Airborne' from the AirPlan Datasheet Form. It would be perfect if somehow I could have an 'Airborne' field on the Aircraft Datasheet Form also so that the person viewing that form can see the box checked. Notice the Aircraft!FlightStatus...this is what I was eluding to earlier that I wrote an event that did the following...if AirPlan!Airborne was checked it would = 1, AirPlan!Fifteen = 2 and AirPlan!OnDeck = 3. This was a work around that had limited success.

Thank so much for your help with this. Hope you enjoyed the weekend.
 

Attachments

VbaInet,

Any pointers would be much appreciated. Thanks again.
 
See attached and here are my comments:

1. The status will change in this specific order On Deck -> Fifteen -> Airborne. The code is a recursive call that reads in that order. You can replace it with a bunch of IF statements if you don't understand the code.
2. The status is not saved and should not be saved. There's just no reason to save it.
3. With regards the three fields, your table design isn't right. Only one field is required and I would have thought that you would incorporate a date/time stamp whenever a status is changed. You could have used a combo box for this since the status is mutually exclusive.
 

Attachments

vbaInet,

Apologize for the delayed response. I have been out of town for the past few days. I can't thank you enough for the help you have provided. I have incorporated the time stamps you mentioned. The actual database goes into much more detail. I sent you the most basic to keep things simple. Per your rec I will change to a combo box.

I am sure this is simple...but how do I download and view the attached file? When I attempt to it turns into a PHP and I cannot view?

Again, thanks so much, wish there were a way I could repay the favor.

la1814
 
It's the same file that you sent me, I just renamed it. Click the file and it will download to your Downloads folder.
 
Re: [SOLVED] Driving input from one form to update another

Figured my issue out with downloading. Thanks for the fix. With a few tweaks it will do exactly what I need. Brilliant...again, thanks for the help and enjoy the rest of your weekend.
 
vbaInet,

Still have an issue with this one. The 'FlightStatus' is nicely updated, however it updates the same for every 'Side' in the Aircraft Form, not just the particular side that was just noted Airborne/Fifteen/OnDeck. I want it to update only for the 'Side' that is Airborne/Fifteen/OnDeck. I tried adding an 'And Side=Forms!Aircraft!Side' into the SetCraftStat(). I also tried it in the Function GetStatus but it did not work. Any thoughts on how this can be fixed.

Code:
 Private Sub Airborne_AfterUpdate()
    SetCraftStat
End Sub
 Private Sub Fifteen_AfterUpdate()
    SetCraftStat
End Sub
 Private Sub OnDeck_AfterUpdate()
    SetCraftStat
 End Sub
  
 Private Sub SetCraftStat()
' Updates the status textbox (not field)
     If Application.SysCmd(acSysCmdGetObjectState, acForm, "AirCraft") <> 0 Then
        Forms!Aircraft!txtFlightStatus = GetStatus("On Deck")
    End If
End Sub
  
  
 Private Function GetStatus(ByVal VarStatus As Variant)
' Rolls back (i.e. from OnDeck -> Fifteen -> Airborne) to find which field has a tick
' If none, it will return Null
     If IsEmpty(VarStatus) Then
            GetStatus = Null
    ElseIf Me(Replace(VarStatus, " ", "")).Value = True Then
        GetStatus = VarStatus
    Else
        GetStatus = GetStatus(GetPrevStat(VarStatus))
    End If    
End Function
  
  
 Private Function GetPrevStat(ByVal StrStatus As Variant) As Variant
' Returns the name of the previous field
     Select Case StrStatus
        Case "On Deck"
            GetPrevStat = "Fifteen"
        Case "Fifteen"
            GetPrevStat = "Airborne"
        Case "Airborne"
            ' do nothing
    End Select
    
End Function
 
Both the Aircraft and AirOpsForm are Datasheet forms. Ultimately they will both be put into a Navigation Form. The Database (a few posts earlier) is the code I am referring to. It updates Airborne/Fifteen/OnDeck when I open the Forms, but it updates for all 'Side's. The code does not work on the Navigation form. I have not attempted to trouble shoot that. I want to ensure the Side issue is fixed before I move on. Thanks so much again. I thought a simple 'And Side = Forms!Aircraft!Side' would have worked.
 
I know the code you're referring to, I wrote it ;)

If you look at the code carefully, I'm not setting the value of any field, I'm just updating the value of the textbox. In datasheet view or continuous form the value for an unbound control will be repeated across all records.

Yes you will encounter problems referencing controls in a Navigation Form because they're mainly used as switchboards not data entry forms. You can only reference the currently visible form at any given time.
 
Am I hearing there is no way to do what I want here? I tried joining the Forms but that did not work the direction I wanted it to (putting a check on AirOpsForm feeds Aircraft Form). It only worked in the opposite direction. I can try to make the Aircraft Form a subform of AirOps and then put the Airborne/Fifteen/OnDeck in the Aircraft Form. This is not ideal, but I think it would fix the issue. I was hoping I could tweak your code to make it work. Is that not possible? Thanks again.
 

Users who are viewing this thread

Back
Top Bottom