Method or data member not found

[VBNew]

I'm new in VB6, and i'm having an error message while debugging...after the login checking, I need to open the form to select a project code from my Access db. The error was stop at RS.OPEN. and prompt the error message "Method or data member not found". Now, when I try to retype the words RS. but I do not found "Open" object anymore. Can anyone help and advise me what mistake I have make? Or any component I need to add in order I can select the "object OPEN"? Please help!

Private Sub Form_Load()
Dim RS As Recordset
Set RS = New ADODB.Recordset


GetDB
RS.Open "Select ProjectCode from PrjDetails", Mydb, adOpenKeyset
   If RS.RecordCount > 0 Then
        RS.MoveFirst
        Do While RS.EOF = False
            PrjCode.AddItem RS!ProjectCode
            'PrjCode.List(PrjCode.ListCount - 1, 1) = RS!ProjectCode
            RS.MoveNext
        Loop
    End If
    
  RS.Close
  Set RS = Nothing


End Sub

Thanks,
VBNew

[kinwang2009]

Hi VBNew,
Can you paste your GetDB code too?

[VBNew]

Dear kinwang,here is the GetDB code!

Public Mydb As ADODB.Connection



Public Sub GetDB()

Set Mydb = New ADODB.Connection
With Mydb
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = App.Path & "\ProjectProgress.mdb"
    .Open
End With

End Sub

Thanks!

[kinwang2009]

Or here is the code Hope this helps

Dim cn As ADODB.cnnection
Dim cmd As ADODB.Command
Dim rs As ADODB.RecordSet
Dim cnString As String
Dim strSQL As String
Set rs As New ADODB.Recordset   
        cnString = "DBQ=" & App.Path & "\ProjectProgress.mdb" & "; Driver={Microsoft Access Driver (*.mdb)};"
    Set cn = New ADODB.cnnection
    With cn
        .cnnectionString = cnString
        .Open , "Admin", "password" 'Username and password of database
    End With
strSQL = "Select ProjectCode from PrjDetails"
rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

If RS.RecordCount > 0 Then
RS.MoveFirst
Do While RS.EOF = False
PrjCode.AddItem RS!ProjectCode
'PrjCode.List(PrjCode.ListCount - 1, 1) = RS!ProjectCode
RS.MoveNext
Loop
End If

RS.Close
Set RS = Nothing
Set cmd = Nothing    
cn.Close
Set cn = Nothing

And also make sure that all below reference are checked.
1. Visual Basic for Applications
2. Visual Basic runtime objects and procedures
3. Visual Basic objects and procedures
4. OLE automation
5. Microsoft Active Data Objects 2.6 library

Thanks

[AndreRet]

VBNew, although Kinwang is correct in his code, it will cause a lot of duplication. Your problem lays in your code, calling myDb, which resides in a different sub. Add this to your function and then call it -

Public Sub GetDB()
 
Set Mydb = New ADODB.Connection
With Mydb
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = App.Path & "\ProjectProgress.mdb"
    .Open
End With
 
End Sub

'Change this to -

Public Sub GetDB(Mydb As ADODB.Connection)
 
Set Mydb = New ADODB.Connection
With Mydb
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = App.Path & "\ProjectProgress.mdb"
    .Open
End With
 
End Sub

'Then on your call -

GetDB (Mydb)
RS.Open "Select ProjectCode from PrjDetails", Mydb, adOpenKeyset

[VBNew]

Dear AndreRet and Kinwang,

I try both of your code, I'm still having the same error message.

If apply Kinwang answer, i have the same error message
If apply AndreRet answer, then I have Type mismatch of GetDB(Mydb)

Maybe I explain in more details:-
1st the login screen will allow user to enter username and password, once is ok then it will allow the user to select the project code and then user have to select the action he needs to do(program not yet reach to this part). So below is my login coding. The error still under the RS.Open. But why in login coding I also use RS.open but why no problem only in the Mainmenu? Could you please advise?

