A problem reguarding saving from listboxes

Okay, I want to have a simple system where two listboxes interact. One shows the MemberID, Forename and Surname from Member and the other shows BookID and title. When you select something from each list, you can click add loan and it will use a table called Loan and add the MemberID and BookIDs to columns (They are made via AutoNumber in Access, they save to a number datatype), and then input today's date. The idea is I can then use this table with foreign keys to show the full details in that relationship.

However, when ever I attempt a loan, it gibes me an error saying "Run-time error '3420': Object invalid or no longer set", highlighting "rs3!MemberID = rs!MemberID" in Private Sub MakeLoanButton_Click().

Basically, what am I doing wrong? All I need is the relevent ID.

As an aside, is there any way to make it so that a message popup appears if you try and add a loan when you haven't selected from both columns, stopping it from adding?


The code follows:
Private Sub Form_Load()
Set MyDataBase = OpenDatabase(App.Path & "\Library.mdb")
Set rs = MyDataBase.OpenRecordset("select * from Member Order by MemberID")
Set rs2 = MyDataBase.OpenRecordset("Book")
Set rs3 = MyDataBase.OpenRecordset("Loan")


Do While Not rs.EOF
MemberList.AddItem rs!MemberID & " - " & rs!Forename & " " & rs!Surname
rs.MoveNext
Loop
rs.Close

Do While Not rs2.EOF
BookList.AddItem rs2!BookID & " - " & rs2!BookName
rs2.MoveNext
Loop
rs2.Close
End Sub

Private Sub MakeLoanButton_Click()
Reply = MsgBox("Are you sure you wish to make this loan?", 1, "Make Loan?")
If Reply = 1 Then
rs3.AddNew
rs3!MemberID = rs!MemberID
rs3!BookID = rs2!BookID
rs3!DateStarted = DateSerial(Year(Date), Month(Date), Day(Date))
rs3.Update

Exit Sub
End If
End Sub




Thanks in advance :)

Recordsets are closed when you try to copy value from the one to the other. I had the same problem once in the past. In the 2nd procedure try to reopen recordsets. You are having the selected values from your listbox so make specific queries when you are reopening them. I hope i help you...

If both recordsets are closed you can't update them. And you should get the values you want, and store them in variables, then CLOSE the recordset. You don't want three open recordsets for no reason. Then within the recordset you want to update, just insert the variable value, such as:



With rs3
.AddNew
.Fields("Field1") = strString
.Fields("Field2") = strValue
.update
End With

rs3.close
Set rs3 = Nothing

Can I ask, why don't I want open recordsets for no reason?



And that doesn't fully do it I don't think. The problem is that I store into the listbox more than one piece of data, so one says "2 - JAMES BOND" or whatever, and all I need to take from that to store is the 2.

You don't have to open the recordsets again. I see what you are talking about. However, you can't update rs3 from those recordsets, you want to update them from the listbox:
rs3!MemberID = 1st portion of MemberList
rs3!BookID = 1st portion of Booklist
rs3!DateStarted = DateSerial(Year(Date), Month(Date), Day(Date))

Yeah, that sounds right. How would you go about getting that portion?

You're lucky... normally parsing strings is cumbersome at times, but since all you want are numbers and your listbox is always formatted like "# - text", it is easy. This wouldn't work in all cases; but it will in this case.
MemberID=Val(MemberList.Text)
BookID=Val(BookList.Text)

Last Note: you probably would want to ensure at least one item from each list is selected, if you haven't already. If a listbox has not items selected, then the value returned by Val(listbox.text) will always be zero.

And because the name part is in text, it wont include any numbers which are within that?


That's great, thanks!

And because the name part is in text, it wont include any numbers which are within that?


That's great, thanks!
True in this case
Example: Val("125 - 1313 Mockingbird Lane") still gets evaluated to 125

That'll do perfect then :)


One other quick question. Is there any need for "Set rs3 = Nothing"? Why would I need to make this blank?

Force of habit. VB is suppose to properly dispose of all variables/objects when it closes; but most coders know that may not always happen for various reasons. When it doesn't happen, you have a memory leak, a file left open somewhere, or a host of other things. My rule of thumb is that if you use the SET keyword, then somewhere in your code use it again, this time with the = Nothing.

Okay then, thanks :)