Return textbox value based on matching fields

[lb10001]

Hi, thank you in advance, new to access, apologies if repeating question- you know I've searched!

Main form has 2 subforms. Subform1: Displays a two boxes, shortext fields that have control sources in a table. they are numbers 02 and 01 respectively in those two boxes.

A second subform shows similar values tied to a different table. There are four this time. Again they are 02 and 01 and another set 03 and 05. These values change based on my selection (from a query).

I want my third subform, to display in a textbox the value 1 if the top two numbers match either of the bottom pairs. Ex: 02 and 01 matches 02 and 01 on subform2 in example above. However if subform to was instead 04 and 04 and 03 and 05....the textbox would return a value of 0. I am calculating and either or match basically between fields.
I have no idea how to do this. Represent them as strings, set fields equal in an If statement. I've tried setting the on load event of subform 3 to be as follows:

If (txtAform1 = txtAform2) And txtBform1 = txtBform2 OR (txtAform1 = txtCform2) And txtBform1 = txtCform2 Then
Me.txtcompare = "1"
Else: Me.txtcompare = "0"
End If
End Sub

txtcompare is the textbox on form 3.

I've tried so many iterations, trying to use syntax on ever copied ever referred mvp page that tells you how to refer to controls on subforms so please do not direct me to that page!!!!!

if you can help great, I'd appreciate it. duh :banghead:

 

[Minty]

I'm not doing this for spite, but the syntax on the http://access.mvps.org/access/forms/frm0031.htm page is correct.

So I suspect your problem is that the subform you are referencing to may not have loaded at the time. To prove a point and to prove you have the syntax correct put a

Debug.Print 	Me!YourSubFormName.Form!YourControlName

in your main forms code and see what it returns as a value.

 

[Uncle Gizmo]

referred mvp page that tells you how to refer to controls on subforms so please do not direct me to that page!!!!!

You mean this one?

http://access.mvps.org/access/forms/frm0031.htm

The fact that you don't want to be directed to this page suggests that you have sought help before.

It might save you the annoyance of us going over the same ground again if you include a link to the previous advice you received.

 

[lb10001]

Thanks Minty I will try.
Gizmo, haha no. Read again. I've never posted this,ever. I've searched and keep seeing that page as advice. It sounds like sounds advice. The fact that I don't want to be directed means it has not helped me.

 

[lb10001]

Main form onload event:

Debug.Print
If Me!subfrm1.Form!txt1 = Me!subfrm2.Form!txt2 Then
Me!subfrm3.Form!txtcompare.Text = "1"
Else
Me!subfrm3.Form!txtcompare.Text = "0"
End If

tried putting .value or .txt after the txtcompare control name.
It returns only zero or nothing depending on how I naively play with it.

bug says "cannot reference property or method for a control unless the control has focus."

eh... thanks again all.

 

[Minty]

Remove the .Text as that forces the actual string value into the control, and to do that the control has to have focus. Change you code to the following;

Debug.Print Me!subfrm1.Form!txt1 & " - " &  Me!subfrm2.Form!txt2
[COLOR="Green"]' You can see the result of this if you press ctrl + G in the code window to open the immediate window[/COLOR]

If Me!subfrm1.Form!txt1 = Me!subfrm2.Form!txt2 Then
       Me!subfrm3.Form!txtcompare = 1
Else
       Me!subfrm3.Form!txtcompare = 0
End If

 

[lb10001]

Minty thanks so much! It's not working but I feel it's close, so grateful for your response.

It's currently displaying zero in subfrm3's textbox although the texts match and should give me a 1.

Does it matter that I have procedures above this in the main form on load event? Is there some special syntax for separating these? I've passed some values from the previous form.

txtcompare in subfrm 3 is unbound. Subfrm3's control source is another table separate from those tied to the other two subforms. Not seeing why this should matter. I'm doing txtcompare for display, not storage.

Also, I am sure to be using the control names and not their sources. I have called them text, because they are all short text types in their tables. I cannot think of any other issues except for this "focus" idea. This main form has a tab control and all three of these subforms are in a single tab. From my reading I hear that shouldn't matter in my functions. If you have time to ponder, please let me know.

thanks!

p.s. I did a little experiment. It works! When I put three forms on a totally new form and go from there. Works wonderfully. Now if I can just get it to form on my more complicated main form. So, placement of code or focus issue?

 

[Minty]

It might be worth uploading a stripped down version of your db, or maybe showing all your forms load code.

This from MS about form events may help https://support.office.com/en-gb/ar...objects-e76fbbfe-6180-4a52-8787-ce86553682f9;
Opening and closing a form

When you open a form, the following sequence of events occurs for the form:

Open >Load > Resize > Activate > Current

If there are no active controls on the form, the GotFocus event occurs for the form after the Activate event but before the Current event.

Working with subforms

When you open a form that contains a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form. However, the Activate event does not occur for subforms. Therefore, opening a main form triggers an Activate event only for the main form.

 

[lb10001]

Minty,
I read. And re-read, and re-read. haha. I am totally naive and lost, but I will say, none of my searches are bearing a good solution to re-work this order. Then I thought, don't mess with nature, why would I want to re-work these order of events?

I added a button that will trigger analysis of all of these comparisons. I guess if I really wanted to, I could hide the button and have it triggered on the on load event of my mainform, but un-necessary. The only thing is, since this button is on subfrm3, I gotta have a longer line of code mainform:sub:control. etc. This could get ugly. But I think I'm gonna go with it. At least it makes sense! Turns out that page wasn't annoying, my newness to access and its complexities is!

Thank you for helping me solve this, my first puzzle! : ) Truly appreciate it.

 

[Minty]

I think you have come to the correct conclusion - you can't alter the order these events happen - it's cast in stone in Access's inner workings, but you can at least react to them in your code in the correct order.