Macro to open Form to a specific record (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
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

I’m here to help
Staff member
Local time
Today, 11:51
Joined
Oct 29, 2018
Messages
21,358
Hi. Try:
Code:
="[Acct_Key]='" & [Acct_Key] & "' AND [Sec_Key]='" & [Sec_Key] & "' AND [Hold_Date]=#" & [Hold_Date] & "#"
I just added the delimiters.
 

Tskutnik

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
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

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
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

I’m here to help
Staff member
Local time
Today, 11:51
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
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

I’m here to help
Staff member
Local time
Today, 11:51
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
Yes, from the button on the row in the datasheet. Convert to SQL maybe?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:51
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
Child for opens, but displays all.
I should have said Continuous Form (looks like a Table), not a Datasheet. Button on right
Like This:
1605647921844.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:51
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
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

I’m here to help
Staff member
Local time
Today, 11:51
Joined
Oct 29, 2018
Messages
21,358
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.
 

Attachments

  • msgbox.png
    msgbox.png
    13.2 KB · Views: 114

Tskutnik

Registered User.
Local time
Today, 14:51
Joined
Sep 15, 2012
Messages
229
Ah - I out thought myself. I tried all 3 and they give the correct info for the given row I click
 

Users who are viewing this thread

Top Bottom