Type mismatch error.. again!

[RipperT]

InmateId is a text field on a form...

Private Sub InmateId_AfterUpdate()
Dim incomingId, outgoingId, newName
With Me
incomingId = .InmateId
outgoingID = .InmateId.OldValue
newName = DLookup("LstName", "tblInmates", _
"[InmateId] = '" & .InmateId.Value & "'")
If IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & .InmateId & "'")) Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
MsgBox .InmateId.Value & " " & newName & " is now on count. "
write_history incomingId:=incomingId
End If
End With
End Sub

Private Sub write_history(Optional outgoingId As Variant, Optional
incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL
strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit, CellNo,
Bunk," _
& "DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" & outgoingId&
"' AND " _
& "tblLockHistory.DateTimeOut Is Null"
With Me
If Not IsMissing(outgoingId) Then
Set rs = db.OpenRecordset(strOutgoingSQL)
'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
If rs.RecordCount = 1 Then
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update
Else:
'add new record to tblLockHistory
rs.AddNew
[...]
End If
End If
End Sub

The call to write_history is causing a Type Mismatch error 13 at the
strOutgoingSQL assignment statement. I've tried everything I can think of. I
have another call to write_history in this sub that calls another sub that
calls write_history and it works fine; I've tried taking cues from that sub
and fiddling with it, but I can't come up with anything. This is the cleaned
up version of what I actually have, so if I've hosed it up, sorry. I'll
clarify anything that needs it, just please help, it's driving me nutz...

Rip

 

[Allen Browne]

If you open tblLockHistory in design view, what is the Data Type of the
InmateID field?

If Number, you don't need the extra quotes in the WHERE clause:
& "FROM tblLockHistory WHERE (tblLockHistory.InmateId = " & outgoingId &
") AND ...

The quotes are used as delimiters for Text fields only.

 

[RipperT]

It is a text field.

Rip

If you open tblLockHistory in design view, what is the Data Type of the
InmateID field?

If Number, you don't need the extra quotes in the WHERE clause:
& "FROM tblLockHistory WHERE (tblLockHistory.InmateId = " & outgoingId
& ") AND ...

The quotes are used as delimiters for Text fields only.

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

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

InmateId is a text field on a form...

Private Sub InmateId_AfterUpdate()
Dim incomingId, outgoingId, newName
With Me
incomingId = .InmateId
outgoingID = .InmateId.OldValue
newName = DLookup("LstName", "tblInmates", _
"[InmateId] = '" & .InmateId.Value & "'")
If IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & .InmateId & "'")) Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
MsgBox .InmateId.Value & " " & newName & " is now on count. "
write_history incomingId:=incomingId
End If
End With
End Sub

Private Sub write_history(Optional outgoingId As Variant, Optional
incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL
strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit, CellNo,
Bunk," _
& "DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" &
outgoingId& "' AND " _
& "tblLockHistory.DateTimeOut Is Null"
With Me
If Not IsMissing(outgoingId) Then
Set rs = db.OpenRecordset(strOutgoingSQL)
'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
If rs.RecordCount = 1 Then
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update
Else:
'add new record to tblLockHistory
rs.AddNew
[...]
End If
End If
End Sub

The call to write_history is causing a Type Mismatch error 13 at the
strOutgoingSQL assignment statement. I've tried everything I can think
of. I have another call to write_history in this sub that calls another
sub that calls write_history and it works fine; I've tried taking cues
from that sub and fiddling with it, but I can't come up with anything.
This is the cleaned up version of what I actually have, so if I've hosed
it up, sorry. I'll clarify anything that needs it, just please help, it's
driving me nutz...

Rip

 

[Allen Browne]

Thn the value you have in outgoingId (whatever that is) appears not to
match.

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

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

It is a text field.

Rip

If you open tblLockHistory in design view, what is the Data Type of the
InmateID field?

If Number, you don't need the extra quotes in the WHERE clause:
& "FROM tblLockHistory WHERE (tblLockHistory.InmateId = " & outgoingId
& ") AND ...

The quotes are used as delimiters for Text fields only.

InmateId is a text field on a form...

Private Sub InmateId_AfterUpdate()
Dim incomingId, outgoingId, newName
With Me
incomingId = .InmateId
outgoingID = .InmateId.OldValue
newName = DLookup("LstName", "tblInmates", _
"[InmateId] = '" & .InmateId.Value & "'")
If IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & .InmateId & "'")) Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
MsgBox .InmateId.Value & " " & newName & " is now on count.
"
write_history incomingId:=incomingId
End If
End With
End Sub

Private Sub write_history(Optional outgoingId As Variant, Optional
incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL
strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit,
CellNo, Bunk," _
& "DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" &
outgoingId& "' AND " _
& "tblLockHistory.DateTimeOut Is Null"
With Me
If Not IsMissing(outgoingId) Then
Set rs = db.OpenRecordset(strOutgoingSQL)
'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
If rs.RecordCount = 1 Then
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update
Else:
'add new record to tblLockHistory
rs.AddNew
[...]
End If
End If
End Sub

The call to write_history is causing a Type Mismatch error 13 at the
strOutgoingSQL assignment statement. I've tried everything I can think
of. I have another call to write_history in this sub that calls another
sub that calls write_history and it works fine; I've tried taking cues
from that sub and fiddling with it, but I can't come up with anything.
This is the cleaned up version of what I actually have, so if I've hosed
it up, sorry. I'll clarify anything that needs it, just please help,
it's driving me nutz..

 

[SteveS]

Hi Ripper,

After working thru the code, it looks to me that you are calling
"write_history" using:

write_history incomingId:=incomingId

(incomingId:=incomingId confused me for a while!!!)


Then, in the WHERE clause of SQL string, you have:

......WHERE tblLockHistory.InmateId = '" & outgoingId& "' AND " _ ......


You are passing "incomingId " to the Sub, but concatenating "outgoingId"
(and you need a space between "outgoingId" and the "&"). Souldn't you be
passing "outgoingId"???


Since "outgoingId" is NULL, I'm thinking this is the problem.


Also, (FWIW), things I have learned from the MVP's:

--It would be easier to read the code if there was a naming convention. Here
are some links:

http://www.accessmvp.com/djsteele/AccessTenCommandments.html

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#NamingConventions


--You don't need to add .Value to a control. Value is the default property.

--The colon after the ELSE is not needed.

-- Declare (DIM) the variables with a type, not as a varient.

--Use comments in the code

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

InmateId is a text field on a form...

Private Sub InmateId_AfterUpdate()
Dim incomingId, outgoingId, newName
With Me
incomingId = .InmateId
outgoingID = .InmateId.OldValue
newName = DLookup("LstName", "tblInmates", _
"[InmateId] = '" & .InmateId.Value & "'")
If IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & .InmateId & "'")) Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
MsgBox .InmateId.Value & " " & newName & " is now on count. "
write_history incomingId:=incomingId
End If
End With
End Sub

Private Sub write_history(Optional outgoingId As Variant, Optional
incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL
strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit, CellNo,
Bunk," _
& "DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" & outgoingId&
"' AND " _
& "tblLockHistory.DateTimeOut Is Null"
With Me
If Not IsMissing(outgoingId) Then
Set rs = db.OpenRecordset(strOutgoingSQL)
'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
If rs.RecordCount = 1 Then
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update
Else:
'add new record to tblLockHistory
rs.AddNew
[...]
End If
End If
End Sub

The call to write_history is causing a Type Mismatch error 13 at the
strOutgoingSQL assignment statement. I've tried everything I can think of. I
have another call to write_history in this sub that calls another sub that
calls write_history and it works fine; I've tried taking cues from that sub
and fiddling with it, but I can't come up with anything. This is the cleaned
up version of what I actually have, so if I've hosed it up, sorry. I'll
clarify anything that needs it, just please help, it's driving me nutz...

Rip

 

[RipperT]

Steve, thanks for taking the time to address these issues. I appreciate it.
As for passing the incomingId but concatenating outgoingId: that is exactly
the problem. Passing only the incomingId is the intent here.
Where it says:

If Not IsMissing(outgoingId) Then...

Well, it IS missing, so the code should skip right over that IF statement
and go on to the next statement (which I've mistakenly left out of my post,
sorry) But it still generates the type mismatch at the SQL assigment, and
I'm not even using the outgoingId in the call! It's optional, and I've left
it out of the call, so why does the compiler even care?

Thanx again, Rip

Hi Ripper,

After working thru the code, it looks to me that you are calling
"write_history" using:

write_history incomingId:=incomingId

(incomingId:=incomingId confused me for a while!!!)


Then, in the WHERE clause of SQL string, you have:

......WHERE tblLockHistory.InmateId = '" & outgoingId& "' AND " _ ......


You are passing "incomingId " to the Sub, but concatenating "outgoingId"
(and you need a space between "outgoingId" and the "&"). Souldn't you be
passing "outgoingId"???


Since "outgoingId" is NULL, I'm thinking this is the problem.


Also, (FWIW), things I have learned from the MVP's:

--It would be easier to read the code if there was a naming convention.
Here
are some links:

http://www.accessmvp.com/djsteele/AccessTenCommandments.html

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#NamingConventions


--You don't need to add .Value to a control. Value is the default
property.

--The colon after the ELSE is not needed.

-- Declare (DIM) the variables with a type, not as a varient.

--Use comments in the code

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

InmateId is a text field on a form...

Private Sub InmateId_AfterUpdate()
Dim incomingId, outgoingId, newName
With Me
incomingId = .InmateId
outgoingID = .InmateId.OldValue
newName = DLookup("LstName", "tblInmates", _
"[InmateId] = '" & .InmateId.Value & "'")
If IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & .InmateId & "'")) Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
MsgBox .InmateId.Value & " " & newName & " is now on count.
"
write_history incomingId:=incomingId
End If
End With
End Sub

