Ref: Tim Ferguson 5/20 response to "Splitter - DAO - Error
3219"

In the referenced response it was correctly pointed out
that dbOpenTable does not work when working with linked
tables. He went on to say "It's a good idea to use a
SELECT query rather than a table name too: there really is
normally no reason to open a whole table."

I was able to open the link following his instructions but
continued to run into problems with code that looked
something like this:
SQLstatement = "SELECT [Bio_Recid] FROM [Applicant
Biographical] WERE [Bio_Recid] = cboBio_Recid"
Set rstAB = CurDB.OpenRecordset (SQLstatement)

I kept getting a "Too few parameters. Expected 1" error
message until I took off the WHERE clause. I did some
research and found the following from MVP Larry
Linson: "Alas, Access front-ends do lose track of the
record when you use the server equivalent of AutoNumber."
I think the WHERE clause didn't work because I was using
linked tables and Bio_Recid is an autonumber field.

Although I think I can work around this, the code has
gotten really ugly at this point. For example,

SQLstatement = "SELECT * FROM [Applicant Biographical]"
Set rstAB = CurDB.OpenRecordset (SQLstatement)
With rstAB
While Not Bio_Recid = Me!cboBio_Recid2
If .EOF = True then
blnFlag = False
End If
.MoveNext
Loop

There has to be a better way! Any suggestions on this
would be appreciated (and, yes, I am 'not' working as a
programmer for my living).

The problem is that you need to concatenate together the SQL string and the
value from the combobox. You currently have the name of the combobox inside
the quotes, which makes it part of the string and so it is ignored until the
query tries to run then it looks for a parameter called cboBio_Recid.

Amend your query to this and see if it works:
If the value of cboBio_Recid is a number
SQLstatement = "SELECT [Bio_Recid] FROM [Applicant Biographical] WERE
[Bio_Recid] =" & cboBio_Recid

or if the value of cboBio_Recid is a text value
SQLstatement = "SELECT [Bio_Recid] FROM [Applicant Biographical] WERE
[Bio_Recid] ='" & cboBio_Recid & "'"
--
Wayne Morgan
Microsoft Access MVP


"Scott" <(E-Mail Removed)> wrote in message
news:118a201c441e4$9e310c60$(E-Mail Removed)...
> Ref: Tim Ferguson 5/20 response to "Splitter - DAO - Error
> 3219"
>
> In the referenced response it was correctly pointed out
> that dbOpenTable does not work when working with linked
> tables. He went on to say "It's a good idea to use a
> SELECT query rather than a table name too: there really is
> normally no reason to open a whole table."
>
> I was able to open the link following his instructions but
> continued to run into problems with code that looked
> something like this:
> SQLstatement = "SELECT [Bio_Recid] FROM [Applicant
> Biographical] WERE [Bio_Recid] = cboBio_Recid"
> Set rstAB = CurDB.OpenRecordset (SQLstatement)
>
> I kept getting a "Too few parameters. Expected 1" error
> message until I took off the WHERE clause. I did some
> research and found the following from MVP Larry
> Linson: "Alas, Access front-ends do lose track of the
> record when you use the server equivalent of AutoNumber."
> I think the WHERE clause didn't work because I was using
> linked tables and Bio_Recid is an autonumber field.
>
> Although I think I can work around this, the code has
> gotten really ugly at this point. For example,
>
> SQLstatement = "SELECT * FROM [Applicant Biographical]"
> Set rstAB = CurDB.OpenRecordset (SQLstatement)
> With rstAB
> While Not Bio_Recid = Me!cboBio_Recid2
> If .EOF = True then
> blnFlag = False
> End If
> .MoveNext
> Loop
>
> There has to be a better way! Any suggestions on this
> would be appreciated (and, yes, I am 'not' working as a
> programmer for my living).

Scott wrote:

>Ref: Tim Ferguson 5/20 response to "Splitter - DAO - Error
>3219"
>
>In the referenced response it was correctly pointed out
>that dbOpenTable does not work when working with linked
>tables. He went on to say "It's a good idea to use a
>SELECT query rather than a table name too: there really is
>normally no reason to open a whole table."
>
>I was able to open the link following his instructions but
>continued to run into problems with code that looked
>something like this:
>SQLstatement = "SELECT [Bio_Recid] FROM [Applicant
>Biographical] WERE [Bio_Recid] = cboBio_Recid"
>Set rstAB = CurDB.OpenRecordset (SQLstatement)
>
>I kept getting a "Too few parameters. Expected 1" error
>message until I took off the WHERE clause.

