none
Select all records to move to another table RRS feed

  • Question

  • Below is a button code to move records from cbDisList (Column List) into table tblInvoice_ItMdt when they are highlighted 

    Is possible to add to this code move all records from cbDisList that have "Yes" in Column(4) of which is a text field.......Thanks for any help.

    Bob

    Private Sub cmdCreateHoldingInvoices_Click()
    On Error GoTo Err_Horse_ID_Click

     Dim recInvoice_ItMdt As DAO.Recordset
    Dim recHorseInfo As DAO.Recordset
    Dim recTmpInvoice_ItMdt As DAO.Recordset
    Dim lngIntermediateID As Long
    Dim nloop As Long



    Set recInvoice_ItMdt = cnnStableAccount.OpenRecordset("SELECT * FROM tblInvoice_ItMdt")
    For nloop = 0 To cbDisList.ListCount - 1
        If cbDisList.Selected(nloop) = True Then

            Debug.Print cbDisList.Column(0, nloop) 'Prints the Horse Id
            Set recHorseInfo = cnnStableAccount.OpenRecordset("Select * from tblHorseInfo where HorseID=" _
            & cbDisList.Column(0, nloop) & ";")
                If recHorseInfo.BOF = False And recHorseInfo.EOF = False Then
                    With recInvoice_ItMdt
                        If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then
                            .MoveLast
                         lngIntermediateID = Nz(DMax("IntermediateID", "tblInvoice_ItMdt"), 0) + 1
                        Else
                            lngIntermediateID = 1
                        End If
                        .AddNew
                        .Fields("IntermediateID") = lngIntermediateID
                        .Fields("dtDate") = Format(Now, "dd/mm/yyyy")
                        .Fields("HorseID") = cbDisList.Column(0, nloop)
                         .Fields("dtDateInv") = DateSerial(Year(Date), Month(Date) + 0, 0)
                        .Fields("GSTOptionsText") = "Plus Tax"
                        .Fields("GSTOptionsValue") = 0
                        .Fields("SubTotal") = 0
                        .Fields("TotalAmount") = 0
                        Application.SysCmd acSysCmdSetStatus, "Horse Name=" & .Fields("HorseName")
                        .Update
                        .Requery

                    End With
                End If

            recHorseInfo.Close

        End If

    Next
    Me.cbDisList.Requery
    Application.SysCmd acSysCmdClearStatus
    Forms!frmMain!cbDisList.Requery
    Forms!frmMain!lstModify.Requery
      [Forms]![frmMain]![sufrmDisList].Form!lstModify.Requery

    Exit_Horse_ID_Click:
        Exit Sub

    Err_Horse_ID_Click:
       'MsgBox "***This Horse is already in Holding Invoices***", vbApplicationModal + vbInformation + vbOKOnly

        Resume Exit_Horse_ID_Click
    End Sub

                                                   

    xxx

    Friday, August 3, 2018 11:46 PM

Answers

  • Hello TurnipOrange,

    Change

    If cbDisList.Selected(nloop) = True Then

    To

    If cbDisList.Column(3, nloop) = "YES" Then

    If you found cbDisList.Column(3, nloop)  is always null, make sure the column count of the cbDisList is 3 above.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TurnipOrange Tuesday, August 7, 2018 11:33 PM
    Tuesday, August 7, 2018 1:51 AM

All replies

  • Hello TrunipOrange,

    What's the current issue of your code?

    Does it cause any error? If so, what's the error message and which line caused error?

    Or the code fails to return the result you want?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 6, 2018 3:13 AM
  • Thanks Terry, This code works perfect when sending one record at a time. What i was wanting to send all records with the word "Yes" in the 4th column and other word is "No"

    Thanks for your help.............Bob


    xxx

    Monday, August 6, 2018 9:58 PM
  • Hello TurnipOrange,

    Change

    If cbDisList.Selected(nloop) = True Then

    To

    If cbDisList.Column(3, nloop) = "YES" Then

    If you found cbDisList.Column(3, nloop)  is always null, make sure the column count of the cbDisList is 3 above.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TurnipOrange Tuesday, August 7, 2018 11:33 PM
    Tuesday, August 7, 2018 1:51 AM
  • Thanks Terry worked a treat :)

    Regards Bob


    xxx

    Tuesday, August 7, 2018 11:34 PM