VBA, 2 questions: Code doesn't work when I split database and Seek/Index question

John
#1 / 6

2 questions: Code doesn't work when I split database and Seek/Index question

I have a "logNotes" table consisting of two fields: an EESSN field (the PrimaryKey) and a Notes (memo) field. I have an "Employee Indicative Data" table and a form that displays this data based on the SSN. There is an unbound text box on the form where employee notes can be entered. When the update button is clicked I need to have this text append to the Notes field on the logNotes table for the given SSN. The code I am using is listed below. Here are my questions:

1. What is the best way to find the correct record in the logNotes table? I tried using an Openrecordset with an SQL source as follows: Set rst = dbs.OpenRecordset("SELECT * FROM logNotes WHERE EESSN = SSN") but couldn't get that to work. I finally got the results I needed using INDEX and SEEK, though I have no idea why or how that worked? Where can I find some good examples and definitions of INDEX and SEEK? (The online help wasn't too helpful.)

2. As I mentioned, INDEX and SEEK worked until I split the database. When I run the code on the split database I get the following error message:
Run-time error '3251':
Operation is not supported for this type of object.
I really want to run this database as a front and back-end. Any idea why I get this error message when I use the split database (I don't get this error with the "un-split" database) and what I can do to resolve it? The error seems to be associated with the Index method.

Thanks,
John

Here's the code I'm using......

Private Sub UpdateNotes_DblClick(Cancel As Integer)

If IsNull(Me!EnterNotes) Then Beep: MsgBox ("There's nothing to update."): Exit Sub

Dim dbs As Database, rst As Recordset, fldNotes As Field
Dim lngSize As Long, strChunk As String, varX As Variant
Dim Header As String

'Sets the Header with a Timestamp and UserName
Header = Now() & " " & Forms!LoginScreen.EnterUserID & ": "

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("logNotes")
Set fldNotes = rst!Notes

'Checks to see if a Notes History exists
'If one doesn't, add SSN to the logNotes table then append new notes.
varX = DLookup("EESSN", "logNotes", "cstr(eessn) =" & CStr(SSN))
Beep

If IsNull(varX) Then
With rst
.AddNew
!EESSN = SSN
!Notes = Header & vbCrLf & Me!EnterNotes
.Update
.Close
End With

Else
With rst
.Index = "PrimaryKey"
.Seek "=", CStr(SSN)
lngSize = Len(fldNotes)
strChunk = fldNotes.GetChunk(0, lngSize)
strChunk = Header & vbCrLf & Me!EnterNotes & vbCrLf & vbCrLf & strChunk
.Edit
!Notes.AppendChunk strChunk
.Update
.Close
End With

End If
'Clears the EnterNotes field and Disables it.
Me.EnterNotes = Null
Me.EnterNotes.Enabled = False
Me.Recalc
End Sub

Thu, 17 Jan 2002 03:00:00 GMT

Radu Lasca
#2 / 6

2 questions: Code doesn't work when I split database and Seek/Index question

Hi John,

First, please don't send HTML formatted messages anymore. Many people
don't have browsers able to read it and it's generally considered bad
netiquette - well, certainly by me.

To answer your question:
Seek works on table type recordsets. You cannot open a linked table as
dbOpenTable. You can either open it as dynaset (dbOpenDynaset) or
snapshot (dbOpenSnapshot). When you don't pass an explicit recordset
type to the OpenRecordset method, it will open the table the best it
can, for editing if it can. For linked tables, this is dynaset. Further
comments inline:

1. What is the best way to find the correct record in the logNotes
table? I tried using an Openrecordset with an SQL source as follows:
Set rst = dbs.OpenRecordset("SELECT * FROM logNotes WHERE EESSN = SSN")
but couldn't get that to work.

Only to find: use a snapshot query,i.e. add dbOpenSnapshot to the
OpenRecordset method, check for returned records and

with rst
if not (.bof and .eof) then
'no records
else
'you found something
end if
end with

If you need to edit those records, use dbOpenDynaset instead.

Hth,
Radu Lascae

Fri, 18 Jan 2002 03:00:00 GMT

Rebecca Riorda
#3 / 6

2 questions: Code doesn't work when I split database and Seek/Index question

John,

Try using .FindFirst instead of .Seek, which only works on local tables.

HTH

- Rebecca

BTW,
While you obviously spent a lot of time making the post easy to read, and I
give you full marks for your intentions, plain text is the preferred format
for ng postings. Not everybody has a news reader that can interpret HTML,
so you're doing yourself a disservice and opening yourself up to flaming.

I have a "logNotes" table consisting of two fields: an EESSN field (the
PrimaryKey) and a Notes (memo) field. I have an "Employee Indicative Data"
table and a form that displays this data based on the SSN. There is an
unbound text box on the form where employee notes can be entered. When the
update button is clicked I need to have this text append to the Notes field
on the logNotes table for the given SSN. The code I am using is listed
below. Here are my questions:

1. What is the best way to find the correct record in the logNotes table?
I tried using an Openrecordset with an SQL source as follows: Set rst =
dbs.OpenRecordset("SELECT * FROM logNotes WHERE EESSN = SSN") but couldn't
get that to work. I finally got the results I needed using INDEX and SEEK,
though I have no idea why or how that worked? Where can I find some good
examples and definitions of INDEX and SEEK? (The online help wasn't too
helpful.)

2. As I mentioned, INDEX and SEEK worked until I split the database. When
I run the code on the split database I get the following error message:
Run-time error '3251':
Operation is not supported for this type of object.
I really want to run this database as a front and back-end. Any idea why I
get this error message when I use the split database (I don't get this error
with the "un-split" database) and what I can do to resolve it? The error
seems to be associated with the Index method.

Thanks,
John

Here's the code I'm using......

Private Sub UpdateNotes_DblClick(Cancel As Integer)

If IsNull(Me!EnterNotes) Then Beep: MsgBox ("There's nothing to update."):
Exit Sub

Dim dbs As Database, rst As Recordset, fldNotes As Field
Dim lngSize As Long, strChunk As String, varX As Variant
Dim Header As String

'Sets the Header with a Timestamp and UserName
Header = Now() & " " & Forms!LoginScreen.EnterUserID & ": "

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("logNotes")
Set fldNotes = rst!Notes

'Checks to see if a Notes History exists
'If one doesn't, add SSN to the logNotes table then append new notes.
varX = DLookup("EESSN", "logNotes", "cstr(eessn) =" & CStr(SSN))
Beep

If IsNull(varX) Then
With rst
.AddNew
!EESSN = SSN
!Notes = Header & vbCrLf & Me!EnterNotes
.Update
.Close
End With

Else
With rst
.Index = "PrimaryKey"
.Seek "=", CStr(SSN)
lngSize = Len(fldNotes)
strChunk = fldNotes.GetChunk(0, lngSize)
strChunk = Header & vbCrLf & Me!EnterNotes & vbCrLf & vbCrLf &
strChunk
.Edit
!Notes.AppendChunk strChunk
.Update
.Close
End With

End If
'Clears the EnterNotes field and Disables it.
Me.EnterNotes = Null
Me.EnterNotes.Enabled = False
Me.Recalc
End Sub

Fri, 18 Jan 2002 03:00:00 GMT

Mario Osori
#4 / 6

2 questions: Code doesn't work when I split database and Seek/Index question

There is a solution at Dev Ashish site that will open a recordset based on a
linked table in a way so u can use Seek/Index

--
HTH/EQTA
Mario Osorio
====================================================
You'll find good answers to common (and not so common) questions
and all kinds of interesting stuff at: http://home.att.net/~dashish
Ud. encontrar buenas respuestas a preguntas comunes (y no tan comunes)
y otras cosas interesantes en esta direccin: http://home.att.net/~dashish
====================================================
--