Posts by johnny001

    Re: Combobox List Lost On Closing Workbook

    Something still not right, please see code below, which is generating a 438 run time error

    Re: Combobox List Lost On Closing Workbook

    Hi Dave,

    Thanks for your suggestion. Populating the combobox from workbook_open event works for initial entries, but I would like to save entries generated while workbook is open. Do you have another way to do this?

    Re: Combobox List Lost On Closing Workbook

    Thanks for the quick reply. Is there a solution that doesn't involve storing the entries? The combobox values I'd like to keep are generated with a before_save event, and the entries are variable (username + timestamp)


    Hello old freinds, Simple combobox query:

    step 1. open excel, control toolbar, add new combobox (named combobox1)
    step 2. create new module, with the following code:

    sub test
    with sheets(1).combobox1
        .AddItem "123"
    end with
    end sub

    step 3. run macro "test", combobox1 should be populated, save and close file.
    step 4. re-open file, drop down on combobox1, no values to select from.

    What am i missing here?

    Re: apostrophe error in macro [solved]

    I am not sure if that's possbile since i'm calling the field, and not a specific record.

    I solved this by using the following code:

    Re: apostrophe error in macro

    hey jva, cheers for the reply, i tried what u suggested, and moved it from a conditional macro to VBA.

    i now have the following code:

    DLookup("[invoiceno]", "[invoice log table]", "[invoiceno] = " & Chr$(34) & [Forms]![Invoice Log Table 4]![InvoiceNo] & Chr$(34) & " and [Supplier Name]= " & Chr$(34) & [Forms]![Invoice Log Table 4]![Supplier Name] & Chr$(34))

    the code returns a blank supplier name if the supplier name has an apostrophe, and it crashes if the supplier name doesn't contain an apostrophe.... :confused:
    any ideas?


    I have a conditional macro that I use to check data entered on a form [invoice log table 4]. My checking looks at the supplier name [supplier name], and invoice number [invoiceno] fields, in a table [invoice log table], to see if the invoice already exists, and should thus not be entered again.
    I'm doing this checking with the following bit of code as the condition

    DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = '" & [Forms]![Invoice Log Table 4]![InvoiceNO] & "'" & " AND [Supplier Name]= '" & [Forms]![Invoice Log Table 4]![Supplier Name] & "'") Is Not Null

    This code works fine until the supplier name has an apostrophe in it, at which point an syntax error message pops up. I've tried to use 4 double quotes ("""") in front of and after the supplier name, which worked for the VBA part, but it doesnt seem to work here.

    Looking forward to your bright ideas and thanks in advance.

    Re: SQL error

    Thanks Shark,

    I'm not exactly sure to what you mean, fairly new at access. Can you plz clarify where the Aggregate Function is changed?


    I am trying to count the number of unique invoices using the following SQL in a query.

    SELECT Count([InvoiceNo]) AS Uniques, [Invoice Log Table].[Batch Number]
    FROM [Invoice Log Table] INNER JOIN [IFIS Receipt] ON [Invoice Log Table].ID = [IFIS Receipt].InvoiceID
    WHERE ((([Invoice Log Table].[Batch Number]) Is Not Null));

    I get the following error message:
    You tried to execute a query that does not include the specified expression Batch Number as part of an aggregate function. (Error 3122)

    Any help much appreciated

    Re: Find Record

    Not too familiar w/ ADO, but still thinking about a possible solution. Will work on this more later. Cheers Carlmack.

    Hi all,

    I have a macro that I've copied straight off the microsoft website. The code checks the invoice number entered on a form to see if it already exists in the underlying table, thus to avoid duplication. The macro is called by the "before update" event of the invoice number. I have tried to modifty this code to look at 2 fields, that is the invoice number AND the supplier name. All fields are text fields.

    Attached the first bit that works. This is placed under the "condition" column on the macro design screen, having MSGBOX as the action.

    DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = Form.[InvoiceNO] ") Is Not Null

    And i've tried to add the 2nd field but this is no longer doing any validation. I've also moved the macro to the supplier name "before update" event, as the invoice number gets entered first.

    DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = '" & Forms![Invoice Log Table 4]![InvoiceNO]  & "'" & " AND [Supplier Name]= '" & Forms![Invoice Log Table 4]![Supplier Name] & "'") Is Not Null

    Re: Find Record

    Did the trick :) thanks a lot carlmack. Any idea on how to bypass showing blank records when it doesn't find a matching record?


    I have a form which allows a user to search for invoices by supplier/number or by username. Both searches are run from a command button, and the code can for both can be found below

    this one has the following error message: Runtime error 3075, missing operator

    this one has the "missing operator" error, but w/o the 3075 code. All values being searched are text.
    Also, if no match is found, how can i put in a MSG BOX to state that?

    Cheers for all ur help in advance.


    Re: auto populate list-box on form

    hi guys

    i have used this bit of code, and it sort of has the right effect:

    Private Sub Send_To_AfterUpdate()
    End Sub

    The only problem is that i have to click the list box selection, after which i have to click it again to update.......

    Is there a way to make it auto refresh?

    Hi All

    I have a form where the user enters a cost centre. Depending on the cost centre selected, I want the "approver" list box to get auto populated with the appropriate approver for that cost centre. There is a table in the db that has an approver for each cost centre.

    If anyone can suggest a solution to having the approver populate the list box as soon as a cost centre is selected, I'll be vry thankful




    In your attachment, you show the formulas used to re-format the data.

    How did u get the info from columns D:F to look like the ones in G:I. Pasting values would yield differnet results.....did u manually erase records?



    In response to R Hind, the format of the data has not been created by me. it is a Lotus notes extract, and I cannot change the way it appears.
    There are gaps, because that's how the Lotus database holds the records, which I cannot fix, and I'm sure the code would be quite messy. Also the 5 days column, can bea easily converted to a number, using morefunc, a quick and simple wmid can get the required results. I left it as is because i was hoping the answer would be provided via formulas

    I looked at the revised pivot table and it is correct, however now i am not sure what to do with it since its not possible for me to reformat the 6000+ records.

    So now my question again is, can a summary of each workstation be provided via formulas? I have tried to play around with filters and countifs to get the number of staff names between stations, but I have nothing concrete yet.
    Are there any other ideas out there?