Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    44

    Unanswered: Setting Values in Subform, Loops & Recordsets

    Help!!

    I have a form with a subform. My subform is as a datasheet with as many records as I choose to be added.

    I am trying to work out how to change a value for each record on a click of a button. The only problem is, I am not sure of how many records there will so need to use BOF & EOF.

    I have tried using a do while/until loop but the condition I am entering seems to be invalid.

    Heres a few things I have been playing around with...

    Do Until Forms![frmMain].[frmSubForm].Form.[ControlName1].Value = ""
    Forms("frmMaint").Form("frmSubform").Form("Control Name2").Value = "No"
    DoCmd.GoToRecord acActiveDataObject, , acNext
    Loop

    The Condition is a cell in the datasheet but it is not recognising it?

    I want it to look through each record and if ControlName1 is equals to "" then stop and in the meantime, set ControlName2 value to "No".

    I would appreciate any comments.

    Thanks

    Smilla

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Forget the fact that you are using a subform. The important thing here is that you must work with the records underlying the subform, i.e. the subform's table or query. Then it's a simple matter of using an Update query to update the records based on your condition, and then re-querying the subform. Or, of course, in VBA you could write:

    DoCmd.RunSQL "UPDATE NameofTable SET FieldName2 = False WHERE FieldName1 = " &Chr(34) &Chr(34)

    The Chr(34) is the ANSI code for the inverted comma(").

    Then requery the subform:

    Forms!NameOfMainForm!NameOfSubform.Form.Requery

    Hope this gives you something to think about!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Update query

    Can you use an update query instead?

    ...Oh you beat me to it Andy!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Jan 2004
    Posts
    44
    Thank you, I always forget the easy options.

    Just been having a play and I am getting an error:

    A type conversion error.

    Any ideas what this means.

    Thanks


    Originally posted by andybriggs
    Forget the fact that you are using a subform. The important thing here is that you must work with the records underlying the subform, i.e. the subform's table or query. Then it's a simple matter of using an Update query to update the records based on your condition, and then re-querying the subform. Or, of course, in VBA you could write:

    DoCmd.RunSQL "UPDATE NameofTable SET FieldName2 = False WHERE FieldName1 = " &Chr(34) &Chr(34)

    The Chr(34) is the ANSI code for the inverted comma(").

    Then requery the subform:

    Forms!NameOfMainForm!NameOfSubform.Form.Requery

    Hope this gives you something to think about!!

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    consider:
    "UPDATE NameofTable SET FieldName2 = False WHERE len(FieldName1) = 0;

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Red face Code

    ...Im a bit slow on the uptake today - Izy to the rescue...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Jan 2004
    Posts
    44
    Thanks for your help - all sorted ..... until the next problem!!

    Regards

    Originally posted by izyrider
    consider:
    "UPDATE NameofTable SET FieldName2 = False WHERE len(FieldName1) = 0;

    izy

  8. #8
    Join Date
    Jan 2004
    Posts
    44
    Hi Izy,

    Another quick question - can I add something to the code to allow the update to happen in the background without the user clicking 'yes' to update?

    Originally posted by izyrider
    consider:
    "UPDATE NameofTable SET FieldName2 = False WHERE len(FieldName1) = 0;

    izy

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    docmd.setwarnings false

    <code for query here

    docmd.setwarnings true
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Jan 2004
    Posts
    44
    This doesn't seem to work

    This is my code:-

    Dim tblSchedule As String
    DoCmd.RunSQL "UPDATE ([tblAsset Types] RIGHT JOIN tblAsset ON [tblAsset Types].AssetTypeID = tblAsset.AssetTypeID) RIGHT JOIN tblSchedule ON tblAsset.AssetID = tblSchedule.AssetID SET tblAsset.Allocated = No WHERE (((tblSchedule.ScheduleID) = [Forms].[frmAllocation].[AllocationID]))"
    DoCmd.SetWarnings True
    Forms!frmAllocation!frmSubAssets.Requery

    It is still coming up with the question "Do you want to update 2 records", yes or no.

    I don't want to user to see this, I just want it to update automatically.

    Thanks

    Originally posted by garethdart
    docmd.setwarnings false

    <code for query here

    docmd.setwarnings true

  11. #11
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    If you change your code to read this....


    Dim tblSchedule As String
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE ([tblAsset Types] RIGHT JOIN tblAsset ON [tblAsset Types].AssetTypeID = tblAsset.AssetTypeID) RIGHT JOIN tblSchedule ON tblAsset.AssetID = tblSchedule.AssetID SET tblAsset.Allocated = No WHERE (((tblSchedule.ScheduleID) = [Forms].[frmAllocation].[AllocationID]))"
    DoCmd.SetWarnings True
    Forms!frmAllocation!frmSubAssets.Requery
    DoCmd.SetWarnings True

    ...then it WILL work as you want it to.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •