OpenForm WhereCondition & ADP

[Allen Davidson]

Im having some strange behaviour with the OpenForm method now my mdb is
upsized to adp.

I have a continuous form to display a list of records, the user selects a
record and clicks an edit button. The code behinf the edit button is below;

Private Sub cmdEdit_Click()
Dim strWhereCondition As String
strWhereCondition = " ID = " + CStr(Me.sfrmMainRespondentList.Form.ID)
DoCmd.OpenForm "frmRespondent", acNormal, , strWhereCondition,
acFormEdit, acWindowNormal
Call cmdClearSearch_Click
End Sub

All work fine. The second form is opened and displays the record to edit.

If without closing the second form I go back to the list form and choose
another record and click edit then the second form (already open) displays
that record.

Now I upsize to adp.


Choosing a record from tthe list form and clicking edit brings up the second
form with the correct record. Going back to the list choosing a different
record and clicking edit brings up the second form but with no data - it
looks like it has created an empty new record.

The form (second form) record source is set to a query (now a view) in esign
view.
The RecordSource property reads 'qryRespondent' (without the quotes.

Any ideas?

 

[Guy]

Allen,



As no one else has yet responded to your posting I feel obliged to.



I have been developing ADP's since A2000 was introduced (7/8 years?) and
whilst I can't remember experiencing the problem you are having, I may have.
Like you it was as a result of upsizing a large MDB application.



It sounds as though you no what you are doing. Glad you made the detail form
a view otherwise you can't use the Form Open method with a Where clause
(lesson 1). Anyway can't help but feel the reason for your problem is based
around the server filter which is applied, and therefore I would suggest
closing the form before you reopen it with the subsequent record. If this
doesn't work then try below.



In the presentation of my forms I use a similar approach to yourself. A list
form from which the user can drill down on to get the detail. It is the only
way to go to minimise traffic across the network and maximise performance.



For each drill down I use the routine documented below. Usage is a follows
and allows for a mouse double click, or key enter event on the field
concerned (note. I have modified the event names to make it obvious):



Private Sub txtPrimayKeyId_DblClick(Cancel As Integer)

Dim blnOk As Boolean

' Drill down to asset detail

blnOk = gfnFindRecord("frmDetails", "Id = " & Me!txtPrimaryKeyId)

End Sub



Private Sub txtPrimaryKeyId_KeyPress(KeyAscii As Integer)

If KeyAscii = 13 Then txtPrimaryKeyId_DblClick 0

End Sub



Hope this helps

Guy



Public Function gfnFindRecord(ByVal strForm As String, ByVal strWhere As
String) As Boolean

'*******************************************************************************

' Populate a form with a subset of records using a server filter.

'

' strForm [in] Name of the form to populate

' strWhere [in] SQL WHERE clause without the where keyword such as

' "Country = 'AUSTRALIA'"

'

' Return Value: Boolean

' True = Success

' False = Failure

'

' If the form is already open, and the current record has been modified, the

' user will be prompted to save the record, before the form closed/opened
and

' populated.

'

' The function is used frequently for form On_Double_Click - drill down

' and current form record refresh events (because the form requery/refresh

' methods don't work if the current form datamode is acFormAdd due to the

' server filters being disabled).

'

' After calling this routine the "Me" property of the form will be invalid

' within the context of the calling subroutine or function because the form

' has been closed/re-opened. Therefore, if required, dimension a form
variable

' and set using the forms collection eg.

'

' Dim frm As Form

' Set frm = Forms("name of form")

'

' Called By: Most forms

'*******************************************************************************

Dim frm As Form

Dim varOk As Variant

On Error GoTo gfnFindRecord_Error



DoCmd.Hourglass True

' Set status bar text

varOk = SysCmd(acSysCmdSetStatus, "Searching...")

DoCmd.Echo False

' Form open?

If gfnIsFormOpen(strForm) Then

Set frm = Application.Forms(strForm)

' Editing a record?

If frm.Dirty Then

DoCmd.Echo True

frm.SetFocus

' Save changes?

If MsgBox("Do you want to save changes to the current record?",
vbExclamation + vbYesNo, "Save Confirmation") = vbYes Then

' Save

DoCmd.RunCommand acCmdSaveRecord

Else

' Undo

DoCmd.RunCommand acCmdUndo

End If

DoCmd.Echo False

End If

' Close

DoCmd.Close acForm, strForm

End If

' Open form with server filter

DoCmd.OpenForm strForm, , , strWhere



gfnFindRecord = True



gfnFindRecord_Exit:

DoCmd.Echo True

DoCmd.Hourglass False

varOk = SysCmd(acSysCmdSetStatus, " ")

Exit Function



gfnFindRecord_Error:

Select Case Err.Number

Case 30025 ' Invalid Where clause

Case Else

MsgBox str(Err.Number) & " - " & Err.Description, vbCritical +
vbOKOnly, "Unexpected Error - gfnFindRecord"

End Select

gfnFindRecord = False

Resume gfnFindRecord_Exit

End Function

 

[Guy]

Allen,

I don't generally follow this newsgroup. The
microsoft.public.access.adp.sqlserver newsgroup which I watch more reguarly
may provide you with a quicker response or direct solution to the problems
you are experiencing.

All I can say at this stage without knowing your background (other than from
MDB to ADP) is think client/server not file/server.

Cheers
Guy

 

[Allen Davidson]

Hi Guy,

Thanks for the reply - I thought everybody had left!

I'll have to work throug your code and understand it.

Crazy that it is happening !

Regards
Allen

 

[Allen Davidson]

Thanks Guy - went with a modal form (time is tight so needed a simple fix)
have posted in microsoft.public.access.adp.sqlserver

Allen