Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Update various subforms (97-->)

    I am designing a form which gives details of various associations (groups of people), each group has a Chair, Vice Chair, Treasurer, Secretary etc.

    To make life easier for the user of the database form I created a series of pages/tabs with the headers described above and then put a subform on each one so that a person could view the officers for that association. Addresses are critical for this database as the information I am collating is referring to a legacy system .

    To get around people putting in rubbish info I decided to have a look table for the property details such as property reference number, building reference and the standard address details. I wanted to add a command button that opened up a look up form that the user could drill down until the correct address was found and then click another button to populate the original form. I can get the look up form to drill down but then I realised ( and I hope not too late) the following snag. Previously when I have used similar code it was just to populate fields directly onto a form but now I have realised I have a series of subforms. The code looked similar to this

    Private Sub cmdSelect_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String

    If IsLoaded("frmIncident") Then

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblProperty")
    strSQL = ("SELECT * FROM tblProperty where ID=" & Me.ID)


    Forms!frmIncident!BlockStreet = Me.BlockStreet.Value
    Forms!frmIncident!Estate = Me.Estate.Value
    Forms!frmIncident!NMO = Me.NMO.Value
    Forms!frmIncident!HouseNo = Me.House.Value
    Forms!frmIncident!Address2 = Me.Address_2.Value
    Forms!frmIncident!Address3 = Me.Address3.Value
    Forms!frmIncident!Address4 = Me.Address_4.Value
    Forms!frmIncident!PostCode = Me.PostCode.Value

    MsgBox "Address has been added to the Incident Report", vbOKOnly

    DoCmd.Close acForm, "frmPropIden"

    Else

    MsgBox " The main incident report is not open." & vbCrLf & " This form will now close", vbOKOnly
    DoCmd.Close
    DoCmd.OpenForm "frmIncident"
    End If
    End Sub

    What I want the code to do in the end is to work out which subform I was working on originally and populate the fields just on that subform....is it possible?
    Jerry

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update various subforms (97-->)

    OK Guys I think I have it and I am playing with the subforms as we speak.

    What I have decided to do is use the Tag property and re use the code for each sub form. The basic details are as following:


    On the sub form write this code

    Private Sub Select_Click()
    DoCmd.OpenForm "frmHouseSearch"
    Form_frmHouseSearch.Tag = "sfrmChair"
    End Sub

    The in my search form I can have a hidden unbound text field with the following properties

    Default Value =[Form].[Tag]

    I can then adjust my code to fit this variable....... It's a plan and I am going to try it.

    Any other ideas from you would be greatly appreciated
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update various subforms (97-->)

    Right I thought I had got there but it maybe my syntax. Can you help?

    To expalin: RAPos is being taken from a text box on a main form where this subform is attached. This is the name of the tag I transferred across which gives the name of the form I am working on the code for it is:

    Private Sub cmdOpen_Click()
    DoCmd.OpenForm "frmHouseSearch"
    Form_frmHouseSearch.Tag = "[frmRAMain]![sfrmChair]"
    End Sub

    the rest of the code on the main form is:


    Private Sub cmdSelect_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim RAPos As String
    Dim strPropref As String
    Dim strAdd1 As String
    Dim strAdd2 As String
    Dim strPost As String
    Dim strStructRef As String


    RAPos = [Forms]![frmHouseSearch]![txtType]

    MsgBox RAPos

    strPropref = "[Forms]!" & RAPos & "![Propref]"
    strAdd1 = "[Forms]!" & RAPos & "![Address1]"
    strAdd2 = "[Forms]!" & RAPos & "![Address2]"
    strPost = "[Forms]!" & RAPos & "![postcode]"
    strStructRef = "[Forms]!" & RAPos & "![Structureref]"

    If IsLoaded("frmRAMain") Then

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPropBlock")
    strSQL = ("SELECT * FROM tblPropBlock where ID=" & Me.ID)


    strPropref = Me.PropertyRef.Value
    strAdd1 = Me.AddressLine.Value
    strAdd2 = Me.City1.Value
    strPost = Me.PostCode.Value
    strStructRef = Me.StructureRef.Value



    etc.....

    My subform does not upate! Please help I am going crazy!
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update various subforms (97-->)

    What function do db, rs and strSQL have in this? You don't seem to use them after initially setting them.

    Instead of using the Tag property, you can pass info to a form in the OpenArgs argument of DoCmd.OpenForm. It will be available in the OpenArgs property of the form that is opened.

    Perhaps you should requery the subform after putting data into its source table.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update various subforms (97-->)

    Hi Hans

    Sorry, been offline for a while as I am working from home. I realised what I was doing about half an hour ago.

    What I have decided to do as the deadline is coming up tomorrow is to hash the code during the testing stage and hope the boss doesn't see. I have fallen back on my old favourite the if statement and wrote this


    blah,blah and then


    RAPos = [Forms]![frmHouseSearch]![txtType]

    If RAPos = "sfrmChair" Then

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPropBlock")
    strSQL = ("SELECT * FROM tblPropBlock where ID=" & Me.ID)

    Forms!frmRAMain!sfrmChair!PropRef = Me.PropertyRef.Value
    Forms!frmRAMain!sfrmChair!Address1 = Me.AddressLine.Value
    Forms!frmRAMain!sfrmChair!Address2 = Me.City1.Value
    Forms!frmRAMain!sfrmChair!PostCode = Me.PostCode.Value

    DoCmd.Close acForm, "frmHouseSearch"

    ElseIf RAPos = "sfrmVChair" Then

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPropBlock")
    strSQL = ("SELECT * FROM tblPropBlock where ID=" & Me.ID)

    Forms!frmRAMain!sfrmVChair!PropRef = Me.PropertyRef.Value

    etc

    It works fine although not the finest piece of code I have used

    Thanks for having a look
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update various subforms (97-->)

    It can be made just a tiny bit more elegant by using Select Case:

    RAPos = [Forms]![frmHouseSearch]![txtType]
    Select Case RAPos
    Case "sfrmChair"
    ...
    Case "sfrmVChair"
    ...
    ...
    End Select

Posting Permissions

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