Move to next record with where condition

[tagteam]

I have a form where the user selects and invoice number from a combo box. The combo box is filtered to show them only the records that have not been archived (using a date field).



The user wants a button to go to the next record. That seems pretty easy, even with the built in query wizard BUT i need it to go to the next record that has not ben archived.

For example. They may be on an invoice with an InvoiceID of 3695 and when they hit next they would need to go to the next record id where the archive date in that record is blank, so move to record 4178 instead of 3697.

I am sure there is a way to do this with some kind of query to get a value for the next available record but I am not sure. I looked at a few options like DoCmd.GoToRecord but not sure if i can do that since the form is based on a query not the table.

If someone can just point me in the right direction I can try to figure it out but not sure where to start.
Thanks

[Welshgasman]

You appear to be asking pretty much the same as this thread, except he is using a listbox.
https://www.accessforums.net/showthread.php?t=87242

[tagteam]

yes, I tried that code and there must be something different about a list box and a combo box because that does not seem to work. I don't get any errors or anything, it just doesnt do anything

[tagteam]

So i searched a bit and found some code that should move to next item in combo box

Dim IntRow As Integer
IntRow = Me!cbMoveTo.ListIndex
If IntRow <> -1 Then
      If IntRow = Me!cbMoveTo.ListCount - 1 Then
         Me!cbMoveTo.Value = Me!cbMoveTo.Column(0, IntRow - 1)
      Else
         Me!cbMoveTo.Value = Me!cbMoveTo.Column(0, IntRow + 1)
      End If
   End If

However, all that does is clear out the option in the combo box and doesnt move to any other records.

[Welshgasman]

Perhaps look here?
https://www.google.com/search?q=vba+...hrome&ie=UTF-8

[tagteam]

I have this code working a little

cbMoveTo.SetFocusIf cbMoveTo.ListIndex <> cbMoveTo.ListCount - 1 Then
      cbMoveTo.ListIndex = cbMoveTo.ListIndex + 1
   Else
      cbMoveTo.ListIndex = 0
Call cbMoveTo_AfterUpdate
End If

It works to move the record, however, after it does I get the error: run-time error 7777 - You've used the ListIndex property incorrectly

i have tried a couple variations on this code and they all give me that 7777 error apparently because in access the ListIndex is a readonly value

[Gicu]

How do you define "next"? Meaning how is your combo's row source sorted? Use that (the row source query of the combo) to get you the next invoice number. You can use a domain aggregate (dLookup,dMin, etc.) or a Top 1 query sorted on a unique ID that identifies the records (using greater that the current one).
To go to that record you can use the form\recordset bookmark method (https://learn.microsoft.com/en-us/of....form.bookmark) or easier the
Docmd.FindRecord (make sure you set the focus first on the control holding the value you're looking for) -https://learn.microsoft.com/en-us/office/vba/api/access.docmd.findrecord

Cheers,

[Micron]

I'm not understanding why the form isn't just loaded with records that are not archived and you just use the built in navigation controls to move amongst the records - thus no or very little code.

[CarlettoFed]

Private Sub cmdMoveTo_Click()
    Me.cboMoveTo = Me.cboMoveTo.ItemData(Me.cboMoveTo.ListIndex + 1)
End Sub

[Gicu]

You would still need to call the AfterUpdate event of the combo as it will not fire if updated programmatically.

Cheers,

[tagteam]

the filter Idea might work. I need to rework some of the form and subform a bit and will give it a try