Posts by Divbad

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    dim ctl as control, booX as boolean
    booX = myform.chk_HideControls '(implied .value = true)
    for each ctl in myform.controls
    if left(ctl.name,4) = "hide" then ctl.visible = booX
    next ctl


    This can get more fun by using numbers (intX instead of booX) and select case intX:


    case 1 'hide all but 1 control
    if left(ctl.name, 4) = "hid1" ...


    case 2 'hide a set of controls
    if left(ctl.name,3) = "hid" ...


    case 3 'hide only checkboxes
    if ctl.controltype = acCheckBox and left(ctl.name,1) = "h" then...


    Have fun!

    Some thoughts:


    tbl_Drivers, tbl_Trucks - easy enough.


    tbl_DriversTrucks - a historical record of who / where with sign in & sign out columns.


    select free trucks: select * from tbl_Trucks inner join tbl_DriversTrucks where tbl_DriversTrucks.SignedBack is null


    select free drivers - as above


    To assign a driver to a truck, use a form to pick from the two select free ~ queries and a bit of code to update signedback field, then append driverID, truckID and signedout as new record.


    This avoids overlapping truck / driver assignments.


    You could also select busy truck, select busy driver the same way.

    Yep: from Excel, Data > Import External Data > New database Query


    Point to the query that you want to analyze, so excel selects * from your access query.


    Save the query, and decide whether you want it to refresh automatically etc.


    Analyze all you like; save the results on a new worksheet or name the range.


    Back to access: new table, link, excel, your workbook, the range / worksheet - and Bob's your uncle.

    I assume you are selecting DISTINCT or DISTINCTROW table1 inner join table2.


    [Account # | Holder] inner join [Account # | Date of Transaction | Amount ] will give you:


    -------------------------------------------
    |Acc#|Holder---Acc#|Date|Amount|
    -------------------------------------------
    |1 | John ----- 1 | 1/1/04 | 200.00|
    |1 | John ----- 1 | 2/1/04 | -31.50|
    |1 | John ----- 1 | 4/1/04 | 151.00|
    -------------------------------------------


    Is this what you are getting?


    The "duplicate" is from table1 - AccountHolders - matching several records in table2 - AccountTransactions.


    If you want to see:


    -------------------------------------------
    |Acc#|Holder---Acc#|Date|Amount|
    -------------------------------------------
    |1 | John ----- 1 | 1/1/04 | 200.00|
    | 1 | 2/1/04 | 31.50|
    | 1 | 4/1/04 | 151.00|
    -------------------------------------------


    you can create a report with a group header containing AccountHolder details, and the report Detail showing the transactions. Report wizard can do the fundamentals of this for you.

    tbl_Cars: carID, Name, Model, Colour
    tbl_Safety: carID, Airbag1, Airbag2, Seatbelts
    tbl_Interior: carID, CD, etc


    Report on select Car inner join on carID


    ...


    (OR:) tbl_AllMyData: carID, safety1, safety2, safety3, interior1 etc


    Report Header: Basic Safety Interior
    Report Detail: safety1, safety2, safety3, interior1 etc

    DoCmd.SendObject , , , DLookup("[LookupValue]", "tbl_Lookup", "[LookupName] = 'Mailto'"), , , "ERROR REPORT", "I have found a bug!", True


    This sends no object to the email address which is stored in a multi-list table as "MAILTO". Edit the mail before sending is "True".


    F1 for help...

    Quote

    How can I enter a value into a form and have data entered into the other fields using a seperate table. Same as a VLOOKUP in Excel.


    To populate this form you could bind the form to your table and use a combo box for the index, e.g.


    cbo_Index: Jan, Feb, Mar, Apr, May


    tbl_MonthlyData: Jan | Cold & Windy, Feb | Cold & Wet, Mar | Windy & Wet, Apr | Windy, May | Spring! etc.


    AfterUpdate() event for the cbo_Index sets the Filter for the form. The combo-box wizard can do this for you.


    This form would then be a lookup for the whole of your table, and you could use VBA or an append query (forms!myform!field1) to populate your "inputs" table.


    But would you want to copy a whole line from the table? Would it make more sense to store only the index (January) and join to table1 only when you need to see the indexed data (Cold & Windy)


    :)