How to check if data has been entered into controls/fields

[dlackey]

Hello,

I have been working on an Access database that has several tabs with each tab having several controls/fields. Some of these controls/fields are to be "required" for data entry (but not using required attribute in the table). Also, on the form there is a command button for users to click to print a report using data from all controls.

Now, when users click the "Print Report" button, I want the database to check if the "required" fields have data in them. If not, another form opens and asks the user if they want to enter the missing data for that particular field or cancel the entire report and close database. When a user clicks continue, the "popup" form closes and focus is sent to the field with the missing data. However, here is the problem. The macro I have running to check the fields generates several "popup" forms for each field missing data, all at the same time. I would like the macro to generate only one "popup" form at a time. The user will enter the data and click the "Print Report" button again. Now, the macro should check the fields again, giving the apropriate "popup" form, (and so on). If no missing data, a report is printed.

I don't know if this can be done, but please advise.

 

[willir]

dlackey

There are several approaches.

Use the BeforeUpdate event procedure. Check to ensure the contents of the form have valid data. If not, use the Cancel method to prevent the update.

Use a form with unbound control fields. A lot more work, but now you have total control, and can review all data. For example, the only way the end user can add a new record is by clicking on an Update command button. The command button evaluates that and updates / inserts the record if the data is valid, or flags the user on what is missing.

Richard

 

[TheAceMan1]

How are ya dlackey . . . . .

Here's another method you can try.

First, in the [blue]Tag Property[/blue] of the [blue]Required Controls[/blue] enter [purple]Req[/purple]

Then . . . in the [blue]Click event[/blue] of your button, copy/paste the following code:

[blue]   Dim ctl As Control, xCtl
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "[purple][b]Req[/b][/purple]" And Trim(ctl & "") = "" Then
         Set xCtl = ctl
         Exit For
      End If
   Next
   
   If IsEmpty(xCtl) Then [green]'All required OK.[/green]
      [green]'Your code for printing report[/green]
   Else [green]'At least one required missing.[/green]
      Msg = "Required Data for '" & xCtl.Name & "' is missing!" & DL & _
            "Click 'Yes' to go back and enter the data . . ." & DL & _
            "Click 'No' to abort, and cancel printing report & close database. . ."
      Style = vbCritical + vbYesNo
      Title = "Required Data Error! . . ."
      
      If MsgBox(Msg, Style, Title) = vbYes Then
         ctl.SetFocus
      Else
         [green]'Your code for closing database[/green]
      End If
   End If[/blue]

Thats it . . . give it a whirl & let me know . . . .

See Ya! . . . . . .

 

[dlackey]

Hello TheAceMan1,

I copied/pasted the code and when button is clicked I get

Run-time error '438':
Object doesn't support this property or method

and the line highlighted in code is

If ctl.Tag = "Req" And Trim(ctl & "") = "" Then

What am I doing wrong?
Thanks...

 

[RoyVidar]

This tests both the .Tag property and the .Value property of the control. Not all controls have a .Value property, which makes them "bomb" with that errormessage. You might want to exclude some controltypes from the looping, for instance by adding something like the below within the loop, but prior to testing the .Value property:

[tt]select case ctl.controltype
case accombobox, actextbox
' select those you want to validate
' rest of the code[/tt]

There should be some samples available thru a search here at Tek-Tips, or have a look here faq702-5010

Roy-Vidar

 

[TheAceMan1]

dlackey . . . . .

Post the [blue]control type[/blue] (Text, Numeric, CheckBox, ect.) [purple]of the controls you added 'Req'[/purple] to the [purpleTag property . . . .[/purple]

See Ya! . . . . . .

 

[dlackey]

Thanks for all the help!

I have it working correctly!