If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A "seek" problem
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 |
#2
|
|||
|
|||
A "seek" problem
In ,
Jerry Natkin wrote: 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
A "seek" problem
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" wrote in news:O$OsO2JCIHA.4476 @TK2MSFTNGP06.phx.gbl: In , Jerry Natkin wrote: 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. |
#4
|
|||
|
|||
A "seek" problem
In ,
Jerry Natkin wrote: 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#5
|
|||
|
|||
A "seek" problem / display problem
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" wrote in news:u3myqPiCIHA.2280 @TK2MSFTNGP02.phx.gbl: In , Jerry Natkin wrote: 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. |
#6
|
|||
|
|||
A "seek" problem / display problem
In ,
Jerry Natkin wrote: 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 -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#7
|
|||
|
|||
A "seek" problem / display problem
Excellent! Thanks
Jerry "Dirk Goldgar" wrote in : 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 |
#8
|
|||
|
|||
A "seek" problem / display problem
I'd appreciate the explanation. Thanks.
Jerry "Dirk Goldgar" wrote in news:OIIH8rpCIHA.3884 @TK2MSFTNGP05.phx.gbl: In , Jerry Natkin wrote: 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. |
#9
|
|||
|
|||
A "seek" problem / display problem
In ,
Jerry Natkin wrote: 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 -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#10
|
|||
|
|||
DAO & ADO
Thanks for the explanation Dirk. Would that also explain why I have trouble declaring databases, tabledefs, etc? Jerry "Dirk Goldgar" wrote in : In , Jerry Natkin wrote: 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 .... |
|
Thread Tools | |
Display Modes | |
|
|