Syntax Error Help with Recordset

[ajetrumpet]

Can someone else put a pair of eyes on this? I've been programming for too long I think, and something obvious is missing, but can't put a finger on it..

The following line is giving me a syntax error in expression:

rsStockIn.FindFirst ("[pID] = " & !pID & " AND [pExhausted] = 0")

Here is part of the module that is relevant:

rsStockIn.MoveLast
rsStockIn.MoveFirst
   rsStockOutTEMP.MoveLast
   rsStockOutTEMP.MoveFirst

      With rsStockOutTEMP
      
         Do Until .EOF
         
            While sItemRecorded = False
            
               rsStockIn.MoveFirst 'PREP FOR FIND RECORD COMMAND
            
               rsStockIn.FindFirst ("[pID] = " & !pID & " AND [pExhausted] = 0") 
      
'''''MORE CODE'''''''

            Wend
            
               sItemRecorded = False
                  .MoveNext
         Loop
      
      End With

The !pID field is a LONG and Exhausted is a BOOLEAN. I ran on a test with a simple recordset with the exact same line of syntax replacing !pID with a LONG variable that was declared and it worked fine and found the record. What am I missing guys? Thanks for any help!

[RuralGuy]

I would try it without the parens or with them inside of the quotes.
rsStockIn.FindFirst "([pID] = " & !pID & " AND [pExhausted] = 0)"
...or...
rsStockIn.FindFirst "[pID] = " & !pID & " AND [pExhausted] = 0"

[ajetrumpet]

I would try it without the parens or with them inside of the quotes.
rsStockIn.FindFirst "([pID] = " & !pID & " AND [pExhausted] = 0)"
...or...
rsStockIn.FindFirst "[pID] = " & !pID & " AND [pExhausted] = 0"

Allen,

I tried both of those and it still throws the error. Is there anything I could give you that would help you help me? The module is kinda long. - 250 lines

[RuralGuy]

Have you tried importing the db into a new db to eliminate a corruption issue?

[ajetrumpet]

Have you tried importing the db into a new db to eliminate a corruption issue?

is the best way to do that importing all at once or the one corrupted module by itself and then the rest of the obj's all at once? what have you done in the past?

also,

do you think copying the mod code to notepad, deleting the module out and then pasting it back to a new module would work?

[RuralGuy]

I would try all at once to start. If the problem follows then the import will probably not fix the problem regardless of how you do it. I don't know about the clipboard method by Export to Text file, delete and then Import from textfile should work.

[RuralGuy]

A little further help:
Application.SaveAsText(ObjectType AS acObjectType, ObjectName As String, FileName As String)

LoadFromText is identical in its parameters. For example:

Application.SaveAsText(acForm,"frmMyForm","H:\Clie ntX\frmMyForm.txt")

[ssanfu]

             rsStockIn.FindFirst ("[pID] = " & !pID & " AND [pExhausted] = 0")

"!pID" is how you refer to a field in a record source from a form.

When you open a recordset in code, to refer to a field in the recordset, you need to you the ".Fields()" property.


Try this:

 rsStockIn.FindFirst ("[pID] = " & .Fields("pID") & " AND [pExhausted] = FALSE")

------------------------
I use FALSE & TRUE instead of 0 (zero) & -1 in case MS ever decides to change the constants. (FYI: In Excel, FALSE = 0 and TRUE = +1.)
------------------------

[ajetrumpet]

When you open a recordset in code, to refer to a field in the recordset, you need to you the ".Fields()" property.

To refer to the collection's INDEX yes, to the value in that field, no not necessary. There is more than one way. Sorry

thanks for the input though. I will try the booleans. I haven't yet. still stuck with the longs.

[ssanfu]

OK,

Have you tried this??

rsStockIn.MoveLast
rsStockIn.MoveFirst
   rsStockOutTEMP.MoveLast
   rsStockOutTEMP.MoveFirst
 
     Dim Criteria as String
 
      With rsStockOutTEMP
 
         Do Until .EOF
 
            While sItemRecorded = False
                 Criteria = ""
'   FindFirst starts from the the beginning of the recordset
'             rsStockIn.MoveFirst 'PREP FOR FIND RECORD COMMAND
 
                 Criteria = "[pID] = " & !pID & " AND [pExhausted] = FALSE"
                 MsgBox Criteria     ' is the string what you expected???
 
                 rsStockIn.FindFirst (Criteria) 
 
'''''MORE CODE'''''''
            Wend
 
               sItemRecorded = False
                  .MoveNext
         Loop
 
      End With

.

[ajetrumpet]

RG,

the module was corrupt. I put the func. in a new module and took out all the args. the code ran fine. not sure why, but it was the last thing I tried.

thanks for the help

[RuralGuy]

Any time, glad to help.