Private Sub write_history(Optional outgoingId As Variant, Optional
incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL
strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit,
CellNo,
Bunk," _
& "DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" &
outgoingId&
"' AND " _
& "tblLockHistory.DateTimeOut Is Null"
With Me
If Not IsMissing(outgoingId) Then
Set rs = db.OpenRecordset(strOutgoingSQL)
'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
If rs.RecordCount = 1 Then
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update
Else:
'add new record to tblLockHistory
rs.AddNew
[...]
End If
End If
End Sub

The call to write_history is causing a Type Mismatch error 13 at the
strOutgoingSQL assignment statement. I've tried everything I can think
of. I
have another call to write_history in this sub that calls another sub
that
calls write_history and it works fine; I've tried taking cues from that
sub
and fiddling with it, but I can't come up with anything. This is the
cleaned
up version of what I actually have, so if I've hosed it up, sorry. I'll
clarify anything that needs it, just please help, it's driving me nutz...

Rip

 

[SteveS]

Rip,

Even though you haven't used the SQL string yet, it is still evaluated when
you create it. The error is trying to concantenate a Boolean when it is
expecting a string.
The simple fix is to move the "strOutgoingSQL" statement inside the IF
Not IsMissing(outgoingId) function.


I changed you code a little... <g> couldn't help myself .... here it is:

'------beg modified code--------------------------
Private Sub InmateId_AfterUpdate()
Dim strIncomingId As String
Dim strOutgoingId As String
Dim strNewName As String

With Me
strIncomingId = .InmateId
strOutgoingId = .InmateId.OldValue

If IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '"
& .InmateId & "'")) Then

'Save record
' DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
If .Dirty Then
.Dirty = False
End If

'get Inmate name
strNewName = DLookup("LstName", "tblInmates", "[InmateId] = '" &
..InmateId & "'")

MsgBox .InmateId & " " & strNewName & " is now on count. "
write_history pIncomingId:=strIncomingId
End If
End With
End Sub

'the p in pOutgoingId is for parameter
Private Sub write_history(Optional pOutgoingId As Variant, Optional
pIncomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strOutgoingSQL As String
Dim intRC As Integer ' RecordCount

Set db = CurrentDb

With Me
If Not IsMissing(pOutgoingId) Then

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

Set rs = db.OpenRecordset(strOutgoingSQL)

rs.MoveLast
rs.MoveFirst
intRC = rs.RecordCount

'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
Select Case intRC
Case 0 'no records
'add new record to tblLockHistory
rs.AddNew
[...]
' more statments

Case 1 ' 1 record
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update

Else ' more than 1 record
'ERROR!! more than one record found for ...
'Handle this somehow
End Select

End If 'IsMissing
End With
End Sub

'------end modified code--------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve, thanks for taking the time to address these issues. I appreciate it.
As for passing the incomingId but concatenating outgoingId: that is exactly
the problem. Passing only the incomingId is the intent here.
Where it says:

If Not IsMissing(outgoingId) Then...

Well, it IS missing, so the code should skip right over that IF statement
and go on to the next statement (which I've mistakenly left out of my post,
sorry) But it still generates the type mismatch at the SQL assigment, and
I'm not even using the outgoingId in the call! It's optional, and I've left
it out of the call, so why does the compiler even care?

Thanx again, Rip

Hi Ripper,

After working thru the code, it looks to me that you are calling
"write_history" using:

write_history incomingId:=incomingId

(incomingId:=incomingId confused me for a while!!!)


Then, in the WHERE clause of SQL string, you have:

......WHERE tblLockHistory.InmateId = '" & outgoingId& "' AND " _ ......


You are passing "incomingId " to the Sub, but concatenating "outgoingId"
(and you need a space between "outgoingId" and the "&"). Souldn't you be
passing "outgoingId"???


Since "outgoingId" is NULL, I'm thinking this is the problem.


Also, (FWIW), things I have learned from the MVP's:

--It would be easier to read the code if there was a naming convention.
Here
are some links:

http://www.accessmvp.com/djsteele/AccessTenCommandments.html

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#NamingConventions


--You don't need to add .Value to a control. Value is the default
property.

--The colon after the ELSE is not needed.

-- Declare (DIM) the variables with a type, not as a varient.

--Use comments in the code

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

InmateId is a text field on a form...

Private Sub InmateId_AfterUpdate()
Dim incomingId, outgoingId, newName
With Me
incomingId = .InmateId
outgoingID = .InmateId.OldValue
newName = DLookup("LstName", "tblInmates", _
"[InmateId] = '" & .InmateId.Value & "'")
If IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & .InmateId & "'")) Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
MsgBox .InmateId.Value & " " & newName & " is now on count.
"
write_history incomingId:=incomingId
End If
End With
End Sub

