Ref to QUery Field

[zapzip]

Hello-
I can't get the reference right in VB code a field in a query. Anyone can help?


I have a Form F_Deposit Slip with a subform FSc_AvialableForDeposit whose Source Object is Query.Q_DepositAvialable. The query has a number of fields including M_FundPaid.FundPaidSelForDeposit (a Yes/No data type).

What I am trying to accomplish is to use an update query to set 2 fields in the query only if FundPaidSelForDeposit = True. The update can be de-selected by unchecking the field.

All works fine accept I can’t get the reference to the Yes/No field in VBA correct. Your help would be greatly appreciated.


Code follows

Private Sub cmdAcceptCkMark_Click()
DoCmd.SetWarnings False
'update Deposit sel to M_Funds adding rent roll month and deposit number
Dim MySQL1 As String
Dim MySQL2 As String
Dim Ctrl As Control
Dim Frm As Form

Dim M_FundPaid As TableDef
Dim FundPaidSelForDeposit As Field
Dim Q_DepositAvialable As QueryDef

Set Frm = Forms![F_DepositSlip]
'check for blank entries Property RentRoll & DepositNumber if non are selected get erradic operation
'null doesn't seem to work
If Frm.cboProperty = 0 Then
    MsgBox "Must select a PROPERTY"
    GoTo MyErrorHandler
End If
If Frm.cboRentRollMonth = 0 Then
    MsgBox "Must select a RENT ROLL"
    GoTo MyErrorHandler
End If
If Frm.cboDepositNumber > 0 Then
    Else
    MsgBox "Must select DEPOSIT NUMBER"
    GoTo MyErrorHandler
End If
[COLOR=red]
'Update when sel box checked (M_FundPaid.FundPaidSelForDeposit = True)
'If M_FundPaid.FundPaidSelForDeposit = True Then
'If FundPaidSelForDeposit = True Then
‘If [Q_DepositAvialable]![FundPaidSelForDeposit] = True Then
‘ I have tried all the above which give various errors
[/color]
    MySQL1 = "UPDATE L_TypeFund "
    MySQL1 = MySQL1 + "INNER JOIN M_FundPaid "
    MySQL1 = MySQL1 + "ON L_TypeFund.TypeFund_ID = M_FundPaid.FundPaidTypeFunds_IDs "
    MySQL1 = MySQL1 + "SET M_FundPaid.FundPaidRRs_IDs = Forms!F_DepositSlip!cboRentRollMonth, "
    MySQL1 = MySQL1 + "M_FundPaid.FundPaidRRSubs_IDs = Forms!F_DepositSlip!cboDepositNumber "
    MySQL1 = MySQL1 + "WHERE (((M_FundPaid.FundPaidSelForDeposit) = True) "
    MySQL1 = MySQL1 + "AND ((M_FundPaid.FundPaidRRs_IDs) Is Null) AND ((M_FundPaid.FundPaidRRSubs_IDs) Is Null)) "
    MySQL1 = MySQL1 + "WITH OWNERACCESS OPTION"
    DoCmd.RunSQL MySQL1
    
'update when sel check box de-selected (M_FundPaid.FundPaidSelForDeposit = False)
Else
    MySQL2 = "UPDATE L_TypeFund "
    MySQL2 = MySQL2 + "INNER JOIN M_FundPaid "
    MySQL2 = MySQL2 + "ON L_TypeFund.TypeFund_ID = M_FundPaid.FundPaidTypeFunds_IDs "
    MySQL2 = MySQL2 + "SET M_FundPaid.FundPaidRRs_IDs = null, "
    MySQL2 = MySQL2 + "M_FundPaid.FundPaidRRSubs_IDs = null "
    MySQL2 = MySQL2 + "WHERE (((M_FundPaid.FundPaidSelForDeposit) = False)) "
    MySQL2 = MySQL2 + "WITH OWNERACCESS OPTION"
    DoCmd.RunSQL MySQL2
End If
    
    
      
Refresh
'restrict number of CVheck &/or Money Order to 21.  Can have unlimited Coin, currency
If Frm.txtCount >= 21 Then
    MsgBox "Maximum 21 Checks &/or Money Orders on Deposit Slip." & "   If need to add another, you must 1st remove an existing item"
    cmdPrint.Visible = False
    GoTo MyErrorHandler
End If
    DoCmd.SetWarnings True

MyErrorHandler:
    DoCmd.SetWarnings True
End Sub

 

[PaulBricker]

TableDef and QueryDef objects are not used to return values in a field. You would use a Recordset object to do that.


Paul