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

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

[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