Docmd delete query

[JoanieB]

I am trying to delete the current record in a recordset. Can I do this without specifying "WHERE" in my statement?

Do Until Cusip <> RS!Cusip

If RS!SegInd = True Then
BBHFx = RS!FoxBal
BBHBl = RS!BlushBal
BBHCC = RS!OptCBal
BBHRR = RS!OptRBal
FName = RS!FundName
FDesc = RS!FundDesc
AcctName = RS!AcctName
AcctNum = RS!Acct6
NewFBal = BBHFx * -1
NewBBal = BBHBl * -1
NewCBal = BBHCC * -1
NewRBal = BBHRR * -1
DoCmd.RunSQL &quot;INSERT INTO TblInput; etc {is LONG}&quot;
DoCmd.RunSQL (&quot;DELETE * From TblNewRec2 ?WHERE? &quot
'**TblNewRec2 is RS recordset**
End If

Loop

I just want to delete this current record. I have tried using the &quot;WHERE&quot; clause to specify, but when I compare the table's field to my variables (cusip, acctnum, etc) it prompts me to enter a value for those variables instead of using the ones I initialized already.

 

[JoanieB]

P.S.
I am using Access 97 if that makes a difference.

 

[jimmythegeek]

If your variable is cusip, then the syntax would look like this if cusip is a number field (change Fieldname to the actual field you are comparing):

DoCmd.RunSQL &quot;DELETE * FROM tblNewRec2 WHERE Fieldname = &quot; & cusip

And it would look like this if cusip is a text field:

DoCmd.RunSQL &quot;DELETE * FROM tblNewRec2 WHERE Fieldname = '&quot; & cusip & &quot;'&quot;
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[JoanieB]

Thanks - that worked, sort of - can I compare more than one field? (I need two qualifiers here). It tells me there is a type mismatch, but the variables are defined as strings and the table they are to be deleted from has them defined as text fields.

DoCmd.RunSQL &quot;DELETE * FROM tblNewRec2 WHERE Cusip = '&quot; & Cusip & &quot;'&quot; And Acct6 = &quot;'&quot; & AcctNum & &quot;'&quot;

I tried messing around with the quotes, but...

 

[WildHare]

DoCmd.RunSQL &quot;DELETE * FROM tblNewRec2 WHERE Cusip = '&quot; & Cusip & &quot;'&quot; And Acct6 = &quot;'&quot; & AcctNum & &quot;'&quot;

I tried messing around with the quotes, but...

DoCmd.RunSQL "DELETE * FROM tblNewRec2 
WHERE Cusip = '" & Cusip & "'"

[red] & &quot; And Acct6 = '&quot; [/red]

& AcctNum & "'"

You just didn't mess around long enough...

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[JoanieB]

Yeah, WildHare, I think I've heard that before...
Thanks for your suggestion. It worked!