Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1282
  • Last Modified:

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
0
Haze0830
Asked:
Haze0830
  • 5
  • 4
2 Solutions
 
Haze0830Author Commented:
PS - if there's a better or different way to do this I am open to suggestions.

Thanks.
0
 
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
0
 
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
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
0
 
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Add this code to the Close event of the Edit form to refresh the Select form:

    Forms!frmProductsSelect.Requery
0
 
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
0
 
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] & "'"

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now