Private Sub write_history(Optional outgoingId As Variant, Optional
incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL
strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit,
CellNo,
Bunk," _
& "DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" &
outgoingId&
"' AND " _
& "tblLockHistory.DateTimeOut Is Null"
With Me
If Not IsMissing(outgoingId) Then
Set rs = db.OpenRecordset(strOutgoingSQL)
'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
If rs.RecordCount = 1 Then
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update
Else:
'add new record to tblLockHistory
rs.AddNew
[...]
End If
End If
End Sub

The call to write_history is causing a Type Mismatch error 13 at the
strOutgoingSQL assignment statement. I've tried everything I can think
of. I
have another call to write_history in this sub that calls another sub
that
calls write_history and it works fine; I've tried taking cues from that
sub
and fiddling with it, but I can't come up with anything. This is the
cleaned
up version of what I actually have, so if I've hosed it up, sorry. I'll
clarify anything that needs it, just please help, it's driving me nutz...

Rip

 

[RipperT]

Hi, Steve,

Thanx for taking the time to write that. It is much appreciated and very
helpful. I have a few questions, tho:

1) Why the If .dirty Then statement immediately after the record save?
Access help states that the dirty property is automatically set to false
after a record is saved.

2) Why declare the arguments to write_history as Variants? Why not Strings?

3) Why the rs.MoveLast and rs.MoveFirst statements?

Thanx again,

Rip

Rip,

Even though you haven't used the SQL string yet, it is still evaluated when
you create it. The error is trying to concantenate a Boolean when it is
expecting a string.
The simple fix is to move the "strOutgoingSQL" statement inside the IF
Not IsMissing(outgoingId) function.


I changed you code a little... <g> couldn't help myself .... here it is:

'------beg modified code--------------------------
Private Sub InmateId_AfterUpdate()
Dim strIncomingId As String
Dim strOutgoingId As String
Dim strNewName As String

With Me
strIncomingId = .InmateId
strOutgoingId = .InmateId.OldValue

If IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '"
& .InmateId & "'")) Then

'Save record
' DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
If .Dirty Then
.Dirty = False
End If

'get Inmate name
strNewName = DLookup("LstName", "tblInmates", "[InmateId] = '" &
.InmateId & "'")

MsgBox .InmateId & " " & strNewName & " is now on count. "
write_history pIncomingId:=strIncomingId
End If
End With
End Sub

