Newbie - Case Select Code?

[M Davidson]

Hello All,

I'm attempting to write a case select procedure for a form to restrict
user movement within the table (recordset? (still trying to learn
terminology)). Depending on what criteria are met I want to enable/disable
the record navigation buttons. In addition, the same criteria will also
change the value of a text box control on the same form. It appears that
I'm not assigning my variables correctly, but I'm not sure.
DoCmd.GoToRecord , , acNewRec

Key.value is the serial number for the table tblScores. This is the same
table that the form's record source is assigned. So Key.value will be the
serial number on the current record being edited/entered. I used
DoCmd.GoToRecord , , acNewRec assigned to the form's open event to ensure
that the form opened on a new record.

Placeholder.value is a number that is assigned to an unbound control on an
unbound form called vForm. This is intended to be a hidden form to hold
variables for use at run time. I assigned the value to Placeholder.value by
using the domain aggregate function dLast to pull the last serial number
used to enter data prior to the current instance of frmScores being opened.

I want the user to be able to go forward and backwards through the records
he is currently entering, but I don't want him to be able to go back beyond
them or before them. The example below is intended to allow the user to
enter 5 scores only and force the change of category in txtCategory.value
on each record change. Once the user gets to the 5th record he can only go
back to edit or click the btnFinished button, which opens a completely
seperate form attached to another table.

another thing, I thought this one up on my own ( the navigation on math from
the serial number), so there's a real good possibility that the whole
concept is flawed. If it is let me know. Or if there's a better way...

Another thing: Should the code be attached to the Form's "On Update" event?


Heres's the code:

Dim Number
Dim Holder

'assign current record's serial number to Number
Number = Me!Key.Value

'assign last serial number from the previous set of 5 scores
(Placeholder.value) to Holder
Holder = Forms!vForm!Placeholder.Value

Select Case Number
Case Holder 'this is equal to the last record already entered
Me!btnBack.Enabled = No
Me!btnFinished.Enabled = No
Me!btnForward.Enabled = Yes
Me!txtCategory.Value = Something's wrong

'First record in set
Case Holder + 1
Me!btnBack.Enabled = No
Me!btnFinished.Enabled = No
Me!btnForward.Enabled = Yes
Me!txtCategory.Value = Cat1

'Second record in set
Case Holder + 2
Me!btnBack.Enabled = No
Me!btnFinished.Enabled = No
Me!btnForward.Enabled = Yes
Me!txtCategory.Value = Cat2
Case Holder + 3
Me!btnBack.Enabled = Yes
Me!btnFinished.Enabled = No
Me!btnForward.Enabled = Yes
Me!txtCategory.Value = Cat3
Case Holder + 4
Me!btnBack.Enabled = Yes
Me!btnFinished.Enabled = No
Me!btnForward.Enabled = Yes
Me!txtCategory.Value = Cat4
Case Holder + 5
Me!btnBack.Enabled = Yes
Me!btnFinished.Enabled = Yes
Me!btnForward.Enabled = No
Me!txtCategory.Value = Cat5

Case Else
Debug.Print "Value not represented."


End Select

 

[M Davidson]

There's a typo in my message:
The first instance of DoCmd.GoToRecord , , acNewRec was a goof up on my part
when pasting.
Sorry... Gee, I even act like a newbie...

 

[M Davidson]

Can anyone advise of any blatant errors in the code structure. It fails
when I try to assign the value of Placeholder.value to Holder

Holder = Forms!vForm!Placeholder.Value

In the books I have, I'm to believe that I'm supposed to use the full path
name when I refer to a value on another form that doesn't have focus.
Right?

 

[Doug M]

I don't know what's wrong with that statement, but I wonder how the
previous statement (where you put the current record's ID into Number) works
when you are on a new record (since there might not be a number in the key
field until you add data to the record?).

There are some potential problems with doing it this way. If multiple
users are adding records, then you are not restricting each user to his own
records, but to all records added by everyone since he opened the form.
Maybe a better way would be to create a unique session value (like the
current time) when the user opens the form, and keep that in a hidden
unbound control on the form. You could then copy that value into another
hidden control bound to a field in the Scores table so all records created
on the form get the same session number. Then if the form were based on a
query that selected only records with session number = the forms unbound
control, the user could only navigate to the records entered by that
instance of the form?

Another way would be to have the form bound to a separate table with the
same structure as Scores, and have the records accumulate in there. Then
when the user is done adding and editing that table could be transfered to
the main Scores table with an append query and emptied out with a delete
query. But again you'd have to have a separate "data entry" table for each
user.

Doug M

 

[M Davidson]

Doug,

Thank you! Thank you! I see what you are getting at. I will explore
both options and see which works best for my situation.
I really appreciate the guidance. I think the basing the form on a query
will work... and I'll be able to have multiple users too. Good deal!

Mike Davidson