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

[beginner]

hello....got some headache and need somebodies help...

I am trying to figure out how to using Access DAO or ADO to "retrieve existing Excel Worksheets" particular cells value(s) and save back to Access table, i can only got some coding from some sources as below but which only show how to "write" data from Access to a "new created" Excel & worksheet only...

Can somebodies help....thanks a lot.


Sub OpenExcel()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add
ActiveCell.FormulaR1C1 = "ABC"
xlApp.Quit
Set xlApp = Nothing
End Sub


Beginner

:hi:

[xCav8r]

This should help you read it.

http://www.exceltip.com/st/Import_data_from_a_closed_workbook_(ADO)_using_VBA_in_Microsoft_Excel/429.html

[beginner]

hi xCav8r....Thanks a lot and I will digest the coding first and tks again.


Beginner

[xCav8r]

If you need help with the reading portion, just let me know. After you're comfortable with reading the data, we'll move on to storing it in Access.

[beginner]

Hi xCav8r, this is beginner and thanks for your info. & help. however, i find it ADO seems a bit more difficult to understand than DAO and can I use DAO sample to ask your help again.....

Below got the coding sample for my headache.....what i want to do in details are :-

1. under Access Form, click a Product_Search button and run the VBA when "on_click"
2. then, the VBA will first clear the records in my current database (ABC.mdb)'s table "ABC_level_1" and open a recordset.
3. then, it also open the other Excel file (Excel.xls) in the same path as the ABC.mdb and located the WorkSheet(1)
4. Then it start to retrieve data from the Excel cells value to access under some criterias ( lines start with "i=4" )

However, the VBA got error and stop at the line "Set xlApp = ......" and i also find some error at the line "Set mySht = .....(either options also got errors)", so I cannot achieve the above step 3. ie. I cannot locate the Excel.xls to access the Worksheet(1) cell values.

I checked the line "myDb.Execute "DELETE * FROM " & myTblName" is working ok as the table was being empty and those data retrieve lines after "i=4" should be ok as i tried run them inside Excel macro. (although the VBA still not yet reach this lines)


Would you help to study what should be the correct coding to open the Excel file and locating the required worksheets or some other coding not correct ??
Many many thanks....!!!


*******************************
Private Sub Product_Search_Click()
Dim xlApp As Excel.Application
Dim filename_excel As String

Dim myDb As DAO.Database
Dim myRst As DAO.Recordset
Dim myFileName As String
Dim myTblName As String
Dim myKey As String
Dim mySht As Worksheet
Dim i As Long
Dim j As Long
myFileName = "ABC.mdb"
myTblName = "ABC_level_1"

