Update multiple text boxes in a report from a single table field

[Nangasaur]

I'm very new to access, mostly self taught through modifying templates and getting the results I want through lots of trial and error.

I've created an access report to basically mimic a DA Form (Deparment of the Army) that records equipment inspection dates, who did the inspection, and when the next inspection is due. It also has a lot of other not important info, for the sake of my question here.

I'm using the Customer Service template for my shop needs, and have modified it quite a bit to collect all the data I need and store it as needed.

Now in order to get my access Report to fully work as an electronic format for what we essentially already do by hand on paper, I need it to be able to pull previously entered data in a table field and continue to update up to 40 new entries of data in the same table field.

It basically looks like this:

Date Updated | Person's Initials | Date Next Due
07/15/2009....|.........TG..........|..01/14/2010.. <----two inspections ago
01/04/2010....|.........AB..........|..06/01/2010.. <----one inspection ago
05/08/2010....|.........FR..........|..11/17/2010.. <----inspected today

So on and so forth, up to possibly 40 times, and then it's time to just start a brand new sheet of paper.

I can obviously get my report to show the most recent update (The 05/08/2010, FR, 11/17/2010), but I need my report to fill in the previous text boxes with the old data, like a log, and even continue to fill in data as it is updated in the future.

I've considered using column history and making each field (Date Updated, Person's Initial's, Date Next Due) a memo field, but of course I could not figure out how to separate each entry into separate text boxes on my report, nor even remove all the system recorded data like date/time stamps.

I've also considered a table that stores each entry a brand new field, but that creates a seemingly very numerous fielded table (three sets of data, x40, = 120 separate columns per entry). I can live with that approach, but then I started also considering how extremely long and complicated of a Nz or IIF statement to check if there was anything in "Date Updated40" all the way down to "Date Updated01", and update my text boxes on my report accordingly, while also showing only the most recent data on my form before someone updates it in the form.

I'm pulling my hair out, thinking surely there's something I'm missing due to my newbieness as to how this is easily done, and would surely appreciate any help I can get. I'd be happy to give a sample of my tables, forms, and report to give a helper a better idea of what I need.

Thanks for any help or tips, and please be gentle on me

 

[wiklendt]

I need it to be able to pull previously entered data in a table field and continue to update up to 40 new entries of data in the same table field.

i don't understand - are you trying to enter data using a report? if not, what do you mean by "continue to update up to 40 new entries"... do you mean you only want 40-odd lines per report?

