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

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"

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

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"

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

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"

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