That error is because the VBA environment does not
automatically resolve query parameters (in this case, the
reference to the combobox).

Since you are using an SQL string, it is far easiest to just
place the value in the string, instead of asking the db
engine to figure it out for you (which it can not do). Try
this instead:

SQLstatement = "SELECT [Bio_Recid] " _
& "FROM [Applicant Biographical] " _
& "WHERE [Bio_Recid] = " & cboBio_Recid

--
Marsh
MVP [MS Access]

That did the trick! Many thanks. Scott
>-----Original Message-----
>The problem is that you need to concatenate together the
SQL string and the
>value from the combobox. You currently have the name of
the combobox inside
>the quotes, which makes it part of the string and so it
is ignored until the
>query tries to run then it looks for a parameter called
cboBio_Recid.
>
>Amend your query to this and see if it works:
>If the value of cboBio_Recid is a number
>SQLstatement = "SELECT [Bio_Recid] FROM [Applicant
Biographical] WERE
>[Bio_Recid] =" & cboBio_Recid
>
>or if the value of cboBio_Recid is a text value
>SQLstatement = "SELECT [Bio_Recid] FROM [Applicant
Biographical] WERE
>[Bio_Recid] ='" & cboBio_Recid & "'"
>--
>Wayne Morgan
>Microsoft Access MVP
>
>
>"Scott" <(E-Mail Removed)> wrote in
message
>news:118a201c441e4$9e310c60$(E-Mail Removed)...
>> Ref: Tim Ferguson 5/20 response to "Splitter - DAO -
Error
>> 3219"
>>
>> In the referenced response it was correctly pointed out
>> that dbOpenTable does not work when working with linked
>> tables. He went on to say "It's a good idea to use a
>> SELECT query rather than a table name too: there really
is
>> normally no reason to open a whole table."
>>
>> I was able to open the link following his instructions
but
>> continued to run into problems with code that looked
>> something like this:
>> SQLstatement = "SELECT [Bio_Recid] FROM [Applicant
>> Biographical] WERE [Bio_Recid] = cboBio_Recid"
>> Set rstAB = CurDB.OpenRecordset (SQLstatement)
>>
>> I kept getting a "Too few parameters. Expected 1" error
>> message until I took off the WHERE clause. I did some
>> research and found the following from MVP Larry
>> Linson: "Alas, Access front-ends do lose track of the
>> record when you use the server equivalent of
AutoNumber."
>> I think the WHERE clause didn't work because I was using
>> linked tables and Bio_Recid is an autonumber field.
>>
>> Although I think I can work around this, the code has
>> gotten really ugly at this point. For example,
>>
>> SQLstatement = "SELECT * FROM [Applicant
Biographical]"
>> Set rstAB = CurDB.OpenRecordset (SQLstatement)
>> With rstAB
>> While Not Bio_Recid = Me!cboBio_Recid2
>> If .EOF = True then
>> blnFlag = False
>> End If
>> .MoveNext
>> Loop
>>
>> There has to be a better way! Any suggestions on this
>> would be appreciated (and, yes, I am 'not' working as a
>> programmer for my living).
>
>
>.
>

Very insightful as well, many thanks, Scott
>-----Original Message-----
>Scott wrote:
>
>>Ref: Tim Ferguson 5/20 response to "Splitter - DAO -
Error
>>3219"
>>
>>In the referenced response it was correctly pointed out
>>that dbOpenTable does not work when working with linked
>>tables. He went on to say "It's a good idea to use a
>>SELECT query rather than a table name too: there really
is
>>normally no reason to open a whole table."
>>
>>I was able to open the link following his instructions
but
>>continued to run into problems with code that looked
>>something like this:
>>SQLstatement = "SELECT [Bio_Recid] FROM [Applicant
>>Biographical] WERE [Bio_Recid] = cboBio_Recid"
>>Set rstAB = CurDB.OpenRecordset (SQLstatement)
>>
>>I kept getting a "Too few parameters. Expected 1" error
>>message until I took off the WHERE clause.
>
>That error is because the VBA environment does not
>automatically resolve query parameters (in this case, the
>reference to the combobox).
>
>Since you are using an SQL string, it is far easiest to
just
>place the value in the string, instead of asking the db
>engine to figure it out for you (which it can not do).
Try
>this instead:
>
>SQLstatement = "SELECT [Bio_Recid] " _
> & "FROM [Applicant
Biographical] " _
> & "WHERE [Bio_Recid] = " &
cboBio_Recid
>
>--
>Marsh
>MVP [MS Access]
>.
>