as an aside, why not have [whatever you're inspecting] as a form, and the inspection dates in a subform, in descending order (so the latest shows on top, followed by older ones)? then the user can see ALL the previous inspection dates...? and any new things to be entered can be either in the subform, or in a different setup depending on what you actually mean...

 

[Nangasaur]

i don't understand - are you trying to enter data using a report? if not, what do you mean by "continue to update up to 40 new entries"... do you mean you only want 40-odd lines per report?

as an aside, why not have [whatever you're inspecting] as a form, and the inspection dates in a subform, in descending order (so the latest shows on top, followed by older ones)? then the user can see ALL the previous inspection dates...? and any new things to be entered can be either in the subform, or in a different setup depending on what you actually mean...

No Not trying to enter data using a report I was merely trying my best at describing my predicament per the posting guidelines. I want my Report to mimic exactly what our paper-form does when we do it by hand.

I dabbled a little bit today in using a subform, before I read your reply.

Here's where I'm currently at:

My customer details form shows the current inspection data. (Date item was inspected, who inspected it, and when the next inspection is due.)

Under those, I created a button to open what I guess you would call a subform. It's a modal dialogue style form. My button opens the modal dialogue form with the following macro:

IsNull([Customer Name]) MsgBox Select a Customer who's vest card...blah blah
...GoToControl cboGoToCustomer
...StopMacro
OpenForm VestCardUpdateForm, Form, , [Customer Name]=[Forms]![Customer Details]![Customer Name], Edit, Dialogue

Now, my VestCardUpdateForm pops up, showing the customer name of the individual who I previously had opened in the Customer Details form. Under it, I have 6 boxes all labeled as such:

Current Date Completed---Current Due Date---Current Initials

New Completion Date---New Due Date---New Intials

Top three text boxes are NOT editable on this form. Bottom three text boxes are editable, so the user can update the table fields appropriately with the new information.

All this data is stored in a table called "Vest Card Log", where I have the following fields for each customer: DC1, Due1, Init1, DC2, Due2, Init2, DC3, Due3, Init3, etc etc so on and so forth, all the way to 40.

This is where my Report comes in, and has text boxes for all those DC1, Due1, Init1, DC2, Due2, Init2 etc etc boxes to plug the data into the appropriate spaces on my Report.

So where I am stuck is here:

I need my VestCardUpdateForm to always reflect the top three boxes (the uneditable ones) with the most recent data, and always have the bottom three boxes ONLY add data to the next 3 fields, in order.

Make any more sense?

I've tried using the .controlsource function with RunCode in a macro that runs when my VestCardUpdateForm loads, in an IIF statement, but alas, I'm either not understanding how the .controlsource function is supposed to work, or I'm just not writing my macro properly. Or even, this is the completely wrong way to do it and I'm just not getting it.

I've also tried doing this directly for each field on my VestCardUpdateForm, in the Property Sheet for the text box Control source:

=IIF([Init1] Is Not Null, [Init2],[Init1])

And this, of course, shows me the correct data I want to display in my top three boxes, however, it won't work for my bottom three boxes because I get an error message telling me I cannot edit those fields because they're bound to an expression.

So, I need either a macro when the form loads, or updates even, or even a really complicated (to me) VBA code segment that allows me to type data into the text boxes, which then sends the data to the appropriate field in my table.

An editable (or user-inputtable) text box that automatically changes it's control source when the form loads, based on the existence of data in the same set of control sources (fields in a table).

Edit: And in reference to your last question, "Do you only want 40-odd lines in your report?"

Yes, somewhat. Because there's only room enough on the paper-form for 40 entries, so I want my electronic Report to look exactly like my paper-form of the report. It is a DA Form (Department of the Army) after all, and I'm really not supposed to make basic changes to the paper-form without submitting the request to higher ups, which can take years at the pace the Army moves. It has to keep a universally recognized design. So, I created my access Report to look EXACTLY like the DA Form, and plug the data into the blocks I need filled in appropriately.

 

[Nangasaur]

Okay so here's where I'm at again, after falling asleep last night with this on my mind.

Using SetProperty function in a macro that fires on load of my VestCardUpdateForm, I'm telling it to do this:

[Init1] Is Not Null   SetProperty  Init1Box, Visible, 0

And Similarly for all my boxes. I also just realized it would make for a much smaller macro if I set the default visible property of them to be invisible, run the macro, and it check for the very first one that is null, set it's property to visible, and stop the macro there before advancing on to the next boxes.

This would then leave me with stacking all the boxes on top of each other in my form, which could be quite messy and rather primitive to me. Because of this, I'm really hoping there's a much better and cleaner way to accomplish what I'm trying to do. Any suggestions would be wonderful at this point; I just hope I'm at least on the right track.

 

[DCrake]

Baffled to say the least. Can you post a copy of your db to look at?

 

[Nangasaur]

Baffled to say the least. Can you post a copy of your db to look at?

Certainly.

For now, I've gone with the mundane and primitive approach of hiding and unhiding boxes on my form, depending on if data is present or not in their associated fields of the table that stores the data. It's working the way I want it to, somewhat, but I really don't like it and it makes for an extremely long macro, which I've only actually written about 1/3rd of it, and won't do the rest until I know I've got it all working exactly how I want it to.
The part that isn't working right, is when I tab through the last editable box (there's three of them), my form clears all data as if it loaded without being pointed to the record I want to view. If I don't tab through after I edit the last of the three boxes, and just click save or hit enter, it closes the form and saves the data as intended. I want to fix this tabbing problem, and I'm suspecting it has something to do with the form refreshing and not grabbing the record data from when I initially launched it.

There's two tables that are important for what I'm working on:

Customers
Vest Insp Log

Main Form:
Customer Details

Form launched from Customer Details with the "LaunchVestCardUpdateForm" button:
VestCardUpdateForm

My Crazy macro is:
GetInspDatesBoxes

When looked at, one will wonder why Customer Details isn't grabbing data from Vest Insp Log, but rather, Customers instead. That's because I haven't fully implemented Customer Details' source query to include that table until I've got this hammered out the way I want it. Only VestCardUpdateForm uses a query to grab data from Vest Insp Log.

Lastly, my Report is "Vest Inspection Card". Launchable from the Customer Details form with the "Create New Vest Card" button.

edit: also, please forgive my terrible looking layout of my VestCardUpdateForm. I don't make things pretty while I'm trying make it work. Make it work first, then make it look pretty

 

[boblarson]

Okay, several issues to start with:

1. Your database needs to be normalized. Currently you have repeating fields in tables, which should not exist. They need to be in a separate table and as RECORDS not columns. For example, DC1, PID1, DUE2, DC2, PID2, etc.

2. Your Customer and Customers Extended table have redundant information stored and it should not be that way. You only need to store the FirstName, LastName, Bacitracin, Bacitracin Exp, etc. in ONE table. And actually the Bacitracin, Bacitracin Exp, Loperamide, Loperamide Exp, etc. again should be RECORDS in another table (as a one-to-many situation).

3. Your Relationships window should NOT have the Queries in it. You set relationships for the TABLES and then all is set.

That is a start and should take you a bit to get it fixed, before we move on further.

 

[Nangasaur]

Okay, several issues to start with:

1. Your database needs to be normalized. Currently you have repeating fields in tables, which should not exist. They need to be in a separate table and as RECORDS not columns. For example, DC1, PID1, DUE2, DC2, PID2, etc.

I'm separating them into records for individual customers. For instance, the example one I left in the database I uploaded is just "John Doe". In the database I'm actually working with, it contains over 300 records (Each record is a specific person), but I deleted all those to upload to here, so as not to put personal info about my soldiers on the internet, and created "John Doe". I'm not sure how making the DC1, PID1, DUE2, DC2, PID2 into records, instead of columns, would work, since they're individual fields for each record (Person), containing different inspection dates, inspector's initials, and next due dates, for each record (person). If you could explain further what you mean, I would appreciate it wholeheartedly.

2. Your Customer and Customers Extended table have redundant information stored and it should not be that way. You only need to store the FirstName, LastName, Bacitracin, Bacitracin Exp, etc. in ONE table. And actually the Bacitracin, Bacitracin Exp, Loperamide, Loperamide Exp, etc. again should be RECORDS in another table (as a one-to-many situation).

Customers Extended is a query of Customers. The Customer Details form is pulling all the data from the Customers table. Yeah, I fully intend to move the bacitracin, acetominophen, etc data to it's own table, and then creating an entirely new "Customers Extended" query for the Customer Details form to work with. I'm assuming that's what you mean by normalizing it; putting different types of data into it's own table, then pulling it all together with a query for the form to work with. I can do that now with my test database fairly quickly, since it only has one record (John Doe).

3. Your Relationships window should NOT have the Queries in it. You set relationships for the TABLES and then all is set.

I laughed at myself over this, because I did all that junk to help me learn exactly what the relationships window does and how it links data. Thanks for letting me know I left this embarassing junk in there

That is a start and should take you a bit to get it fixed, before we move on further.

Thanks a bunch!

 

[wiklendt]

I'm separating them into records for individual customers. For instance, the example one I left in the database I uploaded is just "John Doe". In the database I'm actually working with, it contains over 300 records (Each record is a specific person), but I deleted all those to upload to here, so as not to put personal info about my soldiers on the internet, and created "John Doe". I'm not sure how making the DC1, PID1, DUE2, DC2, PID2 into records, instead of columns, would work, since they're individual fields for each record (Person), containing different inspection dates, inspector's initials, and next due dates, for each record (person). If you could explain further what you mean, I would appreciate it wholeheartedly.

people's inspection dates actually have nothing to do with the people themselves. normally, all you'd expect in a people table is stuff like primary key, first name, surname, eye colour, phone number, address... etc.

then you ought to have an inspections table, with primary key, foreign key (the primary key from the people table), inspection date, outcomes, etc... the two are related via the primary/foreign people key.

this means that any one person can have multiple inspections without having to put that person in millions of times - or as more often occurs, without having an ever growing number of columns in the people table (DC2, DC3, DC4, DC5...).

this is called normalisation (or "normalization" for you yanks).

some designers would go even further and normalise out the contact details of each person into a new table, so that they can have many contact details for each one person if they need (home, work, next of kin, site 2, etc)

 

[Nangasaur]

Okay, I've made 5 separate tables:


Customers - Contact details only

MedData - Medication data (lot numbers and expiration dates of the medications)

Vest Insp Log - Logs all the inspection dates (DC1 thru DC16 for the time being), Who did the inspections (PID1 thru PID16), and next inspection due dates (DUE1 thru DUE16)

Serial-Size Data - Serial numbers and sizes of the equipment containing serial numbers

Check In or Out Status - Just a 2 field table to track if equipment is checked in or out of the shop

I've made primary keys for each table, such as ID for Customers, MedID for the meds table, SerialID for the serial#'s table, VestLogID for the vest insp log, and CheckInID for the checked in status table.

I'm seeing that in order for me to make a One-To-Many relationship between Customers, and the other 4 tables, I have to set the primary key to not be indexed in those other 4 tables. So I try to do that in design view of the tables, and of course I am told I have to turn off the primary key for that table. Should I be doing this? Only have Customers with a primary key set (ID), and the other 4 with no primary key? Is that what a foreign key is when I make a one-to-many relationship between my Customers Table ID to, say, MedID?

Also, in creating the relationship, there's 3 check boxes in the Edit Relationships window:

Enforce Referential Integrity
Cascade Update...
Cascade Delete...

From what I'm reading, I should be checking all three of those boxes if I want to make sure that whenever I update or delete a RECORD from Customers, it will also make sure a record in the other tables that is related to the one I'm updating/deleting in Customers will also be updated/deleted, in order to prevent orphan records in those tables from lingering without a related record in Customers.

Am I understanding that correctly?

Now, when I try to make a Relationship between my Customers primary Key (ID) and my MedData primary key (MedID), with enforce/cascade update/cascade delete checked, I get an error telling me "Invalid field definition 'MedID' in definition of index or relationship.

*puzzled*

 

[Nangasaur]

I did a google search on the error message I was getting, and found a post on another forum from "Bob Larson" (yay! hi there!) describing what I needed to do to fix my relationships and explained the primary key/foreign key stuff enough for me to understand it.

My only question with it is, should I be setting my foreign keys to Indexed (Yes - Duplicates OK), (Yes - No Duplicates), or (NO)?

Thanks, Bob.

edit: and yes, at this point I've completely realized this whole thread is probably far more suited for an entirely different subforum, as my problem really didn't lie with my Report at all.