Compile error: end if without block if (1 Viewer)

[Mwam]

Hi,

I have created a form based on a query where the user can select the records (using a check box named Chk1 which is bound to a yes/no field in the Table) that he/she wants to have exported to a word template (with field codes). The user must be able to select several records and all of these should then be exported. I have the following code but it gives a compile error: end if without block if. Any help would be greatly appreciated, thank you in advance!

here is my code:

For Each ctl In Me.Controls
If Me.Chk1 = -1 Then
With doc
.FormFields("fldProjectName").Result = Me.ProjectName
.FormFields("fldProjectDetail").Result = Me.ProjectDetail
.FormFields("fldProjectEnd").Result = Me.ProjectEnd
.FormFields("fldCharlesRole").Result = Me.CharlesRole
End If
Next ctl

 

[plog]

You are missing an End With

https://msdn.microsoft.com/en-us/library/wc500chb.aspx?f=255&MSPPError=-2147217396

 

[Mwam]

Thanks for the help plog!
My apologies, I only copied part of the script, here is the rest where I have included a End with statement.

Private Sub Command19_Click()

Dim appWD As Word.Application
Dim WordApp As Word.Application
Dim doc As Word.Document
Dim ctl As Control

On Error Resume Next
Error.Clear

Set WordApp = CreateObject("Word.Application")
If Err.Number <> 0 Then
Set WordApp = New Word.Application
WordApp.Visible = True
End If
Set doc = WordApp.Documents.Open("xxxx", , True)

For Each ctl In Me.Controls
If Me.Chk1 = -1 Then
With doc
.FormFields("fldProjectName").Result = Me.ProjectName
.FormFields("fldProjectDetail").Result = Me.ProjectDetail
.FormFields("fldProjectEnd").Result = Me.ProjectEnd
.FormFields("fldCharlesRole").Result = Me.CharlesRole
'Else: Me.Chk1 = 0
End If
Next ctl

End With
WordApp.Visible = True
WordApp.Activate

Set doc = Nothing
Set WordApp = Nothing

End Sub

 

[James Deckert]

move your 'end with' before your 'end if'

 

[fvdb]

Place end with before end if

 

[Mwam]

Thank you James and fvdb!

That solved the error but when I export click on the command button only the first record that has its check box checked gets exported. Do you have an idea how I can export all of the checked records?

My word template only has the fields entered once but even if I copy and paste the field several times, only the first checked record gets exported.

any help would be greatly appreciated.

 

[fvdb]

Your form is based on a query. You mean the data on the form is a continous form with for every record a checkbox wich can be checked to be exported or not?

The best way todo this is ,because your checkbox is bounded, that you make an recordset based on the table of the values where the checkbox is checked and work with that recordset to export to word.

 

[Mwam]

Hi fvdb,

You are correct with the first statement. The checkbox is checked in the form which is based on a query.
And I have now figured out that I need to use a recordset. I get confused since I have a for/next and if and I am not sure where the rst lines must be incorporated. Could you please help me with this, I am new to VBA and would thus greatly appreciate your help.

Thanks!

 

[fvdb]

Easiest way

Make a query with the fields you need to export (ProjectName, ProjectDetail,ProjectEnd, CharlesRole, txtboxcolumn) and filter the values where txtboxcolum = true

Add these code (untested)

 Dim rst as DAO.recorset
  
 set rst = Currentdb.openrecordset("qryname")
  
 do until rst.eof
  
 With doc
.FormFields("fldProjectName").Result = rst.fields(0).value
.FormFields("fldProjectDetail").Result = rst.fields(1).value
 .FormFields("fldProjectEnd").Result = rst.fields(2).value
 .FormFields("fldCharlesRole").Result = rst.fields(3).value
 .Update
end with
 rst.movenext
 loop
  
 set rst = nothing

I am not sure you can add more values to 1 formfield on the Word file. I think you have to put a table and put there the data in.

This link can also help you more on the way to get what you want

http://www.devhut.net/2012/04/23/ms-access-vba-export-records-to-ms-word/

 

[Mwam]

Thank you fvdb.

I think I am starting to make progress. I am just confused with txtboxcolumn = true. Would this be an unbound field? I have it as a bound field right now otherwise when I check the unbound check box in the Form for a record then all of the records get automatically selected.

 

[fvdb]

Txtboxcolum is name if the column(yes/no) where the chkbox is bounded to. In the query you set there criteria for that column true

My mistake wrong name choise. Had to be chkbox.