[SOLVED] Update multiple records simultaneously

youcanread · Last edited by youcanread on Thu Mar 01, 2012 12:21 pm; edited 3 times in total

Hi! I'm using Windows 7 / OpenOffice Base 3.3.0. I've searched around but haven't found an answer to my question.

I'm making a database for the bookstore I work at to help them order books.

Ordering and keeping the database updated would be a lot easier if I could update multiple entries simultaneously:
when I do an order, I'd like to update the "date-ordered" fields of all the books at the same time.

My table is like this:

get | Title | date-ordered
--------------------------
2 | Pies | 2/28/2012
2 | Cats | 2/28/2012
1 | Pony | 2/28/2012
_ | Tree |

When I'm ordering 100 books at a time it's annoying to have to go and paste the identical data for "date-ordered" into a hundred rows. So is there a way to change them all at once?

Thanks for any solutions or leads!

Sliderule · Posted: Wed Feb 29, 2012 7:17 am Post subject:

You can set a DEFAULT value for a database table field ( column ). This is done, for example, with the following commands, that are entered from the Menu ( be sure to change your table and field names as explained below ) :

Tools -> SQL...

youcanread · Posted: Wed Feb 29, 2012 2:45 pm Post subject:

Thanks for such a quick reply Sliderule!

But, hmm, that's not really what I need. I should have clarified: these aren't new records I'm trying to modify. It'll be the same books ordered every few months, so I'd need to change the dates over and over again. Does that make sense? The date the original record was made is irrelevant-- I need to be able to change the date-ordered every time I do an order. And I want to only alter the books in that order, because not all of them would be ordered at the same time.

So is there a way to group all the books that are NOT NULL in the 'get' field and change their 'date-ordered' field to have the same value?

With a spreadsheet it's so easy--you just type it once, copy, select all the fields, and paste--but I can't get it to work in Base. Any ideas?

Sliderule · Posted: Wed Feb 29, 2012 3:13 pm Post subject:

You said:

youcanread · Posted: Thu Mar 01, 2012 12:20 am Post subject:

Thankyou thankyou thankyou! This is exactly what I need!

I just changed it so the date is automatic every time: UPDATE "MyTable" SET "date-ordered" = CURRENT_DATE WHERE "get" IS NOT NULL;

Now... how can I set this as a macro so I can activate it with a push button on a form? I'm making this database for other people to use, and asking people to go into Tools -> SQL every time would be rough.

I'm new at macros so if you'd rather direct me to a good tutorial on macros and buttons that would help.

Orrr, do you know what I'd have to add to this to make it work?
Sub clear
UPDATE "MyTable" SET "date-ordered" = CURRENT_DATE WHERE "get" IS NOT NULL;
End Sub

youcanread · Posted: Thu Mar 01, 2012 12:38 am Post subject:

I mean, I have the button set up to run the macro but when I run what I posted above it highlights "mytable" in the macro and "says BASIC syntax error Expecting ,."

Expecting ,? Comma? I must be missing something.

Sliderule · Posted: Thu Mar 01, 2012 7:15 am Post subject:

Your two posts above are the first time you mention the words form or macro,.

As far as I am concerned, that is a new topic, and, nothing to do with your original post, which I helped you Solve. Therefore, it is time you mark this forum topic as [Solved] per the instructions given to you in each the posts by Sliderule.

Start a separate forum topic if you must, or better yet, search on the forum first.

Sliderule

youcanread · Posted: Thu Mar 01, 2012 12:24 pm Post subject:

I did spend an hour searching and couldn't understand any of the posts about it so I thought it would be easier to ask someone who could answer easily. It just seemed too simple for a new topic. But, fine, I can clutter the forum with a new thread if that's easier.