469,270 Members | 1,285 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Compile error: Method or data member not found?

Hello,

Before I ask my question, I want to say how good this forum is and how much help it has been to a complete novice.

At this moment in time I am having trouble creating VBA syntax (I think that is what it is called) that works to update one combo box (in subform) from another (in the main form). I have tried the same 'code' in the sample Northwind database and it works just fine. I have tried replacing '.' with '!' to no avail and have spent a long time reading other threads on the internet. Some of them are just beyond my understanding at the moment to be of any use.

My code for the After Update function in the first combo box is the following:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub nestcombo_AfterUpdate()
  2.  
  3.     ' Find the record that matches the control.
  4.     Dim rs As Object
  5.  
  6.     Set rs = Me.Recordset.Clone
  7.     rs.FindFirst "[nest] = '" & Me![nestcombo] & "'"
  8.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  9.  
  10.     Me.bird_idcombo.RowSource = "SELECT bird_id FROM" & _
  11.    " bird_id WHERE nest = " & Me.nestcombo & _
  12.    " ORDER BY bird_id"
  13. Me.bird_idcombo = Me.bird_idcombo.ItemData(0)
The error message 'Method or data member not found' comes up when I try to use the combo box and
Expand|Select|Wrap|Line Numbers
  1.  Me.nestcombo 
is highlighted in blue. The first line is highlighted in yellow. The first line isn't highlighted when there is only the first part of the code being used (to connect this combo box to a box in main form).

I read that this bug often highlights a different part of the code to what is actually wrong, but even playing around with it I cannot get it to work, I just get different errors!

I don't understand why the first line would go from being fine then not even though it hasn't changed at all?

Any enlightenment would be much appreciated!

Lizy
Feb 24 '11 #1
15 11035
Mariostg
332 100+
I don't know the structure of your table, but are you sure
Expand|Select|Wrap|Line Numbers
  1. SELECT bird_id FROM" & _ 
  2.    " bird_id
  3.  
is what you want? Because this says that bird_id is the name of the table.
Feb 24 '11 #2
Stewart Ross
2,545 Expert Mod 2GB
Hi Lizy. First observation: if you look closely at line 7 you will see that the FindFirst statement has single quotes within the string on either side of the reference to the value of Me![nestcombo]. This is the correct way to refer to what are known as string literal values - where the contents of the control are included as text within the string to be found. You have missed these out in line 11, which instead should read:

Expand|Select|Wrap|Line Numbers
  1. " bird_id WHERE nest = '" & Me!nestcombo & "'" & _
If your combo box exists on the main form then you should find that Me!NestCombo or alternatively Me.Nestcombo work Ok for you. If that combo is on the subform you will need to alter the syntax, as the Me shorthand will refer to the main form, not the subform.

If the NestCombo control is on the subform, the syntax for referring to it by name using the Me shortform for the main form reference is:

Expand|Select|Wrap|Line Numbers
  1. Me![SubformControlName].Form![NestCombo]
where SubformControlName must be replaced by the real name of the subform control that you have placed on the main form. This should also apply in line 7 if the control is on the subform, but you have not mentioned any errors occurring there as yet.

-Stewart
Feb 24 '11 #3
Thank you for the replies,

The bird_id is not the name of the table, but the field is from a table called tNBIRDS.

Expand|Select|Wrap|Line Numbers
  1.  Private Sub nestcombo_AfterUpdate()
  2.  
  3.     ' Find the record that matches the control.
  4.     Dim rs As Object
  5.  
  6.     Set rs = Me.Recordset.Clone
  7.     rs.FindFirst "[nest] = '" & Me![nestcombo] & "'"
  8.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  9.  
  10.     Me.bird_idcombo.RowSource = "SELECT [tNBIRDS]![bird_id] FROM" & _
  11.    " bird_id WHERE nest = '" & Me![nestcombo] & "'" & _
  12.    " ORDER BY bird_id"
  13. Me.bird_idcombo = Me.bird_idcombo.ItemData(0)
  14.  
  15.  
  16. End Sub
  17.  
