open form and filter

[TheCandyman]

I have two forms(frmMain, frmCourse), when i am in my first form, the user can click on an item, it stores that code which is a number and opens frmCourse. I want it to filter the frmCourse.CourseID(also a Number) so it only shows the items with the code matching from the previous form. This is what i have in my frmMain that opens the next form:

Not Working
Dim varName As Variant
varName = DLookup("[tmpNumber]", "tmpTable")
'DoCmd.OpenForm "frmCourse", , , "[CourseID] = '" & varName & "'"

This one works, but only opens the form
DoCmd.OpenForm "frmCourse", , , acFormEdit


What do i need to fix on the DoCmd??

 

[willir]

TheCandyman

Easiest way...
Add a command button, and use the command button wizard. Follow "Form" -> "Open Form" and select specific record, match your fields, picture or text, give the button a use name.

Also, realize that the OpenForm method has three ways to find your target record, or take a specific action...
[tt]
DoCmd.OpenForm "YourTargetForm", acNormal, strFilter, strWhere, acFormEdit, acWindowNormal, strOpenArg
[/tt]

The Filter option, Where conidtion and Open Arguments can be used to achieve your desired results.

It is late, so instead of giving example, please refer to the Access Help pages.

Richard

 

[TheCandyman]

thanks for the reply, i looked over those pages but didn't see any examples that i could use.

 

[willir]

What do you mean??

Look at a recent post...

http://www.tek-tips.com/viewthread.cfm?SQID=886913&SPID=702&newpid=702&page=1

At the bottom, an example of OpenArg is used.

1) FilterOn

You can also use the WHERE condition passed argument with the OnLoad or OnCurrent event procedure...

Me.Filter = "YourFieldName = " & PassedFieldName
Me.filterOn = True

2) RecordSource

Use OpenArg to pass an SELECT statment, or enough to build your own SELECT statment.

OnLoad event

strSQL = "SELECT ...."

Me.RecordSource = strSQL
Me.Requery

3) Link criteria

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "YourTargetDocument"
    
    stLinkCriteria = "[TargetField]=" & Me![TargetField]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Also look at clone sets.