show current record

[actionashley]

Novice Macro question.

I have a subform open in datasheet view. I want to click on a cell with a tool number (text) in it and open a tools details form showing that record. I created a macro, on the on click event in that cell to open the form but I can't get it to open on that record. Probably simple but ...........

Rich

 

[MarkK]

Check out the parameters for the OpenForm command. You can pass a named filter, a filter condition, or you can pass in OpenArgs the form can use to perform custom actions.

 

[pbaldy]

If you want to use code, here's a method:

http://www.baldyweb.com/wherecondition.htm

 

[pbaldy]

Oops; sorry lagbolt. Got distracted while typing.

 

[MarkK]

Not at all, good link!

 

[actionashley]

Thanks for the quick replies.

I put in baldies code in the on click

DoCmd.OpenForm "ToolList3", , , "ToolSearchCombo = " & Me.ToolNumber

and I get a perameter box opining when I click the cell.

It says ToolSearchCombo, if I click ok, the next perameter box says

RL611 (the tool number I clicked)

If i hit ok it opens the form on a new record.

Does it matter that this is a subform? Can i use the me.ToolNumber or do I need to use the complete path

 

[pbaldy]

Is ToolSearchCombo the name of your field in the table (more accurately, the form's record source)? The parameter prompt implies it is not.

 

[MarkK]

The Parameter box opens if you refer to a field that doesn't exist in the underlying data source of the form. Commonly you'd filter on the ID field of the record, so I'd expect to see something like ...

"ToolID = " & Me.ToolNumber

ToolSearchCombo sounds like a control on a form, not a field in a table.
Keep in mind that if your filter is applied to a string field, the database will expect string delimiters, like ...

"ToolTextID = '" & Me.ToolTextID & "'"

And you can't open a subform using DoCmd.OpenForm. A subform is, by definition, hosted by another form in a subform control.

 

[pbaldy]

Oh, and if the tool number includes alpha characters, it must be a text field. Note on the link the different syntax required for a text value.

 

[actionashley]

No, ToolSearchCombo is not the record source, the record source (i think) is the ToolNumber Control that I clicked on.

I fiddled and this code actually works

DoCmd.OpenForm "ToolList3", , , "ToolNumber = '" & Me.ToolNumber & "'"

I mis-interpreted baldys instructions but you guys got me straigtened out.

Main problem I guess was the syntex I used.

I don't fully understand why it works but I guess the more I keep playing around with small bits of code the more I will understand it.

Thanks again.

 

[pbaldy]

Glad it worked. If you can think of a way I can clarify the instructions on my website, I'd be happy to incorporate it.

 

[actionashley]

Here we go again.

I am using the same technique on 2 different forms and am getting a method or data member not found error. Here is my code.

DoCmd.OpenForm "EditBom3", , , "Product Number = '" & Me.ProductNumber & "'"

The source form is a tabbed form, if that matters and I am running the code from a button on click event.

If I remove the where clause the form will open properly but when trying to make it open to the current record boom error.

aa

 

[actionashley]

one more thing, regarding the complete code

Private Sub Command75_Click()
DoCmd.OpenForm "EditBom3", , , "Product Number = '" & Me.ProductNumber & "'"

The top line is highlighted yellow in the debugger

 

[MarkK]

A field name with a space will need to enclosed in square brackets ...

DoCmd.OpenForm "EditBom3", , , "[Product Number] = '" & Me.ProductNumber & "'"

I never use spaces when naming anything.

 

[actionashley]

Thanks Lag I corrected that

Private Sub Command75_Click()
DoCmd.OpenForm "EditBom3", , , "[Product Number] = '" & Me.ProductNumber & "'"

But still get the same error, Method or data member not found.

The top line is still highlighted yellow and the Me.ProductNumber is selected with a blue highlight'

 

[pbaldy]

The error implies that ProductNumber is not a control on the form. If you type "Me." you should get a list of available choices.

 

[actionashley]

To see a list of all the controls on a form where do I type "Me."??

I tried in the immediate box but that doesn't work

 

[pbaldy]

When you're creating code, you should see a list drop down as soon as you type the period. In any case, you should verify the name of the control containing the product number.

 

[actionashley]

Ok I got the me list part, sorry for being dumb and I changed the control source name to Product_Number and now get a new error. Below is new code


Private Sub Command75_Click()
DoCmd.OpenForm "EditBom3", , , "[Product Number] = '" & Me.Product_Number
& "'"

The EditBom3 form is opening but it is opening to a new record........

Some details about the form I am opening:

Search box on top for product number

Main form based on query to find the bomID number (includes Product_Number field)

subform linked on bomID showing material usages

subform2 linked on bomID showing operation usages.

 

[pbaldy]

Is it really split on two lines, or is that a cut/paste typo? Can you post the db?