Posts by Divbad

    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)


    :)