Posts by cw_lynn

  • Enter ODBC ID and Pass in Macro

    Re: Enter ODBC ID and Pass in Macro


    Okay, I found the .dsn but it only had admin (which is not the userid I use for the connection) for uid...no password section. I think the answer is some kind of code that I can incorporate into the macro. If this were an Excel issue, I could do it, but I don't know how to input code into Access.


    :confused:


    Thanks again for your help.

  • Enter ODBC ID and Pass in Macro

    Re: Enter ODBC ID and Pass in Macro


    Carl,


    Thanks for the reply. I've checked with MIS and we can't use windows authentication with this server. It is sql server 2000. The command is OpenQuery to run a make table and the issue occurs randomly when the query attempts to hit the tables. I'm not sure how to look inside the dsn file. I hope that provides a bit more clarity.


    Thanks.


    Chris

  • Enter ODBC ID and Pass in Macro

    I have an Access Macro (not vba, just standard macro) that runs a series of Make Table Queries each night. An ODBC connection is used to link to the tables. Occasionally when I run the queries, a box pops up asking for my username and password for the ODBC connection. This hangs up my macro. Is there a way through the macro to enter that information? Keep in mind that it doesn't always ask at the same spot. I think it depends on the last time I accessed the particular table. Thanks for any suggestions.


    Chris

  • Removing Blank Spaces in Query Result

    I have a query that pulls in unit numbers from a table. The unit numbers have blanks (ie: AR_ 4819 ). I would like to get rid of all those blanks through the query (ie: AR_4819). Is this possible?


    Thanks for any suggestions,


    Chris

  • Query with Multiple <>

    I am attempting to write a query to exclude multiple items in a table. I would expect it to look something like this:


    <> "10005" or "10007"


    When I try this or any number of variations, I get the first criteria excluded but not the second, third, etc.


    Thanks for any input.


    Chris

  • VBA Unlock Cells Depending on Cell Color

    I plan on creating a large spreadsheet that will be placed on Citrix and accessed by many different users. Each person that uses the spreadsheet has different responsibilities and will need to fill in various pieces of information. I would like to be able to recognize who logs in and unlock only the cells for which that person is responsible. Each cell that needs an input would be color coated (ie: marketing would be green, sales would be blue, etc). I envisioned a code that, upon opening, would ask for a password and then unlock only the green cells if marketing logged on (so marketing doesn't have access to adjust sales fields). Is this possible? If so, what is the code for specifying an action if a cell is a certain color?


    Thanks for any assistance.


    Chris

  • Auto Placement of Images on a Chart

    Re: Auto Placement of Images on a Chart


    The bar chart updates based on data added weekly. The image needs to be added the end of the bar (or just to the right of the end) on the graph. So if one week the bar reaches the value of 50, the image would be just to the right of the bar. If the bar adjusts to the value of 20 next week, the image needs to move to follow the new position of the bar.


    I have an excel addin that can do this to some degree, but it is not an ideal solution.

  • Auto Placement of Images on a Chart

    I am creating a spreadsheet that updates a bar chart once a week. I have one image that I would like to place at the end of each bar automatically after the chart has updated. Is this possible?


    Thanks for any ideas!


    Chris

  • Key Violation -- Append Query Error

    Let me start by saying that I'm not good at Access...I prefer Excel, therefore this may be a dumb question...


    I am attempting to load data to a table through an append query, but I keep getting an error stating that Access didn't add 21 records due to a "key violation error". There is Primary Key in the table, but the 21 records that I am attempting to append are all unique. Is there some easy fix that anyone can think of??? :yikes:


    Thanks for any assistance.


    Chris

  • Place "" In VBA Generated Formula

    I am attempting to use VBA to place the following formula in a cell:


    =sumif(A:A,(cell two columns to the left and one row up),C:C)


    The code I'm using is:


    ActiveCell.Formula = _
    "=SUMIF(A:A," & Range("A" & x - 1) & ",C:C)"


    This problem is that the search criteria is a date and needs "" around it. The formula I am generating at this point is:


    =SUMIF(A:A,10/1/2004,C:C)


    It needs to be:


    =SUMIF(A:A,"10/1/2004",C:C)


    If I try to use """ in VBA, I get an error. How do you place quotation marks in a formula???


    Thanks.


    Chris

  • Linking Macro to Drop Down Box

    The range is populated by an SQL query tied to a data tag located on the spreadsheet. For example, the data tag would be located in cell A2 (%%=LIST.NAMES). Before the spreadsheet is opened, the data is populated in cell a2 down to cell A whatever, apparently by inserting rows. For this reason, I made my named range A2:A3, so that it would expand as rows are inserted.