Finding matching records on a form

G

grantschneider

I have created a form where the SVP field is a record selector and
the
other fields are just used to change the parameters of the query.
They
dont seem to work however. Here is my SQL:

SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, BEShare.SVP, IIf([Master Sales Forecast].SVP Is
Null,0,([svpa]*forms![share comm]!svpa+[svpg]*forms![share comm]!svpg
+
[svpn]*forms![share comm]!svpn+[svpr]*forms![share comm]!svpr-
[vpcom])*forms![share comm]!svpt) AS SVPCom, BEShare.VP, IIf([Master
Sales Forecast].VP Is Null,0,([vpa]*forms![share comm]!vpa+
[vpg]*forms!
[share comm]!vpg+[vpn]*forms![share comm]!vpn+[vpr]*forms![share
comm]!
vpr)*forms![share comm]!vpt*forms![share comm]!vps) AS VPCom,
BEShare.SC, IIf([Master Sales Forecast].SC Is Null,0,([sca]*forms!
[share comm]!sca+[scg]*forms![share comm]!scg+[scn]*forms![share
comm]!
scn+[scr]*forms![share comm]!scr)*forms![share comm]!SCT*forms![share
comm]!sct) AS SCCom, BEShare.EA
FROM [Master Sales Forecast] INNER JOIN BEShare ON [Master Sales
Forecast].ID = BEShare.ID
WHERE ([BEShare].SVP = [Forms]![Share Comm]![SVP]
OR [Forms]![Share Comm]![SVP] IS NULL)
AND ([BEShare].VP = [Forms]![Share Comm]![VP]
OR [Forms]![Share Comm]![VP] IS NULL)
AND ([BEShare].EA = [Forms]![Share Comm]![EA]
OR [Forms]![Share Comm]![EA] IS NULL)
AND ([BEShare].SC = [Forms]![Share Comm]![SC]
OR [Forms]![Share Comm]![SC] IS NULL);



I use the SVP field to find the matching record but it sends me to the
debugger. Here is my code:

Private Sub SVP_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![SVP], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub




It highlights this line:
rs.FindFirst "[ID] = " & Str(Nz(Me!SVP], 0))
 

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


Top