Still having problem with multiselect listbox code

[Skip Bisconer]

This code seems to give me an incorrect result with the rs!Position = Me.Employee.Column(3) in the below code. It gives one of the Position names for all the employees selected. All the others give me the correct results except for that one

Private Sub cmdAddRecord_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  On Error GoTo ErrorHandler
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblTrainingLog", dbOpenDynaset, dbAppendOnly)
  'make sure a selection has been made
  If Me.Employee.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If
  'add selected value(s) to table
  Set ctl = Me.Employee
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!EmpID = ctl.ItemData(varItem)
    rs!CWSPolicy = Me.CWSPolicy
    rs!Title = Me.Title
    rs!DateTrained = Me.DateTrained
    rs!Intv = Me.Intv
    rs!RetrainDate = Me.RetrainDate
   [COLOR=red]rs!Position = Me.Employee.Column(3)[/COLOR]
    rs.Update
  Next varItem
  cmbPolicylookup = ""
  Title = ""
  Intv = ""
  DateTrained = ""
  RetrainDate = ""
  DoCmd.Close
  DoCmd.OpenForm "frmUpdateTrainingLog"
 
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

 

[pbaldy]

Try

ctl.Column(3, varItem)

My daughter was married in Red Bluff.

 

[Skip Bisconer]

Thanks Paul the made it work.

I sold my business in 96 and moved up here on some property along the Sacramento river. Best thing I ever did for myself. I thought I really like San Francisco where my business was located but now I can hardly stand to be there for more than a few days at a time. Living in the country is great. Our crime rate out here is when some one steals some strawberries from Driscol's farm.

 

[Villarreal68]

Hello All,

If I wanted to do something similar to what this code does (I want to post invoice fees to the database), but I want to run through the whole list and not only the selected items on the listbox, how could I modify the code to run through the list without having to select the individual items?

thans for your help.

rené

 

[ByteMyzer]

You can change:

[COLOR="Navy"]For Each[/COLOR] varItem [COLOR="navy"]In[/COLOR] ctl.ItemsSelected

...to

[COLOR="navy"]For[/COLOR] varItem = 0 [COLOR="navy"]To[/COLOR] ctl.ListCount - 1

 

[pbaldy]

Try

  For x = 0 To Me.ListBoxName.ListCount - 1
    Debug.Print Me.ListBoxName.ItemData(x)
  Next i

 

[pbaldy]

Whoops; that's what happens when you start replying and get a phone call.

 

[Villarreal68]

Hello ByteMyzer,

Then you for your response. It got me further.

I'm getting the message:
"2465 - Application-defined or object-defined error."
with the following code:

On Error GoTo Err_InvoicePost
Dim varitem As Variant
Dim count As Integer
'Dim rsCM As Object
'Dim rsCM2 As Object
Dim db As DAO.Database
Dim rsCM As DAO.Recordset
Dim rsCM2 As DAO.Recordset
For varitem = 0 To InvoiceList.ListCount - 1
count = count + 1
Next varitem
If count = 0 Then
MsgBox "There are no records to process."
Exit Sub
End If
Set db = CurrentDb()
Set rsCM = db.OpenRecordset("Dues")
Set rsCM2 = db.OpenRecordset("Membership")
For varitem = 0 To InvoiceList.ListCount - 1
rsCM.AddNew
rsCM!MemberID = CInt(Forms!Invoices1st!.nvoiceList.ItemData(varitem))
rsCM!TranDate = Date
rsCM!DuesTranType = 3
rsCM!DuesAmount = CInt(Forms!Invoices1st.InvoiceList.ItemData(varitem))
rsCM!DuesBal = rsCM2!DuesBal
rsCM!NewBal = rsCM!DuesBal + rsCM!DuesAmount
rsCM!DuesDescription = Me.InvoiceDate
rsCM2!DuesBal = rsCM!NewBal
rsCM.Update
rsCM2.Update
Next varitem



Exit_InvoicePost:
rsCM.Close
Set rsCM = Nothing
rsCM2.Close
Set rsCM2 = Nothing
db.Close
Set db = Nothing
Exit Sub

Err_InvoicePost:
MsgBox Err.Number & Err.Description

Resume Exit_InvoicePost
End Sub

 

[Villarreal68]

Oops! That's what happens when you ask and respond to a question when on the phone helping customers! I didn't see pbaldy's response and also found a typo in the code! Plus a typo on the response to ByteMizer ( I meant Thank you not then you)

I'll try it when I'm more calm and not so busy. That might help.

Thanks you guys for the help!
René

 

[Villarreal68]

OK, I decided to take another shot at it. here's the code:

