Open form to a certain record

[pangeh@hotmail.co.uk]

Hi,

I am using access 2003 and have a form which contains a subform showing a datasheet of a series of records.

The form is like a switchboard with buttons and when you select a certain record on the datasheet and press a button it will open up another form (to edit records, delete, view etc) but with that record selected.

Normally I have previously used vba in the onclick event on a button with code that does this and it alsways works but this time it doesn't.

My code is as below:

Private Sub btn_edit_stdnt_recrd_Click()

Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "frm_edit_Student_Record"

stlinkcriteria = "Student_ID = " & subfrm_student_records_tblview![Student ID]

DoCmd.OpenForm stdocname, , , stlinkcriteria

'MsgBox stlinkcriteria

End Sub

THe problem that is occurring is that when I press the button it asks for a parameter for Student_ID which is the control source on the edit record form I want to open. When I type one in it just opens up on the first record even if I put in a different student number.

I also tried using a static value in stlink criteria but got the same problem.

I used a msgbox to test that stlinkcriteria was actually picking up the right student id from the subform and it was. (i.e. it shows the correct student number of the record selected in the subform).

THe edit student record form has a query linking two tables for its data source. I can manually switch from record to record if I open the form separately and have no problem performing/saving edits.

Any assistance is greatly appreciated.

Many thanks
Pangeh

 

[Galaxiom]

References to controls on subforms require the Form property because, unlike form objects, there is no default property for a subform control.

Also note that subfrm_student_records_tblview must be the name of the subformcontrol rather than the name of the form object that is the Source Object of the subformcontrol.

Me!subfrm_student_records_tblview.Form![Student ID]

Also best to include Me! so Access doesn't have to guess.

 

[pangeh@hotmail.co.uk]

Hi Galaxiom,

Thanks for your help and reply.

I tried what you said but get the same problem.

For some reason when I press the button I get message popping up titled 'Enter Parameter Value' it asks me to enter a value for Student_ID. Its as if the edit record form is not accepting the parameter from the stlinkcriteria string.

To test this I tried using a static value as below and got exactly the same problem. I've tried to have a look at the edit form itself to see if there was anything obvious that would stop the parameter being passed but did not notice anything.

This is the code I used with the static value (this was to make sure that the issue did not lie with the subform):

Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "frm_edit_Student_Record"

stlinkcriteria = "Student_ID = 3"

DoCmd.OpenForm stdocname, , , stlinkcriteria

So even when I want it to choose record 3 for example I get the same problem. Is there any property value or table association that could intefere with this process?

Thanks

 

[pangeh@hotmail.co.uk]

Hi Galaxiom,

Thanks for all of your help. I clearly didn't fully understand your post. After a bit of testing I found the issue was with the way I was referencing the field control on the edit record form. There was some ambiguity in my referencing.

Thanks again for your explanation and time. THis issue is now resolved.