Wrong record selected from macro of an open form button

[gwboolean]

This has been a recurring problem I have had. I have a parent form showing some of the contents of a single record in a parent table. This form contains a button that opens a specific subform (using an IF statement based on the contents of another field in the parent table/form. There are actually any of 8 child forms/tables that could be selected and the related record is in only one of them. The relation between the tables is based on the PK of the parent table and the related FK in the child table. and the data is correct in all tables.

My problem is that when I run this button on the parent form/table it returns the appropriate form/table, but displays the first record rather than the related record. In the past (using this same process) I have been successful in getting to the right record by fiddling with the where statement (sometimes putting in a equal sign or taking it out fixes the problem). However, I have no idea what is taking place and this time no matter what I do to the Where statement nothing works. I have not yet placed the final else condition for where the form selection field (VMValIdentType] is empty yet as there is no point at this time.

Here are the button macro contents:

If [VMValIdentType] = "Software"

OpenForm
Form Name: tmpVMValSW
View: Form
Filter Name:
Where Condition =: "[MBIDPK]="&"'"& [MBIDFK] &"'"
Data Mode:
Window Mode: Normal

Else IF [VMValIdentType] = "Another Variable" There are 8 of these

EndIF

I would just be happy if I had an idea of what I was doing when I am doing this so that in the future I might be able to keep this problem from occurring. I do understand what is supposed to happen, but obviously just enough to be dangerous.

[pbaldy]

I would use code, the syntax of which is:

BaldyWeb wherecondition

In a macro, I think that you have to use the full form reference:

Where Condition =: "[MBIDPK]='" & Forms!FormName.[MBIDFK] & "'"

[gwboolean]

I gave that a try by changing [MBIDFK] to Forms![tmpVMValSW].[MBIDFK]. Same result. The contents of the first record are displayed on the correct form/table. Any other ideas? And I do know John Galt.

[pbaldy]

One of the many reasons I don't use macros is they are harder to debug (I haven't used 2010 macros enough to know if that version is better in that regard). You'd want to confirm that the control with that name contains the value expect. If it's a combo, it may not. Can you post the db here?

[gwboolean]

I have no idea how to post a DB, I can barely navigate through all this. My experience with DBs pretty well stopped in 1994 with Foxbase. I have recently been working to be able to work with OBJ oreiented and am still just figuring out what I am doing.

Having said that, I have setup both forms so that they show the fields that I am using to show what is actually there. Additionally, I have the tables open and can see that the fields of both tables contain the correct data. There are currently two records in the child table and each has the correct number in the field. MBIFK for one record is 26 and the MBIFK for the desired record is 27. When I look at the filter in the property values (after I take the parent form and put it in the design view, the filter in the property shows the appropriate number (27). It occurs to me though that this field is a number field and I seem to be treating everything as if it is a text value. Is there any chance that, that might be the source of my problem? Oh, and the button is not a combo, it is just a button that I have set the imbedded macro event to operate from an IF statement that defines which child/form table should be used.

[pbaldy]

If the data type of MBIDPK is numeric, try

Where Condition =: "[MBIDPK]=" & Forms!FormName.[MBIDFK]

To post a db here, you would perform a compact/repair, zip it and then attach in the Advanced Reply area.

[gwboolean]

That looks like it would require a new thread. Is that OK?

[pbaldy]

It's okay, but it shouldn't be required. Just click on "Go Advanced" under the quick reply area. Then you should see a "Manage Attachments" button.