GoToRecord onClick

[Waldin]

DoCmd.GoToRecord , , acGoTo = [TrackID]

i have a hypelinked ComboBox Field and when i select the TrackID (Contents in field) i would like the form that belongs to the TrackID to open, my code returns an error that says "yo cannot go to the specified record" and sometimes opens forms with a track number i did not click on.

 

[VilaRestal]

Is that the code that's supposed to open the other form?

What it should be is something like

If Not IsNull(Me.TrackID) Then DoCmd.OpenForm "Form1", , , "TrackID = " & Me.TrackID

in the TrackID combobox After Update event sub.

However, your phrase "i would like the form that belongs to the TrackID to open" could be interpreted in other ways and there's not enough info to give any more help than that.

 

[Waldin]

Hi VilaRestal, thanks for your Response.

the TrackID is an Autonumber Field so what i done is i created a ComboBox with its record source set to display all TrackID's the database currently contains and ive hyperlinked them so if im in a entry and the TrackID has generated this number 10009 It will be displayed amongst all the others in the ComboBox, then if i scroll down the ComboBox and select TrackID 10002 i would like the current form to close and the form that belongs to TrackID 10002 to open.

is that abit better?

 

[pr2-eugin]

GoTo specified Record Number or other words an Offset value not the ID number.. For example, specially the ID is an auto number .. The Id number might be 10567 but it might be the record number 10.. so if you say GoTo 10567 and the table only has 700 records it will not be able to goto that record number.. So try Vila's method..

 

[Waldin]

Thanks for the code Vila
Thanks for the Insight Eugin

much apreciated.

 

[VilaRestal]

So this is an unbound Combobox (it must be, Access won't let you change the value of an autonumber field). To do exactly what you want:

If the combobox is called ComboBox1 then the code would be

Private Sub ComboBox1_AfterUpdate()
    If Not IsNull(Me.ComboBox1) Then
        'We're going to close this form so must store all values in variables. The objects won't exist after closing
        Dim i As Long
        i = Me.ComboBox1
        Dim s As String
        s = Me.Name
        DoCmd.Close acForm, s
        DoCmd.OpenForm s, , , "TrackID = " & i
        Forms(s)!ComboBox1 = i 'So the 'new' ComboBox1 has the same value as the 'old'
    End If
End Sub

However, I would suggest the better way to do it is to put the combobox in the form header or footer and change the filter of the existing form. Closing and reopening the same form is a pretty horrible way to do it.

 

[John Big Booty]

Rather than the GotoRecord method which will take you to a record such as the First, Next, Last, Second or Xth record you need to use the FindRecord method which will find the specific record you are after;

Me.FieldName.SetFocus [COLOR="SeaGreen"]' FieldName is the name of the field you wish to search[/COLOR]
DoCmd.FindRecord [TrackID]

 

[Waldin]

hi John Thanks Man, the first code you gave me was a perfect fit, what does your last reply refer to.