Can this be done?

[SF]

Hi

I have a table consist of the follwing fields

SurveyID long PK
SurveyQuestion Text
Answers Number (1 for Yes; 2 for No)
GotoControl Integer

I have all the field on a form. I want to be able to navigate to the
specific record depending on the Answer (eg if Yes to go value mention in
GotoControl (SurveyID =9), No go to SurveyID =25)

I have 4 buttons for navigating thruogh record but I don't know how to code.

I would appreciate some advice here.

SF

 

[strive4peace]

Hi SF,

So is GotoControl actually a reference to the primary key of another
record that you want to move to?

If so, how are you distinguishing the Yes ID and the No ID ?

When you say that you want to move, would this be once the record is
updated? Or while you are looking through records?

"I don't know how to code"

any advice we give you will probably involve code -- to begin to prepare
yourself to understand, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training


*
have an awesome day
*

 

[SF]

Hi SF,

So is GotoControl actually a reference to the primary key of another
record that you want to move to?
Yes


If so, how are you distinguishing the Yes ID and the No ID ?

Yes, I put the Record Number that I want to go...

When you say that you want to move, would this be once the record is
updated? Or while you are looking through records?

It is mostly thru combobox, once the answer is selected the GotoControl will
be fill in and If I click Next I would be able to go to record number
specify in the GotoControl, if GotoControl is blan proceed with subsequence
record

 

[strive4peace]

Hi SF,

if you change a record, you cannot actually move to another one until
the change is committed, so you would need to do something like this on
the AfterUpdate event of the Answer Number control:

'~~~~~~~~~~~~~~~~~~~~~
'your code to fill GotoControl

'save record
me.dirty = false
'~~~~~~~~~~~~~~~~~~~~~

then, on the form AfterUpdate event:

'~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_AfterUpdate()
If Nz(Me.GotoControl, 0) > 0 Then

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.GotoControl

'find the first value that matches
Me.RecordsetClone.FindFirst "SurveyID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
GoToNextRecord
End If
Else
GoToNextRecord
End If

End Sub
'~~~~~~~~~~~~~~~~~~~~~

also put this in the code behind the form:

'~~~~~~~~~~~~~~~~~~~~~
Private Sub GoToNextRecord()
On Error GoTo Proc_Err
With Me.RecordsetClone
.MoveLast
If StrComp(.Bookmark, Me.Bookmark, 0) = 0 Then
MsgBox "This is the last record"
Else
Me.Recordset.MoveNext
End If
End With

Proc_Exit:
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " GoToNextRecord : " & Me.Name

Resume Proc_Exit
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~

I would not, however, recommend automatically moving them on the form
AfterUpdate -- it would be better to put a command button on the form
that the user would click on to move to the next question


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*