GotoRecord 1 and 2 mixup

[stevenblanc]

[SOLVED] GotoRecord 1 and 2 mixup

Hi there folks,

Little trouble here. Probably a simple thing that I'm overlooking.

I was using the following line of code in the double-click event of a list box to allow users to navigate a form to a particular record:

 DoCmd.GoToRecord acDataForm, "frmCustomerRecord", lstCustomers

However, I found that the records were being mixed up. So I replaced the code with:

DoCmd.GoToRecord acDataForm, "frmCustomerRecord", 1

and the event navigated to the second record. So I tried:

DoCmd.GoToRecord acDataForm, "frmCustomerRecord", 2

and the event navigated to the first record. I can't for the life of me understand what the issue is. There are no filters on the form.

Anyone encountered this before?

Cheers,


Steven

 

[Beetle]

It's doing exactly what you told it to do. The third argument of the GoToRecord method determines which record you want to move to and it is defined by an enumerated list of constants;

[B][U]Name[/U][/B]          [B][U]Value[/U][/B]
acPrevious     0
acNext         1
acFirst        2
acLast         3
acGoTo         4
acNewRec       5

So this;

DoCmd.GoToRecord acDataForm, "frmCustomerRecord", 2

will always cause a move to the first record, because 2 is the value of the acFirst constant. Likewise, this;

DoCmd.GoToRecord acDataForm, "frmCustomerRecord", 1

will always cause a move to the next record, whatever the "next" record is (which is going to be dependent on what the current record is). And this;

DoCmd.GoToRecord acDataForm, "frmCustomerRecord", lstCustomers

is going to cause unpredictable results, depending on what the value of the list box is.

The GoToRecord method is likely not what you want to be using here, but we would need more details on what exactly you're trying to accomplish in order to give you advice as to how to proceed (i.e. are you trying to move to a record on the same form that the list box is on, are you trying to open a separate form to a certain record, etc.)

 

[stevenblanc]

Ah. Thank you for that. Makes perfect sense.

He list box contains the customer Id(hidden) and the customer name. When the user double clicks the record I want to open another form and navigate to the correct record. As I do not expect to filter this list the customer id and the customer Id is an auto number field the Id and the record number will be the same.

I'm not sure if this is the optimal solution however. I considered using where to isolate the record but this would remove the ability to navigate between records on the sun form. Which is one option.

Thanks again for the quick and thorough reply.

I accidentally cut off a bit of the code, it was actually originally:

DoCmd.GoToRecord acDataForm, "frmCustomerRecord", lstCustomers + 1[\CODE]

I could allow for sorting of the customer listbox by using the CustomerID:

[CODE] lstCustomers.Column(1) [CODE]

 

[Solo712]

Ah. Thank you for that. Makes perfect sense.

He list box contains the customer Id(hidden) and the customer name. When the user double clicks the record I want to open another form and navigate to the correct record. As I do not expect to filter this list the customer id and the customer Id is an auto number field the Id and the record number will be the same.

I'm not sure if this is the optimal solution however. I considered using where to isolate the record but this would remove the ability to navigate between records on the sun form. Which is one option.

Thanks again for the quick and thorough reply.

I accidentally cut off a bit of the code, it was actually originally:

DoCmd.GoToRecord acDataForm, "frmCustomerRecord", lstCustomers + 1

I could allow for sorting of the customer listbox by using the CustomerID:

 lstCustomers.Column(1)

It seems your best bet would be to pass the value of lstCustomers.Column(1) (convert to a CLng if it is autonumber!) to the Form you want to open as an OpenArgs argument. Inside the Form Open Event execute the FindRecord command in analogy to the example in the linked document. Should be easy to do.

Best,
Jiri

 

[stevenblanc]

Thanks folks,

I solved the matter by moving through the record set as follows:

    With Forms!frmCustomerRecord.Recordset
        .FindFirst "CUST_ID = " & lstCustomers.Column(0)
        If .NoMatch Then MsgBox "The selected record no longer exists."
    End With

I also set the listbox to requery on current in an attempt to minimize errors caused by records being deleted while the customer list is still open.

Cheers.