Passing Numeric Value One Form to Another (1 Viewer)

Zydeceltico · May 24, 2019

Hi all -

I have two tables and three forms.

Forms 1 and 3 are bound to to Table 1. Form 2 is bound to Table 2 and related to Table 1 via ID on Table 1 and ID_FK on Table 2.

Right now I open Form 1; enter some data; click a button to open Form 2; enter some more data; click a save button on Form 2 and both Form 1 and Form close - saving to their respective tables.

I have a condition where I would open Form 1 (do stuff) then open Form 2 - and decide that I need to stop entering data on Form 2 and open Form 3 from Form 2 while Form 1 is still open (with it's autonumber ID still Dirty as Form 2 is modal) and use Form 3 to enter more data that I should have originally entered on Form 1 and which would be recorded on the same row as the record currently represented on Form 1 - - it is kind of an "append" I guess.

That is the ONE thing I have not yet tried to get this to work.

Anyone have any better solutions or ideas?

(I'm being cryptic trying to avoid writing a novel)

Thanks,

Tim

theDBguy · May 24, 2019

Hi Tim. What is the difference between form1 and form3? Why do you have the same controls/fields on two forms? Maybe you could try opening form1 again instead of using form3. Just a thought...

Zydeceltico · May 24, 2019

theDBguy said:
Hi Tim. What is the difference between form1 and form3? Why do you have the same controls/fields on two forms? Maybe you could try opening form1 again instead of using form3. Just a thought...

Good question.

Form 1 and Form 3 are not identical. I have two fields in tblInspectionEvent that are not on Form 1: InspectionOverrideBy (takes an admin or supervisors name) and InspectionOverrideReason.

Form 1 does not have these two fields on it. These are the only two fields bound to controls on Form 3.

I may have to do what you suggest and simply close Form 1 and Form 2 and reopen Form 1. I'd also have to put the controls for those two fields on Form 1.

theDBguy · May 24, 2019

Zydeceltico said:
Good question.

Form 1 and Form 3 are not identical. I have two fields in tblInspectionEvent that are not on Form 1: InspectionOverrideBy (takes an admin or supervisors name) and InspectionOverrideReason.

Form 1 does not have these two fields on it. These are the only two fields bound to controls on Form 3.

I may have to do what you suggest and simply close Form 1 and Form 2 and reopen Form 1. I'd also have to put the controls for those two fields on Form 1.

Hi. You can of course design them as you like. Some people use one form and simply hide or show textboxes bound to fields as needed for the purpose of the form at the moment. If you keep using form3, then I suggest making it unbound and simply modify form1 as the user enters data on form3. What exactly was the problem you're getting? Are you getting a "write conflict" error?

Zydeceltico · May 24, 2019

theDBguy said:
Hi. You can of course design them as you like. Some people use one form and simply hide or show textboxes bound to fields as needed for the purpose of the form at the moment. If you keep using form3, then I suggest making it unbound and simply modify form1 as the user enters data on form3. What exactly was the problem you're getting? Are you getting a "write conflict" error?

I was getting a datatype mismatch - but that was a awhile back. I've made changes since then. Here's a little bit more info:

Zydeceltico said:
I may have to do what you suggest and simply close Form 1 and Form 2 and reopen Form 1. I'd also have to put the controls for those two fields on Form 1.

The bugger is that the control for InspectionOverrideBy is a cbo and I have validation code on Form 1 that does not allow moving to Form 2 unless all cbos have data entered in them.

Is there a way to make an exception to that code for a single cbo?

Here's that code on the button to open frmInspectMill which calls the validation routine (fChkCombo):

Code:

Private Sub cmdOpenInspectFab_Click()

    Me.txtInspectionType.Value = 4
    
    If fChkCombo = False Then Exit Sub

    Me.Dirty = False

    DoCmd.OpenForm "frmInspectFab", , , , , acDialog, Me.txtInspectionEvent_PK

End Sub

And here's the fChkCombo code:

Code:

strSubName = "fChkCombo"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"

On Error GoTo Error_Handler

Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
            Case acComboBox ', acLabel, acListBox, acOptionButton, acOptionGroup, acTextBox, acToggleButton, acCheckBox
            'If Ctrl.Tag = "X" Then
                If fCboNullBlankZero(Ctrl) Then
                    MsgBox " YOU need to enter data in >>> " & Ctrl.Name
                    Ctrl.SetFocus
                    Ctrl.Dropdown
                    Exit Function
                End If
            'End If
        End Select
    Next Ctrl

    fChkCombo = True

theDBguy · May 24, 2019

Hi. Not sure if an exception is the way to go but sure you can add one. For example, (pseudocode)

Code:

If Tag="something" Then
    If exception_condition = True Then
        'ignore
    Else
        'do it
    End If
End If

Zydeceltico · May 24, 2019

theDBguy said:
If you keep using form3, then I suggest making it unbound and simply modify form1 as the user enters data on form3.

I could do that. What mechanism would "send" the values collected on Form 3 to the record currently dirty on Form 1 and Table 1? Would I use an append query on a "Save" button?

Zydeceltico · May 24, 2019

theDBguy said:
Hi. Not sure if an exception is the way to go but sure you can add one. For example, (pseudocode)

Code:

If Tag="something" Then If exception_condition = True Then 'ignore Else 'do it End If End If

Thanks for that. Yeah - I'd like NOT use an exception. Is there a different way to accomplish the same thing? I've tried Visible = False and Enabled = False but that function still finds the cbo and won't let me open Form 2.

I just posted a response/question replying to you about the unbound form idea.

theDBguy · May 24, 2019

Zydeceltico said:
I could do that. What mechanism would "send" the values collected on Form 3 to the record currently dirty on Form 1 and Table 1? Would I use an append query on a "Save" button?

Neither. You could use something like:

Code:

Forms!Form1.ControlName = Me.SameControlName

Zydeceltico · May 24, 2019

theDBguy said:
Neither. You could use something like:

Code:

Forms!Form1.ControlName = Me.SameControlName

I can do that. I also replied to your other reply -

with a question about having all controls on Form 1 and that doggone function always seeming to find the cbo even when I have it not enabled.

theDBguy · May 24, 2019

Zydeceltico said:
Thanks for that. Yeah - I'd like NOT use an exception. Is there a different way to accomplish the same thing? I've tried Visible = False and Enabled = False but that function still finds the cbo and won't let me open Form 2.

I just posted a response/question replying to you about the unbound form idea.

Code can find a control on the form whether it is visible or not. If you want to evaluate whether you need to do something about it, you'll have to use the value/data. I am just giving you some general ideas here, since I don't remember exactly how your form is set up and what all the validation code is doing.

theDBguy · May 24, 2019

Zydeceltico said:
I can do that. I also replied to your other reply - with a question about having all controls on Form 1 and that doggone function always seeming to find the cbo even when I have it not enabled.

Right. Like I said, the control exists on the form, so the code will definitely find it even if it's hidden or disabled. The point is to use data to evaluate whether you need to do something or not - rather than the existence of a control. Tag, you're it...

Zydeceltico · May 24, 2019

theDBguy said:
Right. Like I said, the control exists on the form, so the code will definitely find it even if it's hidden or disabled. The point is to use data to evaluate whether you need to do something or not - rather than the existence of a control. Tag, you're it...

LOL - I know - right?

theDBguy · May 24, 2019

Zydeceltico said:
LOL - I know - right?

Hi. Without realizing it at the time, it turned out to be a play in words since you were using the Tag property.

The_Doc_Man · May 24, 2019

OK, let's backtrack JUST A SMIDGE.

You said earlier that the error was basically, "Need a related value in table X" - which is an example of RELATIONAL INTEGRITY in action. If you have a parent table and a child table and try to put data in the child table first, you cannot if there is a 1/many or 1/1 relation between the tables. And there IS no workaround because there, you are dinking around with basic SQL Set Theory - the underlying theory that governs query behavior. (And of course, bound forms have an actual or implied query as their recordsource.)

Even if you unbound everything, you would not be able to STORE anything using manual recordset operations if you tried to store them in the wrong order as long as that relationship is in force.

And before we get down THAT rabbit-hole, no. You don't ever want to turn off a valid relationship linkage. The correct solution is to head off the attempt to store data from the child before the parent is ready. So you put "smarts" on the form to prevent making this error in the first place. Disallow actions on the dependent form until the independent form is completed AND STORED. This is a design issue first and foremost, so correct the design to correct the issue. And remember this rule that is true in life even as it is true in database theory: There can be no children without first having parents somewhere behind the scenes.

Zydeceltico · May 24, 2019

theDBguy said:
Hi. Without realizing it at the time, it turned out to be a play in words since you were using the Tag property.

I worked it out differently. I added the feature I need to my form frmLineStop because - even though it is actually the opposite of a line stop that I want to record, I already have an override function built in on that form(and table). It nests in well. Slightly different workflow but a whole lot less arcane than what I have been attempting to do.

theDBguy · May 24, 2019

Zydeceltico said:
I worked it out differently. I added the feature I need to my form frmLineStop because - even though it is actually the opposite of a line stop that I want to record, I already have an override function built in on that form(and table). It nests in well. Slightly different workflow but a whole lot less arcane than what I have been attempting to do.

Nice work! Cheers!

The_Doc_Man · May 24, 2019

If the slightly different workflow doesn't terribly violate the concept being portrayed in the model, then you have done nothing terribly wrong by tweaking the form's workflow.

gemma-the-husky · May 25, 2019

There are a few ways for processes to share values. The easiest way is to have a "public" variable that stores the data you want to share. However, this limits you to just one active shared process.

Another way is to put the "public variable" inside the wrapper of a class module. Now you can have multiple shared processes, each with their own shared variable, representing by another instance of the class.

Another way is to pass the variable into the second process by means of a function or sub call including the variable. Here you need to distinguish whether the variable is passed by reference (default) or by value. You can do this same sort of thing between forms with the openargs (obviously always by value).

The "public variable" is probably the easiest "quick and dirty"method - but it puts the onus on the programmer to track all these public variables and make sure they are not reused by a different process, and are reset appropriately. The tighter methods are therefore safer, although slightly more complex.

note also the fun of "recursion" where a process calls itself, by setting up (potentially infinite) loops of code, in a way that allows complex processes to be programmed vey elegantly, but often in a "resource hungry" way. Some processes can be managed with iteration in a way that would be virtually impossible without.

eg - evaluate a Fibonacci number (tested)

Code:

Function fibonacci(x As Long)
    If x <= 2 Then
       fibonacci = 1
    Else
       fibonacci = fibonacci(x - 1) + fibonacci(x - 2)
    End If
End Function

Function main()
MsgBox fibonacci(8)
End Function

more practically, recursion is an easy way of iterating a "tree" structure, like an xml file (or a family tree). the tree consists either of a "node" with no children, or a "node" with children. Each child then has a similar structure. The downward iteration stops at a node without children. The following code traverses the whole tree structure in a few lines of code. It's extremely difficult to do this at all without recursion.

(aircode)

Code:

function traversetree(node)

report node value 'if necessary  
if node has children then 
   for each child node
         traversetree(childnode)
   next
end if
end function


function main
traversetree(startnode)
end function

Uncle Gizmo · May 25, 2019

gemma-the-husky said:
Another way is to put the "public variable" inside the wrapper of a class module. Now you can have multiple shared processes, each with their own shared variable, representing by another instance of the class.

Hi Dave,
I'm not familiar with this method and I'd be grateful for an example of this method if you have one.

Cheers Tony ....

Sent from my Pixel 3a using Tapatalk

Passing Numeric Value One Form to Another (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Registered User.

I’m here to help

Registered User.

I’m here to help

I’m here to help

Registered User.

I’m here to help

Immoderate Moderator

Registered User.

I’m here to help

Immoderate Moderator

Super Moderator

Nifty Access Guy

Similar threads

Users who are viewing this thread