ACCESS FEED: RE: [MS_AccessPros] coding problem

Senin, 12 Agustus 2013

RE: [MS_AccessPros] coding problem

Hi John,

I'm using 2010.

I don't have anything that should be firing the event before the combo is
actually updated.

Thanks for taking a look. I will post if and when I get it solved.

Glenn

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Monday, August 12, 2013 10:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] coding problem

Glenn-

You don't say what version of Access you're using. In 2007, I modified the
Order Details form and added a combo box with a Row Source from the
Customers table. I declared a Form variable at the module level and did
this:

Private Sub Combo95_AfterUpdate()
Set frm = Me.sbfOrderDetails.Form
Debug.Print frm.Name
End Sub

Works fine in the Details section. I then moved the combo box to the form
header and tried it again. Same OK result.

Is there any code anywhere that's firing the AfterUpdate event in the combo?
The only thing that comes to mind is if something is firing the event while
the form is loading. If the combo is in the form header, the subform might
not be there yet when the event fires. In the detail section, the subform
is probably there at the right time.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
Lloyd
Sent: Monday, August 12, 2013 3:04 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] coding problem

Hi John

Yes Questionaire is a Form object - member of clsSurvey

Here is the entire After Update Handler

Const cstrProcedure = "cboSurveySelect_AfterUpdate"

Dim cntr As Integer

Dim ctrl As Control

Dim intSectionCount As Integer

Dim intCounter As Integer

Dim strsql As String

Dim rs As DAO.Recordset

10 On Error GoTo HandleError

'

20 Me.fsbSurvey_Details.SourceObject = "frmSurvey__Details"

30 Me.ScrollBars = 2

'CurrSurvey is a module level clsSurvey object that is instantiated in the
form's load event

40 With CurrSurvey

50 .ID = Me.cboSurveySelect.Column(0)

'how many sections?

60 intSectionCount = .calcSurveySegments

70 Set .Manager = Me 'main form

'set single or multipart subform

'60 Set .QuestionaireControl = Me.fsbSurvey_Details
'questionaire subform control

80 If intSectionCount = 1 Then

90 Me.TabMultiPart.Visible = False

100 Else

'set up for the first survey segment

'get tab names

110 strsql = "SELECT DISTINCT tblSurveys.SurveyID,"

120 strsql = strsql & "tblSurveys.SurveyName,"

130 strsql = strsql & "tblSurveySections.SurveySectionPage AS Page,"

140 strsql = strsql & "tblSurveySections.SurveySectionName AS Title"

150 strsql = strsql & " FROM (tblSurveys"

160 strsql = strsql & " INNER JOIN tblSurveyQuestions"

170 strsql = strsql & _

" ON tblSurveys.SurveyID = tblSurveyQuestions.SurveyID)"

180 strsql = strsql & " INNER JOIN tblSurveySections"

190 strsql = strsql & _

" ON tblSurveys.SurveyID = tblSurveySections.SurveyID"

200 strsql = strsql & " WHERE (((tblSurveys.SurveyID)=" & .ID & "))"

210 strsql = strsql & " ORDER BY
tblSurveySections.SurveySectionPage;"

220 Set rs = CurrentDb.OpenRecordset(strsql)

230 With Me.TabMultiPart

240 .Visible = True

250 rs.MoveLast

260 rs.MoveFirst

270 Do While Not rs.EOF

280 .Pages(rs!Page - 1).Caption = rs!Title

290 intCounter = intCounter + 1

300 rs.MoveNext

310 Loop

320 rs.Close

330 Set rs = Nothing

340 End With

'250 Set .QuestionaireControl = Me.fsbSurvey_DetailsPt1

350 End If

'error at 360 if combo is in the form header but executes normally if it is
in detail section

360 Set .Questionaire = Me.fsbSurvey_Details.Form 'questions and
options form

370 .Questionaire.Detail.Height = 0

380 Set .QuestionaireControls = New Collection 'collection of
question controls

390 Set .Questions = New Collection 'survey questions

400 Set .Responses = New Collection 'available responses
for a question

410 .OffsetTop = 0 'top for first control

'identify survey, related participant and respondent

420 .RespondentID = Me.txtRespondent

430 .ParticipantID = Me.txtParticipant

'get the questions for this survey

440 If intSectionCount = 1 Then

'set up the form

450 Set .QuestionaireControl = Me.fsbSurvey_Details

460 .SetInitialFormSpecs

470 .QuestionsRead

480 .Display

490 Else

500 For intCounter = 1 To intSectionCount

'set up the form

510 Set .QuestionaireControl = Me.Controls("fsbSurvey_DetailsPt"
& intCounter)

'520 If intCounter = 1 Then

530 .SetInitialFormSpecs

'540 End If

'read section questions

550 .QuestionsRead CByte(intCounter)

'calculate offset to get correct control numbers for
subforms

560 .qControlOffset = .Questions(1).QuestionNum - 1

'display the questions and response controls

'Set .Questionaire = Me.Controls("fsbSurvey_DetailsPt" &
intCounter).Form

570 .Display

580 Next

590 End If

'600 .Display

610 End With

HandleExit:

620 Exit Sub

HandleError:

630 ErrorHandle Err, Erl(), cstrModule & "." & cstrProcedure

640 Resume HandleExit

End Sub

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Monday, August 12, 2013 8:43 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] coding problem

Glenn-

What is Questionaire - a Form object?

If it works in the Detail section, it should work in the form header. Can
you post the entire bit of code?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
Lloyd
Sent: Monday, August 12, 2013 2:20 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] coding problem

This code:

360 Set .Questionaire = Me.fsbSurvey_Details.Form

When executed from a combo after update handler in the form's details
section executes correctly.

However, if the combo is located in the form header, it generates a run-time
error, "You entered an expression that has an invalid reference to the
property Form/Report. fsbSurvey_Details is a subform control in the form's
detail section. I have tried to reference the Form through the controls
collection me.controls("fsbSurvey_Details").Form but that generates the same
error.

Can anybody suggest where I am going wrong and how I can reference the
subform's foru?

Glenn Lloyd

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]