Unbound fields value for comparison (1 Viewer)

[Wysy]

Hi, I am trying to solve the following and asking for help
I have a form frm1 with 2 unbound field txt1 and txt2. I want to compare the values of these fields in an if/end if condition. The first field txt1 gets its value from an other form via TempVars! on onLoad event. However it does not work. If i enter values for both field than it works, if txt1 gets its value on onLoad event then it fails. First i am trying to understand this behaviour, second i am looking for a solution.
Thank you

 

[The_Doc_Man]

OK, to start with, you have unbound fields. NOTHING is automatic for either field and you only mention that txt1 gets a value from an OnLoad event from another form. If you have nothing in txt2 then all I can say is, "I'm not surprised." However, I'm also confused by the way you claim to be loading txt1, because it is coming from another form? It comes from an OnLoad event, but fired from which form?

I would suggest setting a breakpoint in the code so you can single-step your way through and see what each line of code actually does. You can hover the mouse's cursor over a variable or field to see its content while you are in debug mode.

My question to you is, what order of events takes place leading up to the comparison you wish to make. That is rhetorical, though, because I don't care. You have to answer the question for yourself so that you can determine when each of txt1 and txt2 will be loaded, and how.

 

[Pat Hartman]

However it does not work.

Have you stopped the code in the Load event and examined the value in the TempVar?

Posting the code in the Load event would be a lot more helpful than "does not work".

 

[MajP]

If i enter values for both field than it works, if txt1 gets its value on onLoad event then it fails.

I am guessing here because not a lot to go on. However, most control events do not occur if the action was done via code. So if the textbox is updated by code then the textbox's after update does not fire.

 

[Wysy]

It seems that the following happens: if the 2 txtbox value is not changed after onLoad, the comparison function works well. If i change only 1 txtbox then the comparison fails. If i change both txtbox then comparison works agian.

 

[jdraw]

Wysy,

As others have said "it does not work" doesn't provide any info to readers.
Please review the comments and tell us
-what you want to happen
-what exactly does happen (msgs etc)
-why unbound controls

 

[Wysy]

Sorry.
I have a form that lists services to customers with prices. When i do accounting i am "sending the customer name and the total price of services to another form. In this form there are two unbound form: one with the total price, the other unbound field is the money received. The default case is that both unbound fields get their values onLoad from the first form. In the case the total price has been received so the unbound fields are equal. The i proceed with accounting. However when no money has been received or only a part of the total price has been payed, then received is smaller than the total price, so if total price is bigger then money received, accounting proceed differently. So the point is to compare the two values for accounting. The total price equals or more that the received amount

 

[MajP]

You will either have to show all the code or post the DB. If not we are just guessing. My blind guess is that it is an order of events issue. I am guessing you are trying to run the code when the form is opened and that is before the controls are loaded. Therefore no values in one of the textboxes. Since it works when you manually enter values this is likely and event or order of events issue.

 

[Wysy]

Private Sub cmbCreateInvoice_Click()
'to turn of warning for running sql statement
DoCmd.SetWarnings False


'to post the book: update tbDiagServMat and insert tbLedger, tbInvoice_Status
'update tbDiagServMat
    'InvoiceDate = TempVars!pass_InvoiceDate
    InvoiceDate = Me.txtInvoiceDate
    PartnerID = TempVars!pass_PartnerID
    HorseID = TempVars!pass_HorseID
    InvoiceLast = DMax("InvoiceID", "tbDiagServMat")
    InvoiceSerial = InvoiceLast + 1
    invoicetotal = TempVars!pass_InvoiceTotal
    Partner_Name = TempVars!Partner
    If Me.OpenArgs = "Horse_Selection" Then
    
              For i = 0 To hlRC
                    HorseID = HLforInvoice(0, i)
                    
    
            strUpdate = "UPDATE tbDiagServMat SET Status='accounted', InvoiceID=" & InvoiceSerial & ",  " & _
                      "InvoiceDate=#" & InvoiceDate & "& WHERE OwnerID=" & PartnerID & " AND Status Is Null " & _
                      " AND HorseID=" & HorseID & ";"
                
                
                DoCmd.RunSQL strUpdate
                
                
              Next i
              
    Else
    strUpdate = "UPDATE tbDiagServMat SET Status='accounted', InvoiceID=" & InvoiceSerial & ", " & _
              "InvoiceDate=#" & InvoiceDate & "# WHERE OwnerID=" & PartnerID & " AND Status Is Null;"
    
    
    End If
        
        
    MsgBox strUpdate
    DoCmd.RunSQL strUpdate
    
