How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Richard Your Own Question

Richard
Richard, Computer Specialist
Category: Computer
Satisfied Customers: 16842
Experience:  Over 15 years of consulting to the IT industry
32989067
Type Your Computer Question Here...
Richard is online now
A new question is answered every 9 seconds

I want to create a macro in Access 2010 that will open a form and go to a specific record

Resolved Question:

I want to create a macro in Access 2010 that will open a form and go to a specific record in that form. What i want to do is click on a record in a subform, datasheet view, and display that particular record in a different view (single form view). Or conversely, click on that record in the datasheet view and open a different form that displays that particular record in that form.
Submitted: 2 years ago.
Category: Computer
Expert:  Richard replied 2 years ago.

Personal Help :

Hi there, and welcome to JustAnswer

Personal Help :

Can you please upload the database to www.wikisend.com

Personal Help :

and provide me with the download link please

Customer:

No can do. There are some cofidentiality issues and I don't have the ability to upload it anyway.

Personal Help :

ok, then I will attempt to explain the solution

Personal Help :

You need to base the single form view record source on a query

Personal Help :

in the query you need to add a where clause in the ID field of the records, that points to the ID text box of the subform

Personal Help :

this way when the single form view opens, the query (its record source for displaying data) will run and will be restricted to that current record

Personal Help :

can you please try to do this and let me know if you have any difficulties thanks

Customer:

Thanks. I'll be OTL for about an hour but I'll try it when I get back. I may need some help with the where clause in the query. I'll let you know if I have any difficulties.

Personal Help :

no problem

Personal Help :

in the where clause, use the Build Expression to easily point it to the text box

Customer:

Hi! Back from lunchand tried it out. I guess I need a little more help. I assume that I need to put the "Where" clause in the "Criteria" field of the query, but how do I write it so thit it only selects the current record?

Personal Help :

you point it to the text box on the subform that has the ID in it

Personal Help :

you can do this by using the Build Expression

Personal Help :

are you 100% sure you dont want to upload to www.wikisend.com as I can do this for you

Customer:

How do I do that?

Personal Help :

go to www.wikisend.com

Personal Help :

upload the file

Personal Help :

and provide me the download link please

Personal Help :

thanks,which form are we refering to?

Customer:

Eventually I'm going to want to do this to a bunch of them, but for now I want to do this for the table Pending Cases Subform1

Personal Help :

ok, it is quite large so i need more info please

Personal Help :

what form holds the subform?

Personal Help :

meaning, which form should I initially open?

Personal Help :

as when I open the pending cases subform I only see a single form

Customer:

Sorry. I was on the phone.

Personal Help :

no problem

Personal Help :

can you tell me the steps to bring up the form in the right way please

Customer:

Open the form based on the Offenders table enrtitleed Display Form. You will find a subform there that says Open/Pending Cases. That is actually the subform "Pendiing Cases Subform1". While in the Display Form fmorm, I want to click on a record in the Pending Cases Subform1 subform, and have Access open that subform in single record view and display the record that I clicked on.

Customer:

