is it possible to update a table using the records on the listbox? say you added new record so in the temp table then append the records to the perm table. how would i do this?
i have used update mysql but i only know how to use this on a single field but have not found success or know the proper way to do it with a listbox.
i know how to work with update but am not sure how select query works. can you give me a sample or short intro?
what i want really is a listbox using a temp_table as a record source, upon exit on the form, once the user exits and press save (the form is unbound).
i actually get this hehe. guess that's the side effect of learning this differently than normal. i do get the idea of temp tables and what not, but what i want to know is if i can get a snippet or sample for a mysql update using the records on the listbox?
To update a table from a ListBox/ComboBox is not a direct task.
A control on a form will update by a combobox/listbox.
If that control is bound to a table then, yes, that table is updated - no more work to do.
if you want some action to take place, ie a number of records to be updated because of a selection in a combobox/listbox then you need to have code in one or more or the properties of a form and or it's controls.
Here is an sql that is the Row Source of a ComboBox
Code:
SELECT TblRefinance.RefinanceID, TblRefinance.NewApplicationID, TBLLOAN.ADPK, TblRefinance.RefinanceAmount, TblRefinance.RefinanceRepayID, TblRefinance.DateInitiated
FROM TBLLOAN INNER JOIN TblRefinance ON TBLLOAN.LDPK = TblRefinance.OldLoanID
WHERE (((TBLLOAN.ADPK)=DLookUp("[ADPK]","TBLLOAN","[ADPK]=" & [Forms]![frmBankStatementsDataOPS]![frmBankStatementsRepaysubfrm]![cboMemberID])) AND ((TblRefinance.RefinanceRepayID) Is Null));
The Row Source Type is Table/Query and it is bound to Coumn 1
There are five events on this control.
The main ones are:
On Enter
Code:
'check data on record to determine if RefinanceID is required
If Me.cboPayMethod = "Refinance" Then
If Me.txtRefinanceID = 0 Then 'Refinance ID is required
'set control properties to allow use
Me.cboRefinanceID.Locked = False
Me.cboRefinanceID.Enabled = True
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim lngOldLoanID As Long 'Loan ID to be Refinanced
Set dbs = CurrentDb
'populate variables
lngOldLoanID = Me.cboLoanID
'Check if record exists in TblRefinance for this LoanID and RepayID combination
Set rst = dbs.OpenRecordset("SELECT TblRefinance.RefinanceID As RefID, TblRefinance.RefinanceAmount " & _
"FROM TblRefinance " & _
"WHERE (((TblRefinance.OldLoanID)=" & lngOldLoanID & ") AND ((TblRefinance.RefinanceRepayID) Is Null));", dbOpenDynaset)
MsgBox rst!RefID
If Not (rst.BOF And rst.EOF) Then 'Record exists. Populate form control
'add code to set control properties and populate
'No data in Form Control. Record exists in TblRefinance but SID not recorded in TblRefinance - allow user to populate the combo box
With Me.cboRefinanceID
.RowSource = "SELECT TblRefinance.RefinanceID As RefID, TblRefinance.RefinanceAmount " & _
"FROM TblRefinance " & _
"WHERE (((TblRefinance.OldLoanID)=" & lngOldLoanID & ") AND ((TblRefinance.RefinanceRepayID) Is Null));"
.ColumnCount = 2
.ColumnWidths = "1cm;1.5cm"
.BoundColumn = 1
End With
Else
MsgBox "I am here"
MsgBox "No record exists of this loan to be Refinanced. Check your Loan Application and try again."
End If
Else
'disable combobox
Me.cboRefinanceID.Locked = True
Me.cboRefinanceID.Enabled = False
End If
Else
'disable combobox
Me.cboRefinanceID.Locked = True
Me.cboRefinanceID.Enabled = False
End If
'Cleanup
rst.Close
Set rst = Nothing
Set dbs = Nothing
And AfterUpdate
Code:
Dim RefinanceRef As Long, RepaymentRef As Long
Dim strSQL As String
RefinanceRef = Me.cboRefinanceID
RepaymentRef = Me.txtRepaymentID
If RefinanceRef & "" <> "" Then
DoCmd.SetWarnings False
strSQL = "UPDATE TblRefinance SET TblRefinance.RefinanceRepayID = " & RepaymentRef & " " & _
"WHERE (((TblRefinance.RefinanceID)= " & RefinanceRef & "));"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'disable combobox
Me.cboRefinanceID.Locked = True
Me.cboRefinanceID.Enabled = False
End If
'set combox to not hold records
With Me.cboRefinanceID
.RowSource = ""
.ColumnCount = 1
.ColumnWidths = "0"
.BoundColumn = 0
End With
Me.cboRefinanceID.Requery
In this example there are two controls on the form.
One is an unbound text box control and the other is an unbound combobox control.
The textbox is populated by a user defined function.
If the result of this is Zero, The combobox next to it is active (conditional Format) and you are able to select a record to be used by the text box control.
If no record is available, you get a message telling you to go away.
If it all works out, when you have updated teh combobox, the a table record is updated with the combobox data.
Requery then allows the form to display the data and not allow any further activity in the combobox.
huh? no i use mysql as my back end. all i really want is a loop code for the query on the listbox so each pk for the query will be matched on the original table and updated if necessary. what i planned to do with the delete was move it in another temp table then of the id is found on the tbl_delete it would delete.
actually i've sorted it out already, sort of. the reason i am doing this is because my forms, ALL OF THEM are unbound. some may say this is unnecessary but i prefer doing it this way to control the users data entry and the way the tables are designed are extremely reliant on referential integrity (e.g 1 tbl for address and multiple tables for binding two records to one with PK FK FK etc.) my approach worked find until i had to deal with listboxes, my real deal is that when i enter a new record an start adding new records to the listbox it would go to a temp table for a bit, when the record is save the records on the temp tbl would be appended to the tbl in mysql then clearing/deleting the temp tbl. but if the record is not new and is subject to edit, whenver a record is removed from the temp table, it would go to a temp_delete where at the end of the user's data entry if he/she decides to keep the changes on the listbox i would do a loop on that table and run a loop for each PK listed = delete on perm_tbl.
the thing is, am not really adept at loops yet. i have only began to learn it and have met only failed attempts so far. and perhaps there's another way to deal with this than loops.
Here is an example of a loop.
Good practice with your original post is to state your software including your mysql backend.
This helps an experienced member to give advice.
Trust the code is not too long.
Code:
'---------------------------------------------------------------------------------------
' Procedure : fncLateFeesToBeCharged
' Author : Bill McKinstry
' Date : 9/09/2011
' Purpose : calculate Late Fee due - maybe debit or credit
'---------------------------------------------------------------------------------------
'
Public Function fncLateFeesToBeCharged(LoanID As String) As Currency 'Calculate Late Fees for selected LoanID
On Error GoTo fncLateFeesToBeCharged_Error
Dim rs As DAO.Recordset
Dim SqlString As String ' Variable to hold SQL String
Dim RepayFreq As String ' Repayment Fortnightly, Monthly or Weekly
Dim CurrentBalance As Currency ' Balance As At each 14 days from the Start Date of the Loan plus 14 days
Dim LateFeeRate As Currency ' Kina Amount of Late Fee to be charged each fortnight
Dim RepayTotal As Currency ' Kina amount of accumulated Repayments up to Given Date
Dim RepayPrev As Currency ' Kina Amount Repaid Up To Last Fortnight
Dim RepayAmount As Currency ' Kina Amount Due to be Repaid Each Fortnight
Dim CommenceDate As Date ' Loan Commencement Date - Start Date plus 14 days
Dim RepayCount As Integer ' Count of Fortnights as Function Loops - Increases by 1 each loop
Dim RepayNum As Integer ' Number of Repayments agreed to for Loan
Dim LateFeeDue As Currency ' Accummulated Late Fee now being calculated
Dim LateFeeNow As Currency ' Kina Charge of Late Fee to be charged as at this date
Dim LateFeesCharged As Currency ' Kina Amount of Late fees charged as at given date
Dim LoanTotalOwing As Currency ' Kina Amount of Loan to Pay
Dim SkipThisDate As Date ' Date No Late Fee is to be Charged
RepayCount = 1 'Set Variable to start at 1
LateFeeDue = 0 'Set variable to Zero
LateFeeNow = 0 'Set Variable to Zero
RepayTotal = 0 'Set Variable to Zero
RepayPrev = 0 'Set Variable to Zero
'Find Loan Commence Date
SqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDSt AS StartDate " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(SqlString)
CommenceDate = rs!StartDate + 14 'Set Commence Date to First Repayment Due Date
'Find Each Repayment Amount
SqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayK AS LDRepayK " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(SqlString)
RepayAmount = rs!LDRepayK 'set Repayment Amount
'Find Repayment Number
SqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayNo AS RepayNo " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(SqlString)
RepayNum = rs!RepayNo 'set repayment Number
'Find Repayment Frequency
SqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LDPayFre AS LDRepayFreq " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(SqlString)
RepayFreq = rs!LDRepayFreq 'set RepayFreq
'Find Late Fee Amount for this loan
SqlString = "SELECT TBLLOAN.LDPK, TBLLOAN.LoanLateFee AS LateFeeKina " & _
"FROM TBLLOAN " & _
"WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
Set rs = DBEngine(0)(0).OpenRecordset(SqlString)
LateFeeRate = rs!LateFeeKina 'set LateFeeRate
If RepayFreq = "Fortnightly" Then 'Only do for Fortnightly Repayments
'Delete old Records in TblLateFeeCalculated as no longer required
fncDeleteLateFeeRecord "TblLateFeeCalculated", "LDPK", LoanID
Do Until CommenceDate > (Date - 1) 'keep looping until CommenceDate reaches or passes today's date less 1 day
SkipThisDate = fncIgnoreLateFeeDate(LoanID, CommenceDate) 'Function to check for matching record in TblLatefeeIgnore
Do While DateValue(CommenceDate) = DateValue(SkipThisDate) 'Check for Matching Record for this Date
RepayTotal = fncLoanRepaymentToDate(LoanID, CommenceDate) 'Function to Get Repayments To Date as at given date
RepayPrev = RepayTotal 'Reset RepayPrev to New Balance
CommenceDate = CommenceDate + 14 'Add 14 Days to get Next Commence date
RepayTotal = fncLoanRepaymentToDate(LoanID, CommenceDate) 'Function to Get Repayments To Date as at given date
If CommenceDate >= Date Then 'Check if CommenceDate has reached current Date
Exit Do 'If so, Exit Do
End If
RepayCount = RepayCount + 1 'Add 1 to RepayCount variable to get Next Repay Count
SkipThisDate = fncIgnoreLateFeeDate(LoanID, CommenceDate) 'Reset SkipThisDate Value with new CommenceDate
Loop
CurrentBalance = fncLoanBalanceToDate(LoanID, CommenceDate) 'Function to Get Loan Balance as at given date
LateFeesCharged = fncLateFeesToDate(LoanID, CommenceDate) 'Function to Get Late Fees Charged as at given date
RepayTotal = fncLoanRepaymentToDate(LoanID, CommenceDate) 'Function to Get Repayments To Date as at given date
LoanTotalOwing = fncLoanTotalToPay(LoanID) 'Function to Get Total Amount Yet to Pay on Loan
If RepayCount = 1 Then 'If First Repayment Due
If (CurrentBalance - LateFeesCharged) > (RepayAmount / 2) Then 'If Repament Made for less then Half of Repayment Amount
LateFeeNow = (LateFeeRate * 2) 'Charge double Late Fees
Else
LateFeeNow = 0 'No Late Fees to Charge
End If
RepayPrev = RepayTotal 'Set Repay Value For up to First Fortnight
ElseIf RepayCount > 1 Then 'If not First Repayment
If (CurrentBalance - LateFeesCharged) > 10 Then 'Check If Overpaid to date or paid in full
If RepayCount <= RepayNum Then 'And is Last Repay Date or earlier
If (RepayTotal - RepayPrev) < (RepayAmount / 2) Then 'Compare Any Recent Repayment to at least half the Agreed Repayment
LateFeeNow = LateFeeRate 'Charge Late Fee
Else
LateFeeNow = 0 'No Late fees Due
End If
RepayPrev = RepayTotal 'Reset RepayPrev to New Balance
Else
RepayPrev = RepayTotal 'Reset RepayPrev to New Balance
LateFeeNow = LateFeeRate 'Charge Late Fee
End If
Else
If (CurrentBalance - LateFeesCharged) < 11 Then 'Check If Original Loan fully repaid
LateFeeNow = 0 'No Late Fees Due
End If
LateFeeNow = 0 'No Late fees Due
End If
End If
'Add this Late Fee record to TblLateFeeCalculated
If LateFeeNow > 0 Then
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("TblLateFeeCalculated", dbOpenDynaset)
rst.AddNew
rst!LDPK = LoanID
rst!LateFeeAmount = LateFeeNow
rst!LateFeeDate = CommenceDate
rst!DateCalculated = Date
rst.Update
rst.Close
Set rst = Nothing
End If
CommenceDate = CommenceDate + 14 'Add 14 Days to get Next loop
RepayCount = RepayCount + 1 'Add 1 to RepayCount variable for next loop
LateFeeDue = LateFeeDue + LateFeeNow 'Add this fortnights Late Fee Charge to Accummulated Late Fees Variable
RepayPrev = RepayTotal 'Reset RepayPrev to New Balance
If CommenceDate > Date - 7 Then
Exit Do
End If
Loop
CommenceDate = Date 'Set CommenceDate to be Today to check for all Late Fees charged
LateFeesCharged = fncLateFeesToDate(LoanID, CommenceDate) 'Refresh Late Fees Charged as at Today's date
LateFeeDue = LateFeeDue - LateFeesCharged 'Deduct any Late Fees already Charged
fncLateFeesToBeCharged = LateFeeDue 'Return Result to Function
Else
MsgBox "Loan Repayments Not Fortnightly - Calculate Late Fees Some Other Way" 'Calculator won't work for Weekly or Monthly repayments
End If
rs.Close
Set rs = Nothing
On Error GoTo 0
Exit Function
fncLateFeesToBeCharged_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fncLateFeesToBeCharged of Module ModLateFees"
End Function
hehe. guess that's the side effect of learning this differently than normal. i do get the idea of temp tables and what not, but what i want to know is if i can get a snippet or sample for a mysql update using the records on the listbox?
