Queries in Access 2003

[paul.hanti]

I've built a shared database that sits on shared network drive using
Access 2003 (Access 2000 file format). I've also built a simple query
form that uses unbound textbox controls to build the query dynamically.
The results of the query are placed into a subform in datasheet view
on the same query form. This query works just like it's supposed to on
my machine (I'm using Access 2003), but when another user tries to run
the exact same query (using Access 2000) the query form just sits there
and doesn't do a thing. Any suggestions?

I'm somewhat new to this Access programming thing.

My code looks like this:
Public Sub cmdSearch_Click()
Dim rs As Recordset

Dim strSQL As Variant
Dim strSelect As Variant
Dim varWhere As Variant

varWhere = Null

'select the table based on checkbox
If Me.chkHistory = True Then
strSelect = "SELECT * FROM ProjListHist WHERE "
Else
strSelect = "SELECT * FROM ProjList WHERE "
End If

'select whether escalated or not
If chkEscalated = True Then
varWhere = (varWhere + " AND ") & " EscStatus = 'Escalated'"
End If

'build string Release Month
If Not IsNothing(Me.txtRelMonth) Then
varWhere = (varWhere + " AND ") & " RelMonth = '" &
Me.txtRelMonth & "'"
End If

'build string Budget Subtype
If Not IsNothing(Me.txtBudgetST) Then
varWhere = (varWhere + " AND ") & " BudgetST = '" &
Me.txtBudgetST & "'"
End If

'build string SPMID
If Not IsNothing(Me.txtSPMID) Then
varWhere = (varWhere + " AND ") & " SPMID = '" & Me.txtSPMID &
"'"
End If

'build string Initiative number
If Not IsNothing(Me.txtInitNum) Then
varWhere = (varWhere + " AND ") & " InitNum = '" &
Me.txtInitNum & "'"
End If

'build string Add date
If Not IsNothing(Me.txtAddDt) Then
varWhere = (varWhere + " AND ") & " addDt = #" & Me.txtAddDt &
"#"
End If

'build string Front door manager
If Not IsNothing(Me.txtFrntDrMgr) Then
varWhere = (varWhere + " AND ") & " FrntDrProj LIKE '*" &
Me.txtFrntDrMgr & "*'"
End If

'build original pc release month
If Not IsNothing(Me.txtOrigPCRelMonth) Then
varWhere = (varWhere + " AND ") & " OrigPCRelMonth = '" &
Me.txtOrigPCRelMonth & "'"
End If

'build curr/esc release month
If Not IsNothing(Me.txtCurrEscRelMonth) Then
varWhere = (varWhere + " AND ") & " CurrEscRelMonth = '" &
Me.txtCurrEscRelMonth & "'"
End If

'build target date
If Not IsNothing(Me.txtTargetDt) Then
varWhere = (varWhere + " AND ") & " TargetedDt = #" &
Me.txtTargetDt & "#"
End If

'build accepted into pmo process date
If Not IsNothing(Me.txtAcceptIntoPMOProcessDt) Then
varWhere = (varWhere + " AND ") & " AcceptIntoPMOProcessDt = #"
& Me.txtAcceptIntoPMOProcessDt & "#"
End If

strSQL = strSelect & varWhere
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Me![frmQueryArea].Form.RecordSource = strSQL
Me![frmQueryArea].Requery

End Sub

[bruce]

Hello Paul,

You can remove the code having to do with the recordset rs you declared
(i.e., the 'dim rs...' and 'set rs...' statements) because it's not
being used anywhere and it is not necessary in order to make your query
work. Also, you don't need to set varWhere = Null. On the other hand,
these should not prevent your code from running.

Check to see that both machines are up to date with respect to service
packs for both the OS and for Access/Office.

If possible, I would step through your code on the machine where it is
not working by setting a breakpoint at the end of your code (line
'Me![frmQueryArea].Form.RecordS*ource = strSQL') by clicking somewhere
in the line and pressing F9 key. Then run the form, fill out all the
fields, and click your command button. When the code execution stops
at that line press Ctrl G to bring up the 'immediate window', then type
'?strSQL' (minus quotes of course) and press enter to display the value
of strSQL. You can then copy and paste the value of strSQL into the
SQL window of a new query and see if it brings up anything in datasheet
view (i.e. see if it is a valid query).

HTH,
Bruce