Copy and paste multiple values in form

[jaryszek]

Hello Guys,

I would like to ask you about pasting a lot of values for specific columns in Access.

I have about 50 documents. In excel User is pasting "X" for each column if document exists (you can select range and paste "X" there)
How to do it in form in Access?

I can show 50 fields and user will be pasting "X" where he wants to.
But it is possible to paste more then 1 document in the same time?

Maybe using checkboxes?

Best Wishes,
Jacek

 

[Minty]

How are you listing these documents? Is it a file location on a server? Or a physical document on a shelf?

 

[jaryszek]

This documents are on a shelf.
So user has to take a briefcase with specific client and check all documents within it.
Next user should input all these documents to database. Excel is not adequate i think so it has to be Access.

Jacek

 

[Frothingslosh]

I'm pretty sure you're SOL on this one.

Excel allows you to do this with ranges because each cell is technically an individual object, and the paste is really telling excel to paste that value in each cell in the selected range.

Databases don't really work like that. At a UI level, you're always working with a single record at a time (even with an aggregate query), and only one field will ever be active at a time. (You can also select one or more records themselves in a datasheet, but none of their fields will actually have the focus until you select one of them instead.) Because of that, there is no method available to paste a value into more than one field at the same time, much less more than one record.

That said, sometimes you can fake it. We would need to know precisely what each person is doing - example, first guy 'checks' all the documents. Does he write them down? Check off a cell in excel? Tattoo them on his forehead? (I don't recommend that last one.)

Once we know precisely what your methodology is, we may be able to come up with a usable work-around.

 

[Minty]

In addition to Frothy's comments, the fact that you are saying they check the documents off, would indicate that you have an existing list of the documents that are supposed to be there?

Where does this come from?

Assuming you have a Client per Document file, the obvious thing would be to store the DocTypeID and client number in a table. Then you simply query what doc ID's don't exist against the master list of all doc types and you have your list of missing documents.

 

[jaryszek]

Thank You My Friends for your help!

I'm pretty sure you're SOL on this one.

What does it mean ?

Databases don't really work like that. At a UI level, you're always working with a single record at a time (even with an aggregate query), and only one field will ever be active at a time. (You can also select one or more records themselves in a datasheet, but none of their fields will actually have the focus until you select one of them instead.) Because of that, there is no method available to paste a value into more than one field at the same time, much less more than one record.

I was doing research about it yestarday and i know it. But i think there is certainly efficient method to do it in Access so i am looking for help here.

That said, sometimes you can fake it. We would need to know precisely what each person is doing - example, first guy 'checks' all the documents. Does he write them down? Check off a cell in excel? Tattoo them on his forehead? (I don't recommend that last one.)

Ok,
we have one master list of documents (all documents for all clients).

For each client can be different set of documents (but there are similar).

So user have 50 documents which can be attached to certain client.
User takes the client's documents (paper documets) and checks if there are all documents for client. If not, user should mark "X" in the column with lacking document name.

Best Wishes
Jacek Antek

 

[Frothingslosh]

You're still making assumptions.

You have a master list. Okay, where and on what? A notepad? A photocopied written form? A word doc someplace? An excel spreadsheet? Is this done by hand or on a computer? If a computer, is it logged into the network at the time or stand-alone?

Does each customer get its own unique documents, do they share from a standard pool, or part of both?

What other information are you planning to store?

I'll say up front that the best answer, both from a design and a user-time-spent perspective (you really want to avoid duplication of work wherever possible), is that the checking is done by someone logged into a computer with an active network connection. If not, though, there are still ways to deal with it.

There are quite a few ways you can set up a checklist in an Access database, depending on how much effort you want to put into it, how strong your programming skills are, and how easy you want FUTURE modifications to be.

 

[Minty]

@Frothy - another thread here https://www.access-programmers.co.uk/forums/showthread.php?t=293988 from the same poster, in case we start repeating each other.

 

[Frothingslosh]

Of COURSE there is.

Oh, and jarysek, I nearly forgot: As I used it, SOL is short for 'Shit Outta Luck'.

Other usages can be 'one planetary day', 'statute of limitations', 'safe operating limit', 'smile out loud', and probably a couple hundred other things.

 

[jaryszek]

@Frothy - another thread here https://www.access-programmers.co.uk/forums/showthread.php?t=293988 from the same poster, in case we start repeating each other.

Hello Minty,

sorry for my late answer. Here is different topic than there.
Here I am thinking about new model and there I have already model and i want to transform it into Access table.

SOL is short for 'Shit Outta Luck'.

Ok thanks

You're still making assumptions.

You have a master list. Okay, where and on what? A notepad? A photocopied written form? A word doc someplace? An excel spreadsheet? Is this done by hand or on a computer? If a computer, is it logged into the network at the time or stand-alone?

excel spreadsheet
Computer but with access to network drive where all files will be.

Does each customer get its own unique documents, do they share from a standard pool, or part of both?

Each client are sharing documents but sometimes they can have onyl a part of documents or more then standard pool of document.

What other information are you planning to store?

Name and surname of person who is marking the lack of documents, client's company name, for which area (HR, PR or other) it is.

There are quite a few ways you can set up a checklist in an Access database, depending on how much effort you want to put into it, how strong your programming skills are, and how easy you want FUTURE modifications to be.

with VBA there is no problem and SQL also, modifications should be easy, the Access database should be done fast

Thank you for your help,
Jacek

 

[jaryszek]

the biggest problem is that i need in one record in one cell has multiple documents.
this is problem even in Excel - you are fullfilling data in one row for one unique client and you want to have multiple documents attached to corresponding column. You can have a multiple columns for it or write it in one cell using decimals or semicolon...

Jacek

 

[Minty]

I think I have explained that this isn't how to store your data in the other thread. You can display it like that if you have to;

Doc1, Doc3, Doc7

But you would never store it like that.

 

[jaryszek]

Thank You Minty,

I don't know if i fully understand you (the last post yes, but earlier maybe not fully).

Okey so Access is more like how displaying data in Form then how storing data.

I have an idea for my whole model after our disccusion here:

1. Create New form based on few columns
2. Add listbox with possibility to check few kind of documents for each client.

Example:
User will be write company name and address will be upload automatically, user's login also. And after that listbox will be displayed with all kinds of documents (about 50). User can choose a lot of them (let say 10) and click button "insert into database".
Later VBA will be clone all data for client for each record but it will be changing records in column "document".

What do you Guys think about it?

Jacek

 

[Frothingslosh]

You have it backwards.

Create the tables, then the reports, THEN worry about the forms. You can denormalize the data for the forms via queries if necessary.

You have what's called a many-to-many relationship. You have multiple clients, multiple forms, and each client can have any number of these forms included. What I would do is set up a junction table - tblClientForms, perhaps. Layout would include the client's ID or PK field, the document's ID or PK field, and any tertiary data - perhaps 'IsNecessary', 'IsSubmitted', 'DateReceived', 'DateDue', who knows.

Perhaps when the client is set up, you can include a list of all 'necessary' documents, or you can skip that and just list whatever has been received.

Anyway, on the client's info form, I would have a button that opens a pop-up which lists all the available documents, along with 'Select' checkboxes and perhaps fields for any required dates.

Your user checks the boxes for each form, and then presses a command button which runs an append query that adds a record to the junction table for each selected form. If you included dates or any other data, this can be submitted at the same time. Once the update is done, close the pop-up and refresh the client form. Include a subform showing all documentation, and the refresh will update that to show the documents that were just selected.

This is a bit more complicated than just adding 50 Y/N columns to the client table, but it follows normalization rules and actually allows for much easier customization (such as addition of new forms) than hardcoding every single document.

It's going to be a bit of work, however. Consider it a learning experience.

Also, get other opinions. Someone else may come up with a better, easier-to-implement solution for you.

 

[jaryszek]

Thank you very very much !

That is it ! Very useful example.

Could we please do it step by step here using sample databases? (I have to fully understand it).

In attachment there is the sample database. I did what i could and what i understand so be patient for me...please.

Ok I have created Tab_Junction with fields ID_Client, ID_Document, NameOfDocument, Client_Name, DataReceived, Adress, IsNecessary --> this is my junction table.

I have created also table Tab_Documents with ID_Document, NameOfDocument and field IsChossen (Tab_name = Select - it is double field).

Now if you start from Form_Main there will be fields for Client_Name and DataReceived.
User can write in Client name in text box, datareceived of ticketID and click the button Select document. Now new form is opened --> Form_Documents where User can select checkboxes and choose lacking documents. And can also add them to my junction table with "add into database" button (its running append query - problem here: how to clone multiple rows but change only one field using append query? Maybe VBA will be better here? )

I think that you were suggesting to add each table for each client? I didn't understand it or better i can say : i can't imagine that so I am asking you Access Profesionals.

I see it like this (I am newbie):
1. Will be one additional table : Tab_clients and there there will be list of all clients with theirs adresses, names, ID client.
2. And user if choose in combobox client_name there will all data (connected with this client) show in other fields in userform (i know how to do it via VBA - not using combobx and textbox only).
3. After that user will select document, choose which of them wants to append into database, click finish and could select new client...

Uff this is awesome experience building this database with you Guys, thank you for that,
Best Wishes,
Jacek

 

[jaryszek]

Hello,

anyone ? Please help.

Jacek

 

[Frothingslosh]

No, I wasn't suggesting a table for each client. A many-on-many requires three tables total. In your case, one would be your clients, one would be the documents, and one would be 'between' them linking to both as well as containing any data specific to that client AND document (such as due date, received date, etc).

Unfortunately, I don't have a lot of time available to build things for you. I'll try to look at what you uploaded this weekend if I can.

 

[jaryszek]

Froth I am appreciate it very much! I will be grateful!

Jacek

 

[jaryszek]

Up,

Best Wishes
Jacek

 

[Frothingslosh]

I'm uploading what I have - you should be able to see where I'm going with it. Sorry it's not quite functional - for some reason, the update query is pulling a space from the ID text box. Other queries return the correct value just fine - just not the update one. Still, you should be able to get the gist.