'the p in pOutgoingId is for parameter
Private Sub write_history(Optional pOutgoingId As Variant, Optional
pIncomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strOutgoingSQL As String
Dim intRC As Integer ' RecordCount

Set db = CurrentDb

With Me
If Not IsMissing(pOutgoingId) Then

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

Set rs = db.OpenRecordset(strOutgoingSQL)

rs.MoveLast
rs.MoveFirst
intRC = rs.RecordCount

'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
Select Case intRC
Case 0 'no records
'add new record to tblLockHistory
rs.AddNew
[...]
' more statments

Case 1 ' 1 record
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update

Else ' more than 1 record
'ERROR!! more than one record found for ...
'Handle this somehow
End Select

End If 'IsMissing
End With
End Sub

'------end modified code--------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve, thanks for taking the time to address these issues. I appreciate it.
As for passing the incomingId but concatenating outgoingId: that is exactly
the problem. Passing only the incomingId is the intent here.
Where it says:

If Not IsMissing(outgoingId) Then...

Well, it IS missing, so the code should skip right over that IF statement
and go on to the next statement (which I've mistakenly left out of my post,
sorry) But it still generates the type mismatch at the SQL assigment, and
I'm not even using the outgoingId in the call! It's optional, and I've left
it out of the call, so why does the compiler even care?

Thanx again, Rip

Hi Ripper,

After working thru the code, it looks to me that you are calling
"write_history" using:

write_history incomingId:=incomingId

(incomingId:=incomingId confused me for a while!!!)


Then, in the WHERE clause of SQL string, you have:

......WHERE tblLockHistory.InmateId = '" & outgoingId& "' AND " _ ......


You are passing "incomingId " to the Sub, but concatenating "outgoingId"
(and you need a space between "outgoingId" and the "&"). Souldn't you be
passing "outgoingId"???


Since "outgoingId" is NULL, I'm thinking this is the problem.


Also, (FWIW), things I have learned from the MVP's:

--It would be easier to read the code if there was a naming convention.
Here
are some links:

http://www.accessmvp.com/djsteele/AccessTenCommandments.html

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#NamingConventions


--You don't need to add .Value to a control. Value is the default
property.

--The colon after the ELSE is not needed.

-- Declare (DIM) the variables with a type, not as a varient.

--Use comments in the code

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

InmateId is a text field on a form...

Private Sub InmateId_AfterUpdate()
Dim incomingId, outgoingId, newName
With Me
incomingId = .InmateId
outgoingID = .InmateId.OldValue
newName = DLookup("LstName", "tblInmates", _
"[InmateId] = '" & .InmateId.Value & "'")
If IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & .InmateId & "'")) Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , _
acMenuVer70
MsgBox .InmateId.Value & " " & newName & " is now on count.
"
write_history incomingId:=incomingId
End If
End With
End Sub

Private Sub write_history(Optional outgoingId As Variant, Optional
incomingId As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strOutgoingSQL
strOutgoingSQL = "SELECT TransactionID, InmateID, HousingUnit,
CellNo,
Bunk," _
& "DateTimeOut, OutUser " _
& "FROM tblLockHistory WHERE tblLockHistory.InmateId = '" &
outgoingId&
"' AND " _
& "tblLockHistory.DateTimeOut Is Null"
With Me
If Not IsMissing(outgoingId) Then
Set rs = db.OpenRecordset(strOutgoingSQL)
'Find only that record where that inmate last moved in
'and add a move-out date to the DateTimeOut field
If rs.RecordCount = 1 Then
rs.Edit
rs!DateTimeOut = Now
rs!OutUser = [CurrentUser]
rs.Update
Else:
'add new record to tblLockHistory
rs.AddNew
[...]
End If
End If
End Sub

The call to write_history is causing a Type Mismatch error 13 at the
strOutgoingSQL assignment statement. I've tried everything I can think
of. I
have another call to write_history in this sub that calls another sub
that
calls write_history and it works fine; I've tried taking cues from that
sub
and fiddling with it, but I can't come up with anything. This is the
cleaned
up version of what I actually have, so if I've hosed it up, sorry. I'll
clarify anything that needs it, just please help, it's driving me nutz...

Rip

 

[SteveS]

Hi, Steve,

Thanx for taking the time to write that. It is much appreciated and very
helpful. I have a few questions, tho:

1) Why the If .dirty Then statement immediately after the record save?
Access help states that the dirty property is automatically set to false
after a record is saved.

Note that the line

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

is commented out. I left it in because it was (is) your code, but it should
be deleted. Checking the dirty property and setting it to false saves the
record.

2) Why declare the arguments to write_history as Variants? Why not Strings?

You (your code) checks the IsMissing property. This property is only
available if the variable type is a Variant. If you change the data type to
string, then you would need to check if the length of the string >0 to see if
the variable had a value. Either way works...

3) Why the rs.MoveLast and rs.MoveFirst statements?

When you open a recordset, you don't know where (which record) the pointer
will be at. If you check the record count at this point, it will be 1 (IIRC).
The only way to get the true record count is to move to the last record. Then
I always move back to the first record.

 

[RipperT]

You've gotten me off down the path to success. Many thanx.

Rip

 

[SteveS]