On Error GoTo Err_InvoicePost
Dim varitem As Variant
Dim count As Integer
'Dim rsCM As Object
'Dim rsCM2 As Object
Dim db As DAO.Database
Dim rsCM As DAO.Recordset
Dim rsCM2 As DAO.Recordset
For varitem = 0 To InvoiceList.ListCount - 1
count = count + 1
Next varitem
If count = 0 Then
MsgBox "There are no records to process."
Exit Sub
End If
Set db = CurrentDb()
Set rsCM = db.OpenRecordset("Dues")
Set rsCM2 = db.OpenRecordset("Membership")
For varitem = 0 To InvoiceList.ListCount - 1
Debug.Print Me.InvoiceList.ItemData(varitem)
rsCM.AddNew
rsCM2.Edit
rsCM!MemberID = CInt(Forms!Invoices1st!.InvoiceList.ItemData(varitem))
rsCM!TranDate = Date
rsCM!DuesTranType = 3
rsCM!DuesAmount = CInt(Forms!Invoices1st.InvoiceList.ItemData(varitem))
rsCM!DuesBal = rsCM2!DuesBal
rsCM!NewBal = rsCM!DuesBal + rsCM!DuesAmount
rsCM!DuesDescription = Me.InvoiceDate
rsCM2!MemberID = CInt(Forms!Invoices1st!.InvoiceList.ItemData(varitem))
rsCM2!DuesBal = rsCM!NewBal
rsCM.Update
rsCM2.Update
Next varitem



Exit_InvoicePost:
rsCM.Close
Set rsCM = Nothing
rsCM2.Close
Set rsCM2 = Nothing
db.Close
Set db = Nothing
Exit Sub

Err_InvoicePost:
MsgBox Err.Number & Err.Description

Resume Exit_InvoicePost
End Sub

It gives me the following error: "3164 Field cannot be updated."

Any thoughts??

 

[pbaldy]

What line? I would also note you haven't used the technique originally asked for in this thread, so you're putting the same value into every field, rather than values from different columns.

 

[Villarreal68]

Hello Pbaldy,

Thanks for bringing up the above discussed code. I have corrected the code to reflect this. It got me even further! Now it actually posted (partially) what I needed into a new record in the right table. But it still gives me an error.

The error is : 3421 Data type conversion error.

To answer your question with a question: what line? -- good question! how can I know what line it's refering to? can I enter some code to show me this?

Thanks for all your help!

Rene

 

[pbaldy]

Comment out this line, and then the error should offer you the option to debug, which will take us to the offending line.

On Error GoTo Err_InvoicePost

 

[Villarreal68]

Thanks for that tip about commenting out the error code. OK this is where it stops:

rsCM2!MemberID = InvoiceList.Column(0, varitem)

rsCM2!MemberID is AutoNumber and InvoiceList.Column(0, varitem) is Number. So I think that's where it fails to go on.

How can I resolve that? Is there a way to trick it think they both are the same?
rsCM2 i my main Table and I wanted MemberID to be unique so I made it the Table's record AutoNumber. Would I have to change this settings to do what I want to do?

Thanks for all your help!
Rene

 

[Villarreal68]

Maybe I'm going all wrong about it.

What I'm trying to do with this command (rsCM2!MemberID = InvoiceList.Column(0, varitem)) is to "find" that record and then change the field on this: rsCM2!DuesBal=rsCM!NewBal.

Thats what I pretend to do. Am I doing it wrong?

I thought I would explain a bit more.

Thanks again!
Rene

 

[pbaldy]

No, that's not the way to find a record. For starters, I wouldn't try to keep a balance stored, if that's what you're trying to do. Generally speaking, you shouldn't store a value that can be calculated from activity, like an inventory "on hand" or an account balance. You simply calculate it on the fly (charges - payments).

That said, the method to find the record would either be to open the recordset on an SQL string that only returned the desired person, or use rsCM2.FindFirst (more info in VBA Help).

 

[Villarreal68]

Hello pbaldy,

I like your recomendation, (about balance on th fly). It would be the best way I think. The thing is that the math is done in a different table than the main form that has the Customer information (which is the one that populates the main form), where the employee would read the customers data.

What would be the best method to link the "Current Balance" from the "Dues" table to the "Membership" form ( that is attached to the "Membership" table and has the "Current Balance" field "on the fly"?

Would I create a query that joins the two tables so I could see that Current Total? I could post the db if you would like to take a look at it.

Thanks!
René

 

[pbaldy]

Probably depends on your table structure, but I would probably have a query that calculated all my balances. For your form, you can either join the member table to that query to display the balance, or use a DLookup.