Help! Updating a Field as Chosen by User Input from a Unbound Control in a Form 
Author Message
 Help! Updating a Field as Chosen by User Input from a Unbound Control in a Form

We need some help please!
We are trying to update the value stored in a field but want the user to be
able to select which field of a selected record to update.  ie our table
has a unique record for each "employee" with 52 fields (one for each week).
 We have the user input the "Employee Number", the "Days" which is the data
to update and the "Week" to select which of the 52 fields to use.  If the
user were to input "100" for the "Employee", "7" for the "Days" and "26"
for the "Week", the result should be that the 26th field of the record
where "Employee" = "100" would be updated to "7".
We've managed to select the correct record and can get it to update a
field, but only have success if the field is "hard coded".  We want to use
a variable to identify the field to update.  Here's what code we've managed
to put together...

Sub Days_AfterUpdate ()
Dim week As Variant
Empnum = Forms![Input]![Emp]    
week = Forms![Input]![week]
days = Forms![Input]![Days]
If IsNull(Empnum) Then
Else
    Dim Seniority As Database, Earnings As Recordset
    Set Seniority = DBEngine.Workspaces(0).Databases(0)
    Set Earnings = Seniority.OpenRecordset("Test")
    Earnings.index = "PrimaryKey"
    Earnings.Seek "=", Empnum
    Earnings.Edit
    Earnings![1] = days

---we want to replace [1] with a variable that has the value of "week" as
this only updates field "1".  We've tried  Earnings.week  and  
Earnings.("week") with no success ---

    Earnings.Update
    If Not Earnings.noMatch Then
        Earnings.Close
    Else
        MsgBox "No Match!"
        DoCmd CancelEvent
        Earnings.Close
    End If
End If
End Sub

Thx,

        Tami and Martin



Mon, 31 Jul 2000 03:00:00 GMT  
 Help! Updating a Field as Chosen by User Input from a Unbound Control in a Form

Hi,

I think you were _very_ close.  Try something like this:

Sub Days_AfterUpdate ()
Dim week As Variant
const cQUOTE = """"

Empnum = Forms![Input]![Emp]
week = Forms![Input]![week]
'etc..etc..etc..

    Earnings.Edit
        Earnings(cQUOTE & week & cQUOTE) = week
    earnings.Update

THis is of course assuming that the fields in teh database are labeled
1,2,3,......52 (ie, integers) and the value entered in Forms!Input!Week is
also integer.

HTH

--
Just my $.001
Dev Ashish
---------------


:We need some help please!
:We are trying to update the value stored in a field but want the user to be
<<<snipped>>>



Tue, 01 Aug 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Update a Bound field with data from unbound field

2. including user input from a form/field

3. Updating existing user defined field in Contact form

4. MYSTERY SOLVED: Updating user form field's default text

5. **Need Help with User INPUT, ASCII Codes, INPUT String length

6. letting user choose the form

7. How do I have a field autoupdate when another field is updated by the user

8. Collecting unbound fields on a form

9. Acc97 and unbound fields in a form

10. How Do I create a Hyperlink Field in an Unbound Form

11. Unbound field in a continuous form

12. How can I get input fields to be updated onChange

 

 
Powered by phpBB® Forum Software