Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 26th, 2004, 02:15 AM
Authorized User
 
Join Date: Jul 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Tasha Send a message via Yahoo to Tasha
Default complile error : method or data member not found!

i have a combo box based on a query. it goes to error.....it worked perfectly when i test it in anew db..but when i add this in another database that connects to sql server it giving me error.

compile error : method or data member not found!...highlighted at private Sub cboMachineNum_click()


Private Sub cboMachineNum_Click()
On Error GoTo err
Dim rsMN As Recordset
Dim TMPSQL As String

    TMPSQL = ""
    TMPSQL = "select machine_id from machine where machine_num like '" & Trim(Me.cboMachineNum.Text) & "'"

    Set rsMN = New ADODB.Recordset
    'rsMN.Open TMPSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

    If Len(Me.cboMachineNum.Text) > 0 Then
            If rsMN.RecordCount > 0 Then
                machine_id = rsMN!machine_id
            End If
        rsMN.Close
        Set rsMN = Nothing
    End If
Exit Sub
err:
    MsgBox "Error in getting machine ID.", vbExclamation + vbOKOnly, "Error!"
End Sub

can anyone out there help me on this?thanks in advance


tasha
__________________
tasha
 
Old October 26th, 2004, 07:17 AM
Authorized User
 
Join Date: Oct 2004
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Tasha-

First, you should change the label of the error procedure. "Err" is a reserved word in Visual Basic. And why is rsMN.Open commented out in this code?

Your error trap is masking the location of the real error. To get it to halt on the statement that is causing the error, open the Visual Basic Editor, choose Options from the Tools menu, and temporarily select Break On All Errors on the General tab. Run your code again, and it should halt on the offending statement.

The problem might be your reference to Me.cboMachineNum.Text - the Text property exists only when the control has the focus. However, I would expect the control to have the focus in the Click event of the control. You could try removing the .Text.

But if the rsMN.Open is really commented out, then rsMN.RecordCount doesn't exist yet.

Hope that helps...

John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
 
Old October 26th, 2004, 07:53 PM
Authorized User
 
Join Date: Jul 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Tasha Send a message via Yahoo to Tasha
Default

Thanks JLovell,

when i commented out the rsMN.Open the method or data member not found..but it goes to
err:
    MsgBox "Error in getting machine ID.", vbExclamation + vbOKOnly, "Error!"
End Sub

i tried removing the .text and change my label for err as err1....but it still giving me the same error..


tasha
 
Old October 26th, 2004, 08:02 PM
Authorized User
 
Join Date: Oct 2004
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Do you have the ADO library loaded in Tools / References? To make sure you're getting the correct Recordset object, declare it as:

Dim rsMN As ADODB.Recordset

But why are you using ADO if this is in an mdb file? If this is an mdb, you should be using DAO. What is it you're trying to do with this code?


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
 
Old October 26th, 2004, 08:34 PM
Authorized User
 
Join Date: Jul 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Tasha Send a message via Yahoo to Tasha
Default

thanks a lot...i changed and now my combo box doesnt give me any error.

but when i click OK i am getting error Object variable or width block variable not set.....
highlighted at rsRec.close


my cboMachineNum is based on this query :

SELECT machine.machine_num
FROM machine
ORDER BY machine.machine_num;

i have cboWinderNum based on this query :

SELECT winder.winder_number
FROM winder
ORDER BY winder.winder_number;

when the user select both machine num and winder number and click OK, i want to store that records in another table called ztCarrier. this records will be stored with current date and seq_number tat increment by 1 every click event.

this is my code :

Private Sub cboWinderNum_Click()
On Error GoTo err1
Dim rsWN As ADODB.Recordset
Dim TMPSQL As String

    TMPSQL = ""
    TMPSQL = "select machine_num from winder where winder_number like '" & Trim(Me.cboWinderNum) & "'"

    Set rsWN = New ADODB.Recordset
    rsWN.Open TMPSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

    If Len(Me.cboWinderNum) > 0 Then
            If rsWN.RecordCount > 0 Then
                machine_num = rsWN!machine_num
            End If
        rsWN.Close
        Set rsWN = Nothing
    End If
