Open recordset

[RipperT]

OpenRecordset below generates a Type Mismatch error. All I want to do
is open tblLockHistory to one record (which is all the SELECT should fetch)
and write to it
programmatically. Help shows several different ways to do it, but they
all generate an error of some kind. I thought I'd get past this one
before moving on the next.

Thanx, Rip

Private Sub write_history(Optional ByVal outgoingId As Variant, Optional
ByVal incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim qdfTemp As QueryDef
Set qdfTemp = db.CreateQueryDef("", "SELECT
tblLockHistory.TransactionID, tblLockHistory.InmateId, " _
& "tblLockHistory.Institution, tblLockHistory.HousingUnit,
tblLockHistory.CellNo, " _
& "tblLockHistory.Bunk, tblLockHistory.DateTimeIn,
tblLockHistory.InUser, " _
& "tblLockHistory.DateTimeOut, tblLockHistory.OutUser FROM
tblLockHistory WHERE " _
& "(((tblLockHistory.InmateId)=outgoingId) AND
((tblLockHistory.DateTimeOut) Is Null));")
Set rs = db.OpenRecordset(qdfTemp)

 

[Allen Browne]

Rip, you don't need to create a QueryDef to open a recordset.
Just use a string containing the SQL statement:

Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT ...
Set rs = db.OpenRecordset(strSql)

 

[RipperT]

Here is what I have:
===============================================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strSQL As String

strSQL = "SELECT tblLockHistory.TransactionID " _
& "FROM tblLockHistory WHERE " _
& "(((tblLockHistory.InmateId)=outgoingId) AND
((tblLockHistory.DateTimeOut) Is Null));"

Set rs = db.OpenRecordset(strSQL)
==============================================
OpenRecordset(strSQL) generates the following error:

Run-time error '3601':
Too few parameters. Expected 1.
Is this parameters in the SQL statement? or parameters in the command?

Thanks, Rip

 

[Douglas J. Steele]

I'm assuming outgoingId is a variable or a field on the form. Either way, it
needs to be outside of the quotes.

If InmateId is a numeric field and outgoingId is a variable, try:

strSQL = "SELECT tblLockHistory.TransactionID " _
& "FROM tblLockHistory WHERE " _
& "tblLockHistory.InmateId=" & outgoingId & " AND " _
& tblLockHistory.DateTimeOut Is Null"

If InmateId is a text field, try

strSQL = "SELECT tblLockHistory.TransactionID " _
& "FROM tblLockHistory WHERE " _
& "tblLockHistory.InmateId='" & outgoingId & "' AND " _
& tblLockHistory.DateTimeOut Is Null"

If outgoingId is a control on the form, you should use Me!outgoingId or
Me.outgoingId instead of just outgoingId

 

[RipperT]

I have a sub:

Private Sub InmateId_AfterUpdate()
Dim outgoingId, incomingId As Variant
Dim strSQLDelete As String

With Me
outgoingId = .InmateId.OldValue
write_history incomingId:=.InmateId.Value
End with
End Sub

Then I have sub write_history:

Private Sub write_history(Optional outgoingId, Optional incomingId)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL, strIncomingSQL As String

strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit,
CellNo, Bunk, DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" & outgoingId &
"' AND " _
& "tblLockHistory.DateTimeOut Is Null"

strIncomingSQL = "SELECT TransactionID, InmateID, HousingUnit,
CellNo, Bunk, DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" & incomingId &
"' AND " _
& "tblLockHistory.DateTimeIn Is Null"

Both of these SQL assignments generate a type mismatch error (I've left some
junk out for clarity). If I put in a literal inmate ID in single quotes
('123456') instead of the variables (incomingId, outgoingId), it works. I've
fiddled with the single quotes and double quotes around the var's but no
luck. What am I doing wrong?

Thanx for the help.

Ripper

 

[Dirk Goldgar]

I have a sub:

Private Sub InmateId_AfterUpdate()
Dim outgoingId, incomingId As Variant
Dim strSQLDelete As String

With Me
outgoingId = .InmateId.OldValue
write_history incomingId:=.InmateId.Value
End with
End Sub

Then I have sub write_history:

Private Sub write_history(Optional outgoingId, Optional incomingId)
[...]

Your call to write_history is not passing the outgoingID. You collected
it in a variable, but you didn't pass it to the subroutine. Instead of
this:

With Me
outgoingId = .InmateId.OldValue
write_history incomingId:=.InmateId.Value
End with

.... try just this:

With Me.InmateID
write_history .OldValue, .Value
End with

 

[RipperT]

Thanks for the reply, Dirk, but as I said, the problem is in the SQL
statements, for if I substitute literals for the strings in the SQL
statements, the whole things works fine.

Rip

I have a sub:

Private Sub InmateId_AfterUpdate()
Dim outgoingId, incomingId As Variant
Dim strSQLDelete As String

With Me
outgoingId = .InmateId.OldValue
write_history incomingId:=.InmateId.Value
End with
End Sub

Then I have sub write_history:

Private Sub write_history(Optional outgoingId, Optional incomingId)
[...]

Your call to write_history is not passing the outgoingID. You collected
it in a variable, but you didn't pass it to the subroutine. Instead of
this:

With Me
outgoingId = .InmateId.OldValue
write_history incomingId:=.InmateId.Value
End with

... try just this:

With Me.InmateID
write_history .OldValue, .Value
End with


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the reply, Dirk, but as I said, the problem is in the SQL
statements, for if I substitute literals for the strings in the SQL
statements, the whole things works fine.

You may also have a problem with your SQL, if your InmateID field is
numeric, not text. If that's the case, you need to remove the
single-quotes from around the incomingId and outgoingId values you are
building into your SQL string. But you said it works when you use a
"literal inmate ID in single quotes ('123456')", so I thought your
InmateID must be text. If it's a number field, you should change the
code in write_history like this:

strOutgoingSQL = _
"SELECT TransactionID, InmateID, HousingUnit, " & _
"CellNo, Bunk, DateTimeOut, OutUser " & _
"FROM tblLockHistory " & _
"WHERE tblLockHistory.InmateId = " & outgoingId & _
" AND tblLockHistory.DateTimeOut Is Null"

strIncomingSQL = _
"SELECT TransactionID, InmateID, HousingUnit, " & _
"CellNo, Bunk, DateTimeOut, OutUser " & _
"FROM tblLockHistory " & _
"WHERE tblLockHistory.InmateId = " & incomingId & _
" AND tblLockHistory.DateTimeIn Is Null"

So make that change if you need to. HOWEVER, regardless of whether the
field is text or number, I can *guarantee* you that the code you posted
will *not* pass outgoingId from the calling routine to write_history().
If you put a breakpoint on some line in write_history and then trigger
InmateId_AfterUpdate(), you can inspect the values of the arguments
outgoingId, and incomingId. What do you see? Is outgoingId reported as
Empty?

So after you make the fix above based on whether the InmateID field is
text or number, I strongly suggest you also make the fix I originally
posted.

 

[RipperT]

Will do. But the inmateId is a text field, so it should work as posted, yes?
(except for the other problem). I will try again at work today and see if it
flies.

Thanx again

Rip