Splitter

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

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 & "'"
 
Scott said:
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.
Click to expand...

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
 
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


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).
Click to expand...


.
Click to expand...
 
Very insightful as well, many thanks, Scott
-----Original Message-----
Scott said:
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.
Click to expand...

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
Click to expand...
 
Back
Top