How to use a multi value result set

[Gr3g0ry]

Private Sub cborentals_AfterUpdate()
Dim who As String

Me.cbostudent.Value = cborentals.Column(1)
Me.Fname.Value = cborentals.Column(5)
Me.Lname.Value = cborentals.Column(6)
Me.sgrade.Value = cborentals.Column(7)
Me.CLASS.Value = cborentals.Column(8)

Me.cbobookid.Value = cborentals.Column(2)
who = cborentals.Column(2)

strSql = "SELECT Bookname FROM Books WHERE Books.Bookid = '" & who & "' "
'strSql2 = "SELECT Books.Subject as SubjectName FROM Books WHERE Books.Bookid = '" & who & "' "

Set rst = CurrentDb.OpenRecordset(strSql)
'Set rst2 = CurrentDb.OpenRecordset(strSql2)

If rst.EOF Then
Else
Me.book.Value = rst!Bookname
End If

'If rst2.EOF Then
'Else
' Me.subject1.Value = rst!SubjectName
'End If


End Sub


Im trying to get subject name and book name from my book table, based on a book ID. how do i do all this with one query & result set

 

[theDBguy]

Your strSql2 seems to do it already. What was wrong with it?

 

[Gr3g0ry]

Your strSql2 seems to do it already. What was wrong with it?

and then this

 

[The_Doc_Man]

First, a suggestion to simplify your work. The default property for anything that HAS a value is the .Value property, so you don't have to expressly use the .Value suffix for everything. VBA will assume that for you. Pretty much every case of Me.xxxx where that control COULD take a value, you have included the suffix when you don't need to.

The second thing that is suspicious is that you have declared a SET for a thing that should be a recordset, but that wasn't declared in the code you showed us. If it is declared externally as a recordset (i.e. it is a PUBLIC variable from some outer context), that is an OK if somewhat questionable method. Questionable because it glosses over what would appear to be an intentional "side effect." If rst is NOT external to this code AND you ALSO don't have OPTION EXPLICIT declared at the top of each module, then your variable rst becomes a variant, which COULD become a recordset. But you would probably do a lot better to explicitly declare variables. It will make things a LOT easier to debug with Option Explicit in effect for every module.

Then, one more "gotcha" is that your code tells us you have something like 9 BookID combos and are declaring values for them based on the extended columns associated with the Rentals combo. It might be totally benign, but it looks like a denormalized setup. I admit I could be wrong, but my "spidey sense" is tingling on that one.

 

[theDBguy]

I was saying you probably don't need strSql, because strSql2 should work. In strSql, you had SELECT Book.Name...

In other words, you used Book (without an 's') while your table name seems to be Books (with an 's').

 

[KitaYama]

If it was me, I wouldn't open two recordsets for this. DLookup does the job

Me.book=DLookup("Bookname","Books ","Bookid='" & who & "'")
Me.subject1=DLookup("SubjectName","Books ","Bookid='" & who & "'")

Bookid suggests it's a number. But who seems to be a string. I wouldn't use a string data type as ID. I would use the Autonumber (PK) field of the book table.

 

[ebs17]

cborentals is a ComboBox with a RowSource, table/query?

... WHERE Books.Bookid = '" & who & "' "

So it is possible to link this RowSource with the Books table. So this linked query should be the same RowSource of the ComboBox, and you have all desired contents available in it, you can do without extra recordsets and DLookups.

Instead of assigning the ComboBox columns to form text fields, you should perhaps use a bound form.

 

[Gr3g0ry]

First, a suggestion to simplify your work. The default property for anything that HAS a value is the .Value property, so you don't have to expressly use the .Value suffix for everything. VBA will assume that for you. Pretty much every case of Me.xxxx where that control COULD take a value, you have included the suffix when you don't need to.

The second thing that is suspicious is that you have declared a SET for a thing that should be a recordset, but that wasn't declared in the code you showed us. If it is declared externally as a recordset (i.e. it is a PUBLIC variable from some outer context), that is an OK if somewhat questionable method. Questionable because it glosses over what would appear to be an intentional "side effect." If rst is NOT external to this code AND you ALSO don't have OPTION EXPLICIT declared at the top of each module, then your variable rst becomes a variant, which COULD become a recordset. But you would probably do a lot better to explicitly declare variables. It will make things a LOT easier to debug with Option Explicit in effect for every module.

Then, one more "gotcha" is that your code tells us you have something like 9 BookID combos and are declaring values for them based on the extended columns associated with the Rentals combo. It might be totally benign, but it looks like a denormalized setup. I admit I could be wrong, but my "spidey sense" is tingling on that one.

im appreciate your input, im teaching myself VBA so its a learning process.

 

[Gr3g0ry]

I was saying you probably don't need strSql, because strSql2 should work. In strSql, you had SELECT Book.Name...

In other words, you used Book (without an 's') while your table name seems to be Books (with an 's').

corrected but now im getting this error

and

 

[Gr3g0ry]

What im trying to do is get two values, book name and subject from the books table. Id like to learn to correctly use a recordset and use individual values from the recordset.

 

[Gr3g0ry]

cborentals is a ComboBox with a RowSource, table/query?

... WHERE Books.Bookid = '" & who & "' "

So it is possible to link this RowSource with the Books table. So this linked query should be the same RowSource of the ComboBox, and you have all desired contents available in it, you can do without extra recordsets and DLookups.

Instead of assigning the ComboBox columns to form text fields, you should perhaps use a bound form.

cborentals is a combobox populated by a query that spans 3 tables

 

[theDBguy]

corrected but now im getting this error

What happens if you replace that line with the following?

MsgBox rst2!Subject

 

[Gr3g0ry]

ive resolved the issues i was having ... thanks a million guys

 

[theDBguy]

ive resolved the issues i was having ... thanks a million guys

Congratulations! Good luck with your project.