Consulting

Page 3 of 3 FirstFirst 1 2 3
Results 41 to 50 of 50

Thread: Solved: How to Retrieve "Existing Excel values" to Access ??

  1. #41
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    You should only need to set the child-parent relationship between the form and subform using the value in the combobox. For example:

    Records:

    A, B, C
    B, B, B
    C, B, A
    B, A, B
    A, A, A

    If both the subform and the main form are based on the same table, and you want to display on the subform all records with A in column one (field one), then you set the parent and child fields for the forms to column one. If the main form contains nothing but a combobox where users select a value from the list, then if, say, C were selected, only the third record would appear in the subform. This shouldn't require any VBA.

  2. #42
    VBAX Regular
    Joined
    May 2005
    Posts
    62
    Location
    hi Yes.....that is what exactly i want to achieve but i can't achieve 2 things:-

    1. after i changed the comboBox value, the subform didn't refresh that selection on the fly.
    2. not sure how to display the last couple records in the subform if not using VBA? (such as Recordset.MoveLast ?? etc.)

    I have set both the main & sub Form's Source property point to the same Table ("sql_EFFTEMP-trim" which generated from a query on another Table "EFFTEMP-trim") and also set (at the subform property) both the main & sub-form's link property to the same comboBox's field ("BADGE_no").

    In my main form, got same fields which present in the subform for data keyin, and some command buttons to do update table (EFFTEMP-trim) action etc.

    I also try using VBA only (without set the above property) , and have sourced some related VBA coding as below but also can't achieve what i want.?!

    [VBA]
    Private Sub Combo_id_AfterUpdate()

    Dim FiltVal As String
    FiltVal = Trim(Me.Combo_ID.Value)

    Me.[sql_EFFTEMP-trim_Sub_form].Form.Filter = "BADGE_NO='" & FiltVal & "'"
    Me.[sql_EFFTEMP-trim_Sub_form].Form.FilterOn = True
    End Sub
    [/VBA]


    Any further adv. appreciated !!! Tks a lot.

    BR/
    beginner

  3. #43
    VBAX Regular
    Joined
    May 2005
    Posts
    62
    Location
    hi xCav8r,

    some more info. that if I manually click the main form bottom record bar record by record, then the sub-form show what i want (except not sure how to show most updated records), but if i updated the comboBox value, the subform didn't refresh, the problem seems how to link up the comboBox value (Combo_id) with the field (BADGE_NO) atwhich both the Main & subform linking in the subform's property.

    I am a bit confusing using subform at this moment.........need help....Many thanks.

    BR/
    beginner

  4. #44
    VBAX Regular
    Joined
    May 2005
    Posts
    62
    Location
    hi.....i can figure out something. It also need to set the comboBox's ControlDataSource Property to the field same as "BADGE_NO" PLUS a "db1.formName.Refresh" coding to refresh the mainform. then it work as what i want!

    Except that still not sure how to display the most updated records in the subForm ?? using VBA or just set Property ? and how ? .....

    Tks for any adv....

  5. #45
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Sorry, I'm not sure why I gave you the advice I did. It was wrong. The best way to do this is to use an unbound main form (or an unbound combobox on a bound form) and use VBA to alter the control source of the subform. I normally use SQL statements for this which I prefer over filters. If you don't see updates on the subform, trying refreshing or requering (can't remember which). One of those will work.

  6. #46
    VBAX Regular
    Joined
    May 2005
    Posts
    62
    Location
    hi xCav8r,

    yes i use unbound comboBox with dataSource from another table. and it seems working by using only subForm's Property setting. plus using a .refresh VBA to achieve what i want.

    Only i am not sure how to display the most current updated records in the subform's "field of view" since if the no. of records display is so large that every time need to scoll the scoll-bar to the last updated record that is troublesome.

    BR/
    beginner

  7. #47
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    I think you'll need to create a field to capture the date and time. Then you can use that to sort.

  8. #48
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    BTW, I'm happy to help you for as long as I'm able to answer questions that you ask, but we should probably consider this thread closed, since we have gone on to many other issues. Someone searching through thread titles might find some of this content useful, but they wouldn't know it was here if they didn't read through it. May I suggest that, if your original problem has been solved, you mark the thread as solved and start new threads for other questions?

  9. #49
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    xCav8r

    I think that's a good idea.

    I've looked at this thread many times and noticed that it has covered many topics.

  10. #50
    VBAX Regular
    Joined
    May 2005
    Posts
    62
    Location
    hi xCaV8r and Norie,

    yes you are right, the major problem relating to this original title should be solved and i should marked this thread closed and will start new thread for other problems. Tks a lot!

    BR/
    beginner

Posting Permissions

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