'to make entry into tbLedger depending on option buttons
    If Me.obTransfer = True Then
        If Me.cmbTotalCurrency = "HUF" Then
            AccID = 3
        Else
            AccID = 31
        End If
        
        Invoice_Status = "open"
        InvMethod = "Bank"
        
    ElseIf Me.obCash = True Then 'cash payment
            
                                    
           'check if payment received is complete or partial
            If Me.txtInvoiceTotal.Value = Me.txtReceived.Value Then 'full payment received
                    'check currency of payment
                    If Me.cmbTotalCurrency = "HUF" Then
                        AccID = 1 'HUF
                    Else
                        AccID = 11 'EUR
                    End If
                        For i = 1 To 2 'double entry with Cash/Revenue account
                            strLedger = "INSERT INTO tbLedger (Acc_Date,InvoiceDate,PartnerID,InvoiceID,Sum," & _
                                        "Payment_Currency,AccId,Acc_Text) " & _
                                        "VALUES (#" & InvoiceDate & "#,#" & InvoiceDate & "#, " & PartnerID & ", " & _
                                        "" & InvoiceSerial & "," & invoicetotal & ",'" & Me.cmbTotalCurrency & "', " & _
                                        "" & AccID & ",'" & Partner_Name & "');"
                            MsgBox strLedger
                            DoCmd.RunSQL strLedger
                            If Me.cmbTotalCurrency = "HUF" Then
                                 AccID = 3 'HUF
                            Else
                                 AccID = 31 'EUR
                            End If
                            'invoiceTotal = Me.txtReceived
                            invoicetotal = -invoicetotal
                        Next i

                    
            ElseIf Me.txtInvoiceTotal > Me.txtReceived Then 'partially payment received
                    'check currency of payment
                    If Me.cmbTotalCurrency = "HUF" Then
                        AccID = 3 'HUF
                    Else
                        AccID = 31 'EUR
                    End If
                        For i = 1 To 2 'double entry with AccRec/Revenue account
                            strLedger = "INSERT INTO tbLedger (Acc_Date,InvoiceDate,PartnerID,InvoiceID,Sum," & _
                                        "Payment_Currency,AccId,Acc_Text) " & _
                                        "VALUES (#" & InvoiceDate & "#,#" & InvoiceDate & "#, " & PartnerID & ", " & _
                                        "" & InvoiceSerial & "," & invoicetotal & ",'" & Me.cmbTotalCurrency & "', " & _
                                        "" & AccID & ",'" & Partner_Name & "');"
                            MsgBox strLedger
                            DoCmd.RunSQL strLedger
                            If Me.cmbTotalCurrency = "HUF" Then
                                 AccID = 2 'HUF
                            Else
                                 AccID = 21 'EUR
                            End If
                            'invoiceTotal = Me.txtReceived
                            invoicetotal = -invoicetotal
                        Next i
                    'check currency of payment
                    If Me.cmbTotalCurrency = "HUF" Then
                        AccID = 1 'HUF
                    Else
                        AccID = 11 'EUR
                    End If
                            invoicetotal = Me.txtReceived
                            For i = 1 To 2 'double entry with Cash/AccR account
                                strLedger = "INSERT INTO tbLedger (Acc_Date,InvoiceDate,PartnerID,InvoiceID,Sum," & _
                                            "Payment_Currency,AccId,Acc_Text) " & _
                                            "VALUES (#" & InvoiceDate & "#,#" & InvoiceDate & "#, " & PartnerID & ", " & _
                                            "" & InvoiceSerial & "," & invoicetotal & ",'" & Me.cmbTotalCurrency & "', " & _
                                            "" & AccID & ",'" & Partner_Name & "');"
                            MsgBox strLedger
                            DoCmd.RunSQL strLedger
                                If Me.cmbTotalCurrency = "HUF" Then
                                    AccID = 3 'HUF
                                Else
                                    AccID = 31 'EUR
                                End If
                            invoicetotal = -invoicetotal
                            Next i
                    End If
            
                'invoicetotal = Me.txtInvoiceTotal - Me.txtReceive
                Invoice_Status = "closed"
                InvMethod = "Cash"
    End If
    
    
    
    
            
'to make entry into tbInvoice_Status depending on option button
    strInvoiceStatus = "INSERT INTO tbInvoice_Status (InvoiceID,Invoice_Status) " & _
                     "VALUES (" & InvoiceSerial & ",'" & Invoice_Status & "');"
    DoCmd.RunSQL strInvoiceStatus
            
'to make entry into tbInvoices (global invoice table)
    InvoiceDate = TempVars!pass_InvoiceDate
    InvoiceDate = Me.txtInvoiceDate
    PartnerID = TempVars!pass_PartnerID
    HorseID = TempVars!pass_HorseID
    InvoiceLast = DMax("InvoiceID", "tbDiagServMat")
    InvoiceSerial = InvoiceLast
    invoicetotal = TempVars!pass_InvoiceTotal
    Partner_Name = TempVars!Partner
 Dim strInvSQL As String
    strInvSQL = "INSERT INTO tbInvoices (InvDate,InvID,PartnerID,InvTotal,InvCurrency, InvMethod) " & _
              "VALUES (#" & InvoiceDate & "#, " & InvoiceSerial & "," & PartnerID & "," & invoicetotal & "," & _
              " '" & Me.cmbTotalCurrency & "','" & InvMethod & "');"
    MsgBox strInvSQL
    DoCmd.RunSQL strInvSQL
