Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo-box datasource problem (2003)

    Hello there!

    2 tables:

    tblSys
    Title
    Value
    <IMG SRC=http://www.rvl-medizintechnik.de/temp/Snap2.gif>

    tblYears
    Year

    1 Form with combo-box
    frmYear with cboreg
    <IMG SRC=http://www.rvl-medizintechnik.de/temp/Snap1.gif>

    Problem:
    The combo Box shows the values from tblYears (2003,2004,2005,...). Next to the combo box you see the "Set..." trigger which enters the value of cboreg to tblSys>Set Year (thats the Title)>2004 (thats the Value)

    Now what I want is this: The default value for the combo box should be the [Set Year]>[Value]. When selecting another year from the combo box I want that value be entered into [Working Year]>[Value].
    I tried = [Value] WHERE [Title] = "Working Year" but nothing seems to work. I hope you get what I mean

    Dennis

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

    Re: Combo-box datasource problem (2003)

    Use the After Update event of the combo box:
    <code>
    Private Sub cboReg_AfterUpdate()
    If Not IsNull(Me.cboReg) Then
    CurrentDb.Execute "UPDATE tblSys SET Value = '" & Me.cboReg & _
    "' WHERE Title = 'Working Year'"
    End If
    End Sub
    </code>
    I put single quotes around the cboReg value because the screenshot indicates that Value is a text field (it is left aligned).

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo-box datasource problem (2003)

    Perfect thanks Hans!

    One more question about this. Would you rather use a Year=[Form]![frmYear]![cboreg] for datasources or refer to the tblSys, now that I have it. This way I could disable the frmYear always being hidden in the background (which seems like some kind of workaround to me). Just wondering about usability etc.

    Dennis

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

    Re: Combo-box datasource problem (2003)

    You could use
    <code>
    Year=DLookup("Value","tblSys","Title='Working Year'")
    </code>
    as condition, or add tblSys to the data source query, with 'Working Year' as criteria for the Title field., and [tblSys].[Value] as criteria for the Year field.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo-box datasource problem (2003)

    So what do you think? Would that be a better approach than the previous one?

    I think so but don't wanna run into too much work now if the previous method was alright too...

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

    Re: Combo-box datasource problem (2003)

    I don't see an objection to keeping a form open hidden, unless you experience problems with low resources (an open form takes up Windows resources). If it ain't broken, don't fix it.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo-box datasource problem (2003)

    Ur right. Never change a running system.

    Problem solved. Thanks

  8. #8
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo-box datasource problem (2003)

    Alright new problem arrived... [img]/forums/images/smilies/wink.gif[/img]

    I can't use the [frmYear]... stuff for a Query because the database isnt always open (neither is frmYear) when running of the query from Word.

    So what I tried is using the DLookup("Value"...) instead as a criteria for [Year].
    The problem is now that Access gives me an Syntax error. I copied the code exactly like you proposed (w/o the Year= of course) into the criteria Field of Year.

    Adding tblSys to the data source query and so on worked....

    Now I'm just curious why DLookup won't work!

    BEst wishes,
    Dennis

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

    Re: Combo-box datasource problem (2003)

    We would have to see the database to know what goes on.

  10. #10
    New Lounger
    Join Date
    Jan 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo-box datasource problem (2003)

    Try:

    Private Sub cboWelcome_AfterUpdate()
    cboWelcome.DefaultValue = cboWelcome
    End Sub

Posting Permissions

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