UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Record Select From Subform, Any Version    
 
   
stephenAA5
post Aug 10 2018, 01:18 PM
Post#1



Posts: 250
Joined: 8-October 09



Hi, all. I have a form (clientdetailform) with a subform (clientdetail joblist subform) On this subform is a control bound to a table for jobnumber. When jobnumber is doubleclicked, I am trying to open another form (jobdetailform), specifically to the record for that specific jobnumber. I do this a lot on forms, but am having problems with it from the subform. Here is my broken code:

DoCmd.OpenForm "jobdetailform", acNormal, "", "[jobnumber]=[Forms]!clientdetailform![clientdetail joblist subform].[jobnumber]", , acNormal

When doubleclicked, it asks for the value for the parameter.

Any guidance is appreciated.

Thanks!

Stephen.
Go to the top of the page
 
Doug Steele
post Aug 10 2018, 01:28 PM
Post#2


UtterAccess VIP
Posts: 21,854
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Put the parameter outside of the quotes.

If jobnumber is numeric,

CODE
  DoCmd.OpenForm "jobdetailform", acNormal, "", "[jobnumber]=" & [Forms]!clientdetailform![clientdetail joblist subform].[jobnumber], , acNormal

If it's text,

CODE
  DoCmd.OpenForm "jobdetailform", acNormal, "", "[jobnumber]='" & [Forms]!clientdetailform![clientdetail joblist subform].[jobnumber] & "'", , acNormal

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
theDBguy
post Aug 10 2018, 01:28 PM
Post#3


Access Wiki and Forums Moderator
Posts: 72,962
Joined: 19-June 07
From: SunnySandyEggo


Hi Stephen,

If you're using the DblClk event, why not just use a Me reference rather than an absolute one with Forms? For example:

DoCmd.OpenForm "FormName", , , "[ID]=" & Me.ID

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Doug Steele
post Aug 10 2018, 02:26 PM
Post#4


UtterAccess VIP
Posts: 21,854
Joined: 8-January 07
From: St. Catharines, ON (Canada)


theDBGuy's comment reminded me that the solutions I posted were incorrect. See Refer to Form and Subform properties and controls for an explanation.

If jobnumber is numeric,

CODE
  DoCmd.OpenForm "jobdetailform", acNormal, "", "[jobnumber]=" & [Forms]!clientdetailform![clientdetail joblist subform].Form![jobnumber], , acNormal

If it's text,

CODE
  DoCmd.OpenForm "jobdetailform", acNormal, "", "[jobnumber]='" & [Forms]!clientdetailform![clientdetail joblist subform].Form![jobnumber] & "'", , acNormal

That assumes that the name of the subform control on clientdetailform is [clientdetail joblist subform]. If it's something different, use that name instead. It doesn't matter what the name of the form being used as a subform is: it strictly matters what the subform control name is.


--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
stephenAA5
post Aug 11 2018, 01:50 PM
Post#5



Posts: 250
Joined: 8-October 09



Hi, guys. I did get an error on Doug's solution, and dBGuy's worked, so I ran with it. Just got it done this morning.

Thank you both. I really appreciate it.

Stephen.
Go to the top of the page
 
theDBguy
post Aug 11 2018, 01:57 PM
Post#6


Access Wiki and Forums Moderator
Posts: 72,962
Joined: 19-June 07
From: SunnySandyEggo


Hi Stephen,

You're welcome. Doug and I were happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Doug Steele
post Aug 11 2018, 02:54 PM
Post#7


UtterAccess VIP
Posts: 21,854
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Glad you got it working but I'd be curious to know what error you got. Is it possible you weren't referring to the correct name of the subform control?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
stephenAA5
post Aug 11 2018, 11:58 PM
Post#8



Posts: 250
Joined: 8-October 09



Hi, Doug. The error was on the first version you posted, which you later said needed modification. I cannot recall the specific error. At that time, I had taken both of your suggestions offline, and just moved on to dBGuy's, which worked.

It wasn't your second post that failed, it was the first.

Stephen.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th August 2018 - 02:42 PM