Solved

Method or Data Member Not Found

Posted on 2008-10-22
13
368 Views
Last Modified: 2010-04-21
I have a main form "swbEquipment" with 2 subforms on it
"fsub01" and "fsub02"

both have a common ID field "UnitID" which is numeric

Basically, Calling by a button on the main form I have some code that I want to make fsub02 visible and goto a record based on fsub01

I have been playing around with this code for nearly a whole day but cannot get it right.

at the moiment it hangs on

.RecordsetClone.Findfirst

I'm sure it is mearly a reference problem but can't seem to get a handle on it.

any ideas where I might be wrong?
ElseIf LblSub3.Caption = "View Service Record" Then
 
 
Dim rs As Recordset
Dim lngUnitID As Integer
lngUnitID = Me!fsub01.Form!UnitID
Set rs = Forms!swbEquipment!fsub01.Form.RecordsetClone
Me.fsub02.SourceObject = "subfrmGRMachineryServicefrm"
Me.fsub02.Visible = True
Forms![swbEquipment]![txtUserID].SetFocus
Forms.swbEquipment!fsub02.SetFocus
rs.RecordsetClone.FindFirst "[UnitID]= " & lngUnitID
If Not rs.NoMatch Then
      rs.Bookmark = rs.Bookmark
Me.fsub01.Visible = False
End If
End Sub

Open in new window

0
Comment
Question by:MGardner
  • 9
  • 4
13 Comments
 
LVL 84
ID: 22776577
Since you're working in 2002, you probably don't have a Reference to the DAO library, which means that your Recordset is probably an ADO recordset ... ADO recordsets aren't the same as the RecordsetClone of your form (which is a DAO recordset).

You first need to set a reference to the Microsoft DAO Object Library (newest version you find on the machine), then change your code like this:

Dim rs As DAO.Recordset

This is known as "disambiguating" your objects. Since both the ADO and DAO library can build a Recordset object, you should always tell VBA which you want to work with. It's cleaner and makes troubleshooting easier.
0
 
LVL 2

Author Comment

by:MGardner
ID: 22776743
Thanks LSMC
I thought it might be something like that but I have just checked the reference library and it is set to "Microsoft DAO 3.6 Object library" change the code but still geting the same error message and at the same point?
0
 
LVL 84
ID: 22777190
Where did you get this code?

Dim rs As Recordset
Dim lngUnitID As Integer
lngUnitID = Me!fsub01.Form!UnitID

Set rs = Forms!swbEquipment!fsub01.Form.RecordsetClone

Me.fsub02.SourceObject = "subfrmGRMachineryServicefrm"
Me.fsub02.Visible = True

Forms![swbEquipment]![txtUserID].SetFocus
Forms.swbEquipment!fsub02.SetFocus
'/you do NOT need the "RecordsetClone" of your Recordsetclone:
'/rs.RecordsetClone.FindFirst "[UnitID]= " & lngUnitID
'/so instead do this:
rs.FindFirst "[UnitID]= " & lngUnitID

If Not rs.NoMatch Then
      rs.Bookmark = rs.Bookmark
Me.fsub01.Visible = False

Your If block above does nothing - it sets the Recordset's Bookmark to ... the Recordset's bookmark, resulting in nothing. Are you trying to move one of your subform's Recordsets? If so, something like this;

Me.fSub02.Form.Bookmark = rs.Bookmark

You can (and should) use the Me construct if you're running your code on the same form as you're trying to manipulate.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 2

Author Comment

by:MGardner
ID: 22777345
Basically cobbled it from two or three sources but basically How I am expecting it to perform is

The code is on the main form and triggered by a button.

It should look at the active subform "fsub01" and store the unique record field in this case "UnitID" for later use in finding the related record in "fsub02" which also has a corresponding unique identifier field "UnitID". So after setting the object source of fsub02 and navigating to the correct record , fsub01 is made invisible. hope this sounds a bit clearer?
0
 
LVL 2

Author Comment

by:MGardner
ID: 22777531
Now I am to this and it compiles ok, but when I run the code I get an error msg that UnitID could refer to one or more tables in the where clause of your sql statment?
I assume this is because The record source on fsub02 is taken from a query drawn from two tables "tblGRMachinery" and "tblGRServiceDetails"
0
 
LVL 2

Author Comment

by:MGardner
ID: 22777545
Sorry forgot to add the code I have so far

Dim rs As DAO.Recordset
Dim lngUnitID As Integer
lngUnitID = Me!fsub01.Form!UnitID
Set rs = Forms!swbEquipment!fsub01.Form.RecordsetClone
Me.fsub02.SourceObject = "subfrmGRMachineryServicefrm"
Me.fsub02.Visible = True
Forms![swbEquipment]![txtUserID].SetFocus
Forms.swbEquipment!fsub02.SetFocus
rs.FindFirst "[tblGRMachinery.UnitID]= " & lngUnitID
If Not rs.NoMatch Then
      Me.fsub02.Form.Bookmark = rs.Bookmark
Me.fsub01.Visible = False
End If
0
 
LVL 2

Author Comment

by:MGardner
ID: 22780409
I have just had a brain wave and tried the following method

I added an unbound text box on the main form "txtMainSubLink" and added an after updat and current to pass the identifier field value "UnitID" from the subform "fsub01" with the thought that I can use that I can set the source object of the second subform "sfrm02" and then navigate to the corect subform with some goto with criteria.

