A "seek" problem

[Jerry Natkin]

I'm trying, in database moved from Access 2000 to 2007, to populate a
link table with pairs of random integers structured to be in
the range of the number of records in the master and detail tables.

The sub hangs, in compilation, on a seek. It says "Compile error: wrong
number of arguments or invalid property assignment."
I've never tried a seek on two variables before, but can't see any
problem. Can someone please help?

Thanks,
Jerry Natkin

***********************************************************************


Sub Build_random_test()
Dim intMasterID As Integer
Dim intDetailID As Integer
Dim rs1 As Recordset
Dim IntRecCount as integer
Set rs1 = CurrentDb().openRecordset("link_Master_Detail")
rs1.Index = ("Master_Detail")

intRecCount = 0
While intRecCount <501
intMasterID = Int(Rnd() * 42 + 1) ' 42 records in master
intDetailID = Int(Rnd() * 174 + 1) '174 records in detail
rs1.Seek "=", intMasterID, intDetailID
' line where error occurs

If rs1.nomatch Then
intRecCount = intRecCount + 1
rs1.Append
rs1.MasterID = intMasterID
rs1.DetailID = intDetailID
rs1.Update
End If

End While

End Sub

 

[Dirk Goldgar]

In

I'm trying, in database moved from Access 2000 to 2007, to populate a
link table with pairs of random integers structured to be in
the range of the number of records in the master and detail tables.

The sub hangs, in compilation, on a seek. It says "Compile error:
wrong number of arguments or invalid property assignment."
I've never tried a seek on two variables before, but can't see any
problem. Can someone please help?

Thanks,
Jerry Natkin

***********************************************************************


Sub Build_random_test()
Dim intMasterID As Integer
Dim intDetailID As Integer
Dim rs1 As Recordset
Dim IntRecCount as integer
Set rs1 = CurrentDb().openRecordset("link_Master_Detail")
rs1.Index = ("Master_Detail")

intRecCount = 0
While intRecCount <501
intMasterID = Int(Rnd() * 42 + 1) ' 42 records in master
intDetailID = Int(Rnd() * 174 + 1) '174 records in detail
rs1.Seek "=", intMasterID, intDetailID
' line where error occurs

If rs1.nomatch Then
intRecCount = intRecCount + 1
rs1.Append
rs1.MasterID = intMasterID
rs1.DetailID = intDetailID
rs1.Update
End If

End While

End Sub

Do you have a reference set to DAO (Microsoft DAO 3.6 Object Library)?
You should.

If you also have a reference set to ADO (Microsoft ActiveX Data Objects
2.x Library), you should qualify the declaration of rs1 to identify
which library it comes from:

Dim rs1 As DAO.Recordset

If that's not your problem, I don't see it.

 

[Jerry Natkin]

Thanks Dirk; I appreciate your suggestions. & will follow through with
them.

I just converted to 2007 from 2000 last week, and am not yet conversant
with DAO. I did try qualifying rs1, but it didn't work. What did work was
to remove the statement: "Dim rs1 as recordset()". After I did that the
sub compiled and ran fine (after also changing "Append" to "addnew").

Thanks again,

Jerry

 

[Dirk Goldgar]

In

Thanks Dirk; I appreciate your suggestions. & will follow through with
them.

I just converted to 2007 from 2000 last week, and am not yet
conversant with DAO. I did try qualifying rs1, but it didn't work.
What did work was to remove the statement: "Dim rs1 as recordset()".
After I did that the sub compiled and ran fine (after also changing
"Append" to "addnew").

I didn't notice that bogus "Append" method. Good catch!

If you removed the Dim statement for rs1 and it worked, I suspect that
you don't have Option Explicit specified at the top of your module (as
you would if you had your VB option "Require Variable Declaration"
checked). I strongly recommend having that option checked, as it will
catch all kinds of errors caused by simple typos and misspellings in
your code. In this case, if I've guessed correctly, not having it
allowed you to wriggle out of trouble; however, in the long run you
really will benefit from declaring all variables.

 

