Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    setting new values on a continued form

    I've set up the following db



    tblstudents
    PupilID_PK
    Names etc
    MathsClass

    (This tbl is updated from a school systems DB)

    Another table tblclasses is created from tblstudents by a create table query

    tblclasses contains
    classID
    Maths class
    DefaultMaths Tier


    So I Now wish to allow users to set the Tier of entry for their next exam

    My starting idea
    Click image for larger version. 

Name:	pic1.png 
Views:	16 
Size:	40.0 KB 
ID:	28736

    The f values are an unbound field (which uses a Dlookup) are for foundation tiers which I'd like staff to be easily able to change to H (higher) should they wish.

    The continuous form wont allow one button to change only one row in a continuous form.

    Any suggestions of a way forward (or a completely different direction would be welcome)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cannot use unbound textbox on continuous form that way. Each row will show the same value.
    They should all be bound.
    DONT use Dlookup, the query should provide the field. The query should do the lookup via joined table.

    The change all button should run a query to update all tiems in the table.

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    here's me thinking Andy's onto something. haha.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Click image for larger version. 

Name:	pic2.png 
Views:	14 
Size:	18.6 KB 
ID:	28740thanks Ranman. I hoped there might be a work around because I've stumbled into the dreaded "The recordset cannot be updated" issue.

    I've attached the qry

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    any chance we can see all your relationships?

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    That's all I have

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I mean for the whole DB, I'm just wondering why these are not linked via an ID. This seems like it should be updateable if it were.

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Then I need a different piece of advice

    The tbldtudent file comes as an uploadCSV file.

    there is no FK for class in it for me to link across in a relationship

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Can you not make one? or set a default value to all students so they have a key?

  10. #10
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    ok sorted one problem

    But get the impending feeling a continuous form is not an option here

    The code behind the form button is
    Code:
    Private Sub cmdChangeTier_Click()
    If Me.txtdefaultTier = "F" Then
    Me.txtdefaultTier = "T"
    Else: Me.txtdefaultTier = "F"
    End If
    Me.Requery
    End Sub
    but clicking one of these changes all the defaultTier fields

    Hope you can help

    (I did wonder whether I'd have to change this using an SQL statement?)

  11. #11
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    does this work?

    Code:
    Private Sub cmdChangeTier_Click()
     
        Dim rst As DAO.Recordset
        Set rst = Me.RecordsetClone
        rst.MoveLast
        rst.MoveFirst
        Do Until rst.EOF
      If rst!txtdefaultTier = "F" Then
    rst!txtdefaultTier = "T"
    Else: rst!txtdefaultTier = "F"
    End If
     rst.MoveNext
        Loop
    Me.requery
     
        rst.Close
        Set rst = Nothing
        MsgBox "Done"
     
    End Sub

  12. #12
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    you will need to change the values so the represent the field names in the query. seems like these are textbox names.

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks Andy and others for prompt responses...

    Now have this code

    Code:
    Private Sub cmdChangeTier_Click()
        Dim rst As DAO.Recordset
        Set rst = Me.RecordsetClone
        rst.MoveLast
        rst.MoveFirst
        Do Until rst.EOF
        If rst!StudentID_PK = StudentID_PK Then
        rst.Edit
      If rst!defaultTier = "F" Then
    rst!defaultTier = "T"
        Else:
    rst!defaultTier = "F"
        End If
    rst.Update
    End If
     rst.MoveNext
        Loop
    Me.Requery
        rst.Close
        Set rst = Nothing
        MsgBox "Done"
     
    End Sub
    which I feel should run through and only update data where the StudentID is the one who was selected.

    The code does exactly that. Leaves everyone else but when it return to the form all the F values have turned to H or vice versa.

    HELP!!

  14. #14
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    have you made the button also refresh/requery your form?

  15. #15
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    It works but was linked to a table so that changed all the values in that table. Found a way round it now using another table.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 04-15-2016, 07:30 PM
  2. Setting a couple of global values??
    By Sue22 in forum Access
    Replies: 7
    Last Post: 10-05-2015, 09:20 AM
  3. Replies: 1
    Last Post: 07-18-2014, 09:58 AM
  4. Setting textbox values on a report using vba
    By Markb384 in forum Reports
    Replies: 6
    Last Post: 01-24-2014, 09:54 AM
  5. Setting Up DB with numeric values
    By dashingirish in forum Access
    Replies: 4
    Last Post: 02-17-2012, 03:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums