Hi All,

I've got a bit of a problem and I wondered if you might be able to help...

I've got a form called frmOrdersByCustomer whose record source is tblCustomers. Each customer has an alerts function so that we can have a flash up on screen of any really important information before we go into any of the main details. To get this form up on screen I have the following code:



Private Sub Form_Current()
If IsNull(Me![CustomerID]) Then
DoCmd.GoToControl "ProprietorAddress"
End If
' Opens frmAlerts form with a filter based on Ticket ID and an Alert where the DateIssued has already passed, but where the DateExpires has not
Dim strSQLAlerts As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strSQLAlerts = "SELECT tblAlerts.* " & _
"FROM tblAlerts " & _
"WHERE (((tblAlerts.DateIssued)<=Now()) AND ((tblAlerts.DateExpires)>=Now()) AND ((tblAlerts.CustomerID)=" & Form_frmOrdersByCustomer.CustomerID & ") AND ((tblAlerts.LogicalStatusID)=1));"
Debug.Print strSQLAlerts
rst.Open strSQLAlerts, CurrentProject.Connection
If Not rst.EOF Then
DoCmd.OpenForm "frmAlerts", , , "[CustomerID]=" & [Forms]![frmOrdersByCustomer]![CustomerID] & " AND [DateIssued] <= Now() And [DateExpires] >= Now() And [LogicalStatusID] = 1", acFormEdit
End If
rst.Close
' Checks the status of the displayed Customer's account and adjusts colours accordingly
AccStatusChange
Me.tabOrderSummary.SetFocus
' Resets the caption on button cmdAllowEdits
' Locks records to prevent user changes
Me.cmdAllowEdits.Caption = "Edit This Customer"
LockCustomer
End Sub



Now it all works perfectly fine... and the form for Alerts is displayed if there are any valid alerts...

Here's where it gets very strange...

On the same form (frmOrdersByCustomer) I've got a number of search criteria to be able to set a new recordsource for the form based on whatever the user wants to narrow the records down to. To do this, I have code:


Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String

strJoin = " AND "
strSQLHead = "SELECT * FROM tblCustomers "

If Len(Me.txtCustomerIDSearch & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

strSQLWhere = strSQLWhere & "[CustomerID]=" & Me.txtCustomerIDSearch

strSQLWhere = strSQLWhere & strJoin
End If

If Len(Me.txtProprietorNameSearch & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

If (Me.chkLikeProprietor) Then
strSQLWhere = strSQLWhere & "[ProprietorName] Like '*" & Me.txtProprietorNameSearch & "*'"
Else
strSQLWhere = strSQLWhere & "[ProprietorName] = '" & Me.txtProprietorNameSearch & "'"
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Me.txtPremiseNameSearch & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

If (Me.chkLikePremise) Then
strSQLWhere = strSQLWhere & "[PremiseName] Like '*" & Me.txtPremiseNameSearch & "*'"
Else
strSQLWhere = strSQLWhere & "[PremiseName] = '" & Me.txtPremiseNameSearch & "'"
End If

strSQLWhere = strSQLWhere & strJoin

End If

If Len(Me.txtPostCodeSearch & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

strSQLWhere = strSQLWhere & "[PostCode]= '" & Me.txtPostCodeSearch & "'"

strSQLWhere = strSQLWhere & strJoin
End If

If Len(Me.cboAuthAreaSearch & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

strSQLWhere = strSQLWhere & "[AuthArea] = '" & Me.cboAuthAreaSearch & "'"

strSQLWhere = strSQLWhere & strJoin
End If

If Len(strSQLWhere) Then
strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
End If

strSQLOrderBy = "ORDER BY "

strSQLOrderBy = strSQLOrderBy & "[CustomerID] DESC"

strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

Debug.Print strSQL

Me.RecordSource = strSQL

End Sub

Now this too works absolutely fine...

EXCEPT for when the first record displayed on the main form after using the search feature, ALSO has a valid alert.... Instead of getting all the Alert information, all I get is #Name? in every box on the frmAlerts form...

Strangely, if I change to a different one and then back, all the details are there and in full... so I guess it's something to do with when I open the recordset on the search button, whilst the On_Current part is also opening a recordset on the alerts form...

The Form_Current code, sets the recordsource for frmAlerts based on all whether or not there are any valid alerts for the Customer displayed on frmOrdersByCustomer (the Form_Current code is run from the frmOrdersByCustomer form).

The cmdSearch button sets the recordsource for the frmOrdersByCustomer, but naturally since the routine would have affected the Form_Current, this code will run too... And even though the recordsource is being changed for two different forms, they still seem to conflict???

The fields with #Name? are ONLY on the frmAlerts form... all the records on the frmOrdersByCustomer form appear correctly...

Problem is, I don't know enough about opening the recordsets in the first place to understand what exactly was happening.

I've attached a sample of forms and code if anyone can have a look and see what they think...

Any ideas?

I can't cause this problem. Can you provide very simple 1, 2, 3, steps to repeat the error?
Cheers,

Sure...

If you are using the sample database I attached... use the yellow search fields on the right of frmOrdersByCustomer... enter 2824.00 in the CustomerID field then press search...

You'll get the record displayed, then the frmAlerts over the top with a valid alert, but all the frmAlerts fields will say #Name?

If you close the frmAlerts form, then press next record, then previous record from the frmOrdersByCustomer form (i.e. to go off and then back to that record, the frmAlerts form will display as normal with the correct values.

Strange eh?

This doesn't happen on my machine.
I do this...
1) Open frmOrdersByCustomer via Dbl_Click in the database window.
2) Type 2824 in CustomerID field of yellow search area on far right
3) Click button labelled 'Search'
This happens...
1) Record source of form is limited or filtered to only display record having CustomerID = 2824
2) frmAlerts opens with no apparent difficulty and there is no visible #Name? error.

How strange...

Did the frmAlerts form come up automatically? Or did you need to click manage alerts?

If you needed to click manage alerts.... can you press the button saying make data live, as for some reason mine wasnt showing as live but erroneous...

If yours also says something other than live... make it live, then run the three step you did again and see whether that gives you the error?

If not, do you have any suggestions for why it might be different on mine?

Is there any possibility that something is wrong on my own system rather than the database?

If so, how could I tell, and what on earth might I be looking for?

I'm stumped with this.
frmAlerts appeared automatically, I did not manage alerts, item was live when form opened.
I have had simillar errors on a subform with multiple calculated fields which was solved by running the form's ReCalc method, but I doubt those were #Name? errors.
One possibility is this:
In the Form_Current event of frmOrdersByCustomer, when you open the frmAlerts your SQL filter evaluates as follows...
"[CustomerID]=" & [Forms]![frmOrdersByCustomer]![CustomerID] & " AND [DateIssued] <= Now() And [DateExpires] >= Now() And [LogicalStatusID] = 1"
I'd try evaluating the Now() function outside the string as follows...
"CustomerID=" & Me.CustomerID & " AND DateIssued <= #" & Now() & "# And DateExpires >= #" & Now() & "# And LogicalStatusID = 1"
Perhaps this Now() function is not consistently evaluated when applied to a form in a filter. Also, beware that the Now() function includes a time, so you may not get equality when comparing to a date, for instance: ? Date() = Now() in the immediate window yields 'False'.
Best of luck with this.

Hi All...

I've worked out that the #Name? return values ONLY occur when using Access 2007. It works great with Access 2000 (although to be fair I haven't test A2k3).

Any ideas why?