Validating data entry (Access 2000)

**ADezii** · Nov 2 '07, 11:10 PM

boliches, I'm not speaking for the rest of the Moderators/Experts in this Forum, but personally I will not attempt to decipher 200 lines of code unles it is properly Formatted and Tagged. If you can get the code in a more readable and tagged format, I will be more than happy to have a look at it.

**boliches** · Nov 3 '07, 12:53 PM

Originally posted by ADezii

boliches, I'm not speaking for the rest of the Moderators/Experts in this Forum, but personally I will not attempt to decipher 200 lines of code unles it is properly Formatted and Tagged. If you can get the code in a more readable and tagged format, I will be more than happy to have a look at it.

I dont fully understand what you want, but I have cut it down to a manageable size (I hope)

Code:

blnError = False
 
strError = "Please complete the following:" & vbCrLf
            
            If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
                  blnError = True
                      strError = strError & "Vehicle Details" & vbCrLf
  
              ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
                    blnError = True
                        End If
            If blnError = True Then
                Cancel = True
                    MsgBox strError, vbOKOnly + vbCritical, "Stop!"
                         Exit Sub
              End If

The problem is the MsgBox is still shown even when all relevant Text Boxes are filled

**ADezii** · Nov 3 '07, 01:44 PM

Originally posted by boliches

I dont fully understand what you want, but I have cut it down to a manageable size (I hope)

Code:

blnError = False
 
strError = "Please complete the following:" & vbCrLf
            
            If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
                  blnError = True
                      strError = strError & "Vehicle Details" & vbCrLf
  
              ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
                    blnError = True
                        End If
            If blnError = True Then
                Cancel = True
                    MsgBox strError, vbOKOnly + vbCritical, "Stop!"
                         Exit Sub
              End If

The problem is the MsgBox is still shown even when all relevant Text Boxes are filled

This is what I was referring to, I'll look at it as soon as I get a chance.
[CODE=vb]
Private Sub Command107_Clic k()
Dim objPDF As New PDFClass
Dim IngResult As Long
Const PDFENGINE_PDF99 5 = 5
Dim stFaxNo As String
Dim rptFaxDeal As String
Dim strBroker As String
Dim strTable As String
Dim strError As String
Dim blnError As String

strBroker = Forms!frmMainFo rm.Text265
strTable = "tblDeals" + strBroker
blnError = False

strError = "Please complete the following:" & vbCrLf

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
trError = strError & "Vehicle Details" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
strError = strError & "Mileage" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![RegNo]) Then
blnError = True
strError = strError & "Registrati on Number" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![RegNo]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Year]) Then
blnError = True
strError = strError & "Year of Registration" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Year]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text118]) Then
blnError = True
strError = strError & "No. of Owners" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text118]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text180]) Then
blnError = True
strError = strError & "Service History" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text180]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Import]) Then
blnError = True
strError = strError & "UK car or Import" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Import]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Colour]) Then
blnError = True
strError = strError & "Vehicle Colour" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Colour]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Interior]) Then
blnError = True
strError = strError & "Interior" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Interior]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Damage]) Then
blnError = True
strError = strError & "Condition" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Damage]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text250]) Then
blnError = True
strError = strError & "Any Extras" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text250]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![DealBid]) Then
blnError = True
strError = strError & "Amount Bid" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![DealBid]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text148]) Then
blnError = True
strError = strError & "Sellers Comments" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text148]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text152]) Then
blnError = True
strError = strError & "Sellers Collection details" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text152]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![AmountFaxed]) Then
blnError = True
strError = strError & "Selling Brokers Fee" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![AmountFaxed]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text150]) Then
blnError = True
strError = strError & "Buyers Comments" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text150]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text154]) Then
blnError = True
strError = strError & "Buyers Collection details" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text154]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text145]) Then
blnError = True
strError = strError & "Buying Brokers Fee" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text145]) Then
blnError = True
End If

If blnError = True Then
Cancel = True
MsgBox strError, vbOKOnly + vbCritical, "Stop!"
Exit Sub
End If

