Solved

vb - Docmd.gotorecord from Doubleclick event of Listbox

Posted on 2014-01-18
15
448 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

770 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