Does that make sense? (sorry, I keephitting "return" to start a new paragraph....

Personal Help :

thanks, undertood

Personal Help :

what is the unique ID?

Personal Help :

CAPRS?

Personal Help :

as i need to refernece this to open the correct record

Customer:

There are a couple of them. In the table the Primary key is simply the auto number ID field. The other one id the CourtCaseNumber field. I meant to index that one and set it to No Duplicates, but that change didn't make it into the version that I sent you.

Personal Help :

ok, i will use CourtCaseNumber

Customer:

copy

Personal Help :

ok, in the query use this as the criteria in the courtcasenumber

Personal Help :

[Forms]![DisplayForm]![Pending Cases subform1].[Form]![CourtCaseNumber]

Personal Help :

make sure you change one of the two as they were both the same

Personal Help :

test and let me know the results please

Customer:

Thanks, XXXXX XXXXX What do I have two of the same of?

Personal Help :

courtcasenumbers

Personal Help :

you see it on the Display Form

Personal Help :

they need to be unique for this to work

Customer:

Yup. Got it. Trying it now.

Personal Help :

thanks

Customer:

No Joy. Wh\en i open the Display Form form, the Pending Cases subform contains now shows no records. When I click on the CourtCase Number XXXXX it retuns a blank subform. If I try to open the subform directly I get the dialog box asking me to specify the selection parameter (Enter Parameter Value) for the where clause.

Personal Help :

ok, let me upload my version as it is working

Personal Help :

here it is

Personal Help :

http://wikisend.com/download/249006/Courtwatch2012.accdb

Customer:

Hmmm... The file you downloaded does not appear to have your changes in it. Can you try again.

Personal Help :

what happens when you click on the subform record? as it is the right file

Customer:

Nothing. The macro to open the form in siingle form view does not run. I looked at the query and I did not see your changes there either. Neither was the CourtCaseNumber field in the Pending Cases table indexed and set to no duplictaes.

Personal Help :

ok, can you try to download it again

Personal Help :

and I will also check on my side again

Personal Help :

ok, somethingis wierd as you are right

Personal Help :

let me upload again please

Customer:

Will you send me a new link when you've uploaded it again?

Personal Help :

yep, 1 min

Personal Help :

ok, i see the problem

Personal Help :

I only have Access 2007 here and this is why it stops working

Personal Help :

but if you paste the critieria I gave it should work

Personal Help :

what happened again when you did?

Personal Help :

[Forms]![DisplayForm]![Pending Cases subform1].[Form]![CourtCaseNumber]

Personal Help :

in the query of the subform under court case number

Personal Help :

Hi there, did you get my last message?

Customer:

Hi. Yeah, I've been playing with it. Istill; get the same problem though. When I have the "Where" expression (



[Forms]![DisplayForm]![Pending Cases subform1].[Form]![CourtCaseNumber]) in the Critera field for the Pendong Cases selection Query, under CourtCaseNumber, the query returns no records.


Personal Help :

that is ok, just save the query

Personal Help :

close the forms

Personal Help :

open displayform and test

Customer:

I did. When I open DisplayForm the Pending Cases Subform is blank. (No Records)

Personal Help :

ok, i see the problem

Personal Help :

give me 1 min

Personal Help :

as we a referencing a where clause, it will open blank as it now has that where clause in it

Personal Help :

to solve this

Personal Help :

you need to have a new form exactly like the pending cases subform

Personal Help :

this new form has the where clause, not the subform

Personal Help :

and the new form is what needs to open when you click on it

Personal Help :

does that make sense?

Customer:

Sort of. But the Where clause isn't in the subform, it's in the Query.

Personal Help :

yes, but the subform runs off this query

Personal Help :

i will do this for you and upload it

Customer:

I think I got it. Let me try.

Personal Help :

ok, ill wait

Customer:

Still no luck. In the DisplayForm form I have a subform that is based on the subform Pending Cases Subform1. That is the subform from which I want to selct my record. That subform is based on the Table Pending Cases. I have another subform, Pending Cases Subform Select. This is the subform that I ultimately want to open and display the selected record. This subform is based on the query Pending Cases Selection Query. That is the query that contains the Where clause. Unfortunately, now when I click on the record in the subform Pending Cases Subform1, the Open Form Macro doesn't run. I don't get an error message or anything like that, just nothing happens.

Customer:

Unfortunately, I have to leave. Is it possible to resume this session on Monday?

Personal Help :

5 min?

Personal Help :

going to do this an upload

Customer:

OK

Personal Help :

sorry but i cant work out how you choose the form to open onclick

Personal Help :

i changed it to open a copy i made but it opens the original?

Personal Help :

but we can resume Monday if you wish

Customer:

In Acess 2010 there's a pretty slick Macro interface that makes it pretty easy. I just select the Open Form function and it provides me with a list of forms that i can open. I just select the form from the list and save the macro. I do have to go now. How do we resume the session?

Personal Help :

you can just return to this page

Personal Help :

post a message and i will be notified

Personal Help :

enjoy your easter!

Customer:

Copy. You too!

Personal Help :

thanks!

Customer:

Hello Personal Help! JACUSTOMER-rf70jy0p here.

Customer:

May we resume our session from last Friday?

Personal Help :

Hi there, yes we can

Personal Help :

did you want to make a copy of the form and open a copy of it based on the new query?

Customer:

Sorry about the dealy there. Yes, I think so.

Personal Help :

ok, and did you test it? did it work for you?

Customer:

Let's go back a minute.

Customer:

Still not working. The subfiorm that I want to open does not open to the correct record. Let me upload what I have and we'll discuss it.

Customer:

