Problem with retrieving data

[haruna_hamasaki]

i want to create a form which when i enter an ID no, it will view the info about the customer.
and when i enter an ID which is not in the database, a message box will pop up.
i've created my database using ms access.
this is halfway done by me.

my db + vb file: http://www.mediafire.com/?jaimfyfttez (refer to the form named frmViewInfo)

Private dbcon As New ADODB.Connection
Private rsCustomer As New ADODB.Recordset


Sub openDBConnection()

    Dim strconn As String
    
    strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=D:\Documents\Works\MTD2013 - SISTEM PANGKALAN DATA\Laundry System\Laundry System.mdb;" & _
              "Jet OLEDB:Database Password=;" & _
              "Jet OLEDB:Engine Type=5;"
            
    dbcon.Open strconn

End Sub

Private Sub Form_Load()
    
    Me.txtICNo.Text = ""
    
    Call openDBConnection
    Call openCustomerRecord
    
End Sub

Sub openCustomerRecord()

    Dim strSQL As String
    
    strSQL = "SELECT * FROM Customer"
    rsCustomer.Open strSQL, dbcon, 3, 1
    
End Sub

Private Sub cmdEnter_Click()

    Me.txtFName.Locked = True
    Me.txtLName.Locked = True
    Me.txtPhoneNo.Locked = True
    Me.txtDSent.Locked = True
    Me.txtDTaken.Locked = True
    Me.txtRegClean.Locked = True
    Me.txtDryClean.Locked = True
    Me.txtTotal.Locked = True
    
    Me.txtFName.Text = rsCustomer!fName
    Me.txtLName.Text = rsCustomer!lName
    Me.txtPhoneNo.Text = rsCustomer!phoneNo
    
End Sub

[si_the_geek]

Welcome to VBForums

I would recommend taking a look at the "Further Steps" link in my signature (and possibly the "ADO Tutorial" it is based on too), as it explains how to create a search feature.

[haruna_hamasaki]

thanks. i'll give it a try first.

[haruna_hamasaki]

I still can't get the correct coding. Actually I want to call data from 3 tables and after the search is done, I want all the chosen data are viewed on text box (which are locked so that they can't be edited).

I got this error:

Operation is not allowed when the object is closed.

I did this to my coding:

Private Sub cmdEnter_Click()
 
    Me.txtFName.Locked = True
    Me.txtLName.Locked = True
    Me.txtPhoneNo.Locked = True
    Me.txtDSent.Locked = True
    Me.txtDTaken.Locked = True
    Me.txtRegClean.Locked = True
    Me.txtDryClean.Locked = True
    Me.txtTotal.Locked = True
    
    Dim strSQL As String
    strSQL = "SELECT * FROM Customer"
    
    If txtICNo.Text <> "" Then
        strSQL = strSQL & " WHERE icNo = " & Val(txtICNo.Text)
    End If
    
    rsCustomer.Close
    
    rsCustomer.Open strSQL, dbcon
            
    Me.txtFName.Text = rsCustomer!fName
    Me.txtLName.Text = rsCustomer!lName
    Me.txtPhoneNo.Text = rsCustomer!phoneNo
    
End Sub

[si_the_geek]

I presume you got that on the rsCustomer.Close line.

Based on the code you have posted I find it odd that it is closed, as you don't close it anywhere (which is not a good idea, you should close things when you are done with them - especially recordsets and connections, even more so when using Access databases).

The way I would recommend to correct it is to change that line to this:

If (rsCustomer.State And adStateOpen) = adStateOpen Then rsCustomer.Close

This will close it only if needed.

[haruna_hamasaki]

As suggested I follow your line given above.
I got this error.

Run-time error '3709':
The connection cannot be used to performed this operation. It is either closed or invalid in this context.

This is my whole coding:

Private dbcon As New ADODB.Connection
Private rsCustomer As New ADODB.Recordset
 
Sub openDBConnection()
 
    Dim strconn As String
    
    strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=D:\Documents\Works\MTD2013 - SISTEM PANGKALAN DATA\Laundry System\Laundry System.mdb;" & _
              "Jet OLEDB:Database Password=;" & _
              "Jet OLEDB:Engine Type=5;"
            
    dbcon.Open strconn
 
End Sub
 
Private Sub cmdEnter_Click()
 
    Me.txtFName.Locked = True
    Me.txtLName.Locked = True
    Me.txtPhoneNo.Locked = True
    Me.txtDSent.Locked = True
    Me.txtDTaken.Locked = True
    Me.txtRegClean.Locked = True
    Me.txtDryClean.Locked = True
    Me.txtTotal.Locked = True
    
    Dim strSQL As String
    strSQL = "SELECT * FROM Customer"
    
    If txtICNo.Text <> "" Then
        strSQL = strSQL & " WHERE icNo = " & Val(txtICNo.Text)
    End If
 
    If (rsCustomer.State And adStateOpen) = adStateOpen Then rsCustomer.Close
 
    rsCustomer.Open strSQL, dbcon, 3, 1
    
    Me.txtFName.Text = rsCustomer!fName
    Me.txtLName.Text = rsCustomer!lName
    Me.txtPhoneNo.Text = rsCustomer!phoneNo
 
End Sub

[dee-u]

You should insert the procedure of opening the connection object first...

Private Sub cmdEnter_Click()
 openDBConnection

[haruna_hamasaki]

^ Looks like it solved but I'm not sure why it stated that there's Run-Time Error -2147217913(80040e07), it's about data mismatch. I've checked the database, I'm not sure which it refers to.


The search function above only valid for data which already in the database. I want to put a msgbox which says "No record found" if the data (IC No) is not in the database. But I don't know where to put it.



To be honest, I am only a beginner in VB6 but I was already asked to do such a difficult system and my lecturer just want me to search for internet help. That's why I have no idea how to make this system works properly...

[dee-u]

On what line does the error occurs? I can only guess that it is on Val(txtICNo.Text) where the value of txtICNo. is not a valid number?

[haruna_hamasaki]

line 37.

[dee-u]

How is icNo declared, is it a number or a text? If it is a text then you should enclose it with quotes...

WHERE icNo = '" & Val(txtICNo.Text) & "'"

[si_the_geek]

While dee-u's suggestion in post #7 will work, it is not an ideal way to do things.. and neither is the way you declared the variables (for info, see the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs).

To see a better way, I would recommend reading the ADO Tutorial (link in my signature), as it shows how to declare/use those variables in a better way, and does other things you might find useful too - and all of it is explained.


Note that it is a bad idea to use "magic numbers" as you did on this line:

    rsCustomer.Open strSQL, dbcon, 3, 1

It is better to use the constants provided instead (such as adOpenReadOnly), as it makes the code much easier to read - and thus easier to spot possible problems, and maintain, etc. The constants are part of the ADO library, so if you can use a recordset or connection etc, you can be sure that the constants are available too.