docmd openform question

[punderful]

First let me say - this is a fantastic forum!!

I am trying to build a form that will use the value from 3 option groups (one of which is a ORDER BY), and also take the name from a combo box. What complicates matters is that the name can appear is one or more of 4 fields in the table. I can write the SQL, but it still doesn't seem to work (some parts do work).

The ORDER BY portion doesn't work, and the combo box gets the error "you can't reference a property or method for a control unless the control has focus"

Here's my code:


Private Sub cmdDisplayRecords_Click()
On Error GoTo Err_cmdDisplayRecords_Click

Dim strOrderBy As String
Dim strPeople As String
Dim strFormName As String
Dim strLinkCriteria As String

'create criteria based on user selection - THIS PART WORKS GREAT!
Select Case fraContract
Case 1
strLinkCriteria = "tblProjects.txtClient = 'Liberty II'"
Case 2
strLinkCriteria = "tblProjects.txtClient = 'Prop2003'"
Case 3
strLinkCriteria = &quot;tblProjects.txtSiteStatus <> 'dead'&quot;
Case Else
MsgBox &quot;You must select a form&quot;, vbExclamation, &quot;Open Form&quot;
End Select

'select only records by combo box selection, if selected
If cboPeople.Value > 0 Then
strPeople = cboPeople.Text
End If
'add to criteria string to look in all name fields for text value
strLinkCriteria = &quot; and tblProjects.txtProjectMgr = &quot; & strPeople & _
&quot; or tblProjects.txtSAID = &quot; & strPeople & _
&quot; or tblProjects.txtZoningInit = &quot; & strPeople & _
&quot; or tblProjects..txt2ndInstrInit = &quot; & strPeople

'create sort option
Select Case fraSortOrder
Case 1
strOrderBy = &quot; ORDER BY tblProjects.txtSite#&quot;
Case 2
strOrderBy = &quot; ORDER BY tblProjects.txtSiteName&quot;
Case 3
strOrderBy = &quot; ORDER BY tblProjects.txtBTA&quot;
Case Else
MsgBox &quot;How do you want records sorted?&quot;, vbExclamation, &quot;Open Form&quot;
End Select

'determine which form will be opened - THIS PART WORKS GREAT!
Select Case fraFormSelect
Case 1
strFormName = &quot;frmProjectsSA&Zoning&quot;
Case 2
strFormName = &quot;frmProjectsArchitectural&quot;
Case 3
strFormName = &quot;frmProjectsPaypoint&quot;
Case Else
MsgBox &quot;You must select a form&quot;, vbExclamation, &quot;Open Form&quot;
End Select

DoCmd.OpenForm strFormName, , , strLinkCriteria & strOrderBy, acFormEdit

Exit_cmdDisplayRecords_Click:
Exit Sub

Err_cmdDisplayRecords_Click:
MsgBox Err.Description
Resume Exit_cmdDisplayRecords_Click

End Sub

 

[MazeWorX]

You can set focus to your combo like this cboPeople.SetFocus

The ORDER BY doesn't work because its not a valid part of a WHERE clause Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[punderful]

Can the ORDER BY go in the openargs part of openform?

 

[MazeWorX]

I dont believe so, but I'm not 100% sure. You could try to build a complete SQL statement then assign it to the forms record source property. you have most of the sql built already.

Just a thought (-: Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[SBendBuckeye]

A couple observations for your use.

I ALWAYS leave at least one space at the end of every SQL line I am building on the fly. If you forget it and it abutts up to the next statement you will get an SQL error that may not be obvious (eg FROM tlbTableWHERE etc).

Your code with strPeople MUST have single quotes ' around them as you have correctly done above with strLinkCriteria
eg(&quot; or tblProjects.txtSAID = '&quot; & strPeople & &quot;' &quot; _ ).

I believe you should have strLinkCriteria = _
strLinkCriteria & and tblProjects.txtProjectMgr, etc.

It appears to me that you probably want parenthesis () around the strPeople or statements something like:
strLinkCriteria = strLinkCriteria & &quot; and &quot; & _
&quot; (tblProjects.txtProjectMgr = '&quot; & strPeople & &quot;' &quot; & _
&quot; or tblProjects.txtSAID = '&quot; & strPeople & &quot;' &quot; & _
&quot; or tblProjects.txtZoningInit = '&quot; & strPeople & &quot;' &quot; & _
&quot; or tblProjects..txt2ndInstrInit = '&quot; & strPeople &') &quot;

Pass the OrderBy into the calling program via OpenArgs which is the last parameter in the OpenForm statement. You will want to modify your string appropriately. It looks like the source table for all of the called programs is tblProjects. In that case, strOrderBy would be set to Site#, SiteName or BTA respectively.

DoCmd.OpenForm strFormName, , , strLinkCriteria, acFormEdit, ,etc, strOrderBy.

Then in the form load code of the called program put code like this to finish the process:

If Not IsNull(OpenArgs) Then
me.OrderByOn = True 'Ensure form ordering is turned on
me.OrderBy = Trim$(OpenArgs)
End If

site# may cause some problems because of the trailing pound sign which the system might try to interpret as a date delimiter (not sure, just a heads up in case). If so, you could dimension a string variable and set it to the OpenArgs so there would be no question about it.

This has been an interesting question! Good Luck and Happy New Year!

 

[cdparisi]

I think that your combo box problem could be solved by using the column() property of the combo box object instead of the value property as you did.

Something like this might work:

    If cboPeople.column(0)> 0 Then
        strPeople = cboPeople.column(0)
    End If

(I don't know if you should test for a zero value or Null but you get the idea.)

As far as OpenArgs goes, you can make that whatever you want as long as it evaluates to a string. You could then use the Form Open event of your newly opened form and use the OpenArgs string to set the form's OrderBy property.

    MyForm.OrderBy = Me!OpenArgs

Hope this makes sense and helps.

Chris :

 

[punderful]

Thanks for your help everyone!!