Getting data from access to VB

Can anyone help me about this?
the VB highlight the code
txtname1.Text = rs.Fields("Name")
and the number in txtid1.Text always being 1
and i can't change the number in this textbox

Code:

---

Private Sub txtid1_change()
FOOD = "Select * from menu"
Set rs = New ADODB.Recordset
rs.Open FOOD, conn, adOpenKeyset, adLockOptimistic
If Not rs.EOF And Not rs.BOF Then
txtid1.Text = rs.Fields("Food ID")
txtname1.Text = rs.Fields("Name")
rs.MoveNext
rs.Close
End If
End Sub

---

It says "Operation is not allowed when the object is closed"

Since I can tell by your post count, you're new to these parts, I'll spare you from my eels comments.

A couple of things. First, in the future, when posting code, PLEASE, for the sake of everyone's sanity, put [code][/code] tags around the code... that will preserve any indents, and make the code easier to read.

Secondly, if VB highlighted that line for some reason, it's probably an error of some kind. VB doesn't just arbitrarily highlight things. If that's the case, then what was the error?

-tg

ps - welcome to the forums!

Did you open conn?

ys i did

Code:

---

Private Sub Form_initialize()
Set conn = New ADODB.Connection
 conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\A2\coursework.mdb"
 conn.Open
End Sub

---

Are you sure that it is highlighting the line

Code:

---

txtname1.Text = rs.Fields("Name")

---

?

I did a quick modification and see if it would work. You are not closing the recordset in all conditions and I fixed that. And the MoveNext is totally unnecessary. Plus, how about just instantiating rs in your form load so as not to instantiate it per call to the Change event of txtid1?

Code:

---

Private Sub txtid1_change()
    FOOD = "Select * from menu"
    Set rs = New ADODB.Recordset
    With rs
        .Open FOOD, conn, adOpenKeyset, adLockOptimistic
        If Not (.EOF And .BOF) Then
            txtid1.Text = rs.Fields("Food ID")
            txtname1.Text = rs.Fields("Name")
            'rs.MoveNext  - No Purpose
        End If
        .Close
    End With
    'clean-up
    Set rs = Nothing
End Sub

---

i m sure it highlights the code

Code:

---

txtname1.Text=rs.Fields("Name")

---

and it still highlighs it after i put your code in
and i got an error saying Items cannot be in the collection corresponding to the requested name or ordinal

Part of the problem might be that Name is a reserved word and should not be used as a field name.

If you must use it as a field name, when referencing it, place it in brackets, i.e., [Name]

However, I suspect the larger issue is that you have your code in the "Change" event.

Why do you have your code in the "Change" event rather than the "Click" event?

That error is very different from the one you had before, and the problem is much easier to solve - make sure you have got the right field and table names, because there is no field in your recordset called "Name".


If the names are both correct, change the field name (in the database and your code) to something other than Name, as it is likely to be a reserved word.

i have changed the names of the fields but it's still the same as before

I changed it to "click" now but every number i put into the txtid1, it automatically changed to 1

It isn't getting the same error, so is not the same as before.

That is because (as Hack pointed out) you are using the change event - every time the value changes (by the user or your code) the value is changed to whatever is in the record you load. As you haven't specified which record to use, it could be any that are in the table.

But shouldn't it be entering the id of the food and then the name of that food will show in the program? and i have changed the 'change' to 'click' now

Your code loads all records from the menu table, and then for whichever record is read first displays the values of "Food ID" and "Name" in the textboxes.

If you want to only load (and display) a particular record, you should specify that in your SQL statement, eg:

Code:

---

    FOOD = "Select * from menu WHERE [Food ID] = " & txtid1.Text

---

Note that if that is what you want, there is no point putting the value of Food ID into the textbox, as you can be certain that it already has that value.

That would also mean you only need to read the Name field, so you should specify that you only want to load that one:

Code:

---

    FOOD = "Select [Name] from menu WHERE [Food ID] = " & txtid1.Text

---

but i want to make it like putting the Food ID in the txtid1 and then the name of that foold will show

Show where?

Name show in the txtname1 and id in the txtid1

You already know how to display it with your own code, do you have another thing in mind?

Code:

---

txtname1.Text=rs.Fields("Name")

---

becox for this coursework we have to link the db and vb with code.......can't use the data control thing........so is more complicated than normal