Help with Macro to open form and lookup record

I have a form that is used to edit and enter records. I have a datasheet which contains part no's. The Part No. is the primary key for the the datasheet\table. I want to click on a part no. and have that record pulled up in the form for editting. I've attached a screenshot of the macro I've currently been working on. I've gotten as far as being able to pull up the form but it won't pull the record I click on - it just pulls the first record in the table. So obviously I'm doing something wrong.

Any help is appreciated. Thanks.
OpenForm.png
LVL 2
Haze0830Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haze0830Author Commented:
PS - if there's a better or different way to do this I am open to suggestions.

Thanks.
Jeffrey CoachmanMIS LiasonCommented:
Haze0830,

Then why do you need two forms?

Why not create a from, and set it's default view property to: Datasheet view

Then simply highlight (Select) the record and edit it directly in this form?

JeffCoachman
Jeffrey CoachmanMIS LiasonCommented:
... besides the "Two form" solution would be the same as your previous question:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23878605.html


JeffCoachman
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Haze0830Author Commented:
These records are spec sheets with A LOT of data. Editing or adding them in a datasheet is a pain and, well, just looks messy. I like for my users not to hate their jobs.

...and I'm a little better with macros than I am with VB so I was just giving it a shot.
Jeffrey CoachmanMIS LiasonCommented:
Haze0830,

When you say "Click on it" I presume you mean; click on the record selector on the left side of the record.
This is the safest way.

Here is a sample.

(Note: If you want to click "Anywhere" on the record, then you cannot use datasheet view, and you must put code on *every* field (textbox) for the record.
Obviously this is not to the accepted way of doing this.)

JeffCoachman


Access-EEQ23882188OpenRelatedRec.mdb
Haze0830Author Commented:
Right, what I'm clicking on is the primary key - one specific field. I have it setup to display and function like a hyperlink.
Jeffrey CoachmanMIS LiasonCommented:
Add this code to the Close event of the Edit form to refresh the Select form:

    Forms!frmProductsSelect.Requery
Jeffrey CoachmanMIS LiasonCommented:
Haze0830,

    "Right, what I'm clicking on is the primary key"
You can surely adapt the code I posted to do this.
The only issue with this is that if you ever wanted to just "Copy" the key value, it would jump to the related record, and not let you copy.

That is why clicking on the record selector is the preferred technique.

JeffCoachman
Haze0830Author Commented:
Went at it from another direction.

It was just easier to apply what I had already done previously. I had to tweak the expression a little bit, but it works.

DoCmd.OpenForm "Part Spec Input", acNormal, , "[mlcat] = '" & Me![mlcat] & "'"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.