MissingBuyer:
Data1 = MsgBox("Buying Dealers details must be completed to continue", vbOKCancel, "Missing data!")
If Data1 = vbCancel Then Exit Sub
If Data1 = vbOK Then Me.Text36.SetFo cus
Me.Text36.BackC olor = 8454143
Exit Sub

Continue:
If IsNull(Me.Text3 6) Or IsNull(cboBuyin gDealer) Or cboBuyingContac t = "" Or Text188 = "" Or txtFaxNo = "" Then
GoTo MissingBuyer
Else
stFaxNo = Me.DealNo
rptFaxDeal = "rptFaxDeal Fax"
ViewReport = "C:\PDF995\ " & stFaxNo + ".pdf"
SQLtext = "INSERT INTO " & strTable & " (BuyingDealer)S ELECT Text97 WHERE FROM " & strTable & "(((" & strTable & ".Ticket)=[Forms]![frmDeal].[Text178]));"
SQLtext1 = "UPDATE " & strTable & " SET " & strTable & ".BuyingDea ler = [Forms]![frmDeal].[text97]WHERE (((" & strTable & ".Ticket)=[Forms]![frmDeal].[Text178]));"

DoCmd.RunSQL (SQLtext1)

With objPDF
.PDFEngine = PDFENGINE_PDF99 5
.reportname = rptFaxDeal
.outputfile = "C:\PDF995\ " & stFaxNo + ".pdf"
.PrintImage
IngResult = .Result
End With

Set objPDF = Nothing

DoCmd.OpenRepor t "rptFaxDealFaxS ell", acPreview
Reports!rptFaxD ealFaxSell.Capt ion = stFaxNo + "-S" + ".pdf"
DoCmd.OpenRepor t "rptFaxDealFaxB uy", acPreview
Reports!rptFaxD ealFaxBuy.Capti on = stFaxNo + "-B" + ".pdf"
Me.comSendFax.E nabled = True
End Sub[/CODE]
NOTE: Is there any reason why the Validation is not done in the BeforeUpdate() Event of frmDeal?

**ADezii** · Nov 3 '07, 06:13 PM

Originally posted by boliches

I dont fully understand what you want, but I have cut it down to a manageable size (I hope)

Code:

blnError = False
 
strError = "Please complete the following:" & vbCrLf
            
            If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
                  blnError = True
                      strError = strError & "Vehicle Details" & vbCrLf
  
              ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
                    blnError = True
                        End If
            If blnError = True Then
                Cancel = True
                    MsgBox strError, vbOKOnly + vbCritical, "Stop!"
                         Exit Sub
              End If

The problem is the MsgBox is still shown even when all relevant Text Boxes are filled

It appears that with your current logic, blnError will never evaluate to False. In the If...End If Statements, it seems to me that the ElseIf Clauses (Not IsNull()) should set the value of blnError to False, and the code syntax can be shortened. A couple of examples will illlustrate my point:
[CODE=vb]If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
trError = strError & "Vehicle Details" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
strError = strError & "Mileage" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
End If[/CODE]
try
[CODE=vb]
'It's either Not Null or Null
If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
trError = strError & "Vehicle Details" & vbCrLf
Else
blnError = False
End If

'It's either Not Null or Null
If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
strError = strError & "Mileage" & vbCrLf
Else
blnError = False
End If
[/CODE]

**boliches** · Nov 4 '07, 11:40 AM

Originally posted by ADezii

It appears that with your current logic, blnError will never evaluate to False. In the If...End If Statements, it seems to me that the ElseIf Clauses (Not IsNull()) should set the value of blnError to False, and the code syntax can be shortened. A couple of examples will illlustrate my point:
[CODE=vb]If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
trError = strError & "Vehicle Details" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
End If

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
strError = strError & "Mileage" & vbCrLf
ElseIf Not IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
End If[/CODE]
try
[CODE=vb]
'It's either Not Null or Null
If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
trError = strError & "Vehicle Details" & vbCrLf
Else
blnError = False
End If

'It's either Not Null or Null
If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Miles]) Then
blnError = True
strError = strError & "Mileage" & vbCrLf
Else
blnError = False
End If
[/CODE]

