Full Version: BlueSky
UtterAccess Forums > Microsoft® Access > Access Forms
BlueSky
Greetings from Columbus, Ohio.
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.
Public Sub ChangeAM()
Dim ctlC As Control
Dim frmAT As Form
Dim strSQL As String
For each control
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "UPDATE tblAssociateMaster SET ctlC.Name = ctlC.Value WHERE " _
& "[PERNR] = '" & [Forms]![frmATInternal]![cmbAID] & "'"
Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC
End Sub
Jack Cowley
Welcome to Utter Access Forums!
On unbound control does not have an OldValue. Bind your form and your code should work...
hth,
Jcak
BlueSky
Hi, Jack,
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!
Jack Cowley
No such thing as a stupid question... It may be easier to create a new form using the Wizard than it is to update the current one, though either will work. To update the current form select the table name from the dropdown list in the forms Record Source. Then go to each control and select the correct field for that control from the dropdown list in the Control Source. Now your form is bound and you should see each record as you navigate through the records. You can delete your DAO code to populate the form and update the table....
th,
Jack
BlueSky
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?
Thank you so much, Jack.
BlueSky
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.
Pub Sub ChangeAM()
Dim strSQL As String
Dim txtFN, txtMI, txtMII...As Control
If txtFN.Value <> txtFN.OldValue Then
strSQL = "Update tblAssociateMaster SET FirstName = txtFN.Value Where [PERNR]= '" & [Forms]![frmATInteranl]![cmbAID] & "'"
DoCmd.RunSQL strSQL
End If
If txtMI.Value <> txtMI.OldValue Then
strSQL = "Update tblAssociateMaster SET MI = txtMI.Value Where [PERNR] = '" & [Forms]![frmATInternal]![cmbAID] & "'"
DoCmd.RunSQL strSQL
End If
...
End Sub
Edited by: BlueSky on Fri Apr 8 9:25:26 EDT 2005.
BlueSky
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.
Jack Cowley
Why can't you use bound forms? What is the reasoning behind the decision to use unbound forms only? I do not understand this because using bound forms would make your life so much easier....
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.
Let me know about the reason for the unbound forms and then we can go from there....
Jack
BlueSky
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.
Jack Cowley
BlueSky -
o problems as I have kept busy...
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.
At this point I can only wish you good luck and if you hit a specific problem then let us know and someone here will do their best to assist you....
Jack
BlueSky
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.
Jack Cowley
In the Control Source of the three unbound controls on frmAT:
[Forms]!frmMain]![cmbAID]
= [Forms]!frmMain]![cmbAction]
= [Forms]!frmMain]![cmbReason]
The form frmMain must remain open for this to work.
Jack
BlueSky
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.
Thank you so much!
Jack Cowley
BlueSky -
If the EmployeeID is the foreing key in related tables then those table should be shown as subfoms on the form were you select the EmployeeID. When you select the EmployeeID in your main form if there are related records in the related tables they will show in the subforms....
hth,
Jack
BlueSky
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.
Jack Cowley
BlueSky -
You are welcome!
It sounds like your db is not properly normalized. EmployeeID should be PK on only one table, tlbEmplyoeeMaster (probably). I strongly suggest your nomalize your structure before you go any further or you will be finding it harder and harder to use the db....
Jack
BlueSky
You know what, Jack, sometimes I feel like I am working in BurgerKing. They come to me and said, I want this, this, and that...It is so tough to load a job in Columbus, Ohio. This is a first job related to what I study. And I got this even three years later after I graduated from college. ...

Edited by: BlueSky on Sat Apr 9 17:19:52 EDT 2005.
Edited by: BlueSky on Sat Apr 9 17:20:56 EDT 2005.
Jack Cowley
BlueSky -
understand your prediciment, but YOU are the Access programmer so you have to create the program correctly and if you do it will do all the things that your employer wants. I assume you have experience with Access so normalize your data and you will be able to have the db do what is required by the people who want it....
Jack
BlueSky
Jack,
Odon't have too much influence in our department. What I want is just get job done and in the mean time, find antoher job. Ride a mule, find a horse...
LueSky
BlueSky
Well, Jack, any suggestion for build a recordset to do this kind of search job? Thanks!
Jack Cowley
BlueSky -
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.
Jack
Jack Cowley
BlueSky -
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.
Jack
BlueSky
Jack,
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.
BlueSky
Jack Cowley
BlueSky -
feel you pain and I am afraid that there is not much I can do for you if the others are the decision-makers for the database....
Good luck!
Jack
BlueSky
Jack,
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.
BlueSky
Jack Cowley
BlueSky -
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.
I am not around today (Sunday), but I will be here tomorrow...
Jack
BlueSky
Hi, Jack,
Thank you for continuous support.
Oupload this db as zip file. Please take a look and kindly give me your feedback. (I am not first designer for this db). For log on to this db, please use user name "fh", and password "fh".)
Jack Cowley
BlueSky -
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.
Let me know what you think....
Jack
BlueSky
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.


BlueSky
Edited by: BlueSky on Mon Apr 11 13:49:29 EDT 2005.
Jack Cowley
BlueSky -
o need to return any favors. We are here to help if and when we can....
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.
Good luck!!!
Jack
BlueSky
Jack,
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..
Hope this could help you understand little better for this "Great DB"...:-)))
BlueSky
Jack Cowley
BlueSky -
understand the db, but only one of those tables is necessary with all the 'employee' data. All the other tables should be deleted and then maybe one or two related tables should be create to replace some of the required information about the drug tests and history. The current design is very bad...
Jack
BlueSky
Hi, Jack,
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.
Warm Regards,
BlueSky
Jack Cowley
BlueSky -
will be willing to make suggestions, but I would not be willing to do the work. The more bandages you will need to apply the tougher they will get.... You know the ultimate solution to the problem....
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.