I have altered my code to the above and still receive the same error message, with the Private Sub line highlighted in yellow and
Expand|Select|Wrap|Line Numbers
  1.  nestcombo 
highlighted in the 11th line....????
Feb 25 '11 #4
Mariostg
332 100+
That is what I meant, replace
Expand|Select|Wrap|Line Numbers
  1.     Me.bird_idcombo.RowSource = "SELECT [tNBIRDS]![bird_id] FROM" & _ 
  2.    " bird_id WHERE nest = '" & Me![nestcombo] & "'" & _ 
  3.  
with
Expand|Select|Wrap|Line Numbers
  1.     Me.bird_idcombo.RowSource = "SELECT [tNBIRDS]![bird_id] FROM" & _ 
  2.    " tNBIRDS WHERE nest = '" & Me![nestcombo] & "'" & _ 
  3.  
You must select FROM [tableName]
Feb 25 '11 #5
Thank you for helping but it still just throws the same error up! I really don't understand why!
Feb 25 '11 #6
Mariostg
332 100+
Well, I don't know the structure of the table, but I have a strange impression that it is the field "nest" that it does not find.

Additionnally, you could modify your code so that you can debug.print the sql statement on the immediate window (Ctrl-G) so that you can see exactly what the statement looks like when the variables have been expended to their values. Then you can run the query in the query builder which often shows better error information.
Feb 25 '11 #7
Hmmm, it finds nest in the first expression just fine, and adding [] around nest in the second to make it identical to the first has no affect still.

I am afraid that I cannot even get this expanding of the values to work, I added the debug.print to my code and to the 'Immediate' window, neither of which seemed to have any effect.?
Feb 25 '11 #8
Mariostg
332 100+
You have to do a little more than just adding debug.print :)

something like this:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub nestcombo_AfterUpdate() 
  2.  
  3.     ' Find the record that matches the control. 
  4.     Dim rs As Object 
  5.     Dim sql as string
  6.  
  7.     Set rs = Me.Recordset.Clone 
  8.     rs.FindFirst "[nest] = '" & Me![nestcombo] & "'" 
  9.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark 
  10.  
  11.     sql="SELECT [tNBIRDS]![bird_id] FROM" & _ 
  12.            " bird_id WHERE nest = '" & Me![nestcombo] & "'" & _ 
  13.            " ORDER BY bird_id" 
  14.       debug.print sql
  15.  
  16.     Me.bird_idcombo.RowSource = sql
  17. Me.bird_idcombo = Me.bird_idcombo.ItemData(0) 
  18.  
  19.  
  20. End Sub 
  21.  
Feb 25 '11 #9
Stewart Ross
2,545 Expert Mod 2GB
Hi. Mariostg has pointed out a fundamental issue relating to the name of the table concerned. If it is tNBIRDS then the SQL statement in rows 11-13 in the previous post needs to be

Expand|Select|Wrap|Line Numbers
  1.    sql="SELECT [tNBIRDS]![bird_id] FROM" & _  
  2.            " tNBIRDS WHERE nest = '" & Me![nestcombo] & "'" & _  
  3.            " ORDER BY bird_id"
-Stewart

PS if you continue to have compile issues with your DB you could attach a simplified copy as a zip file attachment to your message and we'd look at it for you.
Feb 25 '11 #10
Hello,

Thank you for helping, it now seems to say that [code]bird_idcombo[CODE/] is an unknown identifier.

When I ran the expanded code in an SQL Query it said that bird_id is from an unknown source.

Both of the above have left me deeply confused, because bird_id is the name of a field and works absolutely everywhere else.

