Solved

Method or Data Member Not Found

Posted on 2008-10-22
13
348 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now