however am strugling with this too as it hangs on data or member not found on the.Filter
Me.fsub02.SourceObject = "subfrmGRMachineryDetails"
Me.fsub02.Visible = True
Forms.swbEquipment.SetFocus
Forms.swbEquipment!fsub02.SetFocus
Me.fsub02.Filter = "txtUnitID=" & Me.Parent.txtMainSubLink
If txtUnitID.Value = False Then
Me.fsub02.FilterOn = True
Else
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.fsub01.Visible = False

Open in new window

0
 
LVL 84
ID: 22780562
You need to use the fully qualified column name, including the Table ... for example:

rs.FindFirst "[tblGRMachinery.UnitID]= " & tblGRMachinery.lngUnitID

Or whatever Table you need to use. No need to filter anything with this either ...
0
 
LVL 2

Author Comment

by:MGardner
ID: 22781289
Ok tried that and it hangs on tblGRMachinery.lngUnitID when I compile
with the message variable not defined so took out tblGRMachinery. before lngUnitID
and then it hangs on "[tblGRMachinery.UnitID]= " and throws up the msg unknown or invalid field reference.
So I asume then that rs looks at the underlying table values and not the values on the form? only the record source of the field is "tblGRMachinery.UnitID" but the name of the control on both sub forms is "txtUnitID"
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22782682
You MUST have your TableNames and Fieldnames correct ... I have absolutely no idea what those would be, but it's pretty straight forward:

rs.Findfirst "[TABLENAME].[FIELDNAME]=" & Me.CONTROLNAME

or

rs.Findfirst "[TABLENAME].[FIELDNAME]=" & Me!FIELDNAME

Note the square brackets ... I had them incorrect earlier.

"rs" is a RECORDSET ... that is, it's a collection of records from a TABLE, and has nothing to do with your form. Your form may be based on the same table (in this case, it is since you're using RecordsetClone) but that's about the extent of the relationship.

No offense intended, but given your questions, and your obvious lack of understanding of the code you're using, I'd encourage you to read up on the subject of Access and VBA, recordsets etc etc ... perhaps a course at a local community college, or an online course might help.
0
 
LVL 2

Author Comment

by:MGardner
ID: 22784805
Thanks LSM and no offence taken. I do have a number of books that I can reference but they do not seem to cover the topic of recordsets and the use of bookmarks etc. very well. some I can find no references for for the use of book marks at all and even doing a search on this site there is very few postings on the subject. True to say that I do end up cobbling code together and then play around with it and I feel that I have learnt alot about access that way. But of course I do not do it professionally as my vocation is in a different area. I just like to occasionally dip into access and find out what makes it tick. Most college courses that I have seen locally only provide for front end use of access at a very basic level. I am at college today as a matter of fact  as part of my cpd and that, work and home life takes up a great deal of my time without having to add another college course. I do have a great respect for you guys on here and think that the solutions that you come up with and the patiant way in which you explain things is to be marvelled. I will try that when I get home this evening though I take your point regarding the other solution and the use of a filter I replaced
Me.fsub02.Filter = "txtUnitID=" & Me.Parent.txtMainSubLink
If txtUnitID.Value = False Then
Me.fsub02.FilterOn = True

with linking the parent and child fields through vba to the unbound "txtMainSubLink" text box on the main form this seemed to work last night but I havent as yet fully tested it. I managed to get this from the desktop access workbook , chapter 8 which I think is a brilliant book. I will get back to you later this evening.
 
Thanks MG
0
 
LVL 2

Author Comment

by:MGardner
ID: 22789280
Here is the link code that I used
Me.fsub02.SourceObject = "subfrmGRMachineryServicefrm"
Me.fsub02.Visible = True
Forms![swbEquipment]![txtUserID].SetFocus
Forms.swbEquipment!fsub02.SetFocus
Me.fsub02.LinkMasterFields = "txtMainSubLink"
Me.fsub02.LinkChildFields = "UnitID"
Me.fsub02.Requery
Me.fsub01.Visible = False
End If
0
 
LVL 2

Author Closing Comment

by:MGardner
ID: 31522424
I have managed to get it to work in the end with the following. Thank LMS for your sugestions which were most helpful


Dim lngUnitID
Dim sFrm As Form
lngUnitID = Me.txtUnitID
Forms.swbEquipment.fsub01.SourceObject = "subfrmGRMachineryServicefrm"
Set sFrm = Forms.swbEquipment!fsub01.Form
sFrm.Visible = True
Forms.swbEquipment.SetFocus
Forms.swbEquipment!fsub01.SetFocus
sFrm.RecordsetClone.FindFirst "[UnitID]= " & lngUnitID
If Not sFrm.RecordsetClone.NoMatch Then
      sFrm.Bookmark = sFrm.RecordsetClone.Bookmark
ElseIf sFrm.RecordsetClone.NoMatch Then

Forms.swbEquipment.fsub01.SourceObject = "subfrmGRMachineryServicefrmAddNew"

Forms.swbEquipment!fsub01.Visible = True

End If
Forms.swbEquipment!fsub02.Form.Visible = False
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Search for text in a .txt file 14 43
Dynamically Reorder List Box 4 37
Access Changing Number to Date with Seperator 5 21
Access check if a table is open 4 39
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question