By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,084 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,084 IT Pros & Developers. It's quick & easy.

Access XP- Changing information in a Table with a Macro

P: 10
I want to set a macro in a database to run when someone tries to Paste Append information into my Description table. Item # is set as the primary key in this database. The macro should look at the Item # and the Status of the information to be pasted; if the Item # is going to duplicate information already existing in the form, then the macro should look at the Status of the already existing data. If the Status is blank, but the information being pasted does not have a blank Status, then the information should replace the empty cell.

Is this even a possibility? My instinct is to go with something SetValue, but Iím not sure where to go from there. Iíd like to know if itís even possible before driving myself even more batty trying to figure this out!

To make this more fun, I donít know VB and my knowledge of macros is near zero. My last Access XP class was in 2001 and Iíve only fiddled with it a tad bit in the six years since my last class. I know how to build the tables, queries, and forms, but not much more. Iíve checked out numerous books from the library, but they donít get as detailed as I would like; they only go over the basics, which I already know. Kind of makes you wonder why Iím even trying to build all these databases, doesnít it?
Mar 20 '07 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
When you allow people to append the data directly into the tables, than this isn't possible, as Access has no way to trigger a function when a table is updated/appended.

Only a form can work and in that case it's best to use an unbound (not linked to a table) form and have the Item# and status field entered there. With the press of a button you can trigger code to check the situation and update or add a record.

Let me know or you can handle this using only forms and we can look into that.

Nic;o)
Mar 25 '07 #2

ADezii
Expert 5K+
P: 8,627
I want to set a macro in a database to run when someone tries to Paste Append information into my Description table. Item # is set as the primary key in this database. The macro should look at the Item # and the Status of the information to be pasted; if the Item # is going to duplicate information already existing in the form, then the macro should look at the Status of the already existing data. If the Status is blank, but the information being pasted does not have a blank Status, then the information should replace the empty cell.

Is this even a possibility? My instinct is to go with something SetValue, but Iím not sure where to go from there. Iíd like to know if itís even possible before driving myself even more batty trying to figure this out!

To make this more fun, I donít know VB and my knowledge of macros is near zero. My last Access XP class was in 2001 and Iíve only fiddled with it a tad bit in the six years since my last class. I know how to build the tables, queries, and forms, but not much more. Iíve checked out numerous books from the library, but they donít get as detailed as I would like; they only go over the basics, which I already know. Kind of makes you wonder why Iím even trying to build all these databases, doesnít it?
There is a Method by which this can be accomplished:

__1. Paste Append the data into another Table (tblPasteAppend) with the exact structure of your existing Table (Table1).
__2. Create a Query linking these 2 Tables by [Item #].
__3. Change this SELECT Query to an UPDATE Query with the Criteria on the Table1![Status] Field set to Null and Update To on Table1![Status] set to tblPasteAppend![Status]
__4. This would accomplish exactly what you are requesting.
__5. You can save the UPDATE Query, but you must somehow Delete tblPasteAppend prior to each time this operation is performed.
Mar 25 '07 #3

P: 10
Yay! Awesome :) thanks so much for that help, ADezii. It works great! I knew there had to be a solution, but I was unable to wrap my mind around it for some reason. Maybe I just need more Mountain Dew...
Mar 26 '07 #4

NeoPa
Expert Mod 15k+
P: 31,429
__5. You can save the UPDATE Query, but you must somehow Delete tblPasteAppend prior to each time this operation is performed.
With point #5 you should consider this as Deleting (or clearing) the records from the table rather than deleting the table itself.
Mar 26 '07 #5

ADezii
Expert 5K+
P: 8,627
Yay! Awesome :) thanks so much for that help, ADezii. It works great! I knew there had to be a solution, but I was unable to wrap my mind around it for some reason. Maybe I just need more Mountain Dew...
My answer for Mind Stimulation is Starbucks Coffee - whatever works!
Mar 26 '07 #6

ADezii
Expert 5K+
P: 8,627
With point #5 you should consider this as Deleting (or clearing) the records from the table rather than deleting the table itself.
Much better solution.
Mar 26 '07 #7

NeoPa
Expert Mod 15k+
P: 31,429
__1. Paste Append the data into another Table (tblPasteAppend) with the exact structure of your existing Table (Table1).
I felt I was simply clarifying your point as you already said in point #1 to Append the data in.
Mar 26 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.