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