Have tried your suggestion, but for some reason when a textbox is null "blnError" still shows "blnError = False" and not "blnError = True"! WHY?? Is it because at the very beginning of the code I have

Code:

Dim blnError As String
blnError = False

I have concluded the script with:

Code:

 If blnError = True Then
                Cancel = True
                    MsgBox strError, vbOKOnly + vbCritical, "Stop!"
                         Exit Sub
           Else: GoTo [B]Continue[/B] 
 End If

Would this be right? As I get the feeling this code is not as you guys would write it! ("Continue:" is where the concluding code is to complete the task)

Your help please as I am struggling with getting blnError to equal the correct action ie False when False and True when True.

**boliches** · Nov 4 '07, 11:49 AM

I meant to ask how you "Formatted and Tagged" the script to make it easier to read, also what is the benefit of putting the script in the "Before Update" event procedure as against on a Command Button? Is it a speed related thing?

**ADezii** · Nov 4 '07, 12:57 PM

Originally posted by boliches

I meant to ask how you "Formatted and Tagged" the script to make it easier to read, also what is the benefit of putting the script in the "Before Update" event procedure as against on a Command Button? Is it a speed related thing?

Have tried your suggestion, but for some reason when a textbox is null "blnError" still shows "blnError = False" and not "blnError = True"! WHY?? Is it because at the very beginning of the code I have

[CODE=vb]Dim blnError As String
blnError = False[/CODE]

[CODE=vb]'The correct Syntax should be:
Dim blnError As Boolean
blnError = False[/CODE]

I meant to ask how you "Formatted and Tagged" the script to make it easier to read, also what is the benefit of putting the script in the "Before Update" event procedure as against on a Command Button? Is it a speed related thing?

The BeforeUpdate() Event of the Form fires before the actual data present on the Form is Saved to the underlying Record Source and is the logical location for Validation Code. You 'Format' and 'Tag' code segments by the use of Indentations and the Code Tags present in Edit Mod (#). Select a specific code block, then click on the # sign and designate the code context as text, vb, sql, etc.

http://www.thescripts.com/forum/faq.php?faq=vb_read_and_post

boliches, there appears to numerous problems with your code segment, too many for a back-and-forth conversation. If you like, you can send me the Database as an E-Mail Attachment and I'll have a good look at it when I get a chance. The only problem is that it will not be anytime soon and the code itself is not simply a couple of lines. Let me know what you would like to do.

**boliches** · Nov 4 '07, 01:15 PM

Have amended the syntax, but still am failing to get the blnError = False to blnError = True when a text box is "Null". What am I doing wrong?

Thanks for the update on Formatting etc.

Regards

**boliches** · Nov 4 '07, 02:05 PM

Originally posted by boliches

Have amended the syntax, but still am failing to get the blnError = False to blnError = True when a text box is "Null". What am I doing wrong?

Thanks for the update on Formatting etc.

Regards

Thanks for your interest in my problem, but the database is quite sizeable and may be difficult for me to get you to where the problem is within the project.

My main issue here is that I want to alert a user that info is missing from a form, and therfore (via a msgbox) inform that user which items are missing! Have I gone about it in totally the wrong way? If so could you please advise.

Many thanks. (Really appreciate your time!)

**ADezii** · Nov 4 '07, 02:42 PM

Originally posted by boliches

Thanks for your interest in my problem, but the database is quite sizeable and may be difficult for me to get you to where the problem is within the project.

My main issue here is that I want to alert a user that info is missing from a form, and therfore (via a msgbox) inform that user which items are missing! Have I gone about it in totally the wrong way? If so could you please advise.

Many thanks. (Really appreciate your time!)

My advice to you would be the following:

Place the code in the BeforeUpdate() Event of the Form.
Change the Declaration of blnError as previously indicated.
[CODE=vb]Dim blnError As Boolean[/CODE]
Evaluate each If...End If Statement in turn, and if the results are correct, move to the next If..End If (will illustrate shortly).
This Method is time consuming but effective, and eventually should produce the desired results.
[CODE=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)
Dim objPDF As New PDFClass
Dim IngResult As Long
Const PDFENGINE_PDF99 5 = 5
Dim stFaxNo As String
Dim rptFaxDeal As String
Dim strBroker As String
Dim strTable As String
Dim strError As String
Dim blnError As Boolean
strBroker = Forms!frmMainFo rm.Text265
strTable = "tblDeals" + strBroker
blnError = False

strError = "Please complete the following:" & vbCrLf

If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
blnError = True
strError = strError & "Vehicle Details" & vbCrLf
Else
blnError = False
End If

'Avoid Errors, not interested in Saving - only Testing
Cancel = True
End Sub
[/CODE]
Enter a Value in [Forms]![frmDeal]![frmDealsSub].Form![Text62], blnError should return False, if it does add the next If...End If Statement and continue this pattern until all conditions have been tested.

**boliches** · Nov 4 '07, 03:22 PM

I think I now know why this is failing!

Code:

Dim blnError As Boolean
blnError = False
 
strError = "Please complete the following:" & vbCrLf

  If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text118]) Then
     blnError = True
          strError = strError & "No. of Owners" & vbCrLf
  Else
     blnError = False
        End If

 If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Colour]) Then
    blnError = True
            strError = strError & "Vehicle Color" & vbCrLf
  Else
    blnError = False
       End If

 If blnError = True Then                
                    MsgBox strError, vbOKOnly + vbCritical, "Stop!"
                             Exit Sub
   Cancel = True
       End If
