Setting a recordset problem 2

[FireViking]

Hi all,
I am trying to access a table at run time through code to change the value of certain feilds.
The form at run time is bound to table 'A', I have a listbox on the form that is populated from a query of Table 'B'. Once the user clicks an item in the listbox I want to change the value of a feild in Table 'B' so that it does'nt show in the listbox anymore.

The code is written in the click event of the listbox
Dim db As Database
Dim rs As Recordset
Dim intBookID As Integer

intBookID = ListBox.Value 'ID number of the book
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblBooks where BookID = " & intBookID)
rs("Issued&quot = False
rs.Close
db.Close

for some reason i keep getting a mismatch error while tring to set the recordset.

Can someone give some ideas.

 

[tlbroadbent]

intBookID is defined as Integer and you are attempting to concantenate it with a string in the query statement. Change intBookID to string and the OpemRecordset should work. You may also need to use the TEXT property rather than the VALUE property of the ListBox.

Dim intBookID As string

intBookID = ListBox.text 'ID number of the book
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[PaulF]

perform an Update Operation

Private Sub lstBooks_AfterUpdate()
If lstBooks <> &quot;&quot; And lstBooks <> &quot; &quot; Then
DoCmd.SetWarnings False
DoCmd.RunSQL (&quot;UPDATE tblBooks SET tblBooks.Issued = False &quot; _
& &quot;WHERE tblBooks.BookId= &quot; & Me.lstBooks)
DoCmd.SetWarnings True
End If
End Sub

PaulF

 

[tlbroadbent]

I concur with PaulF. His recommendation is actually the better way to update the table and it is simpler! Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[FireViking]

Thank you for your help, it works fine and solved my problem.

Cheers

 

[tlbroadbent]

FireViking,

What worked fine? In other words, which solution did you use? Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin