Multiple criteria in opening form

[Dumferling]

I am trying to open a form with multiple criteria. I hae tried various combinations but come up with either syntax errors or type mismatch."
DoCmd.OpenForm "frmCStdContractDefaults", , , "[CVName]=" & Me!StandardContract & " And [Version] = '" & Me![Version] & "'"
This one gives me a missing operator. I can't see anything wrong with it - based on an earlier post in this forum. Both fields are text. Any help appreciated.

 

[Gasman]

Tip: Put it all into a string variable, then you can debug.print it until correct, then use that in the code instead.

Why would you put single quotes around one field and not the other?

DoCmd.OpenForm "frmCStdContractDefaults", , , "[CVName]='" & Me!StandardContract & "' And [Version] = '" & Me![Version] & "'"

I have not even tested to see if that is in the correct location, just the syntax of the crieria.

No need for [] unless spaces in names (not reccommended) or access keywords (also not recommended).

 

[ahmedjamalaboelez]

Why Yo Force Criteria In Docmd.open form ?
Make Public Module in form you want to open with criteria , so it let you choose between open with/out criteria as attached Example

Public Sub GetCriteria(MyCv As String, MyVersion As String)
    CVName = MyCv
    Version = MyVersion
    
    
    
    'call this by
    Private Sub Command0_Click()
    If Trim([CVName] & "") = vbNullString Or Trim([Version] & "") = vbNullString Then
     DoCmd.CancelEvent
        MsgBox "Fill CV And Version"
    Else
         DoCmd.OpenForm "frmCStdContractDefaults"
        Forms![frmCStdContractDefaults].GetCriteria Me.CVName, Me.Version
    End If
End Sub

A.J

 

[Gasman]

Why Yo Force Criteria In Docmd.open form ?
Make Public Module in form you want to open with criteria , so it let you choose between open with/out criteria as attached Example

Public Sub GetCriteria(MyCv As String, MyVersion As String)
    CVName = MyCv
    Version = MyVersion
   
   
   
    'call this by
    Private Sub Command0_Click()
    If Trim([CVName] & "") = vbNullString Or Trim([Version] & "") = vbNullString Then
     DoCmd.CancelEvent
        MsgBox "Fill CV And Version"
    Else
         DoCmd.OpenForm "frmCStdContractDefaults"
        Forms![frmCStdContractDefaults].GetCriteria Me.CVName, Me.Version
    End If
End Sub

A.J

Not the same thing though, is it?

 

[ahmedjamalaboelez]

Not the same thing though, is it?

I think every thing is possible when you path Criteria to public sub or function , I do not Use Open Form Or Load Form Event
I Just Call The Public Module So I Can Optimize the form To Open With Any Criteria from Any Where

I'm Not Expert But, Being Happy To Help And Save Expert Guys Time to Solve Tough Issues
AJ

 

[Gasman]

I think every thing is possible when you path Criteria to public sub or function , I do not Use Open Form Or Load Form Event
I Just Call The Public Module So I Can Optimize the form To Open With Any Criteria from Any Where

I'm Not Expert But, Being Happy To Help And Save Expert Guys Time to Solve Tough Issues
AJ

That is not setting criteria though, just populating the destination form controls with two values?

 

[Minty]

@ahmedjamalaboelez Two points - that isn't filtering the form just setting some values on it.
Secondly, even if it worked, you are opening a form without any criteria, which means you are bringing the entire underlying recordset into it, which is not good practice. You should always only open a form with a limited recordset to avoid unnecessary data traffic.

Edit @Gasman beat me to point 1!

 

[ahmedjamalaboelez]

Two points - that isn't filtering the form just setting some values on it.
Secondly, even if it worked, you are opening a form without any criteria, which means you are bringing the entire underlying recordset into it, which is not good practice. You should always only open a form with a limited recordset to avoid unnecessary data traffic.

so he can open form with no data then can call query in that public module
I just wanted to help not More , I sent Sample Db , If He Find it Useful to him I will be happy
if some expert have a good solution i think we all will find useful
Full Respect
Thanks
A.J

 

[Gasman]

so he can open form with no data then can call query in that public module
I just wanted to help not More , I sent Sample Db , If He Find it Useful to him I will be happy
if some expert have a good solution i think we all will find useful
Full Respect
Thanks
A.J

The O/P can open the form in a myriad of ways.
However all they were asking for was to get the criteria correct?

 

[ahmedjamalaboelez]

The O/P can open the form in a myriad of ways.
However all they were asking for was to get the criteria correct?

ok got it boss ,
thanks
AJ

 

[Dumferling]

Awesome, thanks. Worked like a dream!