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

[xCav8r]

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.

[beginner]

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

[beginner]

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

[beginner]

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....

[xCav8r]

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.

[beginner]

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

[xCav8r]

I think you'll need to create a field to capture the date and time. Then you can use that to sort.

[xCav8r]

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?

[Norie]

xCav8r

I think that's a good idea.

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

[beginner]

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