Results 1 to 5 of 5
  1. #1
    johnashworth is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Location
    Houston, Texas
    Posts
    7

    Listbox retreiving wrong record if record number gaps exits in table

    Hello



    This is my first time to post here so please forgive me if I don't exactly get it right. My level of knowledge in Access is novice or a little better, to compare it to a college level course I would equate it to about an "Access 102", maybe 103 so I beg your patience if I don't use the correct terminology.

    The Problem
    I am using a listbox to search for a record and when I click on a record where the record number selected comes after a gap in the recordset (via deleting a record), it displays the next record. I have searched everywhere and cannot find a solution. The correct record is retrieved where the record number selected is before the gap.

    Please let me know what information you need me to provide that would help.

    Thank you very much,


    John A.
    Last edited by johnashworth; 05-27-2016 at 08:51 AM. Reason: Additional information

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    When you click on a record in the listbox, what do you do to execute the search, i.e. what is the VBA code?

    What columns are displayed in the listbox, and what is the bound column setting?

  3. #3
    johnashworth is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Location
    Houston, Texas
    Posts
    7
    Quote Originally Posted by John_G View Post
    When you click on a record in the listbox, what do you do to execute the search, i.e. what is the VBA code?

    What columns are displayed in the listbox, and what is the bound column setting?
    Thank you so much for you quick response.

    This is the On_Click code
    Private Sub lbxSongList_Click()
    Dim intRecNum As Integer

    If Not IsNull(Me.txtSongTitle.Value) Then 'Is SongTitle field empty?
    If IsNull(Me.grpChoirEnsemble.Value) Then ' Has Choir or Ensemble been selected?
    ' Yes, give message and bail
    Msg = "You need to select either choir or ensemble for this song"
    Style = vbOKOnly + vbExclamation
    Response = MsgBox(Msg, Style, Title)
    Me.grpChoirEnsemble.SetFocus
    Exit Sub
    End If
    Else
    If Not IsNull(Me.grpChoirEnsemble.Value) Then ' Has Choir or Ensemble been selected?
    ' Yes, give message and bail
    Msg = "You need to enter a song title first"
    Style = vbOKOnly + vbExclamation
    Response = MsgBox(Msg, Style, Title)
    Me.txtSongTitle.SetFocus
    Exit Sub
    ElseIf Not IsNull(Me.cmbBookTitleAll.Value) Or Not IsNull(Me.cmbBookTitleChoir.Value) Or Not IsNull(Me.cmbBookTitleEnsemble.Value) Or Not IsNull(Me.cmbArtist.Value) _
    Or Not IsNull(Me.cmbPracticeCD.Value) Or Not IsNull(Me.txtBookPage.Value) Or Not IsNull(Me.txtBookCD_Number.Value) Then
    ' Yes, give message and bail
    Msg = "You need to enter a song title first"
    Style = vbOKOnly + vbExclamation
    Response = MsgBox(Msg, Style, Title)
    Me.txtSongTitle.SetFocus
    Exit Sub
    End If
    End If

    intRecNum = Me.lbxSongList.Column(0)
    DoCmd.GoToRecord acDataForm, "frmSongData", acGoTo, intRecNum
    Me.Refresh
    Me.txtSongTitle.SetFocus

    End Sub

    This is the After_Update code

    Private Sub lbxSongList_AfterUpdate()
    Me.Refresh

    End Sub

    The column count is 4 and these are the widths: 0";3.8598";2.0702";2.0702"
    The first column is the ID, "SongID", 2nd column is "SongTitle", 3rd is "BookTitle", and 4th is "Artist"

    The bound column is 1

    This is the SQL for the query:
    SELECT DISTINCTROW tblSongData.SongID, tblSongData.SongTitle, tblSongData.BookTitle, tblSongData.Artist
    FROM tblSongData
    WHERE (((tblSongData.[ChoirEnsemble])>0));

    Thank you,
    John

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Your error is here:

    DoCmd.GoToRecord acDataForm, "frmSongData", acGoTo, intRecNum

    The GoToRecord method treats intRecNum as a Record Number, and not as a value to search for. In other words, if intRecNum = 7, it will go to the 7th record in the form's recordset, which might or might not have SongID = 7.

    Try the SearchForRecord method instead:

    DoCmd.SearchForRecord ,,, "SongID = " & intRecNum

  5. #5
    johnashworth is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Location
    Houston, Texas
    Posts
    7
    That worked! A million thanks!

    You just helped me graduate to Access 104, I think.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-18-2015, 11:28 AM
  2. Delete record button from table using listbox
    By Ruegen in forum Programming
    Replies: 5
    Last Post: 09-08-2014, 09:02 PM
  3. Replies: 4
    Last Post: 07-03-2013, 10:02 AM
  4. Check if record is exits in table
    By adam23262 in forum Access
    Replies: 4
    Last Post: 02-08-2013, 01:00 PM
  5. Replies: 2
    Last Post: 12-21-2012, 01:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums