How can I enter several lines of text as a criterion and return several lines from my table?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KitKat - 21 Dec 2005 15:52 GMT

Right now at work, I get forms e-mailed to me in an Excel chart with a
serial number in one column and a new expiration date for a device's
trial period in another column.

For a while, when people only sent forms with one or two lines, I
didn't mind making a simple parameter query that searched for a serial
number (ESN) and showed me the relevant line of my office's tracking
database so I could change the date, etc.

Here's how it looks:
http://klg.cps.com/temp/googlegroups/accesscurrent1.gif
http://klg.cps.com/temp/googlegroups/accesscurrent2.gif

However, when I get 6-20 requests on the same form, it would be really
nice to just hilight the serial number column from the Excel form I
received...
http://klg.cps.com/temp/googlegroups/extensionrequest.gif
...paste it into a form or something in Access, and have a query pop up
that shows the relevant lines of my database for all of those serial
numbers.

I have searched every keyword on Google Groups and Google that I can
think of. I've looked in reference manuals. I'm just plain stuck and
can't figure out how to do this.

If I'm asking in the wrong place, please redirect me. Thank you.

-KitKat

Reply to this Message

salad - 21 Dec 2005 16:40 GMT

> Right now at work, I get forms e-mailed to me in an Excel chart with a
> serial number in one column and a new expiration date for a device's
[quoted text clipped - 24 lines]
>
> -KitKat

I think I were to automate your process, I'd link the Excel spreadsheet
to the table. (Look at the Connect property in help plus Google for
linking objects like a spreadsheet to the database). You can
temporarily link the spreadsheet, then remove the link at your
discretion. Or take a look at TransferSpreadsheet

Since the spreadsheet is now available for use as a table, you can write
a query on the fly to use it as the rowsource for a combo or listbox.
This way you let the user select which serial number to use.

This method would be much more professional that cutting/copying/pasting
stuff between the two apps or even using an input box to prompt for
entry into it.

Reply to this Message

Pachydermitis - 21 Dec 2005 17:06 GMT

Ok, lets say the table in the database is tData. Create a new table,
for the example we'll call it tSerial. It looks like you really only
need one column in tSerial: SerialNumber. Create e query where the
SerialNumber column in tData is linked to the SerialNumber column in
tSerial. Paste your list of serial numbers into tSerial, and open your
query.
Hope that helps

Reply to this Message

KitKat - 21 Dec 2005 18:34 GMT

Well, it almost does--when you say "linked," ummm...well, I'm not sure
how to do that. I assume it's something I type into the criteria
field.

I'll go look it up while I wait for a response.

Also, I hate clicking around. It's simply one of my computer pet
peeves. I suppose I was looking to do it with a form because I know
how to open a query upon closing a form--I found some VBA code to do
that.

I'll look this up, too, but just in case I don't find it, can you tell
me: can I set the table to open that query every time I close the
table? Oh, and maybe the query to empty out the table every time I
close it?

Reply to this Message

KitKat - 21 Dec 2005 19:41 GMT

Also, I tried this. It shows my records, just like I'd hoped, but I
can't edit any data in them.

Reply to this Message

Pachydermitis - 27 Dec 2005 20:20 GMT

To find out why Access is not letting you edit the data, start taking
apart your query piece by piece until you find which part is holding
you up.
A couple places to look are: The relationship between the tables, the
number of tables (Access will let you edit with one left join but not
two), whether or not one of the tables are linked, subqueries.
As far as automation, you can take this to the point where with a push
of a button, Access will import your data from excel, make any needed
changes and print your report. It's a little involved, but not that
hard.

Reply to this Message