Compile error: Method or data member not found?

[Elizabeth Mitte]

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:

 Private Sub nestcombo_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[nest] = '" & Me![nestcombo] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    Me.bird_idcombo.RowSource = "SELECT bird_id FROM" & _
   " bird_id WHERE nest = " & Me.nestcombo & _
   " ORDER BY bird_id"
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

 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

[Mariostg]

I don't know the structure of your table, but are you sure

SELECT bird_id FROM" & _ 
   " bird_id

is what you want? Because this says that bird_id is the name of the table.

[Stewart Ross]

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:

" 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:

Me![SubformControlName].Form![NestCombo]

where SubformControlN ame 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

[Elizabeth Mitte]

Thank you for the replies,

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

 Private Sub nestcombo_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[nest] = '" & Me![nestcombo] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    Me.bird_idcombo.RowSource = "SELECT [tNBIRDS]![bird_id] FROM" & _
   " bird_id WHERE nest = '" & Me![nestcombo] & "'" & _
   " ORDER BY bird_id"
Me.bird_idcombo = Me.bird_idcombo.ItemData(0)


End Sub

I have altered my code to the above and still receive the same error message, with the Private Sub line highlighted in yellow and

 nestcombo

highlighted in the 11th line....????

[Mariostg]

That is what I meant, replace

    Me.bird_idcombo.RowSource = "SELECT [tNBIRDS]![bird_id] FROM" & _ 
   " bird_id WHERE nest = '" & Me![nestcombo] & "'" & _

with

    Me.bird_idcombo.RowSource = "SELECT [tNBIRDS]![bird_id] FROM" & _ 
   " tNBIRDS WHERE nest = '" & Me![nestcombo] & "'" & _

You must select FROM [tableName]

[Elizabeth Mitte]

Thank you for helping but it still just throws the same error up! I really don't understand why!

[Mariostg]

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.

[Elizabeth Mitte]

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.?

[Mariostg]

You have to do a little more than just adding debug.print :)

something like this:

 Private Sub nestcombo_AfterUpdate() 
  
    ' Find the record that matches the control. 
    Dim rs As Object 
    Dim sql as string
  
    Set rs = Me.Recordset.Clone 
    rs.FindFirst "[nest] = '" & Me![nestcombo] & "'" 
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark 
    
    sql="SELECT [tNBIRDS]![bird_id] FROM" & _ 
   		" bird_id WHERE nest = '" & Me![nestcombo] & "'" & _ 
   		" ORDER BY bird_id" 
  	debug.print sql
  	
    Me.bird_idcombo.RowSource = sql
Me.bird_idcombo = Me.bird_idcombo.ItemData(0) 
 
  
End Sub

[Stewart Ross]

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

   sql="SELECT [tNBIRDS]![bird_id] FROM" & _  
           " tNBIRDS WHERE nest = '" & Me![nestcombo] & "'" & _  
           " 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.

[Elizabeth Mitte]

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.

[Elizabeth Mitte]

A simplified version of the database...I am going to make one now!

[Elizabeth Mitte]

Here is a simple version of my database with the form in it. Has exactly the same problem as the original!

[NeoPa]

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 :

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.

Option Compare Database
Option Explicit

Private Sub nestcombo_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As DAO.Recordset

    With Me
        Set rs = .RecordsetClone
        rs.FindFirst "[nest] = '" & .nestcombo & "'"
        If Not rs.EOF Then .Bookmark = rs.Bookmark

        With .[data entry bird_id].Form.bird_idcombo
            .RowSource = _
                "SELECT [bird_id] " & _
                "FROM [tNBIRDS] " & _
                "WHERE [nest] = '" & Me.nestcombo & "' " & _
                "ORDER BY [bird_id]"
            .Value = .ItemData(0)
        End With
    End With

End Sub

PS. Bumping is now illegal on this site. Please refrain in future, but I hope this has helped anyway :-)

[Elizabeth Mitte]

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