The original problem I think was it didn't like the combobox in the main form being called nestcombo, but it always had a problem with this no matter what it was called. And now it doesn't like the name bird_idcombo? But this is what it is referred to under 'name' in the properties.
Feb 28 '11 #11
A simplified version of the database...I am going to make one now!
Feb 28 '11 #12
Here is a simple version of my database with the form in it. Has exactly the same problem as the original!
Attached Files
File Type: zip Temp.DB.zip (966.2 KB, 139 views)
Feb 28 '11 #13
NeoPa
32,171 Expert Mod 16PB
I didn't find out exactly why the compiler was having a specific problem with what it found, but there were one or two invalid references etc which, when cleared, seemed to cause the other issues to go. Further assistance when dealing with subforms can be found at Referring to Items on a Sub-Form. You should also check out the indented passage below, as submitting questions without this set can waste much time :
It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question (Require Variable Declaration).

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.
The following module code at least compiles. I hope it does the what you were trying to do too.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub nestcombo_AfterUpdate()
  5.  
  6.     ' Find the record that matches the control.
  7.     Dim rs As DAO.Recordset
  8.  
  9.     With Me
  10.         Set rs = .RecordsetClone
  11.         rs.FindFirst "[nest] = '" & .nestcombo & "'"
  12.         If Not rs.EOF Then .Bookmark = rs.Bookmark
  13.  
  14.         With .[data entry bird_id].Form.bird_idcombo
  15.             .RowSource = _
  16.                 "SELECT [bird_id] " & _
  17.                 "FROM [tNBIRDS] " & _
  18.                 "WHERE [nest] = '" & Me.nestcombo & "' " & _
  19.                 "ORDER BY [bird_id]"
  20.             .Value = .ItemData(0)
  21.         End With
  22.     End With
  23.  
  24. End Sub
PS. Bumping is now illegal on this site. Please refrain in future, but I hope this has helped anyway :-)
Mar 7 '11 #14
Thank you for trying to help me, I really do appreciate it. However, this doesn't do what I require it! Am going to try and read and hopefully understand more about VBA.

Thank you!

Lizy
Mar 9 '11 #15
Stewart Ross
2,545 Expert Mod 2GB
Hi Elizabeth. I would advise that you look more at design principles of relational databases, as you may find this helps much more than going in to VBA at this stage in your development.

The basic problem with the error message you reported was indeed an incorrectly-named reference to a control located on the subform as NeoPa indicated.
Expand|Select|Wrap|Line Numbers
  1. Private Sub nestcombo_AfterUpdate()
  2.  
  3.     ' Find the record that matches the control.
  4.     Dim rs As Object
  5.  
  6.     Set rs = Me.Recordset.Clone
  7.     rs.FindFirst "[nest] = '" & Me![nestcombo] & "'"
  8.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  9.  
  10.     Me.data_entry_bird_id.Form.bird_idcombo.RowSource = "SELECT [tNBIRDS]![bird_id] FROM" & _
  11.    " tNBIRDS WHERE nest = '" & Me![nestcombo] & "'" & _
  12.    " ORDER BY bird_id"
  13. Me.data_entry_bird_id.Form.bird_idcombobird_idcombo = Me.data_entry_bird_id.Form.bird_idcombo.ItemData(0)
  14.  
  15.  
  16. End Sub
Note the differences in lines 10 and 13 above.

There are other issues with the sample database you have provided which meant I could not myself give you a simple solution which you could apply to your database. Being able to compile does not mean that it is error-free; in testing I find there is a parameter missing error message now arising, which at this remove I am unable to trace without rewriting your application.

You have subforms nested four-deep on your main form, which is highly unusual and makes future maintenance potentially difficult.

It appears to me that relations between your tables may well need some attention, and you may benefit from reading and applying principles of relational database design more than you would by reading about VBA.

We have an insights article on database normalisation and table structures which you may find useful.

-Stewart
Mar 9 '11 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Andy Eshtry | last post: by
7 posts views Thread by Nick J | last post: by
1 post views Thread by Tim::.. | last post: by
reply views Thread by meetmaruthi | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.