#Name? on form after requery in Access 2010

I am using VBA and SQL to re-query my main form based on criteria entered in several controls on a pop up form. As far as I can tell the code is running correctly, the database is re-queried based on the criteria I enter, but 2 of my controls on my main form show as #Name? or blank after re-querying based on the criteria. Anyone know how I can fix this???

The code that runs the re-query is:

Public Sub SuperFilter()
On Error GoTo Err_AdvancedFilter_Click
Dim strSQL As String    
Dim strCallNumber As String
Dim strAsgnTech As String
Dim strClientID As String
Dim strCallGroup As String
Dim strPriority As String
Dim strOpenStatus As String

        If IsNull(Forms![frmTips&Tricks].txtCallNumber) = False Then
            strCallNumber = " (((CallInfo.CallNumber) = forms![frmTips&Tricks].[txtCallNumber])) and "
        Else
            strCallNumber = ""
        End If


        If IsNull(Forms![frmTips&Tricks].cboAsgnTech) = False Then
            strAsgnTech = " (((CallInfo.AsgnTech) = forms![frmTips&Tricks].[cboasgntech])) and "
        Else
            strAsgnTech = ""
        End If


        If IsNull(Forms![frmTips&Tricks].cboClientID) = False Then
            strClientID = " (((CallInfo.ClientID) = forms![frmTips&Tricks].[cboClientID])) and "
        Else
            strClientID = ""
        End If


        If IsNull(Forms![frmTips&Tricks].cboCallGroup) = False Then
            strCallGroup = " (((CallInfo.AsgnGroup) = forms![frmTips&Tricks].[cboCallGroup])) and "
        Else
            strCallGroup = ""
        End If


        If IsNull(Forms![frmTips&Tricks].cboPriority) = False Then
            strPriority = " (((CallInfo.Severity) = forms![frmTips&Tricks].[cboPriority])) and "
        Else
            strPriority = ""
        End If


        If Forms![frmTips&Tricks].optOpenStatus.Value = 1 Then
            strOpenStatus = " (((CallInfo.OpenStatus) = True))"
        Else
            strOpenStatus = " (((CallInfo.OpenStatus) is not null ))"
        End If

    strSQL = "SELECT CallInfo.CallNumber, CallInfo.ClientID,* " & _
            "FROM dbo_HDTechs INNER JOIN ([User] INNER JOIN CallInfo ON User.ClientID = CallInfo.ClientID) ON dbo_HDTechs.TechName = CallInfo.AsgnTech " & _
            "WHERE " & strCallNumber & strAsgnTech & strClientID & strCallGroup & strPriority & strOpenStatus & _
            "ORDER BY CallInfo.RcvdDate;"


    Form.RecordSource = strSQL
    Me.cboCallNumber.RowSource = strSQL
    Form.Requery

If Me.RecordsetClone.RecordCount = 0 Then

    MsgBox "No Records Found: Try Diferent Criteria."
    Form.RecordSource = "qryservicerequestentry"
    Me.cboCallNumber.RowSource = "qryservicerequestentry"
    Exit Sub
End If

Me.cmdSuperFilterOff.Visible = True



Exit Sub

Exit_cmdAdvancedFilter_Click:
    Exit Sub

Err_AdvancedFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdvancedFilter_Click



End Sub

The first control in question is a combo box that displays the Client Name from the CallInfo form (Main Form). Control Source: ClientID And when expanded lists all available clients to select from the Users form (User ID is linked between the User form and CallInfo form). Row Source: SELECT User.ClientID FROM [User]; After the re-query, this combobox will be blank, sometimes showing #Name? if you click on it.

The second control in question is a text box that shows the Client's phone number. Control Source: PhoneNo After the Re-query, this text box always displays #Name?

The third control in question is a text box that displays the clients office location. Control Source: Location What really baffles me is that THIS text box displays correctly after the re-query. I don't know why it would display the correct data when the Phone Number text box does not, seeing as they are so similar and work with similar data....

To Compare, the The form record source is normally based on: SELECT CallInfo.CallNumber, CallInfo.ClientID, CallInfo.RcvdTech, CallInfo.RcvdDate, CallInfo.CloseDate, CallInfo.Classroom, CallInfo.Problem, CallInfo.CurrentStatus, CallInfo.Resolution, CallInfo.Severity, CallInfo.OpenStatus, CallInfo.AsgnTech, dbo_HDTechs.Email, CallInfo.FullName, CallInfo.AsgnGroup, User.Location, User.PhoneNo, CallInfo.OpenStatus FROM dbo_HDTechs INNER JOIN ([User] INNER JOIN CallInfo ON User.ClientID = CallInfo.ClientID) ON dbo_HDTechs.TechName = CallInfo.AsgnTech WHERE (((CallInfo.OpenStatus)=True)) ORDER BY CallInfo.RcvdDate;

Answers


Just going on what you wrote, I may take a slightly different approach (just personal preference).

  1. I would change all of your 'IsNull' tests to also check for 'Empty'. i.e. If IsNull(Forms![frmTips&Tricks].cboClientID) = False AND ...cliientID <> ""
  2. Just today I had an issue relating to form references in a query WHERE clause, so I changed to: strClientID = " (((CallInfo.ClientID) = '" & forms![frmTips&Tricks].[cboClientID] & "')) and"
  3. Add a Debug.Print of your generated SQL, then look at it and try to run that SQL manually Good Luck, Wayne

Solved by designating the form in the control source like: CallInfo.ClientID

I still don't know why the Client Office displayed Correctly... Anybody have a hint? :)

TE


Need Your Help

How to add "addThis" social integration into a lightbox (jquery prettyphoto or jquery fancybox)?

lightbox social sharing addthis

Kindly help me, i am having some problem with the integration of "addthis" social sharing inside a lightbox so that image can be shared from lightbox. Trid with prettyPhoto and fancybox but there is

ListActivity with ActionBar support v7

android list android-actionbar

My main Activity extends ListActivity, and now I have to implement an ActionBar with support for older versions.