By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,485 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.