Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Access data entry continuous form containing bound and unbound controls
I work in a laboratory and would like to create a data entry form to inventory specimens. Each specimen will be stored in a freezer box. This box contains 81 positions to store unique specimens. All 81 unique specimens share common characteristics. I have created a continuous form with the following combo boxes in the header:
ComboBoxID
ComboAnalyte
ComboSpecimenMatrix
ComboYear
ComboFreezer
ComboShelf
ComboRack
ComboBox
These Combo boxes are shared with all 81 unique specimens and are associated with unbound text boxes located in the form's detail section. In the detail section I have also created a bound textbox to record the specimen unique identifier (barcode number) and a bound combo box to select the volume present in the specimen tube. There is also an unbound textbox that populates the box position automatically/consecutivel
Here is my problem. I cannot figure out how to append the information contained in the unbound textboxes to the table containing the information from the bound textboxes. I have tried creating a bound continuous form, but do not want to select the 8 shared characteristics for all 81 specimens. My goal is to quickly scan all 81 specimen bar codes and easily select the defining characteristics at once for all specimens.
Any help would be greatly appreciated.
ComboBoxID
ComboAnalyte
ComboSpecimenMatrix
ComboYear
ComboFreezer
ComboShelf
ComboRack
ComboBox
These Combo boxes are shared with all 81 unique specimens and are associated with unbound text boxes located in the form's detail section. In the detail section I have also created a bound textbox to record the specimen unique identifier (barcode number) and a bound combo box to select the volume present in the specimen tube. There is also an unbound textbox that populates the box position automatically/consecutivel
Here is my problem. I cannot figure out how to append the information contained in the unbound textboxes to the table containing the information from the bound textboxes. I have tried creating a bound continuous form, but do not want to select the 8 shared characteristics for all 81 specimens. My goal is to quickly scan all 81 specimen bar codes and easily select the defining characteristics at once for all specimens.
Any help would be greatly appreciated.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hmm..based on your description above, the contnuous form might not be a good choice for what you trying to do. Perhaps a main form/subfoms combination will work better, but without seeing your db schema it is hard to make a suggestion that will work best.
Please find the attached stripped down version of the database. FYI...I am new to Access and learning as I go.
The two tables of interest are: 1) Molecular Freezer Inventory Mapping and 2) PHIMS/OpenELIS Clinical Specimen Numbers. These tables will be populated using the PHIMS/OE Entry Form.
Thank you for taking a look and let me know if you have any questions.
Test-Molecular-Inventory-Copy.accdb
The two tables of interest are: 1) Molecular Freezer Inventory Mapping and 2) PHIMS/OpenELIS Clinical Specimen Numbers. These tables will be populated using the PHIMS/OE Entry Form.
Thank you for taking a look and let me know if you have any questions.
Test-Molecular-Inventory-Copy.accdb
I am not sure i "got" your design philosophy .....why the use of unbound textboxes when you can have them bound and "fill" them ...
If this is the way you want them a quick and dirty solution would be to create a concatenated field with delimiter either bound or unbound and use this to manipulate your entries...
e.g 12|1|abc|2
and then use a method like this to retrieve the data
If this is the way you want them a quick and dirty solution would be to create a concatenated field with delimiter either bound or unbound and use this to manipulate your entries...
e.g 12|1|abc|2
and then use a method like this to retrieve the data
Public Function ParseText(InputText As String, Delimiter As String, Part As Integer) As String
On Error Resume Next
ParseText = Split(InputText, Delimiter)(Part)
End Function
Thank you for the advice John. I originally designed the form with bound controls, but I had to fill each 81 rows separately with the same information. I appreciate the suggestion of using concatenated fields, but I do not know how to get this to work with my individual combo boxes.
I think you may be right about the redesign. I have attached a stripped down version of the database in a previous post to Rey. I created two main tables to record freezer boxes. 1) Molecular Freezer Inventory Mapping. This table contains the following fields: Designation, BoxID, Year, Analyte, SpecimenMatrix, SpecimenResult, TechInitials, LotNumber, RUO, Freezer, Shelf, Rack, Box and Comments. It is used to track all box types (Designations). 2) PHIMS/OpenELIS Clinical Specimen Numbers. This table supplements the main table with an inventory of the box contents. The fields associated with this table are the following: PHIMS_OpenELISNo, MatrixVolume, Freezer, Shelf, Rack, Box and BoxPosition. The following fields are used to link the two tables together: Freezer, Shelf, Rack and Box. In hindsight I probably should have used a single field, BoxID, to link the two tables which would be unique.
My goal is to take a box from the freezer and scan the contents via barcodes. There are 81 individual tubes that share the same information: Designation, BoxID, Year, Analyte, SpecimenMatrix, Freezer, Shelf, Rack, Box. The fields that are unique are: PHIMS_OpenELISNo, MatrixVolume and BoxPosition. Ideally, I would like to select these specifics once for all the box contents and just scan in the barcodes. I even have the BoxPosition calculated with the creation of a new entry.
I hope this is not too confusing to follow, and helps you better understand my particular needs for the database.
Many thanks!
My goal is to take a box from the freezer and scan the contents via barcodes. There are 81 individual tubes that share the same information: Designation, BoxID, Year, Analyte, SpecimenMatrix, Freezer, Shelf, Rack, Box. The fields that are unique are: PHIMS_OpenELISNo, MatrixVolume and BoxPosition. Ideally, I would like to select these specifics once for all the box contents and just scan in the barcodes. I even have the BoxPosition calculated with the creation of a new entry.
I hope this is not too confusing to follow, and helps you better understand my particular needs for the database.
Many thanks!
I forgot to mention one important detail. This form is for data entry. I would like to see an empty form every time I open it.
Thank you!
Thank you!
The empty form is easy just set the form to Data Entry --> Yes (Properties/Data tab)
As for the redesign maybe you could substitute the "static" fields to a single number :
Designation, BoxID, Year, Analyte, SpecimenMatrix, Freezer, Shelf, Rack, Box. --> No xxx
As for the redesign maybe you could substitute the "static" fields to a single number :
Designation, BoxID, Year, Analyte, SpecimenMatrix, Freezer, Shelf, Rack, Box. --> No xxx
This is what I have done thus far:
1) Added a new field, BoxID, to PHIMS/OpenELIS Clinical Specimen Numbers Table.
2) I deleted the old relationships (freezer, shelf, rack and box) between the parent table, Molecular Freezer Inventory Mapping, and PHIMS/OpenELIS Clinical Specimen Numbers and created a single relationship, BoxID.
3) Created a new bound form with the following controls in the detail section: Designation, Year, Analyte, SpecimenMatrix, Freezer, Shelf, Rack, Box and BoxID. All of these controls, with the exception of BoxID, derive from the parent table. BoxID is from the PHIMS/OpenELIS Clinical Specimen Numbers table.
4) I added a continuous subform displaying the following controls from the the PHIMS/OpenELIS Clinical Specimen Numbers table: BoxPosition, PHIMS_OpenELISNo and MatrixVolume.
I believe everything is working. The tables are populating correctly. My problem now relates to the BoxPosition control automatically populating with each new entry. This number will always start at 1 and may or may not always end at 81. Do you have any ideas?
Thank you!
1) Added a new field, BoxID, to PHIMS/OpenELIS Clinical Specimen Numbers Table.
2) I deleted the old relationships (freezer, shelf, rack and box) between the parent table, Molecular Freezer Inventory Mapping, and PHIMS/OpenELIS Clinical Specimen Numbers and created a single relationship, BoxID.
3) Created a new bound form with the following controls in the detail section: Designation, Year, Analyte, SpecimenMatrix, Freezer, Shelf, Rack, Box and BoxID. All of these controls, with the exception of BoxID, derive from the parent table. BoxID is from the PHIMS/OpenELIS Clinical Specimen Numbers table.
4) I added a continuous subform displaying the following controls from the the PHIMS/OpenELIS Clinical Specimen Numbers table: BoxPosition, PHIMS_OpenELISNo and MatrixVolume.
I believe everything is working. The tables are populating correctly. My problem now relates to the BoxPosition control automatically populating with each new entry. This number will always start at 1 and may or may not always end at 81. Do you have any ideas?
Thank you!
If this is the issue then you could run an append query to populate all the unused Boxes...if i misunderstood then a new sample i guess is needed
Please find that attached database. My new data entry form is "Test." In the subform I would like to have the BoxPosition control to automatically and serially populate from 1. Please let me know if you have any questions.
Thank you.
Test-Molecular-Inventory-Copy83115.accdb
Thank you.
Test-Molecular-Inventory-Copy83115.accdb
In order to have this sequential BoxPosition you could use this code :
BoxPosition = Dmax(Nz("BoxPosition","PHIMS/OpenELIS Clinical Specimen Numbers","BoxID = " & Me.Parent.BoxID),0))+1
Hello John!
Thank you for your help. I do have a couple of questions for you regarding the above mentioned code. I added it to the control source and when in form view see, "#Name?". I also see the following messages when in design view, "Invalid Control Property:Control Source" and "Expressions must begin with an equal sign (=)." I checked the code again and Access altered it to read, "[BoxPosition = Dmax(Nz("BoxPosition","PHI
What are your thoughts? I also tried adding it to Before/After Update with little success.
Thank you
Thank you for your help. I do have a couple of questions for you regarding the above mentioned code. I added it to the control source and when in form view see, "#Name?". I also see the following messages when in design view, "Invalid Control Property:Control Source" and "Expressions must begin with an equal sign (=)." I checked the code again and Access altered it to read, "[BoxPosition = Dmax(Nz("BoxPosition","PHI
What are your thoughts? I also tried adding it to Before/After Update with little success.
Thank you
The above code is for VBA.....right now i can't check your sample but i think you use an "Add" button so on the Click_Event you will insert the above snippet.....
I added a button to the header of my subform and added the snippet of code to the "on click" event. When I clicked on the button I received the following VBA complie error, "Wrong number of arguments or invalid property assignment." It also highlighted "Nz" in the code.
Here is what I have type in VBA:
Private Sub CommandAddBoxPositions_Cli
BoxPosition = DMax(Nz("BoxPosition", "PHIMS/OpenELIS Clinical Specimen Numbers", "BoxID = " & Me.Parent.BoxID), 0) + 1
End Sub
It is actually only three lines in total. The code is all on one line.
Here is what I have type in VBA:
Private Sub CommandAddBoxPositions_Cli
BoxPosition = DMax(Nz("BoxPosition", "PHIMS/OpenELIS Clinical Specimen Numbers", "BoxID = " & Me.Parent.BoxID), 0) + 1
End Sub
It is actually only three lines in total. The code is all on one line.
Yes i see the error ....my wrong....
BoxPosition = Nz(DMax("BoxPosition", "PHIMS/OpenELIS Clinical Specimen Numbers", "BoxID = " & Me.BoxID), 0) + 1
This is the new code:
Private Sub CommandAddBoxPositions_Cli
BoxPosition = Nz(DMax("BoxPosition", "PHIMS/OpenELIS Clinical Specimen Numbers", "BoxID = " & Me.BoxID), 0) + 1
End Sub
But now I get the following error:
Run-time error '3075':
Syntax error (missing operator) in query expression 'BoxID = Bacterial Extracted Specimens (BP/CP/LP/MP) (-)'.
This happens to be what I selected from the drop down box located in the main form detail section. Maybe there is an issue with my form design, but everything was working correctly.
Thank you
Private Sub CommandAddBoxPositions_Cli
BoxPosition = Nz(DMax("BoxPosition", "PHIMS/OpenELIS Clinical Specimen Numbers", "BoxID = " & Me.BoxID), 0) + 1
End Sub
But now I get the following error:
Run-time error '3075':
Syntax error (missing operator) in query expression 'BoxID = Bacterial Extracted Specimens (BP/CP/LP/MP) (-)'.
This happens to be what I selected from the drop down box located in the main form detail section. Maybe there is an issue with my form design, but everything was working correctly.
Thank you
A minor change since the value is alphanumeric
BoxPosition = Nz(DMax("BoxPosition", "PHIMS/OpenELIS Clinical Specimen Numbers", "BoxID = '" & Me.BoxID &"'"), 0) + 1
The box position controls for all records in the subform fill in with "1" when I click the command button. Also, these values are not appending to the PHIMS/OpenELIS Clinical Specimen Numbers Table.
I just realized that my BoxPosition control was unbound. It is now corrected. After scanning in my barcodes and clicking the button the only BoxPosition control to populate with "1" is the record in focus. I hope this helps troubleshooting.
Thank you very much for your time!
Thank you very much for your time!
Hello again John.
Please see the following link:
http://www.access-programmers.co.uk/forums/showthread.php?t=217901
This is what I would like to happen, but don't know how to append the data to the table.
Thank you very much,
Andrew
Please see the following link:
http://www.access-programmers.co.uk/forums/showthread.php?t=217901
This is what I would like to happen, but don't know how to append the data to the table.
Thank you very much,
Andrew
its a slow process...i have tried it in the past but i can't remember how many records where involved....on the other hand if this is what you want it works
I appreciate your time in troubleshooting this aspect of my form. The code works, but it does not append to the table. Do you think I should create a new question specific to this problem?
Thank you again!
Thank you again!
If my memory serves me right the code shows only visual representation....
I must be honest with you..you have an interesting project but in order to get it done it needs some works which in my opinion cannot be covered by a Q&A site...
If it was my project to undertake i would need a clear workflow description...possible a diagram that shows what it needs to be done and in what order...
I must be honest with you..you have an interesting project but in order to get it done it needs some works which in my opinion cannot be covered by a Q&A site...
If it was my project to undertake i would need a clear workflow description...possible a diagram that shows what it needs to be done and in what order...
I will do my best to provide you with description of workflow. Will get back to you soon. Thank you!
Hello again John,
Please see the attached word doc. I hope this helps explain my situation. Please let me know if I misunderstood your previous question.
Thank you much,
Andrew
Work-Flow.docx
Please see the attached word doc. I hope this helps explain my situation. Please let me know if I misunderstood your previous question.
Thank you much,
Andrew
Work-Flow.docx
Ok...now i see...let me think about it...but probably it will take some time because this week is pressing...
No worries John! I can't thank you enough for helping me! Take care and will speak with you soon.
Andrew
Andrew
John,
Please find attached a new and improved database design. I have addressed some data relationship issues and removed any "spaces" and/or "special characters" from table and column names.
The new form we have been working with is titled, "InventoryMapping," and the respective subform is titled, "SpecimenNumbers subform."
Best,
Andrew
Test-Molecular-Inventory-Copy83115.accdb
Please find attached a new and improved database design. I have addressed some data relationship issues and removed any "spaces" and/or "special characters" from table and column names.
The new form we have been working with is titled, "InventoryMapping," and the respective subform is titled, "SpecimenNumbers subform."
Best,
Andrew
Test-Molecular-Inventory-Copy83115.accdb
Premium Content
You need an Expert Office subscription to comment.Start Free Trial