Set myDb = CurrentDb
myDb.Execute "DELETE * FROM " & myTblName
Set myRst = myDb.OpenRecordset(myTblName)
filename_excel = "Excel.xls"
Set xlApp = GetObject(ThisWorkbook.Path & "\" & filename_excel, "Excel.Application")
' Set mySht = xlApp.Workbooks.OpenDatabase(filename_excel).Worksheets(1)
Set mySht = xlApp.Worksheets(1)

i = 4

With myRst
.Index = "PrimaryKey"
For i = 4 To mySht.Range("A100").Row
If Trim(mySht.Cells(i, 23).Text) = "142" Then Exit For
If mySht.Cells(i, 4 + 19).Value <> "" Then
.AddNew
myRst(1).Value = mySht.Cells(2, 10).Value
myRst(2).Value = mySht.Cells(2, 30).Value
For j = 4 To .Fields.Count - 1
myRst(j - 1).Value = mySht.Cells(i, j + 19).Value
Next
If mySht.Cells(i, .Fields.Count + 19).Value = "" Then
myRst(.Fields.Count - 1).Value = "N"
Else
myRst(.Fields.Count - 1).Value = "Y"
End If
.Update
Else
End If
Next
.Close
End With

myDb.Close
Set myRst = Nothing
Set myDb = Nothing

End Sub
*******************************

[mdmackillop]

Hi Beginner,
If you select your text and click on the VBA button it formats the the code as above, making it more "readable" in the forum.
Regards
MD

[beginner]

Hi MD, thanks for your tips.!!


To all helpers in this forum, refer the previous DAO sample, I believe below coding to locate the Excel.xls file and Worksheet(1) got something wrong but not sure how to correct.......any help very much appreciated , thanks !


**********************
Set xlApp = GetObject(ThisWorkbook.Path & "\" & filename_excel, "Excel.Application")
' Set mySht = xlApp.Workbooks.OpenDatabase(filename_excel).Worksheets(1)
Set mySht = xlApp.Worksheets(1)
**********************

[xCav8r]

Sorry, beginner, I've been busy lately and haven't had the time to respond. It looks like you want to read your data by creating an instance of Excel, which, of course, is fine--though it's obviously not DAO. Just replace your existing code using the following as a model:

Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.workbooks.Open strPathToFile


That should get you rolling. If you're still having problems, please post a sample workbook and mdb.

[beginner]

hi XCav8r, grateful for your help....it's working now.!! Although the solution seems simple but its very important for me !!

next step i will try to generalize the search function by using multiple combo with multiple worksheets, may also need your help again once i face another problem.....and thanks again for your help & adv.

beginner.

[beginner]

hi xCav8r,

forget to ask you if i use the create Excel instance (not DAO) as what you mentioned, can i apply it under network file server for multi-users ? if yes, how should i locate my Excel file in the coding ? can i use something like... ThisWorkbook.Path & "\" & ExcelFileName etc. but it seems didn't work in my previous coding.

Thanks in advance for your help again.

beginner

[xCav8r]

You can't use ThisWorkbook.Path in Access unless you create an Excel application object, but that won't help you since you can only return the path of a workbook that's already open, not one you want to open. The best method would be to keep it stored in a static directory on the network. An alternative would be to search for it, but that could take a long time to complete and not necessarily generate the results you desire.

[beginner]

hi xCav8r, thanks for your adv.again.

beginner

[beginner]

hi xCav8r and other helpers....

i encountered another headache and need your adv.

- After running coding under Access2000 to retrieve Excel file data,
- closed Access
- try to open the Excel file which was just opened by Access before, but cannot and got error message window " 'filename' is now available for editing. Choose Read-Write to open it for editing..."

below is the coding structure for ref.


Dim appExcel As Object
Dim objExcel As Object

Dim workBook As Object
Dim workSheet As Object
Dim ExcelFile As String

ExcelFile = "c:\test.xls"

Set objExcel = CreateObject("Excel.Application")
' Show spreadsheet on screen
objExcel.Workbooks.Open "filename"

appExcel.Application.DisplayAlerts = False

*****************
coding

******************
workbook.Close
appExcel.Application.Quit

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing
Set objExcel = Nothing

Any adv. very much appreciated.

Tks.
Beginner

[xCav8r]

If you had an error and stopped execution before the objects were released, it could still think it's open. That happens to me all the time. See if a process is running that you can terminate in the task manager. If not, rebooting should free it up.

BTW, it looks redundant to have objExcel and appExcel since my guess is they are both objects used with CreateObject("Excel.Application"). Was this on purpose? You running two separate threads?

[beginner]

hi xCar8r, tks again for your tips.

below more clear about my coding structure.

Yes, i check the task mgr. got quite a lot of Excel.exe running if i run that function many times (also after closing the Access, some screens remain in desktop showing those Excel workbooks, which opened by coding before, was linked to something and cannot be closed or move to other folders...etc. ) and occasionally the system will give "not enough memory" etc. if run the function too many times,

so i need to use code to close all the Excel stuffs at the end of the coding but my below coding seems can't do it perfectly. any other suggestion on the coding ?

one more query, instead of using below remark line coding, any commands can closed the Active Workbook during running without saving the workbook and without any pop up prompt screen come out ? because if i use below Remark line code, i can avoid those pop up prompt screen but the workbook still open hiddenly and this is not allowed in my system.

' objExcel2.Application.DisplayAlerts = False

Thanks very much for any help again.

BR/
beginner


Private Sub fun_Click()

Dim objExcel2 As Object

Dim myDb2 As DAO.Database
Dim myRst2 As DAO.Recordset

Dim mySht2 As Worksheet
Dim myWb2 As Workbook

Set myDb2 = CurrentDb

Set myRst2 = myDb2.OpenRecordset("table-name")

' objExcel2.Application.DisplayAlerts = False

''''''''''''checked if any unclosed workbook and closed it'''''''''''''

Set myWb2 = objExcel2.ActiveWorkbook

If myWb2 Is Nothing Then

Else

myWb2.Close

End If
'''''''''''''''''''''''''''''''''''''''''

Set objExcel2 = CreateObject("Excel.Application")

objExcel2.Workbooks.Open "File-name"

Set myWb2 = objExcel2.ActiveWorkbook


''''''''''''''''

coding....

''''''''''''''''

' objExcel2.Application.DisplayAlerts = True

myRst2.Close
myDb2.Close

myWb2.Close
objExcel2.Workbooks.Close
objExcel2.Quit

Set myRst2 = Nothing
Set mySht2 = Nothing
Set myWb2 = Nothing
Set myDb2 = Nothing
Set objExcel2 = Nothing

End Sub

[xCav8r]

Private Sub fun_Click()
'Requires MS Excel Library to use Worksheet and Workbook
Dim objExcel2 As Object
Dim myDb2 As DAO.Database
Dim myRst2 As DAO.Recordset
Dim mySht2 As Worksheet
Dim myWb2 As Workbook

Set myDb2 = CurrentDb
Set myRst2 = myDb2.OpenRecordset("table-name")

'objExcel2.Application.DisplayAlerts = False
'you can supress messages, but you should do that by putting in the parameters you need for the commands
'that you're doing (like saying to save or not to save--just search in help for what you're doing to see
'what your options are

''''''''''''checked if any unclosed workbook and closed it'''''''''''''
Set objExcel2 = CreateObject("Excel.Application")
'you have to create the excel.application object before you can use it
'I moved this line up here
Set myWb2 = objExcel2.ActiveWorkbook
'you can't do this from another Office app to see if excel has a workbook open
'it might be possible from VBA to do this (I don't know), but I would suggest that you focus instead
'on making sure that every closes properly instead of trying the shut-down-if-running approach

'Below comments on If Block for learning purposes only (see above for why)
If Not myWb2 Is Nothing Then
'note the change in syntax above
myWb2.Close
'this won't happen with the current code, since there is no activeworkbook of objectExcel2
Else
'this would catch something you didn't plan for
End If

objExcel2.Workbooks.Open "File-name"
Set myWb2 = objExcel2.ActiveWorkbook
'this isn't necessary, since whatever is just opened will be the activeworkbook

''''''''''''''''
'coding....
''''''''''''''''

myRst2.Close
myDb2.Close

myWb2.Close
objExcel2.Workbooks.Close '<-- I don't think this'll work without qualifying workbooks
'which is already implicit in myWb2.Close; this line will generate an error and is
'unecessary
objExcel2.Quit '<--- this is probably what you were forgetting before or your code was
'stopped by an error before it could reach this point; add an error handler to do this
'so you don't end up with a bunch of instances of Excel running

Set myRst2 = Nothing
Set mySht2 = Nothing
Set myWb2 = Nothing
Set myDb2 = Nothing
Set objExcel2 = Nothing

End Sub

[beginner]

hi xCav8r, tks for your valuable adv. again and will try them. tks.

BR/
Beginner

[beginner]

hi xCav8r (or any other helpers in this forum),

one quick question relating to simple Form input (though not involve any VBA coding, but i stuck at this point) that i believe you can help.

Suppose a simple operation involving only data entry in a Form involving lots of fields and then update to the table (not by coding but by the form itself.) , would you adv. how to set the form property or other easiest method so that after operator finished key-in & update the 1st record, those field values still be maintained in the fields of the Form, so that the operator no need re-key in same values again for the 2nd record. I thought there should be some Form's text property can be set but I check all of them cannot figure out which one.

many tks for any adv.

BR/
beginner

[xCav8r]

hi xCav8r (or any other helpers in this forum),

It would be nice for you to get some alternate advice to mine, huh? ;)


Suppose a simple operation involving only data entry in a Form involving lots of fields and then update to the table (not by coding but by the form itself.) , would you adv. how to set the form property or other easiest method so that after operator finished key-in & update the 1st record, those field values still be maintained in the fields of the Form, so that the operator no need re-key in same values again for the 2nd record. I thought there should be some Form's text property can be set but I check all of them cannot figure out which one.

A few unsolicited words of caution: in my experience, the biggest problem with databases is people, and auto-populating fields can often be problematic in that regard. Data entry is tedious, and most people (understandably) get bored then lazy. It's too easy to turn your brain off, and operating on autopilot isn't the best formula for accurate data entry. I assume, of course, that this is something that you've considered, but the circumstances warrant this.

The short answer is I don't know how to do with without VBA. An alternative--though not very flexible and one you've probably already considered--would be to assign default values to your fields.

[beginner]

hi xCav8r, tks for your adv., you already gave me a feasible idea about using default values, so may be it can be done by setting default values of the last records by using coding ? i will try to fig. out first, tks.

beginner

[beginner]

hi xCar8r,

i just source from some web info. about some tips may be you already aware of.....When pressing CTRL+APOSTROPHE (') at the desired Form field, then the value of last record will come out auto. Though this can only solve half of my fellow operator's request...as she want just a "Tab" key to achieve but now still need 2 keys, but at least can satisfy part of her desire....

anyway, i would prefer back to my VBA headache....

Got another critical query for my application would like to seek your help.

My application using Access to retrieve data (by hitting a Form button) from remote Excel files which may be updated anytime. However, how can my Access application know when the remote Excel files just updated & finished saving and trigger the "retrieve data" on_click button action ? any methods suggested ? Also, I am not allowed to do anything or write any coding in those remote Excel files.

many thanks !!


beginner

[xCav8r]

Cool. Thanks for the keyboard shortcut. That's a good thing to know.

As for knowing there have been updates in Excel, to automate that, you'd need to communicate from Excel to Access. It doesn't sound like that's possible, so an alternative would be to could check for updates with a timer event in Access, but your data would only be as good as the last update.

[beginner]

i see, tks for the adv. & i'll try to fig. out the timer event first. tks.

beginner

[beginner]

hi xCar8r, got another query need your help...

do you know how to clear the content of a ComboBox so that i can populate it again in a loop. I am stucking on this.....tks.

it seems don't have the clear function..........ComboxBox_name.Clear ??

BR/
Beginner

[mdmackillop]

Hi Beginner,
Try the following syntax, run from the Sheet module.

Private Sub CommandButton1_Click()
ComboBox1.Clear
For Each cel In Range("Test1")
ComboBox1.AddItem cel
Next
End Sub

[xCav8r]

mdmackillop's method will work if you have a combobox in an Excel spreadsheet. And, regardless of the office application, you can also use the clear method for a combobox on a visual basic form, but comboboxes on Access forms do not support a clear method.

If this is your case, and you're populating the combobox from an array, then just use this:

Me.Combo0.RowSource = Split(avarMyArray, ";", , vbTextCompare)

If you're not using an array, then you should just point it to the new table or query where it can get the values.

[beginner]

hi MD and xCarX8, tks for your adv.

yes, i use Access Form and the Clear function seems didn't work in my case. and below is part of my coding, there are 2 comboBox, when change the 1st combo's (Combo_Product_group) text, then it go to a table and run a loop to check the table's 2nd field values and extract those useful values and then populate into the 2nd comboBox (Combo_Product_group_product), but before it populate the 2nd ComboBox, it need to empty the 2nd ComboBox first.

However, the 1st line didn't work to empty the 2nd combo while the rest is working so the result is those selected values from the table continue to append to the 2nd comboBox cumulatively and that is not what i want.

xCarV8, I am not sure how to implement your adv. coding into the below, would you adv. more ? tks again.



'''''''''''''''''''''''''''''''''''''''''''''
Private Sub Combo_Product_group_Change()

'
' declare
'

myProduct_group4 = Combo_Product_group.Text

Me.Combo_Product_group_product = ""

If myRst4.EOF Then Exit Sub
With myRst4
myRst4.Index = "PrimaryKey"

Do Until .EOF

If Right(Trim(.Fields(1)), myLen4) = myProduct_group4 Then

Combo_Product_group_product.AddItem (myRst4(1))
End If
.MoveNext
Loop
End With
myRst4.Close

'
' coding
'

[xCav8r]

Since you're doing this on an Access form, you cannot use the .clear method, because it isn't supported. Although I had a hard time understanding what you're trying to do, I think you want to make .RowSource = "SELECT blahblah FROM blahblah WHERE combobox1.value = x".

[beginner]

hi all, below is the line i mentioned above seems didn't work to empty the comboBox.

Me.Combo_Product_group_product = ""


BR/
beginner

[beginner]

hi xCav8r,

tks for your adv. i just send out the above before i saw your adv. so i will try you adv. first. tks.again

BR/

[mdmackillop]

Aplogies.:blush
I didn't check the application the question was under! Too used to Excel questions.

[antonin]

You can put a new command button on the from; the Command Button Wizard offers "Duplicate" among the "Record operations" - it is easy, but the "automatic code" it creates makes use of the menu items; you can do the same in the real code easily, by: copy record, goto new record, paste record

This may be an alternative answer to your question, I believe? It simply populates the new record with the values from the record on which you are when you push the "Duplicate" button.

Antonin

[xCav8r]

Aplogies.:blush
I didn't check the application the question was under! Too used to Excel questions.

hehe...with that many posts, it's bound to happen now and again. :peace:

[xCav8r]

You can put a new command button on the from; the Command Button Wizard offers "Duplicate" among the "Record operations" - it is easy, but the "automatic code" it creates makes use of the menu items; you can do the same in the real code easily, by: copy record, goto new record, paste record

This may be an alternative answer to your question, I believe? It simply populates the new record with the values from the record on which you are when you push the "Duplicate" button.

I had forgotten about the duplicate button. Anyway, your post gave me an idea. Beginner mentioned that a user wanted the ability to make it happen with a tab. Beginner can, using the autocode generated by the duplicate button or writing an alternate procedure, use the OnCurrent Event of the form to get the desired behavior (as long as the tabbing is not set to stay on the same record.)

[beginner]

hi MD, antonin & xCav8r,

tks for all your adv. but i believe need some time to figure out myself how to implement and get used to the relating coding first etc., will be back....

BR/
beginner

[beginner]

Since you're doing this on an Access form, you cannot use the .clear method, because it isn't supported. Although I had a hard time understanding what you're trying to do, I think you want to make .RowSource = "SELECT blahblah FROM blahblah WHERE combobox1.value = x".


hi xCav8r,

your suggestion help me just using one query line to achieve what i want to do ! Amazing....!!

Now i don't need to index the whole table record to filter the desired values and add into the comboBox that seems a very silly method comparing to use "query + .RowSource", although i need quite a while to figure out how to incorporate variables inside the query but now it worked. Tks !!

BR/
beginner

[xCav8r]

Here's an example with three different types of fields: text, date/time, and number.

Sub MyComboBoxQueryRowSourceThingee()

Dim strFirstName As String
Dim datBirthDate As Date
Dim intFavoriteInteger As Integer
Dim strSQL As String

strFirstName = "Marco"
datBirthDate = #6/28/2005# 'Note this is American format: Month/Day/Year
intFavoriteInteger = 19

strSQL = "SELECT peeps.strFirstName, peeps.strLastName, peeps.datBirthDate," _
& " peeps.intFavoriteInteger FROM tblPeople AS peeps" _
& " WHERE peeps.strFirstName='" & strFirstName & "'" _
& " AND peeps.datBirthDate=#" & datBirthDate & "#" _
& " AND peeps.intFavoriteInteger=" & intFavoriteInteger & ";"

Debug.Print strSQL
End Sub

SELECT peeps.strFirstName, peeps.strLastName, peeps.datBirthDate, peeps.intFavoriteInteger FROM tblPeople AS peeps WHERE peeps.strFirstName='Marco' AND peeps.datBirthDate=#05/06/28# AND peeps.intFavoriteInteger=19;

[beginner]

Tks for the useful samples and my problem about combo-box was solved, tks !!

[xCav8r]

Here's another sample function you could use (and create something similar for dates and times) to make your life easier when putting SQL strings together...

Function EnquoteString(strText As String) As String
Enquote = "'" & strText & "'"
End Function


Which would be used like so...






Sub MyComboBoxQueryRowSourceThingee()



Dim strFirstName As String
Dim datBirthDate As Date
Dim intFavoriteInteger As Integer
Dim strSQL As String

strFirstName = "Marco"
datBirthDate = #6/28/2005# 'Note this is American format: Month/Day/Year
intFavoriteInteger = 19

strSQL = "SELECT peeps.strFirstName, peeps.strLastName, peeps.datBirthDate," _
& " peeps.intFavoriteInteger FROM tblPeople AS peeps" _
& " WHERE peeps.strFirstName=EnquoteString(strFirstName) _
& " AND peeps.datBirthDate=#" & datBirthDate & "#" _
& " AND peeps.intFavoriteInteger=" & intFavoriteInteger & ";"

Debug.Print strSQL
End Sub




I find using functions like this improves readability, and more importantly, it reduces mistakes.

[beginner]

hi xCav8r, tks for your good suggestion.

However, i encountered another related headache, i use a sub-form inside a form (Form_Labor) to display portion of a myTable's records inside a recordset (myRst) after input a field variable (Combo_ID.Text) but the sub-form seems didn't reflash after the variable accepted thro' a inputbox and still show all records of myTable. (what i want is the sub-form content to display only selected portion of myTable's records thro' an input varilable.)

major coding as below.:

while the subForm's DataSource object property was point to the object (myTable).
(i am not quite sure if i using coding still need point this property to myTable ?? also this property seems didn't accept any variable inside??.)

Any adv. is very much appreciated. !!


'
' declare
'
''''''''''''''''' below is a single line ''''
Set myRst = myDb.OpenRecordset("SELECT * FROM myTable WHERE myTable.BADGE_NO = '" & Combo_ID.Text & "'")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
myRst.MoveLast
db1.[Form_Labor].Refresh
'
'
'


The above coding seems still work for record writing as the record inside myTable was updated but just i cannot display the last records of those selected recordset.

BR/
beginner

[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.?!


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



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