473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

need to populate a form with a predecided set of fields

Please Help; need to populate a form with a predecided set of fileds which may be used seperately as well. To be more specific; I have a form (frmTestValues - a continuous form) based on table (tblTestValue) which stores the TestOrderId, Date etc and most important- values of tests , the name of the test is stored in a lookup form and selected (tblTestName.TestName), value is entered against it.
Now a group of test has to be performed frequently .. like lipid profile which entails selecting TCholesterol, HDLCholesterol and Triglycerides independently each time I have to enter values whenever a lipid profile test is ordered.
Please suggest a command where I could autopopulate the testValue form with a pre decided set of test names so that I don't have to do a lot of selection again and again. What I would like to do is place a command button on the main form and the after click property should populate the subform (frmTestValues) with TestName ( Stored in a different Table tblTestName- which acts as the lookup table)- but there would be multiple test names not just one!
a screencap shows the actual table but is name is different in the pic.
Thanks a lot in advance!!!
Nov 24 '10 #1
25 2147
So what you want is to select an item for test from a dropdown and then generate the associated test fields for filling in and storing in table? Is that what you are after? Roughly how many test in total for all items? (What I am wondering is if a binary flag could be used to bind a test field to an item).
Nov 24 '10 #2
Thnks fr answering! even the largest test group would not contain more than 20 items ( a binary data type would do if thats what you suggest)I am attaching a screen cap of the table and the form. pls see here http://www.dbforums.com/microsoft-ac...ml#post6486489
Nov 26 '10 #3
Hi Rajesh, looking at the screen cap I notice that the first column is headed Non Profile Test. Does this mean that you have the option to build profiles? Profiles are the way to go I feel, for instance basic diabetic test selected in dropdown displays each test required for that profile, asks for job number, result etc then saves data to testscomplete table. A facility to add tests and test id's to a lookup table obviosly needed but I find this an intrigueing project and would like to throw some stuff your way. Attached is some scratch code I knocked up to illustrate the idea, obviously not knowing details of your requirements the fields hold different data. The example shows options for vehicle checks dependant on type, either bicycle or car, selecting car causes a query to lookup all test that should be performed on the car, giving the option to fill in jobnum and result. This info is then stored in tested table. Tests are held in a seperate table with test name and and id code, these id codes are added in a string format to the profiles (car and bicycle). The needed tests are extracted with a query using the instr (in string )function to id if a test id string is contained within the profile string.
Selecting the profile is made on the main form, the tests to be performed and results entering are in a subform and the saved results table displayed in another subform. This is just a mockup of the idea and not functional in saving any data. Just run the idea past you.

My original idea of using binary flags was that one byte holds 8 bits giving 9 differing values (including zero) so you have 8 flags that can be set or unset by anding (bitwise and) the flag byte with 255, I have used this to allow/ disallow access to various menu items in the past as it's relatively easy to assign flag values to users as they log on via a lookup aginst the logon id.
Attached Files
File Type: zip testing.zip (109.3 KB, 129 views)
Nov 28 '10 #4
Thanks for replying Malcolm!
In the meantime as I am downloading and Digesting all that you've told me I would share How I solved this issue for the time being, I used the addnew() command with If
as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TestName_AfterUpdate()
  2. ' Create form entries for CBC test
  3. If Me.TestName = 36 Then
  4.  
  5.     Dim dbN As DAO.Database
  6.     Dim rsN As Recordset
  7.  
  8.     Set dbN = CurrentDb
  9.     Set rsN = dbN.OpenRecordset("NonProfileTests")
  10.  
  11.     rsN.AddNew
  12.     rsN("TestName").Value = "152"
  13.     rsN("TestValue").Value = Null
  14.     rsN("TestOrderID").Value = Me.TestOrderID
  15.     rsN("TestDate").Value = Now()
  16.     rsN.Update
  17.  