Private Sub cmdOK_Click()

  Dim RS As ADODB.Recordset
  Dim IntRSP As Integer
  Set RS = New ADODB.Recordset
    
        Call GetDB(Mydb)
        RS.Open ("select * from UserTable Where UserName = '" & txtUserName & "' and Password='" & txtPassword & "'"), Mydb, adOpenDynamic, adLockOptimistic
   
If RS.EOF Then
      IntRSP = MsgBox("Invalid LoginID and password, please try again!", vbOKOnly, "Login")
        If IntRSP = vbOK Then
            txtUserName = ""
            txtPassword = ""
            txtUserName.SetFocus
            Exit Sub
        End If
End If

    RS.Close
   
    Unload frmLogin
    Load frmMainMenu
    frmMainMenu.Show
   
End Sub

Private Sub Form_Load()
Dim RS As Recordset
'Dim strSQL As String
Set RS = New ADODB.Recordset

GetDB (Mydb)
RS.Open ("Select ProjectCode from PrjDetails"), Mydb, adOpenKeyset
   If RS.RecordCount > 0 Then
        RS.MoveFirst
        Do While RS.EOF = False
            PrjCode.AddItem RS!ProjectCode
            'PrjCode.List(PrjCode.ListCount - 1, 1) = RS!ProjectCode
            RS.MoveNext
        Loop
    End If
    
  RS.Close
  Set RS = Nothing

[AndreRet]

Below is the working code that I have tested. Note that in your public sub 'Getdb' you do not have to declare the connection, it is dine in the first line. Sooo, to a module add -

Option Explicit

Public Sub GetDB(Mydb As ADODB.Connection)

Set Mydb = New ADODB.Connection

With Mydb
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = App.Path & "\ProjectProgress.mdb"
    .Open
End With
End Sub

Now, you have to declare the connection again, otherwise errors. You do however do not have to rewrite the entire connection over again. Sooo, to a form in say a command click event or load event the following -

Private Sub Command1_Click()

Dim Mydb As ADODB.Connection

Set Mydb = New ADODB.Connection

GetDB Mydb

Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
 
RS.Open "Select ProjectCode from PrjDetails", Mydb, adOpenKeyset
   If RS.RecordCount > 0 Then
        RS.MoveFirst
        Do While RS.EOF = False
            PrjCode.AddItem RS!ProjectCode
            'PrjCode.List(PrjCode.ListCount - 1, 1) = RS!ProjectCode
            RS.MoveNext
        Loop
    End If
 
  RS.Close
  Mydb.Close
End Sub

, and viola, all fine.

Did this help?

[VBNew]

Dear AndreRet,

Is work now....thanks a lot!

I have another question, hope you don't mind.

The projectCode actually is a combobox, now I'm able to select the project code available in my database, but I'm not sure how to code the test box when the user select the projectcode then the project name will automatically appear.

Thanks,
Annie Low

[AndreRet]

Annie, under the combobox event, add the following -

Dim Mydb As ADODB.Connection
 
Set Mydb = New ADODB.Connection
 
GetDB Mydb
 
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset

Dim strName As String

strName = Combo1.Text
 
RS.Open "Select ProjectCode from PrjDetails WHERE ProjectCode =" & "'" & strName & "'", Mydb, adOpenKeyset
   
Text1.Text = RS!ProjectCode
 
  RS.Close
  Mydb.Close

If this helped, please mark as solved thanks Annie.

[mavin]

Hi everyone,

i need help in my project in vb, when i try to run to program i always get the message error method or data member not found with regard the formatstring, here is the code.

Private Sub Form_Load()
vsStocks.FormatString = "STOCK NO. | DESCRIPTION | STOCK PRICE | QUANTITY | CATEGORY | SIZE"
cboParameters.Text = "Stock NO."
cboDisplay.Text = "ALL (*)"
End Sub

I have no clue how to get rid of the error message, i hope u will help me tnx in advance.

[AndreRet]

@Mavin, please open a new post with your question. Nobody will respond on your question from here because this is a solved post.

Please read our posting rules.:)

[mavin]

@AndreRet: sorry sir, tnx for the info.

[AndreRet]

No problem. Will reply soon on the new thread.:)