#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;
Just going on what you wrote, I may take a slightly different approach (just personal preference).
- I would change all of your 'IsNull' tests to also check for 'Empty'. i.e. If IsNull(Forms![frmTips&Tricks].cboClientID) = False AND ...cliientID <> ""
- 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"
- 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? :)