Using the seek command.

[somnophore]

Hiya,
The current problem I am experiencing is in getting a button when clicked to move to another record. However it isn't just next or previous. It is to one that is determined by a field within the current record.

For example, for question (record) 1 if the user clicks yes it will move to record n where n is listed in the yes field of record 1.

Any ideas?

My code so far is but it keeps giving an error.

Private Sub yes_Click()
Set db = CurrentDb
Set rs = db.openrecordset("questions")
rs.Index = "PrimaryKey"
rs.Seek "=", Yes
tbQ.Value = rs!question
End Sub

Any ideas would be great.

ray:

 

[Corticus]

Your question is a little confusing,

for question (record) 1 if the user clicks yes it will move to record n where n is listed in the yes field of record 1

It sounds like your saying the record number is data in the 'yes' field (like this is a number field), but then you say you click it (the 'yes' field), like the yes field is a yes/no field that can be turned on or off.

Could you please clarify?

 

[somnophore]

Ok will try my best to clarify this here but please bare with me.

I have a data table with four fields in. Thes are:

Leaf, the primary key for the records and a simple number.
Question, as its name suggests.
Yes, again a number that is the record to (hopefully go to) when the yes button is clicked on the form.
No, similiar to yes but the record to go to when the no button is clicked on the form.

Does this make it any clearer? I understand how seek can go to a number thats been hard coded in i.e

r.s index="primarykey"
r.s seek "=", 4

Would go to the 4th record in the table. I need it to go to the record indicated by the yes field of the current record.

Hope thats made it clearly to understand

 

[Corticus]

OKay, I think I got it.

I would set your form's recordsource to the referenced table. Then as a criteria for the 'yes' field, enter a reference to the control on your form that will hold this value. You might want to add an expression field to a query and have the form be bound to that. Add a field to the query (this is your expression), and have it do something like: iif(isnull([controlreference],null,[controlreference]). Then for the critiria for this field, enter the reference to the control on your form holding the value of the record to go to, and then have an 'or' of 'is null'. That way, when the form opens, or no record is entered, you see all the records. Finally, have your button that is to take you to the desired record execute a forms.requery command, and that should take you to the desired record.

Hope this helps,