Posts by Om Avataar

    I have a simple 3-column table (let's call it 'tblFactors' with columns 'ID', 'Date' and 'Factor') in MS Access 2002, which is populated with say exactly 100 records.

    Each day, i receive an MS Excel spreadsheet file, whose last worksheet has been specifically set up in the exact same format as my 'tblFactors' Access table. On a daily basis, I need to be able to append the records in this last Excel worksheet to 'tblFactors'.

    How do I achive this using VBA, either Excel VBA or Access VBA? Plse help... Many thanks in advance.

    I have a simple tblAcctsRcvable table in Access 2002, with 3 fields: [CustomerID, Date, Outstanding Balance]. Say I have just 6 records in tblAcctsRcvable as follow:

    [CustomerID, Date, Outstanding Balance]
    [1, 31/12/2006, 45678.95]
    [1, 31/1/2007, 55700.95]
    [1, 28/2/2007, 39098.95]
    [2, 31/12/2006, 145678.95]
    [2, 31/1/2007, 105212.95]
    [2, 28/2/2007, 125434.95]

    I need to create a simple query table {call it qryLatestAcctsRcvable} which filters just the LATEST record for each CustomerID. So from the above tblAcctsRcvable, the query result table will show just 2 records:

    [CustomerID, Date, Outstanding Balance]
    [1, 28/2/2007, 39098.95]
    [2, 28/2/2007, 125434.95]

    What is/are the criteria formula(s) I need to put in the query design grid for my qryLatestAcctsRcvable to achieve this? Thanks in advance.

    Hi Access VBA Gurus,

    I am a beginner to Access 2002, let alone Access VBA. I have a quick question. Attached is a database of just a single, sample 'Month-End Outstanding Accounts' table. There are 3 customer ID's, and each month I need to INSERT end-of-month balances into this table at the respective, logical slots (provided end-of-month balances for a given customer are positive).

    Assume CustomerID 2 has a balance of $150,000.95 as at 30/06/2006, while CustomerID 1 and 3 each have zero balances as at this same date. I will thus need to insert just a balance of 150000.95 as a new record for CustomerID 2, with Date field entry as 30/06/2006, as record number 10 of 12 in the attached 'AcctRcvLog' table.

    How do I do this? Note that each month-end 'source data' is contained in an Excel spreadsheet with a 3-column structure similar to the attached 'AcctRcvLog' table. So, the Excel source file dated 30Jun06 will have just a 2-row table, with first row being a header row with entries [CustomerID, Date, Amount O/S], and second row being [2, 30/06/2006, $150,000.95]. Please note that as balances for CustomerID 1 and 3 as at 30/06/2006 are zero each, these do not appear in the Excel source data file.

    If anyone could advise, this humble Access VBA newbie would be eternally grateful.

    Thanks a million in advance.


    Hi Access VBA Gurus,

    Till now, in Access forms, I have used just standard Textbox controls as control sources for fields in an underlying Table or Query. I would now like to to use a combo-box as a control source instead, so that I can restrict user input in a field to entries in a list of 5 valid or 'acceptable' values. How do I do this?

    I mean, what do I put in the Combo-box's ControlSource and RowSource properties? Just in case it helps, my Combo-box is to act as a control for the 'Customer Type' field in qryCustomer, and I have a simple 'lookup' table which lists the 5 valid 'Customer Type' values.

    Hope my question makes sense... And a million thanks in advance to anyone/everyone who can advise me on this.


    Hi Access VBA Gurus,

    I currently have a fully functional SQL Select statement (as shown below) in the RowSource property of an UNbound ComboBox in an Access 2002 form:

    SELECT DISTINCTROW qryKeyAgentAddEditForm.[Key Agent] FROM qryKeyAgentAddEditForm WHERE qryKeyAgentAddEditForm.[Key Agent Type]=cboKeyAgentType.Value ORDER BY qryKeyAgentAddEditForm.[Key Agent];

    I need to put in a conditional IF statement to the following effect:

    IF condition1 Then
    SELECT DISTINCTROW qryKeyAgentAddEditForm.[Key Agent] FROM qryKeyAgentAddEditForm ORDER BY qryKeyAgentAddEditForm.[Key Agent];
    SELECT DISTINCTROW qryKeyAgentAddEditForm.[Key Agent] FROM qryKeyAgentAddEditForm WHERE qryKeyAgentAddEditForm.[Key Agent Type]=cboKeyAgentType.Value ORDER BY qryKeyAgentAddEditForm.[Key Agent];

    Is such a conditional IF condition valid as the RowSource property of an UNBound combo-box in Access VBA?

    Many thanks in advance to anyone who can help/advise

    Hi all,

    I just need my VBA code to uniquely filter a column of values (Cells E7:E65536) in Sheet1, and to paste the filtered results into column B (starting at cell B3) in worksheet Sheet4 in the same Excel workbook. Will the code below work?

    More importantly, does the AdvancedFilter method allow for the filtered results to be deposited into another worksheet within the same workbook?

    Many thanks in advance for any help/advice

    Worksheets("Sheet1").Range("E7:E65536").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Worksheets("Sheet4").Range("B3"), _

    My macro involves iterating thru' a loop close to 500 times, and each iteration involves some movement back and forth between Sheet1 and Sheet2. Thus I have set 'Application.ScreenUpdating = False' just before the start of the iteration to prevent screen flicker and hopefully to speed up the iteration process.

    However, altho' the screen thus appears 'frozen' during the iterative process, I would like a real-time 'status update' box to appear at the start of the iteration; something like: 'Currently Running Iteration 24 of 500', where '24' is the current value of the loop counter within the macro code; next loop increments this to '25', etc.

    If anyone has done something similar, pls advise with code. Thanks in advance

    My code below goes into an infinite loop, with culprit line shown in bold and underline. Please help identify error in my code logic. Thank you

    Re: Paste By Value

    Would this work? I mean, is it correct - syntaxically - speaking?

    Worksheets("Sheet1").Range("A5:A25").Copy Destination:=Worksheets("Sheet2").Range("A5").PasteSpecial(xlPasteValues)

    When using

    Worksheets("Sheet1").Range("A5:A25").Copy Destination:=Worksheets("Sheet2").Range("A5")

    is there a way to paste by Value into the destination cells? I believe that as is, the code above defaults to 'normal' paste mode (which also pastes formats).

    How about formulas? Does the code shown above paste formulas behind cells in the source range into the destination range as well? Because I cannot afford for my code to paste formulas - I need the code to just paste by values, period!

    Any help greatly appreciated. Thanks

    Re: Range(activecell, Activecell.end(xlright)).copy

    Hi Andy,

    So hypothetically, if my current Excel active cell is AM5, and I need to copy ALL cells from AM5 to IV5 (Column IV is last column in any Excel worksheet, and let's assume there's a non-blank value in cell IV5 in this example while cell AM5 is empty, and several random cells between AM5 and IV5 are also empty),

    Range(ActiveCell, ActiveCell.EntireRow.Cells(1, Columns.Count).End(xlToLeft)).copy

    will still work as is? Note that cells A5 to AL5 are all filled with different types of info, but I need to copy only cell AM5 and all cells to its right (incl. the last filled cell in the row)

    Thanks in advance

    Range(activecell, Activecell.end(xltoright)).copy

    I need a variation of the code

    Range(ActiveCell, ActiveCell.End(xlToRight)).Copy

    that will effectively allow me to copy the current active cell and ALL cells to its right up to and including the LAST non-blank cell in that particular row - pls note that any cell (incl. ActiveCell) in the range (ActiveCell, ActiveCell.End(xlToRight)) may be blank/empty, which is why the above code, as is, will not work.

    Note that the current active cell is not fixed either in terms of its row number or its column number, so that ActiveCell could just as easily be D46 as AB7. Thanks.