Macro to open Form to a specific record

[Tskutnik]

All,
Syntax problem somewhere. Any help is appreciated.
In a button on the "Parent" form Macro I have the following, meant to open the "Child" Form to same record selected.
I have this working with using one criteria t find the Child record, but combining the 3 fields is giving me trouble.
The Child form opens but not to any specific records.

Something funky in the following:
="[Acct_Key]=" & [Acct_Key] & " AND [Sec_Key]=" & [Sec_Key] & " AND [Hold_Date]='" & [Hold_Date] & "'"

FYI - Acct_Key and Sec_Key are text fields. Hold_Date is a Date field

As always, Thanks

 

[theDBguy]

Hi. Try:

="[Acct_Key]='" & [Acct_Key] & "' AND [Sec_Key]='" & [Sec_Key] & "' AND [Hold_Date]=#" & [Hold_Date] & "#"

I just added the delimiters.

 

[Tskutnik]

Thanks DB - as always. Something is still not working though. It is still not going to the correct Child record. I'm checking some obvious things....

 

[Tskutnik]

There is something in the Parent. That form is a Datasheet and the buttons to call the Child are on each row. Seems it is not recognizing the specific row in the Parent to pick from in the Child...
Or maybe not. The 3 criteria being used should get me to the specific record in the Child

 

[theDBguy]

There is something in the Parent. That form is a Datasheet and the buttons to call the Child are on each row. Seems it is not recognizing the specific row in the Parent to pick from in the Child.

Datasheet? Are you using a Click event on one of the fields?

 

[Tskutnik]

Datasheet? Are you using a Click event on one of the fields?

Not on a unique Key or ID, no. My thought was the 3 criteria makes the record unique so if I match all 3 I'd be good.

 

[theDBguy]

Not on a unique Key or ID, no. My thought was the 3 criteria makes the record unique so if I match all 3 I'd be good.

Hi. I just realized that. So, I updated my question to, essentially: "Are you using a Click event?"

 

[Tskutnik]

Yes, from the button on the row in the datasheet. Convert to SQL maybe?

 

[theDBguy]

Yes, from the button on the row in the datasheet. Convert to SQL maybe?

Wait, you have a button on a Datasheet form. I thought that shouldn't be possible. Are you sure?

In any case, if the code/macro is firing, then we can do some troubleshooting steps. So, are you saying the other form opens? If so, what does it display? None or all the records?

 

[Tskutnik]

Child for opens, but displays all.
I should have said Continuous Form (looks like a Table), not a Datasheet. Button on right
Like This:

 

[theDBguy]

Child for opens, but displays all.
I should have said Continuous Form (looks like a Table), not a Datasheet. Button on right
Like This: View attachment 86740

Okay, replace the macro with simply one of these (one at a time) just to see what data the button is getting.

MsgBox [Acct_Key]

MsgBox [Sec_Key]

MsgBox [Hold_Date]

PS. But before you do that, can you please post an image of the original macro? Maybe there's something there we can spot.

 

[Tskutnik]

 

[Tskutnik]

Sorry - not 100% what you meant in the MSGbox. But unless you can get anything out of the macro image, I'm going to play around with something. I need to improve my unique keys, which may solve this problem. Hold tight.

 

[theDBguy]

Sorry - not 100% what you meant in the MSGbox. But unless you can get anything out of the macro image, I'm going to play around with something. I need to improve my unique keys, which may solve this problem. Hold tight.

You know, something like this.

 

[Tskutnik]

Ah - I out thought myself. I tried all 3 and they give the correct info for the given row I click

 

[theDBguy]

View attachment 86741

Okay, I don't use macros much, and this is what worked for me, when I gave it a quick try.