At work we have a database (yes it is rubbish in more ways than one) but they wont listen to me, the way they are useing it is wrong. anyway…

As a little help to them i want to select a number of fields (in dataview) then press a button to populate the fields with data.

i.e

we need to be able to select a number of fields (i am not to sure if it is possible) then add a number to it…

I am not to sure if this makes sence?

I don't understand this at all. Perhaps that's why they are not listening to you?

trying to understand what you want to do…

Do you want to populate all records with the same data in a specified field?

Or do you mean you want to select specific records, and then populate a field on all those records with the same data?

sorry Moses i will try again…

if i open a table in Data sheet view.

and say there are 10,000 entries.


col1 col2 col3 etc...
test test test test
test test test
test test test
test test test
test test test test


I want to Add/Change a value to the middle 3 fields of col2.

Are sorting it to an order, so the middle 3 may be rows 200 to 700 etc…

Then we sort it again then Add/Change some other value. the porblem is that i cant use the sql update because most records have to be manualy chacked.

does this make it any easyer to understand? 😳

tinker-belle the second one 🙂 (you make it sound so simple)

Can you import it into Excel and do it that way?

Nezbo – you need to create a query that looks for the specific criteria you're after (records or rows in data view) and includes the fields you want to update (columns) you can then use find/replace to replace the values that you want.

.
something like this
SELECT Table1.col1, Table1.col2, Table1.col3
FROM Table1
WHERE (((Table1.col1)="f"));
.

or this if you want to be able to enter different options
.
SELECT Table1.col1, Table1.col2, Table1.col3
FROM Table1
WHERE (((Table1.col1)=[enter Value]));
.
and then you can manually update what you need

You can even nest the query into a form if that makes it easier for you to work with.

Not sure if that helps?

Run an update query on the records you need to change

this wont work because there is sometimes a number of values (eg postcodes) in the WHERE statment. so useing a SQL select or update is out 🙁 it would have been easy other wise :'(

I may need to use VB or a macro but i am not to sure. even if it is a macro that replucated the ctrl+@ keys

Cheers,
Nezbo 🙂

Updating records with multiple criteria via query is certainly doable – you could try: (bare bones only, check google for loads of tutorials etc)

1) IN(…) statements, basically allows you to list lots of criteria eg
WHERE code IN('a','b','c',,,,,,'z')

2) Create a reference table with your criteria and join it to your main table to define the update set (best if you've got loads of values in your criteria set)

3) Use OR in your criteria (eg. WHERE code = 'a' OR code = 'b') etc

4) Set a flag to indicate an update is required with a conditional statement

To be honest, I'd recommend spending a wee bit of time reading a basic access tutorial / guide as you seem to be approaching it like Excel and not like a relational database

good luck!

I don't think that SQL is the way forward in this case; you will need to use VBA or a Macro. Unfortunately I don't know VBA for Access so I can't give you a definitive answer.

Create a Message Box containing a input field to contain the new value or multiplier and two radio buttons (update and multiply). Show the message and store the new value/multiplier

Find the VBA command to get the selected cells, in Excel this is Application.Range.

Iterate through the selected cells and set each of the cells to either then new value or the current value * the multiplier.

I can't find an example of how to get the selected cells in Access but I hope that this helps

Selected cells in Access do not have a specific reference in the same way as in Excel, they just exist as part of a recordset, you can only reference them separately by SQL or by creating a form where each field is a separate control (not a datasheet form). Access is designed for data entry / update to be controlled either by query (sql) or forms (vba) or a combination of both.

If I was going to do this in Access it would be something like this:

1) Define a new table with the following fields – Main Table Key (so it can link back to your main table), update (yes/no flag), update to – you don't mention what the update involves – is it a variable outcome or always the same?

2) Create a query that links the Main table to the Update table

3) show this query in a form so you can see your records and the update yes/no field – the user can then tick/untick the yes/no field to show which records require updating

4) create an update query that uses the update yes/no field to define which records to update – run this query from a button on the display form created in point 3. If you want to be able to update more than one field (column) then you'll need to define this too.

5) Obviously to get this to work requires quite a bit of development! I'd really recommend Excel – it would be much more straightfoward.

Cheers All. As i thought, it will not be able to do what i want it to do, I need it to work a bit like a spreadsheet, but within a database.

they will have to keep doing what they are doing. the problem is they dont like change, so it is imposible to get them to do something difrent. (sod them)

one of the main problems that data is imported from another application.

nevermind, cheers all.

lol, sounds like a proper solution is required – pub or bike ride!

acjim

Selected cells in Access do not have a specific reference

I thought the same thing but then I noticed that you can copy and paste irregular ranges. It might not be possible via VBA as I couldn't find the call in the API.

Why not add true/false field which the user could check/tick to indicate the records to be selected.

Then run an update query where the only criteria is that this field=TRUE.
Run another query immediately after this to reset this field to false.

edit: bother! didn't read acjim's post properly

Without knowing what the information is, it's hard to visualise what's going on. The users sound like hardcore spreadsheet types. Does the database actually do anything that couldn't just be done in Excel?