Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  ISO: Advise for a current project

Author  Topic  Bishop Starting Member 5 Posts Posted - 2004-11-24 : 15:09:12 Hi Everyone! This is my first post here and judging by what I¡¦ve read so far, I¡¦m glad I found this place. I¡¦m not the worlds greatest Access programmer (actually I¡¦m pretty much a novice), but since I have some experience my boss has promoted me to the departmental Access guru. I¡¦m a healthcare data analyst and I typically program in SAS, which is basically data manipulation and statistical analysis software package, if you aren¡¦t familiar with it. However, based on an analysis we have done for diabetes screening we have to create a tool that will allow the data to be modified via manual medical chart review. The challenge is that I have to make this tool as user friendly and bullet proof as possible, because the people who will be doing the manual data entry are unskilled with databases and might potentially corrupt the data if exposures in data integrity are present. Essentially, I¡¦m coming to this board to try and get a bit of help¡Kif I¡¦m asking to much just ignore me ƒº Here is what I have so far, please feel free to criticize openly. Tables: 1. Tbl_Main = The base table that draws its data from the initial analysis a. Member_ = The unique identifier for each member (primary key) b. A1cLvl = A numeric measurement. This value will be one that can be modified. c. LDLC = A numeric measurement. This value will be one that can be modified. d. PCP = Primary care provider for the particular member. 1. tblMembInfo = I use this table for basic member information and combo box a. Member_ b. MembName c. PCP d. PcpAdr Ok, now the meat and potatoes. I want to use a form that will capture data that is relevant to the Tbl_Main, and then force an update to that table that overwrites existing data (I know, I don¡¦t like the idea of overwriting either but that¡¦s what the boss wants) but only if the person updating has the permission. It has to work something like this: 1. Member is selected via combo box. 2. The fields A1cLvl and LDLC are blank text boxes that can be modified 3. The exclude box: a. Is checked, then form goes grey and nothing can be done to the record. b. Is Not checked, then continue 4. A value is entered into the A1cLvl text box 5. The user clicks the button (btnA1cUpdt) a. Internal process checks to see if the field is locked i. If yes (locked) then msg box, ¡§You are not authorized to modify this field, would you like to enter a password to unlock this record?¡¨ 1. If no, then back to frmDataEntry 2. If yes, then password box pops up. a. Password entered and verified (assume positive verification) ii. If no (unlocked) then continue b. Message box ¡§Are you sure you want to update this record?¡¨ i. If yes, then update the record via an update query. 1. I want to be able to populate the parameters of the update query with the values entered on the form. I¡¦ve looked into some old VBA code and think I do this with a SQL string. Form: frmDataEntry = This is where the magic starts. Below are the fields I have in mind. cmboMemName = Combo box used to select member CmboMemName value has an afterupdate procedure that invokes this Locked = yes/no field that will be used to determine if the record is locked. Exclud = yes/no field. If this is flagged yes, then I want to grey out the record. User = combo box that must be selected before a record can be modified. Reviewer = ID of person who is authorized to modify records. So far I've been able to get my combo box to work, but I'm struggling with everything else and time is running out on me. Here is the code I wrote for my combo box to gather information: Private Sub CmboMemName_AfterUpdate() Dim memname As String memname = DLookup("[memname]", "tblMembInfo", "[member_]='" & Me!CmboMemName.Column(0) & "'") Provname = DLookup("[PCP]", "tblMembInfo", "[member_]='" & Me!CmboMemName.Column(0) & "'") DOB = DLookup("[ymdbirth]", "tblMembInfo", "[member_]='" & Me!CmboMemName.Column(0) & "'") Me!lblMemName.Caption = memname Me!lblProvName.Caption = Provname Me!lblMemDOB.Caption = DOB End Sub A great deal of the reason for me writing this up is to give myself a clear direction, but also to seek any available hints, tips, or advise. Thanks for taking the time to read all this junk! Best wishes to all and please wish me good luck too ƒº Sean