Exit Sub
err1:
    MsgBox "Error in getting winder ID.", vbExclamation + vbOKOnly, "Error!"
End Sub



 '//------------------------------------------------------------------------------Private Sub cmdOK_Click()

    Dim rsRec As ADODB.Recordset
    Dim rsInsertRec As ADODB.Recordset

    Dim vseq_num As Integer
    Dim TMPSQL As String

     '//--- begining adding 1 to seq_num ---
    Me.cboWinderNum.SetFocus

    TMPSQL = "SELECT MAX([seq_num]) + 1 AS seq_num_Default "
    TMPSQL = TMPSQL & "FROM ztCarrier"

     Set rsRec = New ADODB.Recordset
     rsRec.Open TMPSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

    If Len(Me.cboWinderNum) > 0 Then
        If rsRec.RecordCount > 0 Then
            vseq_num = rsRec!seq_num_default
        End If
         rsRec.Close
         Set rsRec = Nothing
    End If

    rsRec.Close
    Set rsRec = Nothing
    '//--- end of adding 1 to seq_num ---

    '//--- begining insert data into the tmpTBL ---
    TMPSQL = ""
    TMPSQL = "select * from ztCarrier"
    Set rsInsertRec = New ADODB.Recordset
    rsInsertRec.Open TMPSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

    Me.cboWinderNum.SetFocus
    rsInsertRec.AddNew
    rsInsertRec!machine_id = Trim(machine_id) '//--- captured during the click event of cboMacniceNum ---
    rsInsertRec!winder_number = Trim(Me.cboWinderNum)
    rsInsertRec!machine_num = machine_num '//--- captured during the click event of cboWinderNum ---
    rsInsertRec!vdate_printed = Format(Now())
    rsInsertRec!seq_num = vseq_num '//--- max seq_mun captured thru the query to ztCarrier table ---
    rsInsertRec.Update

    rsInsertRec.Close
    Set rsInsertRec = Nothing
    '//--- end insert data into the ztCarrier ---
    '//---------------------------------------------------------------------------



tasha
 
Old October 26th, 2004, 10:51 PM
Authorized User
 
Join Date: Oct 2004
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Tasha-

Pasting a bunch of code doesn't help if you don't identify exactly which statement is causing the error. Open the VB Editor, choose Options from the Tools menu, temporarily set Break on All Errors on the General tab, and then run your form. Click Debug when you get the error, and it should take you into the code and highlight the statement that is blowing up.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
 
Old May 1st, 2007, 03:49 PM
Registered User
 
Join Date: May 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to forensicpsyguy Send a message via MSN to forensicpsyguy Send a message via Yahoo to forensicpsyguy
Default

This topic is very old....but....I thought that my solution to this error message may be helpful to someone else. Although there can obviously be more the one reason for receiving any given error, MY error was because of my references.

Although my code had previously worked, I had changed my references to include a reference to ADO since I added some code to my DB that used it. However, once ADO was referenced, my old DAO code no longer worked. Since I never got my ADO code working, I deleted the reference to ADO. My old code that was causing this error (method or data member not found) then started to work again.

Now, I am only an intermediate programmer as far as skill goes. I don't know if there is occasion to reference both DAO and ADO, but if you must do that....the order of your references matters also. Therefore, my error problem may have been fixed by changing the order of the references vs. just deleting the reference to ADO.

Hope my comment helps someone.

Sam J Stephens





Similar Threads
Thread Thread Starter Forum Replies Last Post
Method or Data Member not Found Related to Crystal ishqvj Beginning VB 6 0 November 29th, 2006 03:49 AM
Compile Error data member not found alfiee Access VBA 1 July 7th, 2006 05:35 PM
Method or data member not found Subuana Beginning VB 6 1 March 6th, 2006 03:32 PM
Method or Data member not found Anup Gavate VB How-To 2 March 25th, 2005 04:50 PM
LoadFrom Invoke Member... Method not found DolphinBay ASP.NET 1.0 and 1.1 Basics 0 April 29th, 2004 09:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.