Open the file I sent you and open the form called Display Form. The first record should open to a gentleman named Cill Boswell. I want to click on the court case number XXXXX in the Pending Cases Subform and have it display that particular record on a form called Pending Cases Subform Select. Now, there's omnly one pendiong case for Mr. Boswell, so when you click on the case nuimber (27-CR-12-09620) the proper form opens to that record. However, if you go to the next person in the file, Mr. Braswell, and click on the ccourt case number XXXXX his trial in the Pendiong cases subform, instead of opening the Pending Cases Subform Select subform to that record, it opens it to the previous record for Mr. Boswell. Convwersely, if you close out of the file altogether and re-open it, and then go to Mr. Breaswell's record first, click on the case number XXXXX Braswell, it will open to the correct court caase. But if you subsequently go back to Mr. Boswell and try to open his case, it returns you to Mr. Braswell's. So, what it's doing is openig to the most recently viewed record in that session.

Customer:

Make sense?

Personal Help :

can you upload it then I can look at it and follow your comments

Customer:

I did about 5 minutes ago.

Personal Help :

ok, I need the download link please

Personal Help :

from wikisend as I didnt see you post it

Personal Help :

ok, spent all this time testing and it is working correctly for me\

Personal Help :

no matter how I navigate to which record it opens always the correct one

Personal Help :

do you close the subform before opening a new one from a different record?

Customer:

Uh, no, maybe not.

Customer:

Let me check....

Customer:

OK My bad. It seems to work if I close the subform first.

Customer:

I think we got it. :-)

Personal Help :

great!

Personal Help :

was really confused as I was extensively testing but it was working

Personal Help :

If you need any additional information, please do not hesitate to let me know

Personal Help :

and please remember to click Accept if you are satisfied with the service I provided

Customer:

Thank you sir! I appreciate your help on this. Payment is on it's way.Thanks again!

Personal Help :

I will also delete all copies of the database on my computer

Personal Help :

thanks!

Richard, Computer Specialist
Category: Computer
Satisfied Customers: 16842
Experience: Over 15 years of consulting to the IT industry
Richard and 8 other Computer Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you! RP Austin, TX
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
 
 
 

Meet The Experts:

 
 
 
  • Andy

    Computer Consultant

    Satisfied Customers:

    5311
    11yr exp, Comp Engg, Internet expert, Web developer, SEO
< Last | Next >
  • http://ww2.justanswer.com/uploads/EN/Engineer1010/2012-6-9_132423_jaj12a.64x64.jpg Andy's Avatar

    Andy

    Computer Consultant

    Satisfied Customers:

    5311
    11yr exp, Comp Engg, Internet expert, Web developer, SEO
  • http://ww2.justanswer.com/uploads/BA/barrenrock/2011-10-19_215925_JamesJAFinal.64x64.jpg James's Avatar

    James

    Sr. Computer Support Expert

    Satisfied Customers:

    8376
    20 years of experience building, fixing and servicing PCs and operating systems.
  • http://ww2.justanswer.com/uploads/zeyank/2009-09-26_154244_P8110079.png Ryan H.'s Avatar

    Ryan H.

    Computer Support Specialist

    Satisfied Customers:

    1741
    A+ Certified Technician - 10 Years experience working with all types of computer systems.
  • http://ww2.justanswer.com/uploads/JA/jadedangel57/2011-11-8_193134_janenewsm.64x64.jpg Jane Lefler's Avatar

    Jane Lefler

    Sr Prog Analyst / Technician

    Satisfied Customers:

    0
    Computer Programmer / Technician/ Consultant 16+ years
  • http://ww2.justanswer.com/uploads/RO/robmpreston/2013-9-23_233814_mijiFZm.64x64.jpg RPI Solutions's Avatar

    RPI Solutions

    Support Specialist

    Satisfied Customers:

    3476
    5+ Years in IT, BS in Computer Science
  • http://ww2.justanswer.com/uploads/BA/barunrath/2012-7-5_201954_Profilepic2.64x64.jpg B. Rath's Avatar

    B. Rath

    Computer Support Specialist

    Satisfied Customers:

    8671
    Certified Computer/Networking Support Specialist.
  • http://ww2.justanswer.com/uploads/FS/fszcze/2012-6-18_181848_500test.64x64.jpg Frederick S.'s Avatar

    Frederick S.

    Computer Specialist

    Satisfied Customers:

    7240
    Computer technician and founder of a home PC repair company.