Unbound Form Recordset

vaughan198

Registered User.
Local time
Today, 11:44
Joined
Nov 16, 2011
Messages
40
Hi there.

Any help with this would be hugely appreciated.

I have an unbound form (for many reasons). I have a button which imports all the fileds on the form into a new record in a table (NewAccounts). This all works fine until I try to include a multi-select list box and then the whole thing falls apart.

I dont need to keep the data from the listbox in any particular format. I just need to display it as either a string or list in a report which will then get emailed out.

Currentl I am using the following code:

Code:
 Sub SaveCommand_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
'Dim varTextData As String
Set db = CurrentDb
Set rst = db.OpenRecordset("NewAccounts")
With rst
    .AddNew
    !DateSent = Me.Text51
    !SAUser = Me.sausername
    !USSalesPerson = Me.Sales
 
(some other fields) 
Update
End With
rst.Close
 
some email code
 
End Sub

The listbox is called DesiredProd and the field in the table is called Desire.

Does anyone have any advice. Thanks so much.
 
This code gets the values from a Form and appends a record to a table
Code:
Private Sub CmdSaveNewApplication_Click()
On Error GoTo Err_CmdSaveNewApplication_Click
    
    If cboMemberID > 0 Then
    
       Dim rst As DAO.Recordset
   
       Set rst = CurrentDb.OpenRecordset("TBLAPPLOAN", dbOpenDynaset)
       rst.AddNew
       rst!APLACCDET = Me!cboMemberID
       rst!APLDTE = Me!txtAPLDTE
       rst!APLAMT = Me!FrmNewLoanApplicationSubFormLoanOption.Form!txtPrinc
       rst!APLTER = Me!FrmNewLoanApplicationSubFormLoanOption.Form!txtTerm
       rst!APLSTAT = "1"
       rst!APLCOM = Me!txtAPLCOM
       rst!PayslipAttached = Me!chkPayslipAttached
       rst!RepayMethod = Me!cboRepayMethod
       rst!RepayAmt = Me!FrmNewLoanApplicationSubFormLoanOption.Form!txtRepay
       rst!RepayFreq = Me!cboRepayFreq
       rst!NextPayDate = Me!txtNextPayDate
       rst!NextLeave = Me!txtNextLeave
       rst!DateRequired = Me!txtDateRequired
       rst!ApplNet = Me!txtAplNetPay
       rst!AppLoanOption = Me!cboAppLoanOption
       rst!EmpName = Me!FrmNewLoanApplicationSubFormMember.Form!txtEDName
       rst.Update
   
       rst.Close
       Set rst = Nothing
   
    End If
    
    cboMemberID = ""
    txtAPLDTE = ""
    chkPayslipAttached = -1
    txtAPLCOM = ""
    cboRepayMethod = "Payroll"
    txtNextPayDate = ""
    txtNextLeave = ""
    txtDateRequired = ""
    txtAplNetPay = 0
    cboAppLoanOption = 0
    cboRepayFreq = "Fortnightly"
   
    Dim stDocName As String
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  
    stDocName = "frmLoanApplicationsMain"
    DoCmd.Close
    DoCmd.OpenForm stDocName
    Form_FrmLoanApplicationsMain.ApplicationNewLoan
    
Exit_CmdSaveNewApplication_Click:
    Exit Sub
Err_CmdSaveNewApplication_Click:
    MsgBox Err.Description
    Resume Exit_CmdSaveNewApplication_Click
    
End Sub
It then resets the form control values for the next entry.
Some controls are combo box cbo...
 
Hope this helps. Let me know...

Code:
Function SelectMultiple(ListBoxName As Control, Txt As Boolean, Optional NumberData As Boolean) As String
 Dim ListBoxItm As Variant, Separator As String
Dim ListBoxCriteria As String ' this is the criteria string to be used in queries
Dim txtList As String ' this is the text label to go on the form
Dim varReturn As Variant
'If list box contains number data, set numberdata to true to skip using quotes around items
 'Setting txt to true will furnish output in text format with a carriage return after each field, like:
 'A
 'B
 'C
 'Otherwise, output will be:
 'A,B,C
 
  If NumberData = True Then Separator = "" Else Separator = Chr(39) 'Chr(34)
  
        For Each ListBoxItm In ListBoxName.ItemsSelected
            If Len(ListBoxCriteria) = 0 Then
                 ListBoxCriteria = Separator & ListBoxName.ItemData(ListBoxItm) & Separator
                txtList = ListBoxName.ItemData(ListBoxItm) & vbCrLf
                
            Else
            ListBoxCriteria = ListBoxCriteria & "," & Separator & ListBoxName.ItemData(ListBoxItm) & Separator
            txtList = txtList & ListBoxName.ItemData(ListBoxItm) & vbCrLf
                End If
            Next ListBoxItm
           
            If Len(ListBoxCriteria) = 0 Then
                 MsgBox "You must select one or more items from the " & ListBoxName.Name & " list box!", 0, "No selection made"
            varReturn = SysCmd(acSysCmdClearStatus)
            SelectMultiple = "No Selection" ' added 1/21/03
            Exit Function   'added 1/21/03
            End If
      If Txt = True Then SelectMultiple = txtList Else SelectMultiple = ListBoxCriteria
            Exit Function
            
            
End Function
 
DataMinerHome. That would do the trick I think, Having trouble actually putting it in with the rest of my code. I understand this is a function. Are you (or anyone) able to help?
 
Maybe.. what's your exact question? All you should need to do is add something like
rst!YourListBoxData=selectmultiple(YourlListBox, Youroptions) to your code.
 

Users who are viewing this thread

Back
Top Bottom