Access XP- Changing information in a Table with a Macro

[eskara]

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?

[nico5038]

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)

[ADezii]

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.

[eskara]

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...

[NeoPa]

__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.

[ADezii]

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!

[ADezii]

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.

[NeoPa]

__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.