Hello, everyone:I am new to VB. I am going to audit value on one unbound form. If value changes, use SQL Update query to update the value in the appropriate field of the table. So far, I got the following code. When I run it, it said variable not set. Could someone help me with this issue. I greatly appreciate your assistance.
& "[PERNR] = '" & [Forms]![frmATInternal]![cmbAID] & "'"
o glad to see your reply. Can you please me how should I bind my form? Right now, I run DAO recordset to do the insert , update issues. Because the form is not bound, I can't use Dirty funcion either. This quesion could be sound like stupid, but I am sorry, I really don't know. Thank you in advance!
Thank you for your reply, Jack. This project is not allowed me to use any Control Source in the form - Everything is unbound so that I can't use the navigation botton either. I wonder that I could just use vb code to track the value changes and only update the fields where the value has been changed. I read some articles from Microsoft web site. but they used audit table and set up a module to do this job. Is it possible that don't use audit table and do the same job?
Good Morning, Jack. Here is another solution I come up with. Should I change something to the "Before Update Event" and "After Update Event" of the form? Right now, I put this piece of code as a function. When user click the "Save" button", then call this function. I appreciate your assistance.
strSQL = "Update tblAssociateMaster SET FirstName = txtFN.Value Where [PERNR]= '" & [Forms]![frmATInteranl]![cmbAID] & "'"
strSQL = "Update tblAssociateMaster SET MI = txtMI.Value Where [PERNR] = '" & [Forms]![frmATInternal]![cmbAID] & "'"
Edited by: BlueSky on Fri Apr 8 9:25:26 EDT 2005.
Maybe I should explain my database structure to you. There are 4 tables inside this access database. They are: tblPreHire, tblAssociateTransaction, tblAssociateMaster, tblAssociateHistroy. Those tables will store the record depend on the action that user used. Let say. For Hire action, record should go to tblPreHire and tblAssociateTransaction. For Address Change action, should retriving data from tblAssocaiteMaster, then update its record. For Termination action, should retriving data from tblAssociateMaster, set the TerminationDate, and insert record into tblAssociateHistory, and delete this record from tblAssociateMaster. Since we are in the migration process to SQL server, all we want is trying least transaction in the SQL server. This is the reason I need only update the field that has been changed. Hope this would help you understand better what I stand for.
etting 'old values' using unbound forms means that you will need to enter a new value and then use code to read the old value from the table, compare what the user entered and then update the table. This seems like a lot of unnecessary work to record a single change, but if this is the ONLY way that you can develop the db then you are going to have to use lots of code and lots of work-arounds.
In your last post you state that when a person leaves you put that data in a new table and delete their record from another table. This is unncessary as you can flag the record in the table instead of deleting the record and you do not need to store the data in another table.
Hello, Jack, Sorry for getting back to you so late. This project was assigned by our new Director. He has his own way to do it. I have no choice....Hope that you could understand. Have a nice weekend.
Hmmm. I do not agree with your director as he/she is making this much harder than necessary and he/she is not using good relational database programming if he/she is adding records to one table and deleting them from another.
You have your work cut out for you as you are going to have to do everything with code. Be sure that the db structure is a least properly normalized or it is going to make your task even more difficult.
Thank you, Jack. Now, we changed project little bit. We decide to use one Main Menu after user login to the database. User select actions(NewHire, Rehire, AddressChange, Termination,...), ActionReasons, AssociateID then pull up the form(frmAT). This form could be unbound( like New Hire Action), but if Rehire should be able to pull up the information from tblAssociateHist, For address change, should be pull up the information from tblAssociateHistory. I would like to know how to pass the value from frmMainMenu to frmAT. There are 3 combo box fields in frmMain(cmbAID, cmbAction, cmbReason), but display in frmAT use text box(txtAID, txtAction, txtReason). I couldn't thank you enough. Have a nice weekend!
Edited by: BlueSky on Fri Apr 8 21:50:33 EDT 2005.
Edited by: BlueSky on Fri Apr 8 22:24:00 EDT 2005.
The form frmMain must remain open for this to work.
Hi, Jack, glad to read your reply. It really works. I made little changes. I put your code inside the frmMain, when user clicks "Ok" button, open frmAT first, then assign those values to appropriate fields in frmAT, then close frmMain. This way, I don't need to remain the frmMain open. Thank you.
Well, I do face another hurdel, that I am quite sure that you can help me leap over. How should I search one EmployeeID through 3 different tables, when find it, pull out the record, if not , give a message.
Unfortunately, we have 4 tables so far. tblEmployeeMaster, tblEmployeeHistory, tblPreHire, tblTransaction. EmployeeID is primary key in tblEmployeeMaster, tblEmploeeHistory, tblTransaction(Compositive primary key: EmployeeID, action, actionDate), SSN is the primary key in tblPreHire....Sometime, When we search Employee, we should have go through those tables to find ...hehehe... you could laught at this.... Thank you , Jack!
Edited by: BlueSky on Sat Apr 9 16:59:52 EDT 2005.
Edited by: BlueSky on Sat Apr 9 17:19:52 EDT 2005.
Edited by: BlueSky on Sat Apr 9 17:20:56 EDT 2005.
Well, Jack, any suggestion for build a recordset to do this kind of search job? Thanks!
ho is the Access expert? You or the people asking for the database? If they are the Access experts then I guess you wil have to listen to them and follow their instruction on how to create the db. If you are the expert then you ask them what they want the db to do and then you create the db according to standard relational database norms, which includes normalizing the structure.
efore you consider a search or anything else I strongly suggest you normalize your data. If you don't it is just going to get harder and harder to get the data you want. If you do not understand normalization then I suggest you start with this article and then read some of the others that you will find in the Archives here at UA.
don't consider myself as the Access Expert even though I am MCSE, MCDBA...I truly understand the importance of Data Normalization . As I told you before, I am not the decision maker for this DB project. I hope you could feel my pain.
It seems that you are not going to help me if I don't following your advise. Well, I am sorry. Anyhow, I really appreciate your replies within these 2 days. Thank you again.
I AM willing to help you, but at this point about the only way that I can do that is to have a look at your database. It is very hard to know what you have from your descriptions alone so if you can compact and zip your db and post it here I will take a look at it. There is a 500k max size for the db to be posted here so if it is bigger than that we will have to discuss what can be done.
Is there any chance that this db can be rebuilt? There are so many things wrong with it that it is going to drive you mad trying to get things to work properly. I see no reason for the form to be unbound; redundant data is rampant as well as repeating groups; there are only a few tables with proper primary keys and the list goes on. In my humble opinion you would do your company a favor and rework this database because it is only going to get harder and harder to use as time goes on.
At this point I do not know what to tell you except that it is going to take a lot of work-arounds to get this db to search for the proper data and return it. It certainly can be done, but it I do not have the time to figure this all out for you and in fact I am not even sure where to start...well, I would start by starting over, but that may not be an option for you.
Thank you, Jack. Your opinions make a lot of sense to me. But I really can't do anything with this db structure. I know that a lot of works could be involved for this "Great Project". We will put one bandit after another. I will try my best. This is what I can do.
If it doesn't take you too much time, could you please tell me that what table should have to be rebuilt? I owe you too much favor. I don't know how to return it. I greatly appreciate your assistance.
Edited by: BlueSky on Mon Apr 11 13:49:29 EDT 2005.
The following tables all have redundant data; "tblAssociateHistory", "tblAssociateMaster", "tblPreHire", "tblView" and "tblWithdrawn". These should probably be one table, not 5. "tblPreHire" has repeating groups like the Memo fields, ListA B and C. Things like drug screen and pay should be in related tables. And there are tables with no Primary keys and tables with no relationships to other tables when there should be.
If you cannot change the structure then you are stuck with trying to make what you have work and that, in my opinion, is going to require more work than fixing what you have and then going from there.
et me explain to you about these 5 tables: tblPreHire is for whatever the Hiring Information; tblAssociateMaster is for whoever passed the Drug Screen test and being hire as a full time associate(Means active associate); tblAssociateHistory is for whoever been terminated; tblView is a "View" table(Frankly speaking,this table is created by me, the purpose for this is for searching AssociateId through 2 tables(tblAssociateMaster, tblAssociateHistory), find a record, and retriving data to the frmATInternal. I have to agree, this is not a good solution, this is another bandit); tblWithDrawn is for whoever failed the DrugScreen tested from tblPreHire..
Thank you for your suggestions. I know that my life would be more miserable when I deal with this kind of db. As I wrote in last post, we just put one bandage after another. I wonder whether I could get your help if they need me to put another "bandage" for this great project.