Viewing specific records on form

[uplink600]

I have created a form on which the user enters or ammends records in the main table of the database. One of the fields displayed on the form is the record number which is an AutoNumber and the primary key of the table. This
is updated when a new record is created and can't be edited on the form.

When a record needs editing, I need the user to be able to go straight to the required record when the form is opened as opposed to navigating through all the records using the "next record" command button I created. The record number text box is bound to the AutoNumber field of the table.

Can anyone advise an easy way to do this.

Thanks

VC

 

[Brianwarnock]

Your open form button needs to execute code to open the form at the desired record the stLinkCriteria something like
stLinkCriteria = "[tableID]=" & Me![Text1]

text1 is where the record number required is entered and tableId the field name in the table.

 

[uplink600]

Brianwarnock

Thanks for your suggestion, I tried this using a new text box on my from and a command button, the following code to run the the cmdGoto click event.

Private Sub cmdGoto_Click()

stLinkCriteria = "[Record Number] = " & Me![txtGoto}

End Sub

Is this correct as I couldn't seem to get it working.

Please advise

Thanks

Vere

 

[Brianwarnock]

Sorry about the delay un replying I don't work Fridays You sub needs to open the form

stDocName = "formname"
stLinkCriteria = "[Record Number] = " & Me![txtGoto]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Suggest you read openform in VBA help to understand better reather than just copying the above and moving on.

Brian

 

[uplink600]

Thanks Brian

I see what you mean but I need the user to be able to go to specific records
when the form is open, not when the form opens. So I have created on my form a text box (txtGoto) where the user enters the required record number and a command button (cmdGoto)to go to the record.

I tried the following code on the cmdGoto click event but the results are unpredictable, sometimes the correct record is displayed and at other times
an incorrect record is displayed. I'm really not sure about this code (usual trial & error).

Private Sub cmdGoto_Click()

txtGoto.SetFocus

Dim Entry As Long

Entry = txtGoto.Text

DoCmd.GoToRecord , , acGoTo, [Entry]

txtGoto.SetFocus

Entry = 0

End Sub

I enclose a screen shot of my form, hope you can advise me.

Thanks

VC

 

[Brianwarnock]

Hi

GoToRecord goes to a record at an offset from a given starting point, not what you require.
I,ve had a look in ACCESS help and tho I've not done this try FindRecord
eg

 stsch = Me.txtGoto  
      Me.autonumfldname.SetFocus
       DoCmd.FindRecord stsch
    Me.nameanotherfieldonform.SetFocus

 

[uplink600]

Works fine, thank you for your help.

VC

 

[Brianwarnock]

Happy to help, thanks for letting me know it worked

Brian