'turn on warning after sql statement executed
DoCmd.SetWarnings True
Me.cmbCreateInvoice.Enabled = False
Me.fsubInvoice_Creator.Form.Requery
End Sub

Private Sub Form_Load()
'to create invoice# based on the last invoice
InvoiceLast = DMax("InvoiceID", "tbDiagServMat")
InvoiceSerial = InvoiceLast + 1

'upon loading to use variables from frmMainTM
   Me.txtPartner = TempVars!pass_Partner
   Me.txtPartnerID = TempVars!pass_PartnerID
   Me.txtInvoiceDate = TempVars!pass_InvoiceDate
   Me.txtInvoiceTotal = TempVars!pass_InvoiceTotal.Value
   Me.cmbTotalCurrency = TempVars!pass_Curr.Value
   Me.txtReceived = Me.txtInvoiceTotal
   Me.cmbReceivedCurrency = TempVars!pass_Curr.Value
   Me.txtHorseID = TempVars!pass_HorseID
   Me.txtInvoiceID = InvoiceSerial
  
  
'to have Cash optionbutton be default on
Me.obCash = True


End Sub

 

[Wysy]

During the onLoad event the unbound fields are getting their values and are populated by the correct value. The problem emerges when i change only one of them and not both of them at the same time. That is when code does not run correctly and the comparison fails.

 

[Minty]

Don't turn the warnings off - it will hide ALL useful messages. Instead use

Currentdb.execute yourSQLString

Then you won't get the "Do you want to add/delete these records" notifications.

And we still can't see the code that doesn't run as far as I can see.
Where is there an after update event for either text box?

 

[Wysy]

ElseIf Me.txtInvoiceTotal > Me.txtReceived Then 'partially payment received

This is the part that does not work. Simply this comparison is not performed if only one the fields is changed. Concerning warning i have arleady checked on every step using msgbox s. I have done a small DB to present my problem. I am going to upload it.

 

[Minty]

Add a debug.print to make sure you are actually getting the correct values:

                'check if payment received is complete or partial
        Debug.Print "inv Total: " & Me.txtInvoiceTotal, "Rcvd: " & Me.txtReceived
        If Me.txtInvoiceTotal.Value = Me.txtReceived.Value Then 'full payment received

 

[Minty]

ElseIf Me.txtInvoiceTotal > Me.txtReceived Then 'partially payment received

This is the part that does not work. Simply this comparison is not performed if only one the fields is changed. Concerning warning i have arleady checked on every step using msgbox s. I have done a small DB to present my problem. I am going to upload it.

The problem with turning warnings off is that it suppresses EVERY warning message, so for instance, if you had an illegal use of null error you wouldn't see it. Hence the advice.

 

[Wysy]

So maybe i need to apologies...As I designed the form, the unbound fields format have not been set to general number... May that be the reason for not comparing the values? It seems now that it works i.e. makes the comparison and runs the code correctly. I will keep on testing. I will report back. Thanks for the input!

 

[Minty]

Interesting observation.

On other thing I noticed - this section of code:

  If Me.OpenArgs = "Horse_Selection" Then
    
              For i = 0 To hlRC
                    HorseID = HLforInvoice(0, i)
                    
    
            strUpdate = "UPDATE tbDiagServMat SET Status='accounted', InvoiceID=" & InvoiceSerial & ",  " & _
                      "InvoiceDate=#" & InvoiceDate & "& WHERE OwnerID=" & PartnerID & " AND Status Is Null " & _
                      " AND HorseID=" & HorseID & ";"
   
             DoCmd.RunSQL strUpdate
                 Next i
              
    Else
                strUpdate = "UPDATE tbDiagServMat SET Status='accounted', InvoiceID=" & InvoiceSerial & ", " & _
              "InvoiceDate=#" & InvoiceDate & "# WHERE OwnerID=" & PartnerID & " AND Status Is Null;"
    
   End If
    
    MsgBox strUpdate
    DoCmd.RunSQL strUpdate

Is going to run the Update twice for the last iteration of the Next i loop, once in the loop and once again in the last line above.

 

[ebs17]

strUpdate = "UPDATE tbDiagServMat SET Status='accounted', InvoiceID=" & InvoiceSerial & ", " & _
"InvoiceDate=#" & InvoiceDate & "& WHERE OwnerID=" & PartnerID & " AND Status Is Null " & _
" AND HorseID=" & HorseID & ";"

In any case, something is missing, so that the statement should definitely fail.

 

[Wysy]

Interestingly that part work well. Probably the confusion is caused that i kept the name strUpdate through the code with different sql statement.

 

[Minty]

Interestingly that part work well. Probably the confusion is caused that i kept the name strUpdate through the code with different sql statement.

I can see that, however, it isn't reset to something else, before the execution outside the loop so will execute again.
It probably won't be noticeable as it's not inserting new records simply updating an existing one, just doing the same update twice.

 

[Wysy]

Actually two strUpdate statement is different. The second one has one more criterium in where statement