?
Solved

vb - Docmd.gotorecord from Doubleclick event of Listbox

Posted on 2014-01-18
15
Medium Priority
?
509 Views
Last Modified: 2014-01-19
Hello experts

Having trouble getting my form to goto a particular record if I've double clicked a listbox item.

I've tried

Dim a as long
a = Me.lstExistingRecs.Column(0) ' RecID

DoCmd.GoToRecord acDataForm, "tfrm_Records",a


Nothing happens???
0
Comment
Question by:wlwebb
  • 8
  • 7
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791318
try this

Dim a as long
a = Me.lstExistingRecs.Column(0) ' RecID

with me.recordsetclone
       .findfirst "[RecId]= " & a
        if not .nomatch then
             me.bookmark=.bookmark
             else
             msgbox "record not found"
        end if
end with
0
 

Author Comment

by:wlwebb
ID: 39791340
I'm getting the "Record not found"

stepped through it.  everything looks right.......

When I unlock the recid field and click in it to set my focus there and hit Page up or page down it isn't cycling through records.  
the form's Cycle property is set to "All Records"

????????
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791346
upload a copy of your db
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:wlwebb
ID: 39791366
It's Form "tfrm_Records" and it is the Listbox on that form right hand side.  On it's doubleclick_event it is supposed to go to the record I've double clicked.
NEW-CHouseRecords.accdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791375
try this

Dim a as long
a = Me.lstExistingRecs.Column(0) ' RecID

with me.PARENT.recordsetclone
       .findfirst "[RecId]= " & a
        if not .nomatch then
             me.bookmark=.bookmark
             else
             msgbox "record not found"
        end if
end with


I can't test your db, because you did not include the backend tables.
0
 

Author Comment

by:wlwebb
ID: 39791377
How would I include the backend tables???????????
0
 

Author Comment

by:wlwebb
ID: 39791380
PS...adding "Parent" gives me
Run-time error '2452'
invalid reference to the Parent property.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791395
upload  the NEW CHouseRecords_be.accdb

try this
replace

with me.PARENT.recordsetclone

with this

with Forms!tfrm_Records.recordsetclone
0
 

Author Comment

by:wlwebb
ID: 39791399
Here are the back end table as its own attachment
NEW-CHouseRecords-be.accdb
0
 

Author Comment

by:wlwebb
ID: 39791402
Same.......  "No records found"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791422
there are a lot of codes in the double click event of the listbox, try commenting them first to test like this


Private Sub lstExistingRecs_DblClick(Cancel As Integer)
'Dim a As Long, b As Long, b1 As Long, c As Long, d As Long, e As Long, f As Long, g As Long, h As Long, i As Long
'Dim j As Date, k As Date
'Dim strBk As String, strPg As String
'Dim LResponse As Integer, LResponse2 As Integer
'
'a = Me.lstExistingRecs.Column(0) ' RecID
'b = Me.lstExistingRecs.Column(1) ' CountyCodeID
'b1 = Me.lstExistingRecs.Column(17) ' FIPSStateCodeID
'c = Me.lstExistingRecs.Column(2) ' ReckBkTypeID
'
'd = Me.lstExistingRecs.Column(3) ' RecBkNbr
'e = Me.lstExistingRecs.Column(4) ' RecBkSubNbrID
'strBk = d & "*|*" & e
'
'f = Me.lstExistingRecs.Column(6) ' RecBkPage
'g = Me.lstExistingRecs.Column(7) ' RecBkPageSubID
'strPg = f & "*|*" & g
'
'If Nz(Me.lstExistingRecs.Column(15), "") <> "" Then
'    h = Me.lstExistingRecs.Column(15) ' RecDateID
'    k = Me.lstExistingRecs.Column(16) ' RecDate - Actual Date
'    Me.txtRecDate = k
'Else
'    Me.txtRecDate = Nothing
'End If
'
'If Nz(Me.lstExistingRecs.Column(10), "") <> "" Then
'    i = Me.lstExistingRecs.Column(10) ' RecDatesID
'    j = Me.lstExistingRecs.Column(11) ' RecRecordingDate - Actual Date
'    Me.txtRecordingDate = j
'Else
'    Me.txtRecordingDate = Nothing
'End If
'
'    Select Case Me.TabCtlRecords.Value
'        Case 0 ' Select an Existing Record for entire form
'            With Me.RecordsetClone
'                    .FindFirst "[RecId]= " & a
'                    If Not .NoMatch Then
'                    Me.Bookmark = .Bookmark
'                    Me.cboSelectState = b1
'                    Me.cboSelectCounty = b
'                    Me.cboSelectBkType = c
'                    Me.cboSelectBkNbrSubBkNbr = strBk
'                    Me.cboSelectPgPgSub = strPg
'                Else
'                    MsgBox "Record Not Found!", vbOKOnly
'                End If
'            End With
'        Case 5
'            Forms!tfrm_Records.Form![sfrmtbl_Records_SelectPrior].Form![cboSelectPriorRec] = a
'        Case 6 ' Prior Record Setup Tab
'            LResponse = MsgBox("You have the Prior Record Setup Page open.  Are you selecting an Existing Record for the 'Temp Prior Record' Setup?" & vbNewLine & vbNewLine & "The Temp Prior Record tab and 'Prior Record Setup' Form are used when a Prior Record has not already been defined in the System." & vbNewLine & vbNewLine & "If you select 'Yes' you can select the Existing Record but you could be duplicating data already in the system." & vbNewLine & vbNewLine & "Select 'No' to cancel.", vbYesNo + vbExclamation, "WARNING - Possible Duplicate Record!")
'                If LResponse = vbYes Then
'                    LResponse2 = MsgBox("Are you sure you want to select an Existing Record for the Temporary Prior Record setup?.  This process can not be undone!", vbYesNo + vbExclamation, "Confirm Existing Record for Temp Prior Record Setup.")
'                    If LResponse2 = vbYes Then
'                        With Forms!tfrm_Records.Form![sfrmTmptbl_Records_PriorRecSetup].Form
'                        !cboSelectState = b1
'                        !cboSelectCounty = b
'                        !cboSelectBkType = c
'                        !txtRecDate = k
'                        !txtRecordingDate = j
'                        !cboSelectBkNbrSubBkNbr = strBk
'                        !cboSelectPgPgSub = strPg
'                        End With
'                    End If
'                Else
'                End If
'    End Select
'

Dim a As Long
a = Me.lstExistingRecs.Column(0) ' RecID

With Forms!tfrm_Records.RecordsetClone
       .FindFirst "[RecId]= " & a
        If Not .NoMatch Then
             Me.Bookmark = .Bookmark
             Else
             MsgBox "record not found"
        End If
End With
End Sub

Open in new window

0
 

Author Comment

by:wlwebb
ID: 39791426
Same result
Record not found
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39791430
that code is working here...
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39791517
make sure that the form tfrm_Records "Data Entry" property is set to NO
0
 

Author Closing Comment

by:wlwebb
ID: 39791964
Something so simple can cause hours of frustration.!!!  That was it
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question