loop through form fields 1

[lespaul]

I want to check all the fields on a document and check if they are filled in. I'm not a VBA programmer and I can't get the syntax right. Here's some psuedocode:

For i = 0 to FormFIelds.COunt do
  If FormField.Value = '' then 
    ShowMessage("Please complete all the items")
    exit Sub
  else
    ThisDocument.SaveAs ("O:\Conference Questionnaire\" & Environ("username"))
  end if
next

any suggestions? Don't think it should be that hard.

Thanks,
les

 

[PHV]

Something like this ?
For Each aField In ActiveDocument.FormFields
If Trim(aField.Result) = "" Then
MsgBox "Please complete all the items"
Exit Sub
End If
Next aField
ActiveDocument.SaveAs "O:\Conference Questionnaire\" & Environ("username")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

perfect! thanks

Leslie

 

[fumei]

Just a note: The code will only display the message for empty textboxes. If there are any other types such as a checkbox, or a dropdown, that code will not find if they are checked, or an item selected from the list.

A checkbox Result is never = "", or NOT = "", it will either 0 or 1.

A dropddown Result will never = "", or NOT = "", it will be the ListIndex item.

You can catch those by using the Type property of the field.

See my FAQ on FormFields. faq68-5299

Gerry

 

[lespaul]

even if I've included a " " entry in the drop down box?

" "
< 1
1
2
3




Leslie

 

[lespaul]

Also, what do I need to exit the word application? I can get the document to close, but I want word to exit. I'm calling word from another program with the CreateObject command.

thanks,
leslie

 

[RoyVidar]

Don't know much about userform controls, I'm afraid, but closing word from another app - use the .Quit method of your Word application object:

[tt]oWord.Quit[/tt]

I'll issue a little warning, too. You probably know, but still... Remember to fully qualify all your references to objects, methods and properties in Word when doing automation, else you'll be experiencing extra instances of Word, error 462, 429... Here are two threads on opening Word (first one) and Excel from Access thread705-920385, thread705-920968, discussing such issues.

Roy-Vidar

 

[lespaul]

I'm opening Word from Notes; the user needs to fill out the form fields in the Word document. Then I have the button that saves the document, but it leaves the word application open. If I use the Word.Quit from the Notes application, then it doesn't stay open for the user to enter the information and I don't want to add a timer to the notes application because I don't know how long it will take the person to finish completing the form.

I don't mind them closing it manually, this is a quick and dirty little form for our Employee Conference, nothing mission critical or even very important if you come right down to it!



Leslie

 

[fumei]

Good advice from Roy. Do explicitly reference your objects correctly.

And no, " ", is not the same as "". So if the check is for "", then " " would return a False.

Just as a point, here are the Type code constants for formfields.

Textbox = 70
CheckBox = 71
DropDown = 83

You can do a Select Case on the Type of FormField and check from there.

Gerry

 

[lespaul]

I know that " " is not the same as "".


The code provided by PHV:

For Each aField In ActiveDocument.FormFields
  If Trim(aField.Result) = "" Then
    MsgBox "Please complete all the items"
    Exit Sub
  End If
Next aField
ActiveDocument.SaveAs "O:\Conference Questionnaire\" & Environ("username")

trims the result of each field, so " " is converted to "". All I wanted to make sure of, is that this code is checking the drop down boxes and if the user leaves the dropdown box on the " " selection, the message box will trigger. In Delphi I can check the "text" of the drop down selection and the above code would work just fine.

Do explicitly reference your objects correctly

this is what I was asking for help on, do you know how I should go about doing it from my notes application?

Here's my Notes "app", its a lotus script run on a button in an email:

Sub Click(Source As Button)
  Set wordapp = createobject("word.application")
  wordapp.visible = True
  wordapp.documents.add("O:\Conference Questionnaire\Who Am I.dot")	
End Sub

the Word application starts and the document is created from the template. The user fills in 5 form fields and two drop downs. Both drop downs have " " as the first choice so the field is blank when the user opens the document.

There is a button with the following code:

Private Sub btnSubmit_Click()

  For Each aField In ActiveDocument.FormFields
    If Trim(aField.Result) = ""  Then
      MsgBox "Please complete all the items"
      Exit Sub
    End If
  Next aField
  ActiveDocument.SaveAs "O:\Conference Questionnaire\" & Environ("username")
  ActiveDocument.Close
End Sub

This leaves Word open in front of the user after they press the button. I want to close Word at the end of this routine. I don't want to use a sleep or timer function in the Lotus Notes app because I don't know how long it will take to complete all the information. I want to close the word application after saving the document.

Any useful suggestions?

Thanks,
leslie

 

[fumei]

wordapp.Quit
set wordapp = Nothing

You should use both Quit, and explicitly release the object.

Gerry

 

[lespaul]

I'm starting to feel very insulted!!! I am not an idiot, I know that if I put that in the Lotus notes Button Click, then Word will quit.

Did you even read my post?

If I use the code you suggested above in NOTES, then the WORD application opens and closes IMMEDIATELY!!!!! I need the word document to stay open until after the WORD button is pressed so the code runs that checks the document.

Here's the process I need. I have all of it done except 2c.

1. Send email to court with Lotus Notes button.
a. When button in email is pressed Open Word and add template.
2. Employee answers all the questions on the word document and press the Word button.
a. When Word button is selected, make sure all the fields are completed.
b. Save the document with the username in a specific directory.
c. Close word instance FROM WORD.

So, I am STILL looking for USEFUL ideas on how I can accomplish 2c.

Leslie

 

[PHV]

Make wordapp global and replace all ActiveDocument. by wordapp.ActiveDocument. to avoid implicit NEW instance of the Word.Application object.
Finally, at the end of btnSubmit_Click, add this:
wordapp.Quit
Set wordapp = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fumei]

Certainly did not intend to insult. Sorry you feel that way.

Yes I did read your post.

Gerry

 

[lespaul]

PHV, how do I:

Make wordapp global

??

Gerry, I in a snippy mood yesterday, sorry it got taken out on you!!

les

 

[PHV]

Make wordapp global
Simply define it outside any procedure, ie in the Declarations section.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

So, are you saying that if I put wordApp in the Declarations section of my Lotus Notes button, then I can close it from the word button?

Thanks,
les

 

[PHV]

My bad, didn't realize the 2 VBA instances.
In the Lotus side make sure to always qualify any word object/method/property with wordapp to avoid hidden implicit instantation of word.
In the word side, after the SaveAs call simply call the Application.Quit method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244