Runtime Error 3070

D

Don Ireland

Ok,

I am working on another database. I tried coping the code from a previous
database that I developed and keep getting an error - Runtime 3070 - MS Jet
Database Enginer does not recognize "insert entered text here" as a valid
field.

Here is what I am trying to do:

Public Sub ItemFinder(LookingFor As String)
'On Error GoTo Err_ItemFinder

Dim RS As DAO.Recordset
Dim STrWhere As String
Dim Finder, SearchFailed

Select Case LookingFor
Case "Parent"
Finder = InputBox("Enter the last name of the Primary Parent to look
for", "Parent Finder")
Set RS = Forms![Primary].RecordsetClone
STrWhere = "[PrimaryLastName]=" & Finder

Case Else
Finder = MsgBox("Bug in Code.", vbOKOnly, "Invalid Search Parameter")
GoTo ExitRoutine
End Select

If Finder = "" Then GoTo ExitRoutine

RS.FindFirst STrWhere (This is where it craps out)

If RS.nomatch Then
SearchFailed = MsgBox("Item not found", vbExclamation + vbOKOnly,
"Error!")
Else
If LookingFor = "Parent" Then Forms![frmPrimaryInfo].Bookmark =
RS.Bookmark
End If

ExitRoutine:
Set RS = Nothing
Exit Sub

Err_ItemFinder:
MsgBox Err.Description
Resume ExitRoutine

End Sub

This routine works wonderfully well in the previous database - I pass it the
thing I am looking for and it locates it. I have checked the references in
both databases and they match. I started the databases on 2 seperate PC's
but I would think that would not matter since both PC's have the same version
of access installed.

The references I have loaded are :VB for applciations, MS Access 9.0 Object
Library, OLE Automation, ActiveX Data Object 2.1 Library, Office 9.0 Object
Library, MS Forms 2.0 Object Library, MS Windows Common Controls 6.0(SP6), &
MS DAO 3.6 Object Library.

The references are the same for both databases. I'm lost....any help would
be appreciated.

Thanks

Don
 
D

Douglas J. Steele

Since you're dealing with text, you need to put quotes around it.

STrWhere = "[PrimaryLastName]=""" & Finder & """"

or

STrWhere = "[PrimaryLastName]=" & Chr$(34) & Finder & Chr$(34)

As it stands, you're typing Ireland in, and Access interprets

[PrimaryLastName]= Ireland

as an attempt to compare it to a field in the table named Ireland.

The change above will result in

[PrimaryLastName]= "Ireland"

so that Access will realize you're trying to compare to literal text.
 
D

Don Ireland

So then why does it work as coded for the other database?

That is what is confusing me.... I copied the code verbatim and only changed
the field/table names.....I have more cases to look for in the previous
database, but that should not matter...

Here is that code from the other database where it works...

Public Sub NumberFinder(ProjectType As String)
On Error GoTo Err_NumberFinder
Dim rs As DAO.Recordset
Dim STrwhere As String
Dim Finder, SearchFailed


Select Case ProjectType
Case "Job"
Finder = InputBox("Enter Job Number to Look For", "Job Finder")
Set rs = Forms![jobinfo].RecordsetClone
STrwhere = "[JobNumber]=" & Finder
Case "Service Ticket"
Finder = InputBox("Enter Service Ticket Number to Look For",
"Service Ticket Finder")
Set rs = Forms![serviceticketentry].RecordsetClone
STrwhere = "[serviceticketnbr]=" & Finder
Case "Direct Sale"
Finder = InputBox("Enter Direct Sale Number to Look For",
"Direct Sale Finder")
Set rs = Forms![directsalesentry].RecordsetClone
STrwhere = "[directsalenbr]=" & Finder
End Select

If Finder = "" Then GoTo Exitroutine

rs.FindFirst STrwhere
If rs.NoMatch Then
SearchFailed = MsgBox("Item Not Found", vbExclamation + vbOKOnly,
"Error!")
Else
If ProjectType = "Job" Then Forms![jobinfo].Bookmark = rs.Bookmark
If ProjectType = "Service Ticket" Then
Forms![serviceticketentry].Bookmark = rs.Bookmark
If ProjectType = "Direct Sale" Then
Forms![directsalesentry].Bookmark = rs.Bookmark
End If
AddNewServiceTicket = False
Exitroutine:
Set rs = Nothing
Exit Sub

Err_NumberFinder:
MsgBox Err.Description
Resume Exitroutine
End Sub


Douglas J. Steele said:
Since you're dealing with text, you need to put quotes around it.

STrWhere = "[PrimaryLastName]=""" & Finder & """"

or

STrWhere = "[PrimaryLastName]=" & Chr$(34) & Finder & Chr$(34)

As it stands, you're typing Ireland in, and Access interprets

[PrimaryLastName]= Ireland

as an attempt to compare it to a field in the table named Ireland.

The change above will result in

[PrimaryLastName]= "Ireland"

so that Access will realize you're trying to compare to literal text.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Don Ireland said:
Ok,

I am working on another database. I tried coping the code from a previous
database that I developed and keep getting an error - Runtime 3070 - MS
Jet
Database Enginer does not recognize "insert entered text here" as a valid
field.

Here is what I am trying to do:

Public Sub ItemFinder(LookingFor As String)
'On Error GoTo Err_ItemFinder

Dim RS As DAO.Recordset
Dim STrWhere As String
Dim Finder, SearchFailed

Select Case LookingFor
Case "Parent"
Finder = InputBox("Enter the last name of the Primary Parent to
look
for", "Parent Finder")
Set RS = Forms![Primary].RecordsetClone
STrWhere = "[PrimaryLastName]=" & Finder

Case Else
Finder = MsgBox("Bug in Code.", vbOKOnly, "Invalid Search
Parameter")
GoTo ExitRoutine
End Select

If Finder = "" Then GoTo ExitRoutine

RS.FindFirst STrWhere (This is where it craps out)

If RS.nomatch Then
SearchFailed = MsgBox("Item not found", vbExclamation + vbOKOnly,
"Error!")
Else
If LookingFor = "Parent" Then Forms![frmPrimaryInfo].Bookmark =
RS.Bookmark
End If

ExitRoutine:
Set RS = Nothing
Exit Sub

Err_ItemFinder:
MsgBox Err.Description
Resume ExitRoutine

End Sub

This routine works wonderfully well in the previous database - I pass it
the
thing I am looking for and it locates it. I have checked the references
in
both databases and they match. I started the databases on 2 seperate PC's
but I would think that would not matter since both PC's have the same
version
of access installed.

The references I have loaded are :VB for applciations, MS Access 9.0
Object
Library, OLE Automation, ActiveX Data Object 2.1 Library, Office 9.0
Object
Library, MS Forms 2.0 Object Library, MS Windows Common Controls 6.0(SP6),
&
MS DAO 3.6 Object Library.

The references are the same for both databases. I'm lost....any help
would
be appreciated.

Thanks

Don
Click to expand...
Click to expand...
 
D

Don Ireland

Does it matter that the first database (where it works) the field that I am
searching in is a number while in the new database, the field is string?

I tried the multiple quote option and the line turned red - syntax
error......I think I am still missing something......

Any ideas?


Don Ireland said:
So then why does it work as coded for the other database?

That is what is confusing me.... I copied the code verbatim and only changed
the field/table names.....I have more cases to look for in the previous
database, but that should not matter...

Here is that code from the other database where it works...

Public Sub NumberFinder(ProjectType As String)
On Error GoTo Err_NumberFinder
Dim rs As DAO.Recordset
Dim STrwhere As String
Dim Finder, SearchFailed


Select Case ProjectType
Case "Job"
Finder = InputBox("Enter Job Number to Look For", "Job Finder")
Set rs = Forms![jobinfo].RecordsetClone
STrwhere = "[JobNumber]=" & Finder
Case "Service Ticket"
Finder = InputBox("Enter Service Ticket Number to Look For",
"Service Ticket Finder")
Set rs = Forms![serviceticketentry].RecordsetClone
STrwhere = "[serviceticketnbr]=" & Finder
Case "Direct Sale"
Finder = InputBox("Enter Direct Sale Number to Look For",
"Direct Sale Finder")
Set rs = Forms![directsalesentry].RecordsetClone
STrwhere = "[directsalenbr]=" & Finder
End Select

If Finder = "" Then GoTo Exitroutine

rs.FindFirst STrwhere
If rs.NoMatch Then
SearchFailed = MsgBox("Item Not Found", vbExclamation + vbOKOnly,
"Error!")
Else
If ProjectType = "Job" Then Forms![jobinfo].Bookmark = rs.Bookmark
If ProjectType = "Service Ticket" Then
Forms![serviceticketentry].Bookmark = rs.Bookmark
If ProjectType = "Direct Sale" Then
Forms![directsalesentry].Bookmark = rs.Bookmark
End If
AddNewServiceTicket = False
Exitroutine:
Set rs = Nothing
Exit Sub

Err_NumberFinder:
MsgBox Err.Description
Resume Exitroutine
End Sub


Douglas J. Steele said:
Since you're dealing with text, you need to put quotes around it.

STrWhere = "[PrimaryLastName]=""" & Finder & """"

or

STrWhere = "[PrimaryLastName]=" & Chr$(34) & Finder & Chr$(34)

As it stands, you're typing Ireland in, and Access interprets

[PrimaryLastName]= Ireland

as an attempt to compare it to a field in the table named Ireland.

The change above will result in

[PrimaryLastName]= "Ireland"

so that Access will realize you're trying to compare to literal text.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Don Ireland said:
Ok,

I am working on another database. I tried coping the code from a previous
database that I developed and keep getting an error - Runtime 3070 - MS
Jet
Database Enginer does not recognize "insert entered text here" as a valid
field.

Here is what I am trying to do:

Public Sub ItemFinder(LookingFor As String)
'On Error GoTo Err_ItemFinder

Dim RS As DAO.Recordset
Dim STrWhere As String
Dim Finder, SearchFailed

Select Case LookingFor
Case "Parent"
Finder = InputBox("Enter the last name of the Primary Parent to
look
for", "Parent Finder")
Set RS = Forms![Primary].RecordsetClone
STrWhere = "[PrimaryLastName]=" & Finder

Case Else
Finder = MsgBox("Bug in Code.", vbOKOnly, "Invalid Search
Parameter")
GoTo ExitRoutine
End Select

If Finder = "" Then GoTo ExitRoutine

RS.FindFirst STrWhere (This is where it craps out)

If RS.nomatch Then
SearchFailed = MsgBox("Item not found", vbExclamation + vbOKOnly,
"Error!")
Else
If LookingFor = "Parent" Then Forms![frmPrimaryInfo].Bookmark =
RS.Bookmark
End If

ExitRoutine:
Set RS = Nothing
Exit Sub

Err_ItemFinder:
MsgBox Err.Description
Resume ExitRoutine

End Sub

This routine works wonderfully well in the previous database - I pass it
the
thing I am looking for and it locates it. I have checked the references
in
both databases and they match. I started the databases on 2 seperate PC's
but I would think that would not matter since both PC's have the same
version
of access installed.

The references I have loaded are :VB for applciations, MS Access 9.0
Object
Library, OLE Automation, ActiveX Data Object 2.1 Library, Office 9.0
Object
Library, MS Forms 2.0 Object Library, MS Windows Common Controls 6.0(SP6),
&
MS DAO 3.6 Object Library.

The references are the same for both databases. I'm lost....any help
would
be appreciated.

Thanks

Don
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Yes, it definitely matters that it's numeric in one case, and text in the
other case.

What exactly did you enter that turned red?

In the first scenario, it's 3 double quotes before the variable name, and 4
double quotes after:

STrWhere = " [PrimaryLastName]= " " " & Finder & " " " "

It's for that reason that I prefer the second syntax.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Don Ireland said:
Does it matter that the first database (where it works) the field that I
am
searching in is a number while in the new database, the field is string?

I tried the multiple quote option and the line turned red - syntax
error......I think I am still missing something......

Any ideas?


Don Ireland said:
So then why does it work as coded for the other database?

That is what is confusing me.... I copied the code verbatim and only
changed
the field/table names.....I have more cases to look for in the previous
database, but that should not matter...

Here is that code from the other database where it works...

Public Sub NumberFinder(ProjectType As String)
On Error GoTo Err_NumberFinder
Dim rs As DAO.Recordset
Dim STrwhere As String
Dim Finder, SearchFailed


Select Case ProjectType
Case "Job"
Finder = InputBox("Enter Job Number to Look For", "Job
Finder")
Set rs = Forms![jobinfo].RecordsetClone
STrwhere = "[JobNumber]=" & Finder
Case "Service Ticket"
Finder = InputBox("Enter Service Ticket Number to Look For",
"Service Ticket Finder")
Set rs = Forms![serviceticketentry].RecordsetClone
STrwhere = "[serviceticketnbr]=" & Finder
Case "Direct Sale"
Finder = InputBox("Enter Direct Sale Number to Look For",
"Direct Sale Finder")
Set rs = Forms![directsalesentry].RecordsetClone
STrwhere = "[directsalenbr]=" & Finder
End Select

If Finder = "" Then GoTo Exitroutine

rs.FindFirst STrwhere
If rs.NoMatch Then
SearchFailed = MsgBox("Item Not Found", vbExclamation + vbOKOnly,
"Error!")
Else
If ProjectType = "Job" Then Forms![jobinfo].Bookmark =
rs.Bookmark
If ProjectType = "Service Ticket" Then
Forms![serviceticketentry].Bookmark = rs.Bookmark
If ProjectType = "Direct Sale" Then
Forms![directsalesentry].Bookmark = rs.Bookmark
End If
AddNewServiceTicket = False
Exitroutine:
Set rs = Nothing
Exit Sub

Err_NumberFinder:
MsgBox Err.Description
Resume Exitroutine
End Sub


Douglas J. Steele said:
Since you're dealing with text, you need to put quotes around it.

STrWhere = "[PrimaryLastName]=""" & Finder & """"

or

STrWhere = "[PrimaryLastName]=" & Chr$(34) & Finder & Chr$(34)

As it stands, you're typing Ireland in, and Access interprets

[PrimaryLastName]= Ireland

as an attempt to compare it to a field in the table named Ireland.

The change above will result in

[PrimaryLastName]= "Ireland"

so that Access will realize you're trying to compare to literal text.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok,

I am working on another database. I tried coping the code from a
previous
database that I developed and keep getting an error - Runtime 3070 -
MS
Jet
Database Enginer does not recognize "insert entered text here" as a
valid
field.

Here is what I am trying to do:

Public Sub ItemFinder(LookingFor As String)
'On Error GoTo Err_ItemFinder

Dim RS As DAO.Recordset
Dim STrWhere As String
Dim Finder, SearchFailed

Select Case LookingFor
Case "Parent"
Finder = InputBox("Enter the last name of the Primary Parent
to
look
for", "Parent Finder")
Set RS = Forms![Primary].RecordsetClone
STrWhere = "[PrimaryLastName]=" & Finder

Case Else
Finder = MsgBox("Bug in Code.", vbOKOnly, "Invalid Search
Parameter")
GoTo ExitRoutine
End Select

If Finder = "" Then GoTo ExitRoutine

RS.FindFirst STrWhere (This is where it craps out)

If RS.nomatch Then
SearchFailed = MsgBox("Item not found", vbExclamation + vbOKOnly,
"Error!")
Else
If LookingFor = "Parent" Then Forms![frmPrimaryInfo].Bookmark =
RS.Bookmark
End If

ExitRoutine:
Set RS = Nothing
Exit Sub

Err_ItemFinder:
MsgBox Err.Description
Resume ExitRoutine

End Sub

This routine works wonderfully well in the previous database - I pass
it
the
thing I am looking for and it locates it. I have checked the
references
in
both databases and they match. I started the databases on 2 seperate
PC's
but I would think that would not matter since both PC's have the same
version
of access installed.

The references I have loaded are :VB for applciations, MS Access 9.0
Object
Library, OLE Automation, ActiveX Data Object 2.1 Library, Office 9.0
Object
Library, MS Forms 2.0 Object Library, MS Windows Common Controls
6.0(SP6),
&
MS DAO 3.6 Object Library.

The references are the same for both databases. I'm lost....any help
would
be appreciated.

Thanks

Don
Click to expand...
Click to expand...
Click to expand...
 
D

Don Ireland

Doug,

I entered the 3 double quotes before the variable name and 4 after it. That
was the line that turned red..... Not sure why but it did.

I tried the second syntax that you offered and it worked very well. I guess
I need to know what/when to use certain syntax. Perhaps it is time for a
course in programming or at least a good book... any recommendations?

Thanks again for the help.

On a similar topic, how would I do a search for a partial string? For
example, if I wanted to find a record where the last name contained a string
of text (knew partial spelling and that was it....)

Any ideas?


Could I

Douglas J. Steele said:
Yes, it definitely matters that it's numeric in one case, and text in the
other case.

What exactly did you enter that turned red?

In the first scenario, it's 3 double quotes before the variable name, and 4
double quotes after:

STrWhere = " [PrimaryLastName]= " " " & Finder & " " " "

It's for that reason that I prefer the second syntax.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Don Ireland said:
Does it matter that the first database (where it works) the field that I
am
searching in is a number while in the new database, the field is string?

I tried the multiple quote option and the line turned red - syntax
error......I think I am still missing something......

Any ideas?


Don Ireland said:
So then why does it work as coded for the other database?

That is what is confusing me.... I copied the code verbatim and only
changed
the field/table names.....I have more cases to look for in the previous
database, but that should not matter...

Here is that code from the other database where it works...

Public Sub NumberFinder(ProjectType As String)
On Error GoTo Err_NumberFinder
Dim rs As DAO.Recordset
Dim STrwhere As String
Dim Finder, SearchFailed


Select Case ProjectType
Case "Job"
Finder = InputBox("Enter Job Number to Look For", "Job
Finder")
Set rs = Forms![jobinfo].RecordsetClone
STrwhere = "[JobNumber]=" & Finder
Case "Service Ticket"
Finder = InputBox("Enter Service Ticket Number to Look For",
"Service Ticket Finder")
Set rs = Forms![serviceticketentry].RecordsetClone
STrwhere = "[serviceticketnbr]=" & Finder
Case "Direct Sale"
Finder = InputBox("Enter Direct Sale Number to Look For",
"Direct Sale Finder")
Set rs = Forms![directsalesentry].RecordsetClone
STrwhere = "[directsalenbr]=" & Finder
End Select

If Finder = "" Then GoTo Exitroutine

rs.FindFirst STrwhere
If rs.NoMatch Then
SearchFailed = MsgBox("Item Not Found", vbExclamation + vbOKOnly,
"Error!")
Else
If ProjectType = "Job" Then Forms![jobinfo].Bookmark =
rs.Bookmark
If ProjectType = "Service Ticket" Then
Forms![serviceticketentry].Bookmark = rs.Bookmark
If ProjectType = "Direct Sale" Then
Forms![directsalesentry].Bookmark = rs.Bookmark
End If
AddNewServiceTicket = False
Exitroutine:
Set rs = Nothing
Exit Sub

Err_NumberFinder:
MsgBox Err.Description
Resume Exitroutine
End Sub


:

Since you're dealing with text, you need to put quotes around it.

STrWhere = "[PrimaryLastName]=""" & Finder & """"

or

STrWhere = "[PrimaryLastName]=" & Chr$(34) & Finder & Chr$(34)

As it stands, you're typing Ireland in, and Access interprets

[PrimaryLastName]= Ireland

as an attempt to compare it to a field in the table named Ireland.

The change above will result in

[PrimaryLastName]= "Ireland"

so that Access will realize you're trying to compare to literal text.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok,

I am working on another database. I tried coping the code from a
previous
database that I developed and keep getting an error - Runtime 3070 -
MS
Jet
Database Enginer does not recognize "insert entered text here" as a
valid
field.

Here is what I am trying to do:

Public Sub ItemFinder(LookingFor As String)
'On Error GoTo Err_ItemFinder

Dim RS As DAO.Recordset
Dim STrWhere As String
Dim Finder, SearchFailed

Select Case LookingFor
Case "Parent"
Finder = InputBox("Enter the last name of the Primary Parent
to
look
for", "Parent Finder")
Set RS = Forms![Primary].RecordsetClone
STrWhere = "[PrimaryLastName]=" & Finder

Case Else
Finder = MsgBox("Bug in Code.", vbOKOnly, "Invalid Search
Parameter")
GoTo ExitRoutine
End Select

If Finder = "" Then GoTo ExitRoutine

RS.FindFirst STrWhere (This is where it craps out)

If RS.nomatch Then
SearchFailed = MsgBox("Item not found", vbExclamation + vbOKOnly,
"Error!")
Else
If LookingFor = "Parent" Then Forms![frmPrimaryInfo].Bookmark =
RS.Bookmark
End If

ExitRoutine:
Set RS = Nothing
Exit Sub

Err_ItemFinder:
MsgBox Err.Description
Resume ExitRoutine

End Sub

This routine works wonderfully well in the previous database - I pass
it
the
thing I am looking for and it locates it. I have checked the
references
in
both databases and they match. I started the databases on 2 seperate
PC's
but I would think that would not matter since both PC's have the same
version
of access installed.

The references I have loaded are :VB for applciations, MS Access 9.0
Object
Library, OLE Automation, ActiveX Data Object 2.1 Library, Office 9.0
Object
Library, MS Forms 2.0 Object Library, MS Windows Common Controls
6.0(SP6),
&
MS DAO 3.6 Object Library.

The references are the same for both databases. I'm lost....any help
would
be appreciated.

Thanks

Don
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

STrWhere = " [PrimaryLastName] Like " " * " & Finder & " * " " "

or

STrWhere = " [PrimaryLastName] Like " & Chr$(34) & "*" & Finder & "*" &
Chr$(34)

This will look for what's in Finder anywhere in the string. If you only want
to look for names that start with what's in Finder, use

STrWhere = " [PrimaryLastName] Like " " " & Finder & " * " " "

or

STrWhere = " [PrimaryLastName] Like " & Chr$(34) & Finder & "*" & Chr$(34)

To be honest, I'm not sure you'll find specifics like this in a book. Jeff
Conrad has a list that points to many good lists of books at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Don Ireland said:
Doug,

I entered the 3 double quotes before the variable name and 4 after it.
That
was the line that turned red..... Not sure why but it did.

I tried the second syntax that you offered and it worked very well. I
guess
I need to know what/when to use certain syntax. Perhaps it is time for a
course in programming or at least a good book... any recommendations?

Thanks again for the help.

On a similar topic, how would I do a search for a partial string? For
example, if I wanted to find a record where the last name contained a
string
of text (knew partial spelling and that was it....)

Any ideas?


Could I

Douglas J. Steele said:
Yes, it definitely matters that it's numeric in one case, and text in the
other case.

What exactly did you enter that turned red?

In the first scenario, it's 3 double quotes before the variable name, and
4
double quotes after:

STrWhere = " [PrimaryLastName]= " " " & Finder & " " " "

It's for that reason that I prefer the second syntax.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Don Ireland said:
Does it matter that the first database (where it works) the field that
I
am
searching in is a number while in the new database, the field is
string?

I tried the multiple quote option and the line turned red - syntax
error......I think I am still missing something......

Any ideas?


:

So then why does it work as coded for the other database?

That is what is confusing me.... I copied the code verbatim and only
changed
the field/table names.....I have more cases to look for in the
previous
database, but that should not matter...

Here is that code from the other database where it works...

Public Sub NumberFinder(ProjectType As String)
On Error GoTo Err_NumberFinder
Dim rs As DAO.Recordset
Dim STrwhere As String
Dim Finder, SearchFailed


Select Case ProjectType
Case "Job"
Finder = InputBox("Enter Job Number to Look For", "Job
Finder")
Set rs = Forms![jobinfo].RecordsetClone
STrwhere = "[JobNumber]=" & Finder
Case "Service Ticket"
Finder = InputBox("Enter Service Ticket Number to Look
For",
"Service Ticket Finder")
Set rs = Forms![serviceticketentry].RecordsetClone
STrwhere = "[serviceticketnbr]=" & Finder
Case "Direct Sale"
Finder = InputBox("Enter Direct Sale Number to Look For",
"Direct Sale Finder")
Set rs = Forms![directsalesentry].RecordsetClone
STrwhere = "[directsalenbr]=" & Finder
End Select

If Finder = "" Then GoTo Exitroutine

rs.FindFirst STrwhere
If rs.NoMatch Then
SearchFailed = MsgBox("Item Not Found", vbExclamation +
vbOKOnly,
"Error!")
Else
If ProjectType = "Job" Then Forms![jobinfo].Bookmark =
rs.Bookmark
If ProjectType = "Service Ticket" Then
Forms![serviceticketentry].Bookmark = rs.Bookmark
If ProjectType = "Direct Sale" Then
Forms![directsalesentry].Bookmark = rs.Bookmark
End If
AddNewServiceTicket = False
Exitroutine:
Set rs = Nothing
Exit Sub

Err_NumberFinder:
MsgBox Err.Description
Resume Exitroutine
End Sub


:

Since you're dealing with text, you need to put quotes around it.

STrWhere = "[PrimaryLastName]=""" & Finder & """"

or

STrWhere = "[PrimaryLastName]=" & Chr$(34) & Finder & Chr$(34)

As it stands, you're typing Ireland in, and Access interprets

[PrimaryLastName]= Ireland

as an attempt to compare it to a field in the table named Ireland.

The change above will result in

[PrimaryLastName]= "Ireland"

so that Access will realize you're trying to compare to literal
text.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Ok,

I am working on another database. I tried coping the code from a
previous
database that I developed and keep getting an error - Runtime
3070 -
MS
Jet
Database Enginer does not recognize "insert entered text here" as
a
valid
field.

Here is what I am trying to do:

Public Sub ItemFinder(LookingFor As String)
'On Error GoTo Err_ItemFinder

Dim RS As DAO.Recordset
Dim STrWhere As String
Dim Finder, SearchFailed

Select Case LookingFor
Case "Parent"
Finder = InputBox("Enter the last name of the Primary
Parent
to
look
for", "Parent Finder")
Set RS = Forms![Primary].RecordsetClone
STrWhere = "[PrimaryLastName]=" & Finder

Case Else
Finder = MsgBox("Bug in Code.", vbOKOnly, "Invalid Search
Parameter")
GoTo ExitRoutine
End Select

If Finder = "" Then GoTo ExitRoutine

RS.FindFirst STrWhere (This is where it craps out)

If RS.nomatch Then
SearchFailed = MsgBox("Item not found", vbExclamation +
vbOKOnly,
"Error!")
Else
If LookingFor = "Parent" Then Forms![frmPrimaryInfo].Bookmark =
RS.Bookmark
End If

ExitRoutine:
Set RS = Nothing
Exit Sub

Err_ItemFinder:
MsgBox Err.Description
Resume ExitRoutine

End Sub

This routine works wonderfully well in the previous database - I
pass
it
the
thing I am looking for and it locates it. I have checked the
references
in
both databases and they match. I started the databases on 2
seperate
PC's
but I would think that would not matter since both PC's have the
same
version
of access installed.

The references I have loaded are :VB for applciations, MS Access
9.0
Object
Library, OLE Automation, ActiveX Data Object 2.1 Library, Office
9.0
Object
Library, MS Forms 2.0 Object Library, MS Windows Common Controls
6.0(SP6),
&
MS DAO 3.6 Object Library.

The references are the same for both databases. I'm lost....any
help
would
be appreciated.

Thanks

Don
Click to expand...
Click to expand...
Click to expand...
 
D

Don Ireland

Thanks Doug for the help..

I very much appreciate it.

Don

Douglas J. Steele said:
STrWhere = " [PrimaryLastName] Like " " * " & Finder & " * " " "

or

STrWhere = " [PrimaryLastName] Like " & Chr$(34) & "*" & Finder & "*" &
Chr$(34)

This will look for what's in Finder anywhere in the string. If you only want
to look for names that start with what's in Finder, use

STrWhere = " [PrimaryLastName] Like " " " & Finder & " * " " "

or

STrWhere = " [PrimaryLastName] Like " & Chr$(34) & Finder & "*" & Chr$(34)

To be honest, I'm not sure you'll find specifics like this in a book. Jeff
Conrad has a list that points to many good lists of books at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Don Ireland said:
Doug,

I entered the 3 double quotes before the variable name and 4 after it.
That
was the line that turned red..... Not sure why but it did.

I tried the second syntax that you offered and it worked very well. I
guess
I need to know what/when to use certain syntax. Perhaps it is time for a
course in programming or at least a good book... any recommendations?

Thanks again for the help.

On a similar topic, how would I do a search for a partial string? For
example, if I wanted to find a record where the last name contained a
string
of text (knew partial spelling and that was it....)

Any ideas?


Could I

Douglas J. Steele said:
Yes, it definitely matters that it's numeric in one case, and text in the
other case.

What exactly did you enter that turned red?

In the first scenario, it's 3 double quotes before the variable name, and
4
double quotes after:

STrWhere = " [PrimaryLastName]= " " " & Finder & " " " "

It's for that reason that I prefer the second syntax.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Does it matter that the first database (where it works) the field that
I
am
searching in is a number while in the new database, the field is
string?

I tried the multiple quote option and the line turned red - syntax
error......I think I am still missing something......

Any ideas?


:

So then why does it work as coded for the other database?

That is what is confusing me.... I copied the code verbatim and only
changed
the field/table names.....I have more cases to look for in the
previous
database, but that should not matter...

Here is that code from the other database where it works...

Public Sub NumberFinder(ProjectType As String)
On Error GoTo Err_NumberFinder
Dim rs As DAO.Recordset
Dim STrwhere As String
Dim Finder, SearchFailed


Select Case ProjectType
Case "Job"
Finder = InputBox("Enter Job Number to Look For", "Job
Finder")
Set rs = Forms![jobinfo].RecordsetClone
STrwhere = "[JobNumber]=" & Finder
Case "Service Ticket"
Finder = InputBox("Enter Service Ticket Number to Look
For",
"Service Ticket Finder")
Set rs = Forms![serviceticketentry].RecordsetClone
STrwhere = "[serviceticketnbr]=" & Finder
Case "Direct Sale"
Finder = InputBox("Enter Direct Sale Number to Look For",
"Direct Sale Finder")
Set rs = Forms![directsalesentry].RecordsetClone
STrwhere = "[directsalenbr]=" & Finder
End Select

If Finder = "" Then GoTo Exitroutine

rs.FindFirst STrwhere
If rs.NoMatch Then
SearchFailed = MsgBox("Item Not Found", vbExclamation +
vbOKOnly,
"Error!")
Else
If ProjectType = "Job" Then Forms![jobinfo].Bookmark =
rs.Bookmark
If ProjectType = "Service Ticket" Then
Forms![serviceticketentry].Bookmark = rs.Bookmark
If ProjectType = "Direct Sale" Then
Forms![directsalesentry].Bookmark = rs.Bookmark
End If
AddNewServiceTicket = False
Exitroutine:
Set rs = Nothing
Exit Sub

Err_NumberFinder:
MsgBox Err.Description
Resume Exitroutine
End Sub


:

Since you're dealing with text, you need to put quotes around it.

STrWhere = "[PrimaryLastName]=""" & Finder & """"

or

STrWhere = "[PrimaryLastName]=" & Chr$(34) & Finder & Chr$(34)

As it stands, you're typing Ireland in, and Access interprets

[PrimaryLastName]= Ireland

as an attempt to compare it to a field in the table named Ireland.

The change above will result in

[PrimaryLastName]= "Ireland"

so that Access will realize you're trying to compare to literal
text.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Ok,

I am working on another database. I tried coping the code from a
previous
database that I developed and keep getting an error - Runtime
3070 -
MS
Jet
Database Enginer does not recognize "insert entered text here" as
a
valid
field.

Here is what I am trying to do:

Public Sub ItemFinder(LookingFor As String)
'On Error GoTo Err_ItemFinder

Dim RS As DAO.Recordset
Dim STrWhere As String
Dim Finder, SearchFailed

Select Case LookingFor
Case "Parent"
Finder = InputBox("Enter the last name of the Primary
Parent
to
look
for", "Parent Finder")
Set RS = Forms![Primary].RecordsetClone
STrWhere = "[PrimaryLastName]=" & Finder

Case Else
Finder = MsgBox("Bug in Code.", vbOKOnly, "Invalid Search
Parameter")
GoTo ExitRoutine
End Select

If Finder = "" Then GoTo ExitRoutine

RS.FindFirst STrWhere (This is where it craps out)

If RS.nomatch Then
SearchFailed = MsgBox("Item not found", vbExclamation +
vbOKOnly,
"Error!")
Else
If LookingFor = "Parent" Then Forms![frmPrimaryInfo].Bookmark =
RS.Bookmark
End If

ExitRoutine:
Set RS = Nothing
Exit Sub

Err_ItemFinder:
MsgBox Err.Description
Resume ExitRoutine

End Sub

This routine works wonderfully well in the previous database - I
pass
it
the
thing I am looking for and it locates it. I have checked the
references
in
both databases and they match. I started the databases on 2
seperate
PC's
but I would think that would not matter since both PC's have the
same
version
of access installed.

The references I have loaded are :VB for applciations, MS Access
9.0
Object
Library, OLE Automation, ActiveX Data Object 2.1 Library, Office
9.0
Object
Library, MS Forms 2.0 Object Library, MS Windows Common Controls
6.0(SP6),
&
MS DAO 3.6 Object Library.

The references are the same for both databases. I'm lost....any
help
would
be appreciated.

Thanks

Don
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
RunTime Error 3070 11
Error loadingtype library/DLL. 0
Data type conversion error 0
Selecting rows in Listbox 7
runtime Error.. cant assign value to this object 0
Error with automation - Access 2007 creating Outlook calendar appo 1
Run-Time Error 3070 when using FindFirst 0

Top