Computer

For Online Computer Support, Ask a Computer Technician

Ask a Computer Expert,
Get an Answer ASAP!

Computer
This answer was rated:

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...
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.
Show More
Show Less
Ask Your Own Computer Question
Answered in 43 minutes by:
4/6/2012
Richard
Richard, Computer Specialist
Category: Computer
Satisfied Customers: 39,535
Experience: Over 20 years of consulting to the IT industry
Verified

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
Richard, Computer Specialist
Category: Computer
Satisfied Customers: 39,535
Experience: Over 20 years of consulting to the IT industry
Verified
Richard and 87 other Computer Specialists are ready to help you
Ask your own question now
Ask Richard Your Own Question
Richard
Richard
Richard, Computer Specialist
Category: Computer
Satisfied Customers: 39,535
39,535 Satisfied Customers
Experience: Over 20 years of consulting to the IT industry

Richard is online now

A new question is answered every 9 seconds

How JustAnswer works:

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

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 CustomerNew York

I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you!

RPAustin, 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!

FrankCanada

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!!!!

AlexLos 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.

GPHesperia, CA

< Previous | Next >

Meet the Experts:

Andy

Andy

Computer Consultant

5,316 satisfied customers

11yr exp, Comp Engg, Internet expert, Web developer, SEO

Engineer John C.

Engineer John C.

Computer Science Engineer

3,227 satisfied customers

Computer Science Engineer with 10 years of experience in Computer Support, and Microsoft, A+ and Cisco certified

Ryan H.

Ryan H.

Computer Support Specialist

1,741 satisfied customers

A+ Certified Technician - 10 Years experience working with all types of computer systems.

Jane Lefler

Jane Lefler

Sr Prog Analyst / Technician

2 satisfied customers

Computer Programmer / Technician/ Consultant 16+ years

Frederick S.

Frederick S.

Computer Specialist

7,241 satisfied customers

Computer technician and founder of a home PC repair company.

lifesaver

lifesaver

Computer Software Engineer

4,288 satisfied customers

Engineering degree in Computer science,Microsoft Certified Professional.JA computer expert.

Sudipto

Sudipto

Hardware Engineer

4,115 satisfied customers

Certificate Course In Computer Maintenance

< Previous | Next >

Related Computer Questions
Seeking PC expert for Win7 OS re-installation, ASAP I'm a
Seeking PC expert for Win7 OS re-installation, ASAP Hi, I'm a low tech senior citizen who needs assistance in reinstalling Win7 Home Premium OS on a five year old DELL XPS8500 PC using image media sen… read more
Damian Costa
Damian Costa
Owner
Computer Network System Engineer
40 satisfied customers
How do I change my password when I can't remember the old
How do I change my password ***** I can't remember the old one? … read more
Mr.Med
Mr.Med
902 satisfied customers
I have a newer laptop. Many problems. Current problem, it
I have a newer laptop. Many problems. Current problem, it will not receive emails just out of the blue. More current problem is that suddenly it won't print. PLEASE HELP … read more
Moses C
Moses C
Bsc
215 satisfied customers
My google chrome won't open. Windows 10. Had the computer
Had the computer for 6-12 months. Been fine. I turned PC off and back on. I uninstalled and reinstalled. I reran Norton security ot check for viruses. … read more
Moses C
Moses C
Bsc
215 satisfied customers
I have somehow deleted all my passwords in the computer. How
I have somehow deleted all my passwords in the computer. How do I get my email one back … read more
Cabletech
Cabletech
High School or GED
139 satisfied customers
I just have iPad & smart phone. I am disabled and
I just have iPad & smart phone. I am disabled and technically challenged. My question is if someone has your IP address and old your information email name address phone number but not my Social Secur… read more
Kris R
Kris R
IT Manager
Computing
2,296 satisfied customers
I need to know how to open my printer in order to install
I need to know how to open my printer in order to install black ink which I have. Printer HP OfficeJetPro 6979 and I am sick and tired of asking the same question. Please just tell me how to open the … read more
Aparna Indresh
Aparna Indresh
Engineer
Electronics & communication engineering
17 satisfied customers
Windows 10 version 1709 is downloaded, but fails to install
windows 10 version 1709 is downloaded, but fails to install when I restart my computer … read more
Cody
Cody
Computer Science
1,464 satisfied customers
I have a Western Digital external hard drive that is
Hi - I have a Western Digital external hard drive that is refusing me permissions to access my files. The WD customer support seems to suggest that Microsoft customer support can help. Can you? … read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
952 satisfied customers
I forgot my yahoo password. Several months ago
I forgot my yahoo password JA: When did you last reset your username and password? Customer: Several months ago JA: Have you installed any updates recently? Customer: Yes JA: Anything else you want th… read more
Aparna Indresh
Aparna Indresh
Engineer
Electronics & communication engineering
17 satisfied customers
Of a sudden my ipad pro locked up, so I shut it down, then
Hi all of a sudden my ipad pro locked up, so I shut it down, then restarted it, now its asking me for a passcode, i dont recall ever putting one in … read more
AnswersByEric
AnswersByEric
Software Developer
Associate Degree
31 satisfied customers
I. I'm having trouble getting my Quicken program to work. I
I'm having trouble getting my Quicken program to work. I downloaded it, and when I try to open in I get to a screen asking me to Sign in with my Intuit ID. The box where the sign in should be is blank. A big white box of nothing. I was able to go to the file tab and tried to set up a new account. I filled in my user id# ***** psw for my bank and instead of connecting me to the bank, it simply highlights my user id#. I have triple checked to make sure the number is ***** and have emailed my bank to see if there is some problem on their end. Once I cancel that operation it bounces me back to the Sign in to Intuit screen. So frustrating. Can you help? … read more
Damian Costa
Damian Costa
Owner
Computer Network System Engineer
40 satisfied customers
I can't remember my password to wifi router. That is not the
yes that is not the problem I have another device I need to login … read more
Cabletech
Cabletech
High School or GED
139 satisfied customers
Bookmark bar and bookmars have disappeared. ImacOS High
since upgrading firefox I get a lot of extra stuff I do not want - advertisig etc… read more
Jason Jones
Jason Jones
AAS Information Technology
6,316 satisfied customers
I'm using a MacBook Air. For past few days I have not been
Hi Pearl, I'm using a MacBook Air. For past few days I have not been able to log in to outlook 365 for emails. My service provider says its a machine issue and not network issue. Can you help? Thanks … read more
Jon
Jon
Bachelors Degree
390 satisfied customers
I can't anything but the wall paper background. Help, Apple
I can't anything but the wall paper background. Help, … read more
Jon
Jon
Bachelors Degree
390 satisfied customers
I would like to change my router password. Never. MacOS High
NO, I think changing Password ***** help, I now have gone from Wi-FI to Ethernet for Network.… read more
arsufi2002
arsufi2002
Bachelor's Degree
322 satisfied customers
Can't access my email. Yahoo.com
Can't access my email JA: Just to clarify, what email service provider are you using? Customer: yahoo.com JA: Have you installed any updates recently? Customer: No JA: Anything else you want the Compu… read more
Moses C
Moses C
Bsc
215 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x