[Jerry Natkin]

You're right; I had deleted Explicit in desperation, since this was a
non-essential module I used ony once, to populate a table for development
& testing. It's still very puzzling, though, & I hope not to encounter it
again.

I also have a problem with a form where I scroll through records
containing memo fields, which always display highlighted until I click
the background. Any idea of a cure?

Thanks,
Jerry

 

[Dirk Goldgar]

In

You're right; I had deleted Explicit in desperation, since this was a
non-essential module I used ony once, to populate a table for
development & testing. It's still very puzzling, though, & I hope not
to encounter it again.

If I was right about the nature of the problem, it's because both the
DAO and the ADO libraries define a Recordset object, but they are not
compatible. I could explain in more detail, if you like.

I also have a problem with a form where I scroll through records
containing memo fields, which always display highlighted until I click
the background. Any idea of a cure?

If the memo field is the first record in the tab order, it will normally
get the focus when you open the form and move from record to record.
The default behavior of the selection when a text box gets the focus is
for the control's contents to be selected. You can control that, for
the whole database, by changing an option setting (Tools -> Options...,
Keyboard tab, Behavior entering field). If you don't want to change the
behavior for all fields, but just this field, you can add an event
procedure for the control's GotFocus event, along these lines:

Private Sub YourTextboxName_GotFocus()

Me!YourTextboxName.SelLength = 0

End Sub

 

[Jerry Natkin]

Excellent! Thanks

Jerry

 

[Jerry Natkin]

I'd appreciate the explanation. Thanks.

Jerry

In

If I was right about the nature of the problem, it's because both the
DAO and the ADO libraries define a Recordset object, but they are not
compatible. I could explain in more detail, if you like.

 

[Dirk Goldgar]

In

I'd appreciate the explanation. Thanks.

Both the DAO and the ADO object libraries define a Recordset object. By
default, Access 2000-2002 sets a reference to ADO and not to DAO. Even
if you later add a reference to DAO, it defaults to a lower priority
than the ADO reference, though you can move it up in the priority list.

Therefore, by default, a declaration such as "Dim rs As Recordset" is
going to be declaring an ADO recordset. However, the Recordset and
RecordsetClone of a form in an MDB file are DAO recordsets, as is the
recordset object returned by CurrentDb.OpenRecordset or a QueryDef's
OpenRecordset method. Hence, you get a type mismatch when you try to
assign any of these recordsets to the (ADO) recordset you've declared.
You may also get a compile-time error if your code uses one of the
properties or methods that belong to the DAO Recordset object but not to
the ADO Recordset.

To correct this, be sure you've added a reference to the Microsoft DAO
3.6 Object Library (via the Tools -> References... dialog in the VB
Editor), and either remove the ADO (Microsoft ActiveX Data Objects)
reference -- if you're not going to use it -- or qualify your
declaration of DAO objects with the "DAO." prefix, like this:

Dim rs As DAO.Recordset

Incidentally, the Recordset object isn't the only object that is defined
in both the DAO and ADO libraries. All of the following objects are
declared in both libraries, and should be disambiguated if you have
occasion to declare them:

Connection
Error
Errors
Field
Fields
Parameter
Parameters
Property
Properties
Recordset

Note: the following objects exist with the same names in the ADOX and
DAO models as well:

Group
Groups
Index
Indexes
Property
Properties
User
Users

 

[Jerry Natkin]

Thanks for the explanation Dirk. Would that also explain why I have
trouble declaring databases, tabledefs, etc?

Jerry

 

[Douglas J. Steele]

Not having a reference to DAO would explain why you can't declare objects as
Database or TableDef.

Having both references shouldn't matter, since the Database and TableDef
objects only exist in the DAO model, not the ADO one. Still. it's never a
bad idea to be explicit and use DAO.Database and DAO.TableDef

 

[Jerry Natkin]

Good advice. I'll follow it.

Jerry