Writing macro to go to specific record

[johnjmcnaughton]

I learned the basics when it comes to macros just by trial and error and reverse engineering from templates that I have found. But I am having trouble on this one.



I have a form, (datasheet) that lists all inventory transactions. One column is the Purchase Order Number. I am trying to setup a macro that will open another form, (single form) to the specific Purchase Order Number when double clicked.

I have tried several different ideas with OpenForm, GoToRecord, FindRecord, Requery etc.... But I am not having any luck. I can get the Form to open to a new purchase order, and the First Purchase Order. But not the specific one that was double clicked.

Ideas???

[Bob Fitz]

I don't use macros, so this is just a guess. I use vba code. To do what you want I would use DoCmd.OpenForm an set some criteria so that it opened at the required record. So I would expect you to be able to do this using OpenForm in a macro.

[Bob Fitz]

Use OpenForm. Set "Where Condition" to something like:
[NameOfFieldOnFormBeingOpened]=[Forms]![NameOfFormThatIsShowingTheValueToFind]![NameOfControlShowingValueToBeFound]
Change the coloured text to appropriate values.

[johnjmcnaughton]

Ok....

When I tried this an Enter Parameter Value pop up window came up. After hitting enter a few times the form opened to "New Purchase Order" rather than the Purchase Order that was selected.

The Form I want to open is called "PurchaseOrders".
The Control Source for "Purchase Order Number" is "PurchaseOrderID"

If I am reading this right...
the "NameOfFieldOnFormBeingOpened" would be "PurchaseOrderID"
the "NameOfFormThatIsShowingTheValueToFind" would be "PurchaseOrders"
the "NameOfControlShowingValueToBeFound" would be "PurchaseOrderID"
Which would be [PurchaseOrderID]=[Forms]![PurchaseOrders]![PurchaseOrderID]

Does this look right?

[Bob Fitz]

Yes, it looks right. PurchaseOrderID needs to be the actual name of the control on each form, rather than the name of the underlying field.

[johnjmcnaughton]

I still get a pop up window when I try it this way. And After pressing enter it opens up to a New Purchase Order.

You said that you would use DoCmd.OpenForm in vba code.
Would that be easier? FYI - I have no experience in writing vba code...

[Bob Fitz]

The vba code would be:
DoCmd.OpenForm "[NameOfFormToBeOpened]", , , "[PurchaseOrderID]=" & [Forms]![PurchaseOrders]![PurchaseOrderID]

Can you post a copy of your db in A2003 mdb format.

[johnjmcnaughton]

I thank you for trying to help me out! But I do not have any zip software and I do not have permission to make any downloads. So I don't think I will be able to upload any files on here.

When I try the code "DoCmd.OpenForm "[PurchaseOrders]", , , "[PurchaseOrderID]=" & [Forms]![PurchaseOrders]![PurchaseOrderID]"

it says that it cannot find the form named PurchaseOrders...

[Bob Fitz]

So what is the name of the form that you want to order?

Try:
"DoCmd.OpenForm "[PurchaseOrders]", , , "[PurchaseOrderID]=" & Me.[PurchaseOrderID]"

[johnjmcnaughton]

The form name is "PurchaseOrders". So I just don't understand why it says it can't find it.
I just tried "DoCmd.OpenForm "[PurchaseOrders]", , , "[PurchaseOrderID]=" & Me.[PurchaseOrderID]".

When I tried to run that one I got a pop up window with "Run-time error '2102': The form name '[PurchaseOrders]' is misspelled or refers to a form that doesn't exist."

Confused some more.....

That form does exist and I have checked the spelling over and over again.

[Bob Fitz]

Have you tried without the square brackets around the form name:
"DoCmd.OpenForm "PurchaseOrders", , , "[PurchaseOrderID]=" & Me.[PurchaseOrderID]"

[johnjmcnaughton]

I ended up putting a space between Purchase and Orders for the form name. That worked.
I found a website http://www.baldyweb.com/wherecondition.htm

This was helpful in describing the code. But I still am Unable to get it to open the the selected record. It opens to a new record.

this is the code I tried:

Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
"DoCmd.OpenForm [Purchase Orders], , , "[PurchaseOrderID]=" & Me.[PurchaseOrderID] & "'"
End Sub

I just changed it to:

Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Purchase Orders", , , "PurchaseOrderID = " & Me.PurchaseOrderID & ""

End Sub


With this change it is close!!!!
It opens the form to a new Purchase Order, but limits the records to a new Purchase Order and the one that was clicked on. So clicking on the next button takes me to the record I want. I'm sure it has something to do with the syntax in the code. But I am almost there.

Does any of this info help you identify what I am doing wrong???


Thanks Again!!!

[Bob Fitz]

A couple of questions.

What is the Data Type of the field "PurchaseOrderID"? (Text or Number) You will need to open the table in design view to find out.

Do you have any code or macros that run when the new form is opened? Look at the form's On Open and On Load events.

[johnjmcnaughton]

The Data Type of the field "PurchaseOrderID" in the table is set to "Number".

On the form that I want to open called "Purchase Orders" I have the "On Load" set to go to a New Purchase Order.
(Products Macros.CreateNewPurchaseOrder : On Click)


I do want this to happen when they open the Form Purchase Orders... I have it set for that form to open when they open the database. This way when they open the database they can immediately start a New Purchase Order.

Am I not able to have this setting and still accomplish opening the form to the specific record?

[Bob Fitz]

Try this:

Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
  DoCmd.OpenForm [Purchase Orders], , , "[PurchaseOrderID]=" & Me.[PurchaseOrderID] , , , "Any text"
End Sub

Use the following code in the On Open event of the "Purchase Orders" form:

If IsNull(Me.OpenArgs) Then
  DoCmd.GoToRecord , , acNewRec
End If

A form has an OpenArgs property which is usually Null. The code that open the form sets this property to "Any text". The code in the On Open event only goes to a new record if this property is Null.