Open a form on a selected record

[joee]

Hi. please let me know if anybody can help me a bit on this one.
I have a form with several records. Project No is my key. Once I have selected the record that needs to be worked on, I want to be able to open another form which have all the details. I do not want to use the filter mode which I know how to use but I am looking for a mechanism which can make search for the same project No in the form I want to open. Please does it make sence and does anybody have an idea for me to make this?
Appreciate any help.

 

[Mike375]

This

DoCmd.OpenForm "FormToBeOpened", acNormal, "", "[IDNumber]=[Forms]![FormWhereCodeIsRun]![IDNumber]", acEdit, acNormal

If done in a macro then it is OpenForm action and the Where Condition is basically the same as the above. That will be a filter situation. It will say 1 of 1 filtered on the navigation buttons.

Another way is to have an OpenForm action and then change the record source on the form that is opened to a query that uses the ID (or whatever matching field) on the main form as its criteria. This is not a filter situation.

 

[Tanya]

You could try this is the OnClick event code, easier if you use a command button with the wizard, it will guide you through the process.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "NameofFormtoOpen"

stLinkCriteria = "[Project No]=" & Me![NameofFormtoOpen].Form![Project No]
DoCmd.OpenForm stDocName, , , stLinkCriteria, ,

Good luck

 

[joee]

Thanks for quick reply. I cannot make it the way I want.
Below is the code I currently use which opens my second form in filter mode. I want it just to open on the same Project No as I make dobbel click on in my primary form.

On Error GoTo Err_Kommandoknap32_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Main Project Data"

stLinkCriteria = "[Project No]=" & "'" & Me![Project No] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Kommandoknap32_Click:
Exit Sub
Err_Kommandoknap32_Click:
MsgBox Err.Description
Resume Exit_Kommandoknap32_Click
End Sub

You could try this is the OnClick event code, easier if you use a command button with the wizard, it will guide you through the process.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "NameofFormtoOpen"

stLinkCriteria = "[Project No]=" & Me![NameofFormtoOpen].Form![Project No]
DoCmd.OpenForm stDocName, , , stLinkCriteria, ,

Good luck

 

[Tanya]

Sorry I haven't responded sooner, it was late last night here in Sydney when I last responded.

Your problem appears to be in this line of code:

stLinkCriteria = "[Project No]=" & "'" & Me![Project No] & "'"

Try this:

stLinkCriteria = "[Project No]=" & Me![Main Project Data].Form![Project No]

Cheers
Tanya

You should avoid spaces between words for control names i.e. it would be better to call Main Project Date frmMainProjectData and Project No could be named ProjectNo or Project_No

 

[CameronM]

If [Project No] is actually a number field, then Tanya's solution should work. If it is a text field then you will need the code:

stLinkCriteria = "[Project No]='" & Me![Main Project Data].Form![Project No] & "'"

 

[joee]

Sorry but still not reached the goal.
[Project No] is text field but once I use below string

stLinkCriteria = "[Project No]='" & Me![Main Project Data].Form![Project No] & "'"

Then I get the message, cannot find the field "Main Project Data" which is referenced to in the expression.
The form called "Main Project Data" does exist and can be opened manually without problems.

 

[CameronM]

As long as [Primary No] is on the main form you can use:

stLinkCriteria = "[Project No]='" & Me![Project No] & "'"