Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    48

    Question Unanswered: Using Combo Box to select different record field content to display on form?

    Hiya folks!

    I have a table in which multiple columns represent different years.

    At the moment on the Form there is a field present to represent each year (of course with this method, in 20 years there will be 20 fields on the Form!!).

    Can anyone tell me how I might use a single Combo Box on the Form to select a specific year column from the Table, and another Combo Box on the Form to display/edit the value associated with that year?

    If it is possible to do it this way, no matter how many years pile on, the form will only ever need to the two fields!!

    Cheers for any help you can give on this!!!

    Luke

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    in the first combo adjust its RecordSource by a Select statement
    combo1.RecordSource="SELECT thisField FROM thisTable WHERE yourCriteria"

    on the secound combo adjust its RecordSource something like that:
    combo2.RecordSource="SELECT thisField, anotherField FROM thisTable WHERE thisField =" & ME.Combo1

  3. #3
    Join Date
    Aug 2003
    Posts
    48
    Cheers Mate!

    I'll give it a shot!

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by hammbakka
    in the first combo adjust its RecordSource by a Select statement
    combo1.RecordSource="SELECT thisField FROM thisTable WHERE yourCriteria"

    on the secound combo adjust its RecordSource something like that:
    combo2.RecordSource="SELECT thisField, anotherField FROM thisTable WHERE thisField =" & ME.Combo1

    Sorry, I mean RowSource and not RecordSource

  5. #5
    Join Date
    Aug 2003
    Posts
    48
    Hi there hammbakka!

    I had a go at using your code, but it seems to me that the only way I will be able to apply the code will be using VBA.

    The Column Field Headings on the Table are:

    2001Muster
    2002Muster
    2003Muster
    2004Muster

    and so on.

    I need the first Combo box on the form to contain the values:

    "2001"
    "2002"
    "2003"
    "2004"

    The second Combo box then uses some clever VBA code to SELECT it's rowsource by taking the value from the first Combo box and combining it with the string "Muster" to create the valid Column heading:

    e.g. - "2001Muster"

    Code:
    Dim thisfield As String
    Me.Combo84.RowSource="SELECT thisfield FROM File_Information WHERE thisfield ="cboMusterYear.value & 'Muster'"
    
    End Sub
    With the code you posted, I came up with this. Can you advise me how I can 'finish it off' to get it running (that is if you think this is the correct approach!).

    Cheers,

    Luke

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1


    code:--------------------------------------------------------------------------------
    Dim thisfield As String
    Me.Combo84.RowSource="SELECT thisfield FROM File_Information WHERE thisfield ="cboMusterYear.value & 'Muster'"

    End Sub--------------------------------------------------------------------------------


    With the code you posted, I came up with this. Can you advise me how I can 'finish it off' to get it running (that is if you think this is the correct approach!).
    WHERE thisfield = '" & cboMusterYear.value & "Muster'"

    double quotes around "cboMusterYear.value & 'Muster'" causes that literal string to be passed.

  7. #7
    Join Date
    Aug 2003
    Posts
    48
    Thanks Teddy! That would have tripped me up for a start!

    I'm having difficulty actually referencing a field (column) as opposed to a specific record (row).

    Having already selected the record, the first combo box is supposed to select which one of the YEAR fields (column) the 2nd combo box is looking at for it's value.

    Code:
    Private Sub cboMusterYear_AfterUpdate()
    
    Dim thisfield As String
    Dim thisvalue As String
    
    Me!Combo84.RowSource = thisvalue
    
    
    thisvalue = "SELECT thisfield FROM File_Information WHERE thisfield = '" & cboMusterYear.Value & "Muster'"
    
    
    
    
    End Sub
    This is what I have in the AfterUpdate event of the 1st Combo box.

    Unfortunately, the 2nd combo box is currently doing nothing (not even giving an error message, which would be something to work with!).

    Can anyone suggest where I might be going wrong here?

    By the way, thisfield is not an ACTUAL field in the File_Information table, it is just supposed to be a variable that picks up the year value "2001" from the first Combo box, and then combines it with the string "Muster" to form the valid name of an existing field (Column) "2001Muster".

    Save me please!!

Posting Permissions

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