Forms & Recordsets

[Garry]

I have the following code in the After Update event of a combo box,
cboGiftCertNo, that selects GiftCertNo.

Dim db As Database
Dim rec As Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "tblGiftCertificatesSold"
Set rec = db.OpenRecordset(strSQL)
rec.Index = "PrimaryKey"
rec.Seek "=", Me.cboGiftCertNo

GiftCertNo is the PrimaryKey in tblGiftCertificatesSold, I have used this
type of code before with no problems. However in this form when I select a
GiftCertNo I get a run time error:
“Run Time Error ‘3251’
Operation not supported for this type of objectâ€

I checked the Indexes for tblGiftCertificatesSold, and it says that the
index Name for GiftCertNo is “PrimaryKeyâ€

What could be the problem???

Thanks -- Garry Gross

 

[Ken Snell [MVP]]

Just guessing, but ACCESS may think that your Recordset is an ADODB one, not
a DAO one. Try disambiguating the Dim statement:

Dim rec As DAO.Recordset

Is the error occurring on the rec.Seek line?

 

[Garry]

I should have said that the error is occuring on
rec.Index = "PrimaryKey"
At least that's what Debug highlights.
I tried Dim rec as DAO.Recordset and still got the same error.

Garry Gross

 

[Ken Snell [MVP]]

Is tblGiftCertificatesSold a linked table? I believe Index will work only
for a local table.

 

[Garry]

Yes it is linked to one other table, BUT the other instance where I use
rec.index="PrimaryKey" is a table that has links to 5 other tales??

Garry Gross

 

[Garry]

I should probably have mentioned that the other instance of use of
rec.index="PrimaryKey" is in a module, not in the code behind the form. Can
this make a differe4nce??

Garry Gross

 

[Garry]

I tried putting the code in a module & still got the same error.
Garry Gross

 

[Garry]

The database was split, so it would not let me delete the table link. I
deleted all the linked tables, then imported the tables from the "be" file,
deleted the table link, and it now works??? (I hope you can follow the
reference to "links")
The table link I deleted I don't think is really necessary, but why does the
other instance work?? (In the other case, the table that is used for the
rec.index="PrimaryKey" is linked to 5 other tables & those links are
necessary).
It seems Access has a little bit of "black magic" or so it seems at times.

Garry Gross

 

[Garry]

Ken, I misunderstood you, I thought you were refering to a relationship link
between 2 tables. Did some more checking & found tha you were correct, the
database was split, & when I re-imported them all works fine with or without
the relationship link between the 2 tables. So, you were correct, I was
getting the error because the table was linked (split). It was late last
night & I wasn't too clear headed. Thanks!

Garry Gross

 

[Ken Snell [MVP]]

Splitting a database is usually a good thing to do, so merging back into one
just to fix this issue is not what I'd recommend. I would suggest taking a
different approach to what you seek to do -- either one that doesn't use the
..Index property, or one that does but will still work with a split database.

If you'd like to pursue one of these approaches, post back with more details
about what you want to achieve, and we can go from there.

 

[Garry]

Ken,
I was using the index property to accomplish two different things - pull
a value out of a table & modify the value in a table, both based on the
primary key. I have rewritten the code to use SQL (a SELECT and an UPDATE)
and have both cases working with a split database. Thanks for the offer, but
I think I'm OK for now.

Garry Gross