General discussion

Locked

Put an Open box in an Access macro

By msoldan ·
What is the best way to place an Open dialog box in an Access 2000 macro? I would like for this box to come up and the user can select a file, instead of having to tell it the file location in the macro itself. Thanks.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by CptOmlly In reply to Put an Open box in an Acc ...

When you say file, are you talking about a record in the database? I am thinking that you want a query to select a certain record, but you would rather use a listbox/combobox to select it rather than just typing it. I would recommend doing this with a function rather than a macro.

You will have to create a small form with a combo/listbox bound to the key field in the table you want to pull from. Then you will need to create a function that returns the value selected in the combo box. Finally, you will want to create a query that uses the function as a criteria for that key filed. When all this runs, you will launch the small form, select the record in the listbox/combobox, click a button on the form, which launches the query. The query will call the function that will pull the record from the form. Complicated enough?

Here's an simple example. You've got a table called tblEmployees with primary key of SSN in column one of the table. You create frmEmployeeSelect with lstSSN. You want to set lstSSN properties (no control source, Row Source Type=Table/Query, Row Source=tblEmployees, Bound to Column=1). You will also have a command button on the form that has an onClick event that launches the query (DoCmd.OpenQuery "qryEmployee"). Now you need to create the query to pull the data as needed, but for the tblEmployees SSN field you will enter FindEmployee() as the criteria. Finally, you will need to create the module for the FindEmployee function. Create a new module and enter this code:
Function FindEmployee()
FindEmployee = Forms!frmEmployeeSelect!lstSSN
End Function

Thats it, you will have to decide what you want to do with the data after you run the query.

Hope this helps. Feel free to email me through Peer Directory if you have any questions on how to make this work with your specific application. If I?ve totally missed the mark on what you are trying to do, post some more details.

Collapse -

by msoldan In reply to

The answer was great, however it was not what I was looking for. I appreciate the attempt though.

Collapse -

by msoldan In reply to Put an Open box in an Acc ...

I'm sorry. I didn't explain it correctly. I have a text file that I am trying to import into a table. I am trying to create a macro to do this for me. In the design view of the macro, you have to tell it the file location. I would rather that it brings up an Open box so that I can find and choose the file. Is there a command I can put in, in the design field or if I convert this to VBA, could I put this in the code? Thanks.

Collapse -

by CptOmlly In reply to Put an Open box in an Acc ...

Well... You sure don't make it easy on a guy to get some points!! Just kidding... but I do have another approach, given the clarification. Go to this page: http://www.meadinkent.co.uk/aopendlg.htm.

You are going to want a new module with ALL the code in RED section, GREEN section and BLUE Section. Just copy and paste. The blue section is the code for an ImportFile() function that uses the rest of the code to handle the windows common dialog box to browse/select the file, then runs the VBA TextTransfer function. This seems to be EXACTLY what you need.

I tested the RED and GREEN sections, but not the actual ImportFile() function. This code is really awesome and is built to be reused, so you may want explore it a little and see what it can do. (NOTE: I find it much easier to read once pasted into the VBA Editor, because it color codes it and breaks it up better.)

Also, this is noted on the page, but its important: You will have to define an file import specification BEFORE you can use the ImportFile() function. In this code its called "Temp_Text", but you can of course edit the code as necessary to accomodate your own spec name. You can also change the line FileSpec = OpenTextFile("D:\Download\") to what ever directory you want to default to when called. If you have any syntax issues with the ImportText() function, check VB help (accessed from within the VBA editor) for the TransferText method, and edit as necessary.

SO.... with all this code, all you need to do now is create a command button and an OnClick event to call ImportFile() and you should be set!!!!

Collapse -

by msoldan In reply to

This was EXACTLY what I was looking for. Thanks. I increased the points since I wasn't very clear in the explanation. Thanks again.

Collapse -

by msoldan In reply to Put an Open box in an Acc ...

Point value changed by question poster.

Collapse -

by msoldan In reply to Put an Open box in an Acc ...

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums