UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run Time Error On Search Form, Access 2010    
 
   
MOMO88
post Sep 12 2017, 11:01 AM
Post#1



Posts: 53
Joined: 2-August 17



I am getting an error on my search form when I try to test it. It is highlighting a piece of the code that I have used in a previous search and worked fine. please advise what I should try to do in order to fix it.
CODE
Option Compare Database
Option Explicit

Private Sub BTNSEARCH_Click()
    Dim SQL As String
        
    SQL = "SELECT tblJobOrders.ID, tblJobOrders.JobOrderNumber, tblJobOrders.DateWritten, tblJobOrders.IssuedBy," _
        & "tblJobOrders.ISSUEDTO , tblJobOrders.StreetNumber, tblJobOrders.[Street Direction], tblJobOrders.StreetName, tblJobOrders.DESCRIPTION," _
        & "tblJobOrders.RefrenceOtherJobOrder, tblJobOrders.COMPLETEDBY, tblJobOrders.SSESRECOMMENDED, tblJobOrders.SSESPROJECT, tblJobOrders.CIPPRecommended," _
        & "tblJobOrders.CIPPPROJECT, tblJobOrders.MANHOLENUMBER, tblJobOrders.LINENAME, tblJobOrders.DateAssined, tblJobOrders.CrewAssigned," _
        & "tblJobOrders.WorkRequested, tblJobOrders.AssetType, tblJobOrders.ProactiveReactive, tblJobOrders.ActionTriggeredBy, tblJobOrders.OverflowNumber," _
        & "tblJobOrders.DisconnectPermitNumber, tblJobOrders.ContractorWorkOrDamage, tblJobOrders.ContractorName, tblJobOrders.ContractorWorkingForUtility," _
        & "tblJobOrders.UtilityProjectAndContractorName, tblJobOrders.AssignedTo1, tblJobOrders.StartDate1, tblJobOrders.FinishDate1, tblJobOrders.AssignedTo2," _
        & "tblJobOrders.StartDate2, tblJobOrders.FinishDate2, tblJobOrders.AssignedTo3, tblJobOrders.StartDate3, tblJobOrders.FinishDate3, tblJobOrders.AssignedTo4," _
        & "tblJobOrders.StartDate4, tblJobOrders.FinishDate4, tblJobOrders.AssignedTo5, tblJobOrders.StartDate5, tblJobOrders.FinishDate5, tblJobOrders.AssignedTo6," _
        & "tblJobOrders.StartDate6, tblJobOrders.FinishDate6, tblJobOrders.AssignedTo7, tblJobOrders.StartDate7, tblJobOrders.FinishDate7, tblJobOrders.AssignedTo8," _
        & "tblJobOrders.StartDate8, tblJobOrders.FinishDate8, tblJobOrders.JobOrderStatus, tblJobOrders.StatusDate, tblJobOrders.PriorityLevel" _
        & "FROM tblJobOrders" _
        & "Where JobOrderNumber = '" & Me.TXTKEYWORD & "' " _
        & "OR MANHOLENUMBER = '" & Me.TXTKEYWORD & "' " _
        & "ORDER BY tblJobOrders.JobOrderNumber" _

            Me.SUBFRMRESULTS.Form.RecordSource = SQL
            Me.SUBFRMRESULTS.Form.Requery
              

End Sub


