Is it SetFocus that I should use

[Steven811]

Hi

I have a cmd button to export a record set to Word from the current form, this works okay. What I also want to do is move to the nested subform and do the same again. I have tried using SetFocus to move to the next form but, this doesn't work.

Am I barking up the wrong tree, should I be using something else?

Any assistance would be appreciated.

Steven811

 

[KenReay]

Hi

"but, this doesn't work."

is not very helpful,

error message(s)?

what happens

what does your code look like




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Steven811]

Hi Ken

Good point.

I enclose a copy of the code that I use and you can see the commented blocks with the code I have tried to use.

There is no error message, Word opens the MyMerge document and inserts that text into the bookmarked fields from the parent form forCustomerDetails2. The remaining bookmarks remain unchanged.

As you can tell I'm a novice.

Regards

Steven811

Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err

Dim objWord As Word.Application


'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Open ("C:\MyMerge.doc")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("CompanyName").Select
.Selection.Text = (CStr(Forms!forCustomerDetails2!CompanyName))


'Me.forSiteName.Form.SetFocus

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("SiteName").Select
.Selection.Text = (CStr(Forms!forSiteName!SiteName))

.ActiveDocument.Bookmarks("cboDesignation").Select
.Selection.Text = (CStr(Forms!forSiteName!cboDesignation))

.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = (CStr(Forms!forSiteName!SiteAddress1))


'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("Supplier").Select
.Selection.Text = (CStr(Forms!forJobTracking!Supplier))

.ActiveDocument.Bookmarks("JobNo").Select
.Selection.Text = (CStr(Forms!forJobTracking!JobNo))

.ActiveDocument.Bookmarks("Description").Select
.Selection.Text = (CStr(Forms!forJobTracking!Description))


' Me.forProject2.Form.SetFocus

'Me.forProject2.SetFocus
'Me.forProject2!DateofComments.SetFocus
'
'
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("DateofComment").Select
.Selection.Text = (CStr(Forms!forProject2!DateofComment))

.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(Forms!forProject2!Comments))

.ActiveDocument.Bookmarks("Action").Select
.Selection.Text = (CStr(Forms!forProject2!Action))

.ActiveDocument.Bookmarks("ActionByDate").Select
.Selection.Text = (CStr(Forms!forProject2!ActionByDate))

.ActiveDocument.Bookmarks("ByWhom").Select
.Selection.Text = (CStr(Forms!forProject2!ByWhom))



End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
' objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
' objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'
' 'Quit Microsoft Word and release the object variable.
' objWord.Quit
' Set objWord = Nothing
' Exit Sub

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If

Exit Sub
End Sub

 

[KenReay]

Steven

I am not clear if this code is being execited from an event in the main form (or a button on that form) if it is then you do not need to move focus to the relevant control in order to address it, you should be able to do it using the .FORM property of the subform control something like:

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("DateofComment").Select
.Selection.Text = (CStr(Me.forProject2.FORM!DateofComment))


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Steven811]

Hi Ken

That was really useful. I have now moved to the 3rd subForm and Access stops there, no error message.

The code has altered slightly from:

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("DateofComment").Select
.Selection.Text = (CStr(Me.forProject2.FORM!DateofComment))

To:

.ActiveDocument.Bookmarks("cboManager").Select
.Selection.Text = (CStr(Me!forContactsJobTracking.Form!cboManager))

The change being the exclamation mark has become a full stop on the last line. Without this change I get the error message "Compile error Method or data member not found"

Any ideas?

Regards

Steven811

 

[KenReay]

double check the cboManager control on the subform, have you miss-spelled the name?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Steven811]

Hi Ken

I've double checked and it's definately called cboManager.

Regards

Steven811

 

[TheAceMan1]

How are ya Steven811 . . . . .

In your code you show four forms:

[blue]forCustomerDetails2
forSiteName
forJobTracking
forProject2[/blue]

Who's Parent to Who or Independent?

Which form/subform contains the button?

See Ya! . . . . . .

 

[KenReay]

Hi

OK

.Selection.Text = (CStr(Me!forContactsJobTracking.Form!cboManager))

are you sure that forContactsJobTracking is teh name of the subform CONTROL?

Basically what I am saying here is that the method works, you have proved it with the first two subforms, so, there must be some silly little error in this line and the most likely error is a typo, or something like that



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Steven811]

Hi TheAceMan1

The order of the forms is exactly as you've listed them.

forCustomerDetails2 is the parent
forSiteName is nested in forCustomerDetails2
forJobTracking is nested in forSiteName
forProject2 is nested in forJobTracking

Regards

Steven

 

[Steven811]

Hi Ken

I think you're right.

Must have got too close to it last night and couldn't spot the problem. Will tackle it again shortly.

Regards

Steven811

 

[Steven811]

Hi
I can't resolve the problem that once I get to the 3rd form Access does not complete the transfer, no error message. I know that this code works as I have used it via a cmd button with the subForm as the parent.

The bookmarks remain in the word document for the unmerged fields.

When I then use the cmd button on the parent, the transfer works successfully through the parent and the 1st subForm and stops there. The code that I am using is as follows:

[Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open ("C:\MyFile.doc")

'ParentForm
.ActiveDocument.Bookmarks("ParentField").Select
.Selection.Text = (CStr(Forms!ParentForm!ParentField))

'1st SubForm
.ActiveDocument.Bookmarks("1stsubFormField").Select
.Selection.Text = (CStr(Me!1stSubForm.Form!1stsubFormField))

'2nd subForm
.ActiveDocument.Bookmarks("2ndsubFormField").Select
.Selection.Text = (CStr(Me!2ndsubForm.Form!2ndsubFormField))


MergeButton_Err:
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If

Exit Sub
End Sub]

I'd be really grateful for any advice.

Steven811

 

[TheAceMan1]

Steven811 . . . .

To cut the referencing confusion try this:

[blue]Dim frm As Form, sfrm1 As Form, sfrm2 as form
Set frm = Forms![purple][b]ParentFormName[/b][/purple]      ' Main Form
Set sfrm1 = frm![purple][b]SubForm1Name[/b][/purple].Form   ' subform level 1
Set sfrm2 = sfrm1![purple][b]SubForm1Name[/b][/purple].Form ' subform level 2[/blue]

Then to reference the controls on the form, the format is:

[blue]frm!ControlName
sfrm1!ControlName
sfrm2!ControlName[/blue]

See if that helps . . . .

See Ya! . . . . . .

 

[Steven811]

Hi TheAceMan1

Your suggestion makes good sense and I've tried to implement it as follows:

Dim frm As Form, sfrm1 As Form, sfrm2 As Form, sfrm3 As Form
Set frm = frm!forCustomerDetails2 ' Main Form
Set sfrm1 = sfrm1!forSiteName.Form ' subform level 1
Set sfrm2 = sfrm2!forJobTracking.Form ' subform level 2
Set sfrm3 = sfrm3!forProject2.Form ' subform level 3

resulting in:

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("CompanyName").Select
.Selection.Text = (CStr(Forms!frm.CompanyName))

.ActiveDocument.Bookmarks("SiteName").Select
.Selection.Text = (CStr(Forms!sfrm1!SiteName))

.ActiveDocument.Bookmarks("cboDesignation").Select
.Selection.Text = (CStr(Forms!sfrm1.Form!cboDesignation))

.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = (CStr(Forms!sfrm1.Form!SiteAddress1))

.ActiveDocument.Bookmarks("DateofComment").Select
.Selection.Text = (CStr(Forms!sfrm2!DateofComment))

I can't get it work and can't see why.

Any suggestions would be very welcome.

Regards

Steven811

 

[KenReay]

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("CompanyName").Select
.Selection.Text = (CStr(Forms!frm.CompanyName))

.ActiveDocument.Bookmarks("SiteName").Select
.Selection.Text = (CStr(sfrm1!SiteName))

.ActiveDocument.Bookmarks("cboDesignation").Select
.Selection.Text = (CStr(sfrm1.Form!cboDesignation))

.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = (CStr(sfrm1.Form!SiteAddress1))

.ActiveDocument.Bookmarks("DateofComment").Select
.Selection.Text = (CStr(sfrm2!DateofComment))


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Steven811]

Hi Ken

Thanks for the reply.

That's exactly as I have it, still doesn't work though.

Do you think the problem may be in here:

Dim frm As Form, sfrm1 As Form, sfrm2 As Form, sfrm3 As Form
Set frm = frm!forCustomerDetails2 ' Main Form
Set sfrm1 = sfrm1!forSiteName.Form ' subform level 1
Set sfrm2 = sfrm2!forJobTracking.Form ' subform level 2
Set sfrm3 = sfrm3!forProject2.Form ' subform level 3

Regards

Steven811

 

[TheAceMan1]

Steven811 . . . .

Yes . . . objectivity is still a little off, should be:

[blue]   Set frm = frm!forCustomerDetails2     'Main Form
   Set sfrm1 = [purple][b]frm[/b][/purple]!forSiteName.Form    'subform level 1
   Set sfrm2 = [purple][b]sfrm1[/b][/purple]!forJobTracking.Form 'subform level 2
   Set sfrm3 = [purple][b]sfrm2[/b][/purple]!forProject2.Form    'subform level 3[/blue]

Understand you only have to use the reference object:
[blue]frm.CompanyName[/blue] for instance.
Not
[blue]Forms!frm.CompanyName[/blue] which by the way is wrong for the [purple]companyname bookmark[/purple].

Note: [purple]all were doing is setting up Form Objects with full referencing to the forms[/purple]. Instead of that long line that would normally occur, we now have a simple object name!

See Ya! . . . . . .

 

[Steven811]

Hi TheAceMan1

Thanks for taking the time to help.

I have changed the syntax to accurately reflect your advice.

The bookmark that I use is in Word and is the fieldname/control on the form. Could you clarify why the bookmark name CompanyName is incorrect?

Thanks

Steven811

 

[TheAceMan1]

Steven811 . . . . .

Sorry about that. I should've said [blue]the Form Reference for setting the bookmark[/blue]. In other words:

[blue]   .Selection.Text = (CStr([purple][b]Forms!frm[/b][/purple].CompanyName))
Shold be:
   .Selection.Text = (CStr([purple][b]frm[/b][/purple].CompanyName))[/blue]

See Ya! . . . . . .