Help with Subform Code.

[DeanRowe]

Hi,

I have a form called "InvoiceForm" - on this form are two subforms called "NewPurchaseTable" and "TheirPurchaseTableQuery".

Below is a screenshot of how "InvoiceForm" looks:

I have blanked out other columns that are irrelevant to this problem to avoid confusion.

What I am trying to do is match the entries in both subforms. If the selected record's [Duplicate ID] from the left subform matches the selected record's [OurOrderID] from the right subform, and the Prices match as well, then I want to copy the [PurchaseID] into the [Invoice ID] field.

Before I attempt to copy the [PurchaseID] into the [Invoice ID] field, I am trying to make sure the selected records are correct, and here lies the problem...

First of all I tried various versions of the following code:

If Forms!InvoiceForm!NewPurchaseTable.Form.[Duplicate ID] = Forms!InvoiceForm!TheirPurchaseTableQuery.Form.[OurOrderID] then
msgbox "Correct"
else
msgbox "Incorrect"
End if

However this code always resulted in an "Incorrect" message. I then created 3 text boxes (at the top of the screenshot) called "Test1", "Test2" and "Result". "Test1" shows the [Duplicate ID] of the selected record, and "test2" shows the [OurOrderID] of the selected record. The screenshot below shows the code used to populate the text boxes, and if you look at the first screenshot you can see it works properly.

I tried the following code, and various variations, to try and compare these two values:

If test1 = test2 then
msgbox "Correct"
else
msgbox "Incorrect"
End if

However this also results in an "incorrect" message every time, could anyone please help me with the code to check these records

I know that my intention to copy and "duplicate" data is breaking normalization rules, however it is necessary to do it in this manner. a simple yes/no check box to say if the invoices are correct will not suffice as I need to be able to see exactly which ones are matched, as occassionally our suppliers mess up and charge us twice on the same invoice number.

Any help would be welcomed. Thank you for your time.

Dean.

 

[boblarson]

Are you making sure to use the subform container name (control on the main form that houses the actual subform) instead of the subform name?

 

[DeanRowe]

Yes I've double checked and I am using the container name - if you look at the first screenshot above, right at the top it shows the test1 and test2 fields are bringing back the right data - I've also tried pasting the data into these fields using the oncurrent event of each subform, but when I check if the values match it always returns "incorrect" - I think something is wrong with the code I am using to check if the two text boxes match or not, but I can't figure out what.

 

[ajetrumpet]

I tried the following code, and various variations, to try and compare these two values:

If test1 = test2 then
msgbox "Correct"
else
msgbox "Incorrect"
End if

However this also results in an "incorrect" message every time, could anyone please help me with the code to check these records

Dean,

the screen shot you posted with the numbers in the two boxes will give you an "incorrect" message. I hope you have tried it with the numbers being the same (as in, navigating to records in both the subforms that will give you equal values). Did you put the above code in with the "Match" button's click event?? The only thing I noticed about the code is the textbox references. The actual code that you posted is comparing two strings that have nothing to do with the form ("test1" and "test2"). To compare the actual boxes, you have to type "Me.Test1" and "Me.Test2", but that's only if your boxes hold those two names.

Anyway, you probably know that, but I thought I would point it out anyway.

 

[DeanRowe]

Hi Ajetrumpet, first of all I selected different records to demonstrate that each text box was showing the relevant record selected in the subform. I navigate to matching records, and both test1 and test2 are showing the same number, but it always appears as incorrect. The above code is in the Match buttons click event.
I've tried renaming the code as you suggested but still no luck, its still the same "incorrect" msgbox that appears - I'll try and paste the form and relevant tables in a moment...

 

[DeanRowe]

Heres the part of the database that I'm struggling with, "InvoiceForm" is the form that contains the problem. I just can't get the msgbox to say correct when the "Duplicate ID" from the left subform is the same as the "OurOrderID" from the right subform.

I'd appreciate any ideas at all, thank you.

Dean

 

[boblarson]

Okay, two things

1. You are comparing two different datatypes so they will never be the same without casting one. If you are comparing text to a number you can use

If Val(Me.Test1) = Val(Me.Test2)

to get that.

2. You had quotes around "Me.Test1" and "Me.Test2" but they don't get quotes. What you were actually doing was comparing two text strings together and the string "Me.Test1" doesn't equal "Me.Test2" and never will. "Me.Test1" will equal "Me.Test1" because that text string is exactly the same.

 

[DeanRowe]

thank you very much bob, I used the "If Val(Me.Test1) = Val(Me.Test2)" and it worked perfectly. The "Quotes" around the code was the last of many variations that I'd tried, I didn't think it would work but I wanted to test everything and forgot to change the code back when I posted it here.

Thank you again for your help Bob.

 

[ajetrumpet]

You had quotes around "Me.Test1" and "Me.Test2" but they don't get quotes. What you were actually doing was comparing two text strings together and the string "Me.Test1" doesn't equal "Me.Test2" and never will. "Me.Test1" will equal "Me.Test1" because that text string is exactly the same.

Boy, does that sound like an iteration, or what!? I think I've read that somewhere before...