Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Removing Subform Fields

    Hi all,

    Is there a way to remove the subform fields (Datasheet) depending on another combo box selection.

    So if combobox at top = "FieldsSelection1" then the subform displayed below (as datasheet) only shows the appropriate fields??

    Whats the easier way to do this to link the fields to the combobox at the top of the form?
    I have attempted to use the .visible = true or false property but this doesn't change anything on the datasheet subform?

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So you want an afterupdate event on your combobox...
    Why don't you change the RecordSource of the subform based on the selection? It might work
    Code:
    Dim SQL As String
    
    SQL = "SELECT Field1, Field2, Field3 "
    SQL = SQL + "FROM MyTable "
    SQL = SQL + "WHERE Field1 = '" & me.MyComboBox.value & "'"
    
    me.MySubForm.RecordSource = SQL
    me.Refresh
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    i believe i know what you mean, but surely the best way around this is just to create seperate queries for each selection (which I have done) and link the different queries as the record source for the subform?

    I have done this but would the code still stay the same as above, as it's not doing what it should?!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why create multiple queries when one dynamic one will do?

    Is the only change in the queries the criteria that you populate with the value of your combobox?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    well here's the siuation of two of my selections/queries;

    Risk Assessment: Fields = ID, Name, Type, Date, AssessRisks, AssessSolutions, AssessResponsible, Completed
    Eye Test: Fields = ID, ID, Name, Type, Date, EyeGlasses, Completed

    (*Type is linked to Combobox selection box)
    So when I select Risk Assessment it shows all the Risk Assessments using only the fields selected above, and the same for Eye test, etc..

    Understand what I need it to do better?

    P.S. I can do this using a normal form and using IF statements but I can't seem to work it out on a Subform Datasheet.
    I have tried using say an IF statement to make visible the appropriate fields but that didn't work, and now I'm onto the idea that I should just using an IF statement to change the recordsource of the subform, that way I can easily add new types and change criteria using the appropriate queries.
    Last edited by NeilMansell; 04-23-07 at 10:34.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Dim SQL As String
    
    If me.MyCombobox.Value = "Risk Assessment" Then
    SQL = "ID, Name, Type, Date, AssessRisks, AssessSolutions, AssessResponsible, Completed FROM [Risk Assesment]"
    ElseIf me.MyCombobox.Value = "Eye Test" Then
    SQL = "ID, Name, Type, Date, EyeGlasses, Completed FROM [Eye Test]"
    Else
    MsgBox "Oh dear, something's not right"
    exit sub
    End If
    
    me.MySubForm.RecordSource = SQL
    me.Refresh
    Try that - this is not tidy code, but it should give you a better idea.
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    FROM [Risk Assesment] ?? what is this linking to? As all the data is under TBL_Main

    I have tried both ways and neither are giving the results - all fields still show in the subform?!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by NielMansell
    Risk Assessment: Fields = ID, Name, Type, Date, AssessRisks, AssessSolutions, AssessResponsible, Completed
    Eye Test: Fields = ID, ID, Name, Type, Date, EyeGlasses, Completed
    Due to the convention you wrote that I assumed that was the table name.
    Code:
    Dim SQL As String
    
    SQL = "SELECT ID, Name, Type, Date "
    If me.MyCombobox.Value = "Risk Assessment" Then
    SQL = SQL & ", AssessRisks, AssessSolutions, AssessResponsible, Completed "
    End If
    SQL = SQL & "FROM TBL_Main"
    MsgBox SQL
    me.MySubForm.RecordSource = SQL
    me.Refresh
    I missed of the SELECT part in the first query.
    I'm doing far too much of this for you
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    it brings me back to thinking of a way to make invisible the fields within the subform. so if Type = "Risk Assessment" then
    [Name].visible = True, etc etc etc

    But it doesn't seem to want to work like that in a datasheet, works fine on a form!

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by NeilMansell
    FROM [Risk Assesment] ?? what is this linking to? As all the data is under TBL_Main

    I have tried both ways and neither are giving the results - all fields still show in the subform?!
    It's also useful if you tell me that sort of information, I was only guessing at your table names.

    If the new solution doesn't work then post the SQL you are currently using.
    George
    Home | Blog

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    oops didn't see your reply. anyway, tried that and it doesn't remove any of the unwanted fields in the datasheet??? (based on the Type combobox)

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is your subform BOUND?
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2004
    Posts
    287
    Code:
    Dim SQL As String
    
    SQL = "SELECT ID, Name, Type, Date "
    If Me.Type = "Risk Assessment" Then
    SQL = SQL & ", AssessRisks, AssessSolutions, AssessResponsible, Completed "
    End If
    SQL = SQL & "FROM TBL_Main"
    MsgBox SQL
    Me.JobList.RecordSource = SQL
    Me.Refresh

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    yes its bound to TBL_Main but I have tried it being unbound and it doesn't still work? any ideas?

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't think this will work with a bound subform.
    Can you possibly post a VERY cutdown version of your app...
    The form taht we are dealing with and it's relative tables.
    remove any sensitive data before posting.
    George
    Home | Blog

Posting Permissions

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