471,355 Members | 1,658 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

How to build a Find statement on the fly without the leading/ending Quotes?

Hi All,

I want to build a Find Statement on the fly.
The code below starting with 'This Code Works! is fine but as I said I
want to build the
Me!cboSelect1 & "=" & rs(Me!cboSelect1)
portion of the Find statement below on the fly:
rsTemp1.FindFirst Me!cboSelect1 & "=" & rs(Me!cboSelect1)
on the fly.

The problem with my on the fly code is I end up with a leading and
ending quote(")
"Me!cboSelect1 & "=" & rs(Me!cboSelect1)"
How do I get rid of the leading and ending quotes?

To build my on the fly Find statement:
Me!cboSelect1 & "=" & rs(Me!cboSelect1)
I do this:
If Me!cboSelect1 <"None" Then
vFind1 = "rsTemp1.FindFirst Me!cboSelect1 " & Chr(38) & " ""="" " &
Chr(38) & " rs(Me!cboSelect1)"
endif

The value of vFind1 looks like this:
"Me!cboSelect1 & "=" & rs(Me!cboSelect1)"
It should look like this:
Me!cboSelect1 & "=" & rs(Me!cboSelect1)
'This Code Works!
Dim rs, rsTemp1 as recordset
Dim vFind1 as String
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Set rsTemp1 = CurrentDb.OpenRecordset("tblTemp1", dbOpenDynaset)

If rs.RecordCount 0 Then
Do Until rs.EOF
'I want to put the vFind1 after "rsTemp1.FindFirst" in the line below
rsTemp1.FindFirst Me!cboSelect1 & "=" & rs(Me!cboSelect1)
If rsTemp1.NoMatch = True Then
'Add a Record
If vProjectID <rs![tblProjects.lngProjectID] Then
rsTemp1.AddNew
rsTemp1(Me!cboSelect1) = rs(Me!cboSelect1) 'rs!Dept
rsTemp1!NoProject = 1
rsTemp1!NoComp = 1
rsTemp1!lngProjectID = rs![tblProjects.lngProjectID]
rsTemp1.Update
End If
Else 'Edit existing record.
If vProjectID <rs![tblProjects.lngProjectID] Then
rsTemp1.Edit
rsTemp1!NoProject = rsTemp1!NoProject + 1
rsTemp1!NoComp = rsTemp1!NoComp + 1
rsTemp1.Update
Else
rsTemp1.Edit
rsTemp1!NoComp = rsTemp1!NoComp + 1
rsTemp1.Update
End If
End If
vProjectID = rs![tblProjects.lngProjectID]

rs.MoveNext
Loop
End If

rs.close
set rs = nothing
rsTemp1.close
set rsTemp1 = Nothing
Thanks for your help.
Barry

Dec 12 '06 #1
2 1320
The problem with my on the fly code is I end up with a leading and
ending quote(")
"Me!cboSelect1 & "=" & rs(Me!cboSelect1)"
How do I get rid of the leading and ending quotes?
When your existing code runs, the argument to FindFirst is evaluated,
so what starts out as

rsTemp1.FindFirst Me!cboSelect1 & "=" & rs(Me!cboSelect1)

is evaluated to something like

rsTemp1.FindFirst "FieldName=123"

and then the FindFirst is executed. So, instead of trying to do

vFind1 = "rsTemp1.FindFirst Me!cboSelect1 " & Chr(38) & " ""="" " &
Chr(38) & " rs(Me!cboSelect1)"

you would just do

vFind1 = Me!cboSelect1 & "=" & rs(Me!cboSelect1)
rsTemp1.FindFirst vFind1

which, as it turns out, is exactly what your current code does.
ba*****************@sympatico.ca wrote:
Hi All,

I want to build a Find Statement on the fly.
The code below starting with 'This Code Works! is fine but as I said I
want to build the
Me!cboSelect1 & "=" & rs(Me!cboSelect1)
portion of the Find statement below on the fly:
rsTemp1.FindFirst Me!cboSelect1 & "=" & rs(Me!cboSelect1)
on the fly.

The problem with my on the fly code is I end up with a leading and
ending quote(")
"Me!cboSelect1 & "=" & rs(Me!cboSelect1)"
How do I get rid of the leading and ending quotes?

To build my on the fly Find statement:
Me!cboSelect1 & "=" & rs(Me!cboSelect1)
I do this:
If Me!cboSelect1 <"None" Then
vFind1 = "rsTemp1.FindFirst Me!cboSelect1 " & Chr(38) & " ""="" " &
Chr(38) & " rs(Me!cboSelect1)"
endif

The value of vFind1 looks like this:
"Me!cboSelect1 & "=" & rs(Me!cboSelect1)"
It should look like this:
Me!cboSelect1 & "=" & rs(Me!cboSelect1)
'This Code Works!
Dim rs, rsTemp1 as recordset
Dim vFind1 as String
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Set rsTemp1 = CurrentDb.OpenRecordset("tblTemp1", dbOpenDynaset)

If rs.RecordCount 0 Then
Do Until rs.EOF
'I want to put the vFind1 after "rsTemp1.FindFirst" in the line below
rsTemp1.FindFirst Me!cboSelect1 & "=" & rs(Me!cboSelect1)
If rsTemp1.NoMatch = True Then
'Add a Record
If vProjectID <rs![tblProjects.lngProjectID] Then
rsTemp1.AddNew
rsTemp1(Me!cboSelect1) = rs(Me!cboSelect1) 'rs!Dept
rsTemp1!NoProject = 1
rsTemp1!NoComp = 1
rsTemp1!lngProjectID = rs![tblProjects.lngProjectID]
rsTemp1.Update
End If
Else 'Edit existing record.
If vProjectID <rs![tblProjects.lngProjectID] Then
rsTemp1.Edit
rsTemp1!NoProject = rsTemp1!NoProject + 1
rsTemp1!NoComp = rsTemp1!NoComp + 1
rsTemp1.Update
Else
rsTemp1.Edit
rsTemp1!NoComp = rsTemp1!NoComp + 1
rsTemp1.Update
End If
End If
vProjectID = rs![tblProjects.lngProjectID]

rs.MoveNext
Loop
End If

rs.close
set rs = nothing
rsTemp1.close
set rsTemp1 = Nothing
Thanks for your help.
Barry
Dec 12 '06 #2
Thanks, Gord,

Good explanation, worked like a charm.

Kindest regards,
Barry

Dec 15 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Larry R. Baker | last post: by
10 posts views Thread by Douglas Buchanan | last post: by
3 posts views Thread by Christopher Baldwin | last post: by
4 posts views Thread by wesbland | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.