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

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.

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

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

Oops; sorry lagbolt. Got distracted while typing.

Not at all, good link!

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

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

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.

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.

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.

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.

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

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

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.

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'

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

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

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.

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.

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

Good Grief!!!!

I spent a bunch of time copying tables, removing "sensitive" info, putting in bogus info so I could post the DB and now it seems to be working. Don't know why, do I have to close it and re-open it for changes to take effect? can't be. anyway it works but.........

When It opens the EditBom3 form it goes to the record I want but it is now stuck on that record and can't be changed to another unless I close that form and re-open it. Is there some code so I will be able to search another number without closing the form.

Well, this will open the form to the specified record but leave the other records available:

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

You could use the combo box wizard to add a search combo to that form.

Ok, so remembering I'm a novice and this is actually my 1st DB

Dim rs As Object ---- Whats rs? a record set?
Dim lngBookmark As Long --- lngBookmark is just a variable name? Mine will be Dim StringBookmark as String?

'set a variable to the current record
lngBookmark = Me.txtEmpID ----- mine will be StringBookmark=Me.Product_Number

'open the new form
DoCmd.OpenForm "frmEmployeesDetail" ----- mine DoCmd.OpenForm "EditBom3"

'take it to the selected record
Set rs = Forms!frmEmployeesDetail.RecordsetClone ------ mine Set rs =
Forms!EditBom3.RecordsetClone

rs.FindFirst "EmpID = " & lngBookmark ------ mine rs.FindFirst "Product_Number =" & StringBookmark

Forms!frmEmployeesDetail.Bookmark = rs.Bookmark ----- mine Forms! EditBom3.Bookmark (not StringBookmark?)

Set rs = Nothing


hope this makes sese

Looks close, presuming your value is text. This would change to:

rs.FindFirst "Product_Number ='" & StringBookmark & "'"

I guess I messed up somewhere. Here is the code i put in.



Private Sub Command75_Click()
'DoCmd.OpenForm "EditBom3", , , "[Product Number] = '" & Me.Product_Number & "'"
Dim rs As Object
Dim StringBookmark As String

'set variable to current record
StringBookmark = Me.Product_Number

'open new form
DoCmd.OpenForm "EditBom3"

'take it to selected record
Set rs = Forms!EditBom3.RecordsetClone

rs.FindFirst "Product_Number ='" & StringBookmark & "'"
Forms!EditBom3.StringBookmark = rs.StringBookmark

Set rs = Nothing

sorry, the error is unknown or invalid field reference Product_Number

I've seen you use both a space and an underscore in the name. Make sure of which it is, and if it's an inadvisable space the name will have to be bracketed.

Sorry for the long delay with this but I got pulled off onto another project and am just getting back to this. I fixed the bracketing problem

Dim rs As Object
Dim StringBookmark As String

'set variable to current record
StringBookmark = Me.Product_Number

'open new form
DoCmd.OpenForm "EditBom3"

'take it to selected record
Set rs = Forms!EditBom3.RecordsetClone

rs.FindFirst "[Product Number] ='" & StringBookmark & "'"
Forms!EditBom3.StringBookmark = rs.StringBookmark

now the error goes to the last line and says
Object does not support this property or method.:(

Try

Forms!EditBom3.Bookmark = rs.Bookmark

:DThank you pbaldy that worked. I looked up bookmark property and understand it a little better but don't think i get it fully.

Thanks again for all your help.

Glad we got it sorted out.