the "Me.SUBFRMRESULTS.Form.RecordSource = SQL " is highlighted in yellow, and I have attached a picture of the error. Thanks for the help.
This post has been edited by MOMO88: Sep 12 2017, 11:02 AM
Attached File(s)
Attached File  runtime_error_.JPG ( 23.2K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Sep 12 2017, 11:06 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,629
Joined: 19-June 07
From: SunnySandyEggo


Hi,

It looks like you're missing a space character just before the FROM keyword and WHERE clause.

Try changing this part:

CODE
        & "FROM tblJobOrders" _

to this:

CODE
        & " FROM tblJobOrders " _

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
MOMO88
post Sep 12 2017, 12:29 PM
Post#3



Posts: 53
Joined: 2-August 17



now I am getting a run-time error '3464'

Data type mismatch in criteria expression.
Go to the top of the page
 
theDBguy
post Sep 12 2017, 12:31 PM
Post#4


Access Wiki and Forums Moderator
Posts: 70,629
Joined: 19-June 07
From: SunnySandyEggo


Can you show us the new code and screen shot of the error message? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
MOMO88
post Sep 12 2017, 12:34 PM
Post#5



Posts: 53
Joined: 2-August 17



CODE
Option Compare Database
Option Explicit

Private Sub BTNSEARCH_Click()
    Dim SQL As String
        
    SQL = "SELECT tblJobOrders.ID, tblJobOrders.JobOrderNumber, tblJobOrders.DateWritten, tblJobOrders.IssuedBy," _
        & "tblJobOrders.ISSUEDTO , tblJobOrders.StreetNumber, tblJobOrders.[Street Direction], tblJobOrders.StreetName, tblJobOrders.DESCRIPTION," _
        & "tblJobOrders.RefrenceOtherJobOrder, tblJobOrders.COMPLETEDBY, tblJobOrders.SSESRECOMMENDED, tblJobOrders.SSESPROJECT, tblJobOrders.CIPPRecommended," _
        & "tblJobOrders.CIPPPROJECT, tblJobOrders.MANHOLENUMBER, tblJobOrders.LINENAME, tblJobOrders.DateAssined, tblJobOrders.CrewAssigned," _
        & "tblJobOrders.WorkRequested, tblJobOrders.AssetType, tblJobOrders.ProactiveReactive, tblJobOrders.ActionTriggeredBy, tblJobOrders.OverflowNumber," _
        & "tblJobOrders.DisconnectPermitNumber, tblJobOrders.ContractorWorkOrDamage, tblJobOrders.ContractorName, tblJobOrders.ContractorWorkingForUtility," _
        & "tblJobOrders.UtilityProjectAndContractorName, tblJobOrders.AssignedTo1, tblJobOrders.StartDate1, tblJobOrders.FinishDate1, tblJobOrders.AssignedTo2," _
        & "tblJobOrders.StartDate2, tblJobOrders.FinishDate2, tblJobOrders.AssignedTo3, tblJobOrders.StartDate3, tblJobOrders.FinishDate3, tblJobOrders.AssignedTo4," _
        & "tblJobOrders.StartDate4, tblJobOrders.FinishDate4, tblJobOrders.AssignedTo5, tblJobOrders.StartDate5, tblJobOrders.FinishDate5, tblJobOrders.AssignedTo6," _
        & "tblJobOrders.StartDate6, tblJobOrders.FinishDate6, tblJobOrders.AssignedTo7, tblJobOrders.StartDate7, tblJobOrders.FinishDate7, tblJobOrders.AssignedTo8," _
        & "tblJobOrders.StartDate8, tblJobOrders.FinishDate8, tblJobOrders.JobOrderStatus, tblJobOrders.StatusDate, tblJobOrders.PriorityLevel" _
        & " FROM tblJobOrders " _
        & " Where JobOrderNumber = '" & Me.TXTKEYWORD & "' " _
        & " OR MANHOLENUMBER = '" & Me.TXTKEYWORD & "' " _
        & " ORDER BY tblJobOrders.JobOrderNumber " _

            Me.SUBFRMRESULTS.Form.RecordSource = SQL
            Me.SUBFRMRESULTS.Form.Requery
              

End Sub

Attached File(s)
Attached File  error_2.JPG ( 16.57K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Sep 12 2017, 12:43 PM
Post#6


Access Wiki and Forums Moderator
Posts: 70,629
Joined: 19-June 07
From: SunnySandyEggo


Thanks. Can you please add a Debug.Print SQL and post the result?

By the way, SQL is a "reserved" word in Access, so it might be a good idea to use a different name for your variable.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
MOMO88
post Sep 12 2017, 12:49 PM
Post#7



Posts: 53
Joined: 2-August 17



I don't know how to do that. sorry I am new to this.
Go to the top of the page
 
MadPiet
post Sep 12 2017, 12:55 PM
Post#8



Posts: 2,143
Joined: 27-February 09



This works:

CODE
Option Compare Database
Option Explicit

Public Sub BTNSEARCH(ByVal strKeyword As String)
    Dim SQL As String
        
    SQL = "SELECT tblJobOrders.ID, tblJobOrders.JobOrderNumber, tblJobOrders.DateWritten, tblJobOrders.IssuedBy," _
        & "tblJobOrders.ISSUEDTO , tblJobOrders.StreetNumber, tblJobOrders.[Street Direction], tblJobOrders.StreetName, tblJobOrders.DESCRIPTION," _
        & "tblJobOrders.RefrenceOtherJobOrder, tblJobOrders.COMPLETEDBY, tblJobOrders.SSESRECOMMENDED, tblJobOrders.SSESPROJECT, tblJobOrders.CIPPRecommended," _
        & "tblJobOrders.CIPPPROJECT, tblJobOrders.MANHOLENUMBER, tblJobOrders.LINENAME, tblJobOrders.DateAssined, tblJobOrders.CrewAssigned," _
        & "tblJobOrders.WorkRequested, tblJobOrders.AssetType, tblJobOrders.ProactiveReactive, tblJobOrders.ActionTriggeredBy, tblJobOrders.OverflowNumber," _
        & "tblJobOrders.DisconnectPermitNumber, tblJobOrders.ContractorWorkOrDamage, tblJobOrders.ContractorName, tblJobOrders.ContractorWorkingForUtility," _
        & "tblJobOrders.UtilityProjectAndContractorName, tblJobOrders.AssignedTo1, tblJobOrders.StartDate1, tblJobOrders.FinishDate1, tblJobOrders.AssignedTo2," _
        & "tblJobOrders.StartDate2, tblJobOrders.FinishDate2, tblJobOrders.AssignedTo3, tblJobOrders.StartDate3, tblJobOrders.FinishDate3, tblJobOrders.AssignedTo4," _
        & "tblJobOrders.StartDate4, tblJobOrders.FinishDate4, tblJobOrders.AssignedTo5, tblJobOrders.StartDate5, tblJobOrders.FinishDate5, tblJobOrders.AssignedTo6," _
        & "tblJobOrders.StartDate6, tblJobOrders.FinishDate6, tblJobOrders.AssignedTo7, tblJobOrders.StartDate7, tblJobOrders.FinishDate7, tblJobOrders.AssignedTo8," _
        & "tblJobOrders.StartDate8, tblJobOrders.FinishDate8, tblJobOrders.JobOrderStatus, tblJobOrders.StatusDate, tblJobOrders.PriorityLevel" _
        & " FROM tblJobOrders " _
        & " Where JobOrderNumber = '" & strKeyword & "'" _
        & " OR MANHOLENUMBER = '" & strKeyword & "'" _
        & " ORDER BY tblJobOrders.JobOrderNumber "

Debug.Print SQL

End Sub


I just changed the code so strKeyword gets passed as a string... (and you should sort out the JobOrders.AssignedTo[n], JobOrders.StartDate[n], JobOrders.FinishDate[n] thing sometime.)
Go to the top of the page
 
MOMO88
post Sep 12 2017, 01:00 PM
Post#9



Posts: 53
Joined: 2-August 17



I am working on that currently. This is for the mean time. I know everyone on here hates to hear that, but I am doing what I can
Go to the top of the page
 
MadPiet
post Sep 12 2017, 01:01 PM
Post#10



Posts: 2,143
Joined: 27-February 09



I can do it and send it. Super easy.
Go to the top of the page
 
MOMO88
post Sep 12 2017, 01:20 PM
Post#11



Posts: 53
Joined: 2-August 17



I sure can use all the help I can get.
Go to the top of the page
 
MadPiet
post Sep 12 2017, 01:27 PM
Post#12



Posts: 2,143
Joined: 27-February 09



If you get stuck, holler. I can probably do it pretty easily. As long as the DB looks like the one I have.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th September 2017 - 11:58 AM