and so on....... ( the numbers are ID's of the various tests from lookup table).

But here I have a problem..
some tests are used in different profiles e.g ALP is used in Liver and Kidney profile if I add it to both the profiles I have a double entry which messes up the report.( logically if the codewas to "lookup" if the entry exists if yes the Cancel=true or proceed further with the task.. I tried writing the code as I understand but it doesn'nt work.. its adding the parameter all the time...Pls give yr valuable suggestion on this as well!

Could you pls expand a little on the flagging part ( how to do it practically) that would be helpful to me in reporting and categorizing reports- otherwize I need to make seperate columns for different categories
Thanks in Advance
Rajesh
Nov 29 '10 #5
I'm very thankful to you , you've taken grt pains to understand/ and explain to me. I'm still trying to understand everything, at the onset I figure out that my problem is something like this -in tblTestID if I select Break overhaul-(break cable) and (break fluid) is inserted automatically. But the problem now is in there was a group Hydraulic systems which may include break fluid as well as other parameters ( so break fluid is common to 2 or more profiles) how to exclude that!!!
Thanks
Attached Images
File Type: gif testid.gif (15.6 KB, 270 views)
Nov 29 '10 #6
Not sure I understand, the best way forward is profiling I think. Brake fluid may well show in more than one profile as I am sure there is some overlap with your medical tests. To remove brake fluid from a particular profile simply deselect it!
The attachment is as I said a rough guide as to where I am thinking with this. I presume that in your example you are setting brake overhaul as your profile then adding cable and fluid options as test to be performed within that profile?
Anyhow I have attached a demo of binary flags as you requested along with a written explanation of it ; but I don't think that it is suitable for your needs due to the large amount of tests. The option I would use is short id strings for the tests and concatenate them to form one large string in the profile flag. Using instr you can then see which tests are in which profile. I will try to get something more pertinent to your situation written and post that when I can. Will pm you about actual tables, fieldnames and types required along with pertinent questions if you don't mind me persuing this as a personal project as I find it fascinating and can learn a lot from working on a solution wether you take it up or not.
Regards, Malcolm.
Attached Files
File Type: zip binary flags.zip (39.0 KB, 77 views)
Nov 29 '10 #7
ADezii
8,834 Expert 8TB
I just did a cursory reading of this Thread, and I do apologize if I am over-simplifying matters, but it appears than an efficient and simple solution to the problem would be:
  1. Add a [TestID] Field to tblTestName. This Field would be the Primary Key (Auto Number) and uniquely identify each and every Test along with additional information for the Tests.
  2. Create a Child Table named tblDependentTests which Links to tblTestName via the [TestID] Field in a MANY to 1 Relationship. You would populate this Table with all dependent Tests for any given Test along with the corresponding ID. This would make the retrieval of required Tests very easy.
  3. A simple illustration for the Lipid Profile Test would be:
  4. tblTestName
    Expand|Select|Wrap|Line Numbers
    1. [TestID]              [Test Name]
    2.    1                  Lipid Profile
  5. tblDependentTests
    Expand|Select|Wrap|Line Numbers
    1. [TestID]        [TName]
    2.    1            TCholesterol
    3.    1            HDLCholesterol        
    4.    1            Triglycerides
  6. tblTestName.[TestID]{1} ==> tblDependentTests.[TestID]{MANY}
Nov 29 '10 #8
ADezii, glad to have you aboard, this is a bit complicated for me and I may have gotten hold of the wrong end of the stick and be over complicating things, I really don't know.
Using your example above then if table 1 contains 2 profiles like so
Expand|Select|Wrap|Line Numbers
  1. [TestID]      [Test Name]
  2.    1         Lipid Profile
  3.    2         Another profile
  4.  
Then if Another profile also requires the HDLCholestrol test how is it reflected in the dependant tests table.
tbldependanttests
Expand|Select|Wrap|Line Numbers
  1. [TestID]        [TName]
  2.    1            TCholesterol
  3.    1            HDLCholesterol        
  4.    1            Triglycerides
  5.  
Does the test id contain both 1 and 2 or the sum of 1 + 2?
This is why I suggested building a string flag to id each test applicable to a specific profile.
Judging by the screenshot provided by OP (referred to in post number 3 , can't get a pasted copy of the link to work for me for some reason)the first column shows a heading of non profile tests, this suggests to me that there is already a facility to build test profiles grouping specific tests to specific job profiles which to me would seem to already be the answer?
I may just not know enough here so I am ready to learn if you are ready to point the way.
Thanks ADezii.
Nov 30 '10 #9
ADezii
8,834 Expert 8TB
It would look something like this, I realize that this is somewhat redundant, but I did not want to over complicate matters at this point. In reality the Dependent Table would contain the related [TestID] as well as an Index [ID] pointing to tblTestName indicating the actual Test Name. If I have time, I'll create a simple Demo.
Expand|Select|Wrap|Line Numbers
  1. [TestID] [TName]                [ID]
  2. 1         TCholesterol
  3. 1         HDLCholesterol         33
  4. 1         Triglycerides
  5. 2         HDLCholesterol         33
  6. 2         YaDa-YaDa Test
P.S. - Assumes HDLCholesterol has a PK Value of 33 in tblTestName.
Nov 30 '10 #10
Thanks for writing! This could be an intresting way out. If I am understood rightly-
You propose a seperate table for profiles and link them to testName ( In fact Malcolm also proposes a seperate table but with help of Binary flags, only here the flags are ID,,,, am I getting things right?)
I'll try to work out a new table with profiles and get back on this forum.
Nov 30 '10 #11
ADezii
8,834 Expert 8TB
Attached, please find an Example of how you can implement this functionality within your Application. I dropped my initial approach simply because it would involve a major Redesign of your Database. Keep in mind that this is just a Demo, and does not covers all bases. It is simply meant to point you in the right direction
Attached Files
File Type: zip Lab Tests.zip (25.6 KB, 82 views)
Dec 1 '10 #12
Thanks a lot!
As I opened it It looks exactly what i was looking for..
I'm writing this just to thank you! Would get back after i've gone through the code.
Dec 1 '10 #13
The Problem I am facing here is
1) one test belongs to 2 or more profile- so if I add a profile column do I make it multiple values, would the code work same or give double values??
2) Even with this model there is repetition of parameters ( which is the core issue now!!!)
thnx
( I've learned a lot with yr example and still trying to digest all BTW- if possible pls clarify/ explain this line in simpler terms..
Set rstTests = MyDB.OpenRecordset("SELECT * FROM tblTestName WHERE Instr([ProfileRef], '" & varProfileID & "') > 0", dbOpenForwardOnly)

Thnx again
Dr Kesari
Dec 1 '10 #14
Rajesh, I have put together a partly working piece of code (attached) which illustrates the process of building the profiles. The example contains more detailed stuff (which may or may not be usefull to you but is to be included in my pursuit of the project for my own satisfaction) such as customer id or who pays for / requested the test? Also categorising the tests ( you make up your own categories ). I think the profile builder is self axplanatory simply selecting a test in the listbox and using the >> or << buttons to add or remove from profile. As yet this is as far as I got but I will continue in this vein (no pun intended haha). If it's gonna be useful for you as a reference tool or otherwise please let me know, if its not of interest then please also let me know.
Regards.
Attached Files
File Type: zip testitems.zip (96.1 KB, 88 views)
Dec 1 '10 #15
ADezii
8,834 Expert 8TB
one test belongs to 2 or more profile
That is a part the problem as you stated in Post#14. Any given Profile can required a multitude of Tests, and any given Test can also be an integral part of multiple Profiles. This is what is described as a MANY <==> MANY Relationship, and is definitely not supported by Access as well as many other Database Management Systems. The way to get around this is to create an Intermediate Table whose Composite, Primary Key, consists of the Primary Keys of the Profiles and Tests Tables. Now, instead of a MANY <==> MANY Relationship between Profiles and Tests, you have two 1 ==> MANY Relationships, one would be Profiles ==> Intermediate Table, and the other Tests ==> Intermediate Table. The combination of Profile and Test is Unique. I've attached a Demo showing how this work around would be structured. What I have mentioned, however, would require a major Restructure of your existing Database, so I opted for a simple, maybe not so efficient alternative.
  1. The Main Form is Bound to tblTestVales.
  2. All Profiles as well as Tests are stored in the Table tblTestName with the Profile/Test Name being displayed in the [TestName] Field.
  3. What differentites a Profile (Lipid Panel) from a Test is the presence of a single character code (A for Lipid Panel) in the [ProfileID] Field allowing for a Maximum of 26 Profiles with the current Logic.
  4. If a Test is required for a specific Profile, then the Profiles
    s' Code (A - Z) will exist in the [ProfileRef] Field. LDL as well as HDL Cholesterol Tests have an 'A' in the [ProfileRef] Field indicating that they are required for the Lipid Panel Profile. Tests belonging to Multiple Profiles will have a Comma-Delimited list in [ProfileRef] indicating the Profiles. Potassium has B,C in the [ProfileRef] Field indicating that it is required for the Fictional Profile as well as the Comprehensive Metabolic Panel With EGRF.
  5. The Combo Box is populated with all the Profile and Test Names in Alpha Order, with [TestID] being the Bound Column.
  6. In the AfterUpdate() Event of the Combo Box:
    1. We need to determine if the selected Item is a Profile or Test. We perform a Lookup on the [ProfileID] Field in tblTestName to see if it is a Profile. If it returns NULL, it is simply a Test and the Update of the Record proceeds normally via exiting the Routine..
      Expand|Select|Wrap|Line Numbers
      1. 'First of all, is it a Single Test or a Profile (we must differentiate). Look for
      2. 'a Profile ID
      3. varProfileID = DLookup("[ProfileID]", "tblTestName", "[TestID] = " & Me![TestID])
      4. If IsNull(varProfileID) Then Exit Sub       'It's not a Profile, get outta Dodge!
    2. If it is a Profile ([ProfileID] = some Value), then we create a Recordset based on all of the Records in tblTestName whose [ProfileRef] would equal the Value returned for the [ProfileID] in varProfileID. Does the [ProfileID] appear in the [ProfileRef] Field of any Test, either singularly or part odf a Delimited String?
      Expand|Select|Wrap|Line Numbers
      1. 'Yep, it's a Profile
      2. Set MyDB = CurrentDb
      3. Set rstTests = MyDB.OpenRecordset("SELECT * FROM tblTestName WHERE Instr([ProfileRef], '" & varProfileID & "') > 0", dbOpenForwardOnly)
    3. We then Loop through this Recordset appending the[TestID] as well as any other Fields you deem necessary to tblTestValues.
      Expand|Select|Wrap|Line Numbers
      1. With rstTests
      2.   Do While Not .EOF
      3.     CurrentDb.Execute "INSERT INTO tblTestValues ([TestID]) VALUES(" & ![TestID] & ")", dbFailOnError
      4.       .MoveNext
      5.   Loop
      6. End With
    4. Requery the Form to reflect the Additions.
      Expand|Select|Wrap|Line Numbers
      1. Me.Requery
  7. Again, I am not stating that this is the most efficient Algorithm in the world, to me it appears to be an easy integration to what you already have so far.
  8. I would also take some time and look at what 'malcolmk' has proposed regarding this Thread, his options may very well be much better than the ones which I have stated.
Attached Files
File Type: zip MANY to MANY.zip (14.1 KB, 88 views)
Dec 1 '10 #16
Thanks Malcolm, i sincerely hope this is not too "painful" a "headache" for you. I'm downloading and will get back after studying it..
Rajesh
Dec 2 '10 #17
Thanks ADezii, fr the thorough explanation, would get back after studying all..
Dec 2 '10 #18
No headache Rajesh, I really enjoy getting to grips with new projects, especially when it throws up unexpected challenges as it always results in me learning something new ;)
Dec 2 '10 #19
Sorry Didn't reply earlier- was out of town and touch fr three days, will come back soon with my seemingly foolish questions again...Rajesh
Dec 5 '10 #20
Thanks to both of you..
As suggested I have changed my tables and included Profiles, even though for data entry I have used ( at least fr the time being) Unbound forms with AddNew command to each control...
The profiles approach has significantly changed everything, I had to make different reports as well. But I found it to be more effective than my earlier approach of using filtered subforms.. thnks to you again.
I am trying to understand how to use the recordset function but its not working the way I've understood, still looking up in the 'Bible' and net.

I tried solving the problem of multiple entries because of some tests which are present in two or more profiles by putting two profiles with common fields on a single form- but I know this is not the right thing..(Screencap)

I've also tried using the following code in onLoad condition,

Expand|Select|Wrap|Line Numbers
  1. "If Forms![TestOrderParentForm]![TestOrderForm].Form![NonProfileTests].Form![TestName]= 123 Then
  2. Me.Text4.Visible=False"
It works only when the cursor had been left on the field (123); it doesnt look for the field in other records; then I tried the following code but it shows error (screencap attached)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3.  
  4. Dim mydb As DAO.Database
  5. Dim rs As Recordset
  6. Dim str As String
  7. str = Forms![TestOrderParentForm]![TestOrderForm].Form![NonProfileTests].Form![TestName]
  8. Set mydb = CurrentDb
  9. Set rs = mydb.OpenRecordset("SELECT * NonProfileTests WHERE TestOrderID = Me.TestOrderID")
  10.  
  11. With rs
  12. Do While Not .EOF
  13. If str = 11 Then
  14. Me.Text8.Visible = False
  15. End If
  16. Loop
  17. End With
  18.  
  19. End Sub
-- I feel - The solution lies in making a recordset which is limited by the TestOrderID ( which is unique for each test Order) the searching in this recordset wether a record with the given testname exists or not, if it exists then it can be manipulated in many ways... Pls help in implementing this...
Rajesh
Attached Images
File Type: jpg multipleProfilesin1Frm.jpg (48.0 KB, 199 views)
File Type: gif error1.gif (8.5 KB, 246 views)
File Type: jpg NewTestTbl.jpg (74.6 KB, 196 views)
Dec 11 '10 #21
Hi, are you saying that the procedure is to build a list of tests for patient x (this could be a temporary created profile called patient x) and you then want to merge existing groups of tests but avoid duplication?
My thinking of the procedure is that you or somebody else creates a job for patient x (job_id patient_x_123)and builds from existing profiles or by selecting individual tests a list of required tests.
This is then called up on screen by the technician by entering the job number (patient_x_123), the tests are carried out and the results entered in the record for the job number. Reports on the completed job number can then be printed out and sent to the requesting physician.
Is that a general outline of the procedure? Does the technician receive a printed copy of the job sheet, with tests required and fill in the results on the jobsheet; maybe later transferring them to the database?
If the above outline is generally correct ( this is the way I have organised my version of the project ) then I am near to completion.
The way I merge two profiles is to scan the profile test list and break it into individual parts, then scan the second profile testlist and if it already exists discard it, if not then add it.
I have found that most of the work is to be done in the profile building section, I have attached what I have so far. A lot of the column and other info visible is simply for tracking / debugging purposes and will be hidden in the final version.
A module (duplicates) contains the string scan code I referred to earlier and I use a module for populating the test list box as it is called from various locations.
Attached Files
File Type: zip testitems.zip (204.0 KB, 103 views)
Dec 11 '10 #22
Yes the above outline is generally correct, so as not to confuse you any frther am enclosing a copy of the project;

Sorry I didnt know earlier .. its too bigger than the limit set here, I'll try to delete some parts and send it again,
Good night for now
Dec 11 '10 #23
I've tried to remove every thing else except the most relevant things to fit into the 5 mb limit. Pls have a look and give yr comments. Thanks
Rajesh
Dec 12 '10 #24
As I use access 2003 and am not vastly experienced with complex databases, I find the attached db rather overpowering. Maybe ADezii will be more at home here.
I am not clear where exactly you are stuck with it, Is it simply avoiding multiple entries for a given test because it appears in multiple profiles? if that is the case then a simple string for each profile that holds the test id's is all that is needed as implemented in my attachment! The test order id is equivalent to a job or order number I guess?
Sorry I can't be more helpful but unless I can understand better I can only offer the above suggestions / info. I will experiment with the attachment you sent and see if it registers in my brain.
Dec 14 '10 #25
ADezii
8,834 Expert 8TB
I'm not exactly clear as to what is going on neither but it is perfectly natural for a Test to belong to 1 or more Profiles. It is the Unique combination (Composite Index) of Profile <==> Test which must be Unique and can not be duplicated. Simply stated, a specific Test cannot belong to a specific Profile more than once.

P.S. - I am not seeing an Attachment for Download from my end.
Dec 14 '10 #26

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Rizyak | last post by:
This message is cross posted in alt.comp.lang.php & comp.lang.javascript I have a form for a user to input an establishment's hours and what time an event is taking place. After the user inputs...
1
by: anirudh | last post by:
Hello all, I want to know if this is even possible. This is my DB structure Table SampleDate : SampleID SampleDate Comment 1 1/1/2005 X
0
by: biap | last post by:
I'm working on a client/server application.The client is a window Form and the server is a Console Application. But now I also need a form in the server application,which I added. but if I call the...
11
by: alphaomega3 | last post by:
I have a form called frmIlogEntry. this form is based on the table tblILOG. I have a fields in the table and on the form VendNo and VendName. On the form I want when the user types in the VendNo...
15
by: terbolee | last post by:
I have two tables: Organisations & Invoices. I have a form called Invoices that is used to enter data into the invoices Table. When entering data, I'd like to be able to type into the field, and...
21
by: bha123 | last post by:
Hi I am struck with this problem . i have 2 forms .. Form A has 2 dropdown box state : city :
3
by: MIkeC | last post by:
Apologies if this has already been posted. I am coding in VB I am trying to find the best way to populate a form from a sql Database in .net 2005 I have a company table that has > 20...
4
by: brad | last post by:
Hi there, New to javascript. Question about populating a form on a webpage that I do not control. I can see the field names and ids and currently have a button that onsubmit opens the page in a...
1
by: ramel | last post by:
Hi all, I have a problem related to javascript , ajax and jsp. Please see this url http://xil.co.in/print_screen_2.JPG. I am working on this form. There are some buttons (like submit ,...
5
tsubasa
by: tsubasa | last post by:
I have a form in ASP.net that has a checkbox option to auto populate the form with the user infomation that is held in an table. When I select the checkbox the auto populate works well with texboxes...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.