Add new name to list box??? 1

[scrappe7]

i have a worksheet that contains links to another xls file called dummy. this dummy file is used to hold exports from an access query that gives me unique names in a database. Then the dummy file is used as the source file for a column on my DATA ENTRY sheet

each cell in the column of DATA ENTRY looks like this ( ='G:\New Ideas\[dummy.xls]Sorted Names For Excel'!$A2)

The problem here is that if a new name that is not already on the database needs to be entered it can't be because a list box is there. The list box has the names that appear on the dummy file from Access. How can i make it easy to add a new name to the list, while still allowing the user to select from the list? I was thinking an update query but it is not installed on our machines. Anyone know a way to solve this stumper. If you need more info just ask, thanks alot.

 

[tomschafer]

Have you tried writing the SQL for an update query, it should work. I believe you just don't have the wizards installed to do it graphically.

 

[DaleWatson123321]

Hi scrappe7,

I have a similar application. The main exception, which might be the solution to your objective, is as follows:

My Access data is first exported to a "dummy" file, but then I have that data automatically imported into the "data-input" Excel file with a "Workbook Open" routine - so it is imported whenever the file is opened.

With the data being imported into the "data input" file, it enables me to give access to the end-user regarding their "manual" input of new items to the list(s). If the user wants to add new item(s), the user is directed to the sheet containing the lists, with instructions to enter the new name(s) at the bottom of the existing list(s). The list ranges are set to accommodate the addition of new items.

I hope this helps. Please let me know.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[scrappe7]

Thanks for the help Dale. What i ended up doing was a little different because the dummy file has a list of unique names from access. It is linked to my MAIN excel file and used as a range for a list box in the data entry section. I wrote a macro and assigned a button to the column that displays a prompt for the user and asks them to enter in the new name not already on the list. The macro format the name into F. Last. When this MAIN file gets imported into access the name becomes a unique name. so, the next time you open the MAIN file the dummy file already has that new name on it and htere is no need to run the macro again. I think the prompt works better becuase the user does not have to see the list of names nor find where to input it. The type in the display box and it gets entered in there perfectly fine. Thanks alot for your suggestion though. Let me know if mine may be of an use to you. I can show you the code if you need it.

Ed

 

[DaleWatson123321]

Hi Ed,

Thanks for the offer of your code. At the moment, I'm not pressed to solve any problems where I could use it. So, I would suggest that IF it is NOT a large task to forward the code, I would appreciate it for future use and reference. If it is not too large you could post it (for my benefit and others), or if too large, you might want to email it to me at: dwatson@bsi.gov.mb.ca.

A minor point regarding my original suggestion: The key advantage of my method, is that the user can "instantly" add a name or names to the list(s). An option could be to do as you described, i.e. add a button, with code which would prompt the user for the name(s), and add the name to the list. Then, as you pointed out, the user would never gain access to the list(s), BUT they would have the benefit of IMMEDIATE additons to the lists. As I said, this IMMEDIATE additon option could be a "minor" consideration, but it is an option.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca