UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Macro Or VBA-SQL, Access 2013    
 
   
jyiyo
post Dec 12 2017, 07:45 AM
Post#1



Posts: 17
Joined: 7-December 17



Hi!

I am a newbie here. I have some experience with VBA, but Access have some data Macro that I think can do the job. Could you please help me with the following?

I have two tables in my Access database, their fields are exactly the same. One is called Uncheched (Preventive Maintenance that haven't been complete). The other one is called Checked (Preventive Maintenance done).

So what is needed is a way that when I check the individual record in table Unchecked, the now checked record, go to the table checked (automatically). I saw in Access something about "After Update" data Macro, however I don't know how to used. The other way that I know that it can be done is using VBA, I think I have an idea of how to do it, but I don't know how to run that automatically after I check the record.

I think the code that I can use can be something like that, but I know sure.

IF Me.Ischeck = True Then
DoCmd.RunSql "Insert into tblChecked"
DoCmd.RunSql "Delete From tblUnChecked where (EquipmentID = " & Me.EquipmentID & ");"
End IF


Any help will be very helpful,

Thank you!
Go to the top of the page
 
Doug Steele
post Dec 12 2017, 07:58 AM
Post#2


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


welcome2UA.gif

Data macros are associated with the table. That means the action(s) will occur whether or not the data is input via your front end code. That means it's far more reliable to use Data Macros than VBA code.

There's an introduction to the topic at Create a data macro.

--------------------
Go to the top of the page
 
Larry Larsen
post Dec 12 2017, 08:04 AM
Post#3


UA Editor + Utterly Certified
Posts: 24,169
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
welcome2UA.gif

You could get away by simply using a single table to reflect either "done" or "not done", but a question I would ask is there a more frequent review of check's and how often..??

The above option would need to have some supporting tables..

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
orange999
post Dec 12 2017, 08:28 AM
Post#4



Posts: 1,749
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


jyiyo,

If the 2 tables have identical fields, and the difference is the value of one field Checked vs UnChecked, what is the rationale for 2 separate tables?
Just looking for some clarity and the business context.

--------------------
Good luck with your project!
Go to the top of the page
 
jyiyo
post Dec 12 2017, 09:20 AM
Post#5



Posts: 17
Joined: 7-December 17



thank you all for the quick responses.

I'm using two separate tables because I want to present the information in a Tab control Form in different tabs. The first tab present all the preventive maintenance that need to be done (unchecked) and the second tab shows the preventive maintenance history that have been done (checked). I don't want both information to be in the same area. Also, the data base need to be able to allow the user to check what they have done and that information be move to another area as a task been complete. I don't know if having two table is the best way, but I don't know what else to do to accomplish that.
Go to the top of the page
 
Doug Steele
post Dec 12 2017, 10:01 AM
Post#6


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


It's trivial to populate the two tabs using a single table. Simply create two queries (one that returns the unchecked records, one that returns the checked records) and use the queries as the RecordSources for the two subforms.

However, I'd question using tabbed forms for this purpose. Why not have two list boxes side by side? You can easily set it up to transfer entries from one list box to the other. I outline the approach in Let me check my list.

--------------------
Go to the top of the page
 
jyiyo
post Dec 12 2017, 10:34 AM
Post#7



Posts: 17
Joined: 7-December 17



Thank you guys,

I did what Doug Steele suggested and its meeting my need. Thank you Doug.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th April 2018 - 06:51 PM