End Sub

This code works fine when all data is missing! However I think the problem is that as the code unfolds the blnError will equal what ever the final textbox is! For example if the final textbox (in the above case "Colour") has text in it then blnError = False. If the initial textbox (Text118) is Null the blnError will need to be True to action the msgbox. It is being overriden by the next textbox.

Hope this makes sense, but is there a resolution? Would some sort of loop work?? Would not have a clue myself!

**boliches** · Nov 4 '07, 05:39 PM

I think I may have it:

Code:

Dim strError As String
Dim blnError As Boolean
    
blnError = False
 
    strError = "Please complete the following:" & vbCrLf
         
            If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text118]) Then
                  blnError = True
                      strError = strError & "No of Owners" & vbCrLf
                             End If
                       
            If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Colour]) Then
                  blnError = True
                      strError = strError & "Colour" & vbCrLf
                             End If
            
           If blnError = False Then MsgBox "All Completed"
           If blnError = True Then
                 MsgBox strError, vbOKOnly + vbCritical, "Stop!"
                         Exit Sub
           End If

What do you think?

Really appreciate your help on this matter, youve been very helpful.

**ADezii** · Nov 4 '07, 07:35 PM

Originally posted by boliches

I think I now know why this is failing!

Code:

Dim blnError As Boolean
blnError = False
 
strError = "Please complete the following:" & vbCrLf

  If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text118]) Then
     blnError = True
          strError = strError & "No. of Owners" & vbCrLf
  Else
     blnError = False
        End If

 If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Colour]) Then
    blnError = True
            strError = strError & "Vehicle Color" & vbCrLf
  Else
    blnError = False
       End If

 If blnError = True Then                
                    MsgBox strError, vbOKOnly + vbCritical, "Stop!"
                             Exit Sub
   Cancel = True
       End If
End Sub

This code works fine when all data is missing! However I think the problem is that as the code unfolds the blnError will equal what ever the final textbox is! For example if the final textbox (in the above case "Colour") has text in it then blnError = False. If the initial textbox (Text118) is Null the blnError will need to be True to action the msgbox. It is being overriden by the next textbox.

Hope this makes sense, but is there a resolution? Would some sort of loop work?? Would not have a clue myself!

You could greatly simplify your If...End If logic via:

See if data in a Field is missing, if it is display an appropriate Message Box, if not drop through to the next If...End If Statement.
If the data was missing, set Focus to the Field with the missing data.
If the data was missing, Exit the BeforeUpdate() Event..

[CODE=vb]If IsNull([Forms]![frmDeal]![frmDealsSub].Form![Text62]) Then
Msgbox <missing data appropriate Message Box>
[Forms]![frmDeal]![frmDealsSub].Form![Text62].SetFocus
Exit Sub
End If[/CODE]