Can't find my error

[Bill]

In the Sub below, I get the error:
"No value given for one or more required parameters"
on the statement:

rsRoster.Open strSQL, con, adOpenKeyset, adLockOptimistic

What am I missing?
-------------------------------------------------------
Also, I'm not sure about the statement:

rsRoster.Find "[E-mailAddr] = " & Trim(InRec)

Is that a valid method for finding the record within the
recordset?
=============================================
Option Compare Database
Option Explicit
Dim con As ADODB.Connection
Dim rsRoster As ADODB.Recordset
Dim InRec As String
Dim strSQL As String
Public Sub Get_Names_for_badEMAs()

Open "c:\CRS\BadEMAs.txt" For Input As #1 'File of bad e-mail
addresses

Set con = Application.CurrentProject.Connection
strSQL = "Select Lastname, Firstname, e-mailAddr From [ReunionRoster] WHERE
"
strSQL = strSQL & "E-mailAddr = NotNull"
Set rsRoster = CreateObject("ADODB.Recordset")
rsRoster.Open strSQL, con, adOpenKeyset, adLockOptimistic

Line Input #1, InRec 'Get first Record

InRec = Replace(InRec, Chr(9), " ") 'Get rid of any tab
characters

Do While Not EOF(1) ' Loop until end of file.
rsRoster.Find "[E-mailAddr] = " & Trim(InRec)
Debug.Print InRec & " " & rsRoster![LastName] & " " &
rsRoster![FirstName]

Line Input #1, InRec 'Get next record
InRec = Replace(InRec, Chr(9), " ") 'Get rid of any tab
characters
InRec = Trim(InRec)
Loop
Close #1 ' Close file.


rsRoster.Close
Set rsRoster = Nothing
End Sub

 

[Allen Browne]

Try:
strSQL = strSQL & "[E-mailAddr] Is Not Null"

 

[Bill]

VBA didn't like that either.

Try:
strSQL = strSQL & "[E-mailAddr] Is Not Null"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In the Sub below, I get the error:
"No value given for one or more required parameters"
on the statement:

rsRoster.Open strSQL, con, adOpenKeyset, adLockOptimistic

What am I missing?
-------------------------------------------------------
Also, I'm not sure about the statement:

rsRoster.Find "[E-mailAddr] = " & Trim(InRec)

Is that a valid method for finding the record within the
recordset?
=============================================
Option Compare Database
Option Explicit
Dim con As ADODB.Connection
Dim rsRoster As ADODB.Recordset
Dim InRec As String
Dim strSQL As String
Public Sub Get_Names_for_badEMAs()

Open "c:\CRS\BadEMAs.txt" For Input As #1 'File of bad
e-mail addresses

Set con = Application.CurrentProject.Connection
strSQL = "Select Lastname, Firstname, e-mailAddr From [ReunionRoster]
WHERE "
strSQL = strSQL & "E-mailAddr = NotNull"
Set rsRoster = CreateObject("ADODB.Recordset")
rsRoster.Open strSQL, con, adOpenKeyset, adLockOptimistic

Line Input #1, InRec 'Get first
Record

InRec = Replace(InRec, Chr(9), " ") 'Get rid of any
tab characters

Do While Not EOF(1) ' Loop until end of file.
rsRoster.Find "[E-mailAddr] = " & Trim(InRec)
Debug.Print InRec & " " & rsRoster![LastName] & " " &
rsRoster![FirstName]

Line Input #1, InRec 'Get next record
InRec = Replace(InRec, Chr(9), " ") 'Get rid of any tab
characters
InRec = Trim(InRec)
Loop
Close #1 ' Close file.


rsRoster.Close
Set rsRoster = Nothing
End Sub

 

[Allen Browne]

VBA didn't like that either.

"... didn't like it" is not very specific about what error you received.

Here's an example of how to open a recordset in ADO:
http://allenbrowne.com/func-ADO.html#AdoRecordsetExample

If you still have trouble, add the line:
debug.Print strSql
above the attempt to Open the recordset.
When it fails press Ctrl+G to open the immediate window, and compare the
output of your code to what you see in SQL View of a query that does work.

 

[Bill]

Sorry Allen, I should have posted that I was still
getting the "same error".

Anyway, the problem turned out to be that JET/SQL
did not like the "-" in the field name, "E-mailAddr".
I don't know where my head was at the time I created
the field name, as I know that the dash character can
sometimes cause subtle problems in Access.

==============================================
And yes, your website of tips is typically where I go
to find tips and code examples when I encounter
problems. That reminds me, running your DAO
example
(http://allenbrowne.com/func-DAO.html#DAORecordsetExample)
executed on the same table that I'm working with
here, I added:

rs.movelast 'to force completion
rs.recordcount 'to verify all records read, of which there are 314
rs.movefirst 'before loop starts

I also added a record counter to the debug.print intRecCnt ..........

After execution, the immediate window shows record contents
starting at 117 and ending at 314, which implies either a bug in
the code or a limitation of the immediate window. So, I added a
limit to the While loop, intRecCnt < 118. All of the records that
were missing from the first run were in fact displayed in the
immediate window. Is the immediate window limited in how much
it holds.........hard to believe that?????

Bill

 

[Dale Fye]

Yes, the immediate window is limited, and the only way I know of to actually
clear its contents is to put the Debug.Print command in a loop that runs
about 200 times.

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Bill]

Well, it sure is helpful to know that the immediate
window is limited...........saves a whole bunch of
time looking for code and/or table problems.

Thanks,
Bill