[Slimboy Fat]

Using Access XP

Is it possible to run a query on a list of records?

Example: Our database using vehicle registration numbers (VRM). I want a form where I can paste a list of VRMs then run a query on each VRM.

Something along the lines of...

If VRM={1st line of list} then FIELD2=Yes
Repeat for the following lines until the end of the list.


TIA
SlimboyFat

[OBP]

Hi SBF, yes of course you can do that, although where you "paste" the list is problematic, it would probably be best in a Temporary table.
Is it possible to "Import" the list?
Can you provide and example of the data that is being pasted?

[Slimboy Fat]

This list is just in a standard text file and is a list of Car Registration Numbers, not wanting to use "real" data it would be along the lines of...

AA51 AAA
BB02 BBB
C333 CCC


etc

[OBP]

OK, is the text file comma delimited?
Once we establish the file type it can be Imported directly in to a temporary Access table, run the query and then delete the contents of the table.

[Slimboy Fat]

It's not comma delimited just 1 VRM per line.

Thanks

[OBP]

Can you email me a copy of the file?
If not have you tried pasting the data in to a table?

[Slimboy Fat]

Hi,

I can import the txt file into a table using the File \ Get External Data option.

I can email you the list if needed but it is just a txt file with a list of Registration Numbers.


FYI: The original query I stated in the original post was simplified, I am then planning on editing the code to do exactly what is required but I will try to explain the full process...
Each record in the DB represents one car that we are dealing with. One of the jobs is to scan and email a document to the clients. This scanned image is named as the registration number. We have a batch file that creates the text file listing the registration numbers that we are dealing with (from the image file names) in that batch.
This is mainly used to copy and paste the list into the email sent to the client.
The problem now is that the gaffer wants us to go into each record and add a note to a field we have called "Internal Comments" to state that the document has been sent.
Was hoping that instead of finding each record individually we could use the txt file to update them all at once.

Sorry for the rant. Hope this explains everything.


TIA

[OBP]

If you can import the file in to a temporary table we can do the rest with a Query and some VBA including sending the email if you want.
I have just posted the VBA for emailing to multiple recipients.
Just create a query on your main Customer table, add the temporary table to it and then Join them via the VRM.
Would you like to do that?
I can assist you with this if you would like to send me a zipped copy of the database in Access 2000 - 2003 format with a few dummy records in the Customer table and a couple of matching records in the Imported table.
Do you send them stuff by snail mail as well?

[Slimboy Fat]

Thanks for your help.

Sending the email is not a problem as I already have a template in Outlook, just a case of copy and paste the list, attach the files and click send.

The DB we have is large (and to be honest it's a mess) so it would be easier if I create a new "test" DB with just the fields we need and send you that. Is this OK.

[OBP]

That would be fine.

[Slimboy Fat]

Hi,

Sorry for the delay (suddenly got busy here )

Attached the temp DB.

TIA

[OBP]

Enter the required text in to the Form's Internal Comment text box, it saves it to a new "Comment" table and then updates the AuditTrail table's required Record's Internal Comment using the new query.

Are you sure that you don't want the VBA to compile the Recipients and put the Comment in the email body and send the email or let you check and then send the email?

[Slimboy Fat]

That looks like what we need, will implement it into the live DB.

no need for the email, as I said the email is such an easy job once I have got the text file (which I need to create the table) and is sent before the database side of it.

Thanks a lot

[Slimboy Fat]

Not sure whether this should be a new thread...

As a side problem, is it possible to get queries to ignore spaces when finding records. Some of our clients now insist on sending the registration numbers without spaces and due to the DB "talking back" to their system we cant change them.

In the case of the procedure above the list is always without spaces but the records could be saved either way.

Would also be great if the "Find Record" button on the DB could work the same way.

Please note: As stated above it is not an option to trim the field in all records to remove the spaces.

eg. A "Query" / "Find Record" searching for AA11AAA would find the record whether the field was saved as AA11AAA or AA11 AAA.

TIA

[OBP]

I always use a Find Record Combo for finding Records on Form so that would work for both formats.
The query will depend on whether or not you are using a Form for the Query's Search Criteria. If you are you could have a second field on the form which VBA puts the second format in to. The have the Criteria lokk for Field1 Or Field2.