DoCmd.??? 1

[KCcasey]

Firstly, thanks for time to look at this. I'm painfully aware of my VBA noobness right now so its much appreciated.

Now for some context. I have a main form with multiple pages. The first page has search functionality, the search results are displayed in a subform.

The functionality I am trying to implement is when a record in the subform (search results) is d/clicked:

A) Current record on the main form will be changed to be the record selected in the subform
B) The main form focus will switch to a different page of the main form (eg. mainform search page > mainform detail page)

Pretty basic huh?

Well I'm having a nightmare trying to figure out A).

Here's what I've got so far:

'Main-form has a field called contactID
'Sub-form has a field called contactID

'Need to update main-form contactID = sub-form contactID

Function OpenRecordForEditing()
On Error GoTo ProcError

Dim varRecord As Variant

If Not IsNull([ContactID]) Then
    
    'SWITCH TO THE DETAIL PAGE
    Forms!Contacts.Controls!TabControl_Contacts.Value = 1
    Forms!Contacts!FirstName.SetFocus
          
    'FIND THE RECORD
    'DoCmd.FindRecord ContactID, acAnywhere, , acSearchAll, , , True
    'DoCmd.FindRecord Forms!ContactsSubform.ContactID, acAnywhere, , acSearchAll, , , True
    
End If

ExitProc:
   Exit Function
ProcError:
   MsgBox "Error " & Err.Number & ": " & Err.Description, _
           vbCritical, "Error in OpenRecordForEditing event procedure..."
   Resume ExitProc
End Function

Any ideas wise ones?

 

[PWise]

how about something like

Dim RS As Object

    Set RS = Me.parent.Recordset.Clone
    RS.Find "[ContactID] = " & str(Nz(Me![ContactID], 0))
    If Not RS.EOF Then
        Me.parent.Bookmark = RS.Bookmark
    Else
       MsgBox "contat Not in Selected Term!", vbInformation, CurrentProject.Properties("apptitle")
    End If

 

[KCcasey]

Thanks for your reply Pwise.

I slapped your code into the function, and which generated the error: 'Error -2147...: The expression you enteredhas a field, control, or property name that MS Office Access can't find'. Hmm.

So then I tried to simply return the fields value using

MsgBox "ContactID=" & Forms!ContactsSubform.Form.ContactID

. This generated the error Error 2450: MS Office Access can't find the form 'ContactsSubform' referred to in a macro expression or Visual Basic code.

What on earth can I be doing wrong? It must be some fundamental requirement that I don't know about(?)

 

[KCcasey]

Hi All,

Sorted my issues out. Firstly, the recordsource of my subform had mysteriously changed - the field I wanted wasn't being return.... bizarre.

With that fixed, made some minor corrections (in red) to Pwise's suggestion, to end with this:

Function OpenRecordForEditing()
On Error GoTo ProcError

Dim varRecord As Variant
[COLOR=blue]Dim RS As [COLOR=red]dao.Recordset[/color][/color]

'If Not IsNull([ContactID]) Then
    
    'SWITCH TO THE DETAIL PAGE
    Forms!Contacts.Controls!TabControl_Contacts.Value = 1
    Forms!Contacts!FirstName.SetFocus

    'SWITCH CURRENT RECORD ON MAIN FORM
    [COLOR=blue]Set RS = Me.Parent.Recordset.Clone
    [COLOR=red]RS.FindFirst[/color] "[ContactID] = " & Str(Nz(Me!ContactID, 0))
    If Not RS.EOF Then
        Me.Parent.Bookmark = RS.Bookmark
    Else
       MsgBox "contat Not in Selected Term!", vbInformation, CurrentProject.Properties("apptitle")
    End If[/color]

End If

ExitProc:
   Exit Function
ProcError:
   MsgBox "Error " & Err.Number & ": " & Err.Description, _
           vbCritical, "Error in OpenRecordForEditing event procedure..."
   Resume ExitProc
End Function

Thanks Pwise!

 

[PHV]

Error 2450: MS Office Access can't find the form 'ContactsSubform'

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886