How to fill a field in a subform using a field in the parent form as a lookup?

[sueb]

I have a form/subform arrangement where the parent form has a Patient_Type that I want to translate into a Hospital_Category based on an existing lookup table. The relationship between Patient_Type and Hospital Category is many-to-one; i.e., there are several patient types that collapse into a single hospital category.

The parent form is "FBR220V direct form" (this is a tab-delimited file supplied to us that we import into an existing table structure). The Patient_Type field's control is "PAT_TYP".

The subform's control is "Coders' Comments". The Hospital_Category field's control is "HospitalCategory".

The lookup table, "Patient Types / Categories", has three fields: "Index" (an autonumber), "Patient Type", and "Hospital Category".

On the subform, the "first" field is the user's initials, which would be the first thing they enter, and I'm thinking that I would like the Hospital Category to be filled only after they supply their initials. So I think I want whatever I do to be in the AfterUpdate property, but I don't know the syntax for using the parent's "PAT_TYPE" as a key to extract "Hospital Category" from the lookup table.

Thanks for your help!

[TheSmileyCoder]

Assuming Patient type is string:

In the afterUpdate event:

dim strPatType as string
strPatType=Me.Parent.PAT_TYP
Me.HospitalCategory=Dlookup("Hospital Category","[Patient Types / Categories]","PAT_TYPE='" & strpatType & "'")

[NeoPa]

Another, possibly less obvious, solution is to use a prepopulated, multi-column, ComboBox control where the user can select from the available Patient Types, but what is saved is the column containing the Hospital Category. This avoids repeated lookups in the data and uses a prepopulated list (so table only accessed once) that ensures only valid options are selected.

[sueb]

I like both these suggestions, and will probably try each in turn, just to see what happens.

NeoPa, is there a way that I could make what you suggest happen programmatically? The imported data provides the Patient_Type, and I think it would be nice and smooth if the subform just filled in the HospitalCategory field (based on the Patient_Type in the parent form) as soon as the user fills in her initials.

(Did I explain that the user is entering data only into the parent form? All the fields on the parent form are locked. If this makes anything clearer, or makes any difference.)

[sueb]

@TheSmileyCoder: I added the code you suggested to the AfterUpdate event of the field that accepts the coder's initials, but I get run-time error # 3075"Syntax error (missing operator) in query expression 'Hospital Category'." on line 6 (I assume that, for our purposes here, "line 6" is no different from "line 4"--it's just the end of the statement):

Private Sub Coder_AfterUpdate()
    Dim strPatType As String
    strPatType = Me.Parent.PAT_TYPE
    Me.HospitalCategory = DLookup("Hospital Category", _
                                  "[Patient Types / Categories]", _
                                  "PAT_TYPE='" & strPatType & "'")
End Sub
 

I don't have a very clear understanding of exactly what's happening on that line (which bits are pointing to what), so I'm a little at a loss to know how to fix it.

[sueb]

@NeoPa: I also want to put buttons on the parent form that would filter the parent records by the Hospital Category of the PAT_TYPE in the parent record.

I'm thinking that, once I get the lookup SQL statement correct, I would put that same statement behind buttons on the parent form. I would like there to be a separate button for each Hospital Category. What do you think?

[NeoPa]

I'm a bit lost ATM Sue. I don't see how the subform links to the main form, and I don't see any correlation between the user and the PatientType. Without these I don't see how the question even works I'm afraid. I gave general advice that should help without even any understanding of your structure, but to answer your latest comment I'd need an understanding of what's what.

PS. If there's something in the database I've downloaded that might make it clearer you can explain it that way if you prefer.

[sueb]

I guess I didn't really make very clear what's going on with this data.

The users are provided each day with data from another system. Within this data is the PAT_TYPE field. Each day's data is intended to "overwrite" the previous day's, except that part of what they do with this data requires that they store some processing comments about each record. Currently, they have this horrific manual process involving importing the foreign data into an Excel spreadsheet, and manually copying the previous day's work, matching it up with the new records.

They divide the work (which is relatively massive, 6-7k records each day) among themselves using HospitalCategory, and each hospital category comprises several patient types. Currently, they manually manipulate that gigantic spreadsheet and copy-and-paste the different category sections into different spreadsheets, which they deal out to the right worker.

What I'm trying to set up is two linked tables: one that contains each day's imported data (FBR220V direct import), and another (Coder's Comments) that allows them to store their work. These tables are joined by the unique field PAT_ACCT_NBR. The form I'm working on displays the table containing the imported foreign data (those fields are all locked), and a subform that allows them to store their work.

I've actually changed my mind a little from my original question: I realized that I don't want to store the hospital category, because I was also planning to put some buttons on the parent form that would filter the parent records by HospitalCategory using PAT_TYPE, so if I can just get some help creating those filters (there would be a separate button for each HospitalCategory), I think I'll be cool. (Please note that the attached subform still has the field HospitalCategory, but I'm going to remove that, so please ignore it.)

The lookup table is named Patient Types / Categories.

I hope this makes it a little clearer. If not, here's a scrubbed and decimated database. (The daily table has between 6-7k records in it; this one has a little over 200.) The form that comes up when you open it is the one that I'm working on.

I appreciate any advice; I really want to get them something to ease their pain! :)

[NeoPa]

Very little clearer I'm afraid Sue, even with the database to hand.

However, if your requirement is simply about how to implement some sort of filtering on either your form or your subform, then I think I can help you if you make what you want clear. I have a copy of the database now, so it should be a little easier to specify I would hope.

[sueb]

I'm not sure why I'm having such trouble explaining what they need. Basically, they just need to make notes about accounts they are working on, and be able to retain those notes as the daily set of accounts changes. The filter thing is just the way they divvie up the work--each person takes a different hospital category--so I want to make it easy for them to select a category of records to work on.

I want to automate the filtering function with buttons on the parent form, one for each hospital category. Each button would, using the lookup table, filter the parent records (foreign data) to include only those records whose PAT_TYPE fell into the chosen HospitalCategory.

Does that make sense?

[NeoPa]

It does Sue. It seems this is a simpler question than I think you realise possibly. If I point you at an article that covers what I suspect you need (Cascaded Form Filtering), you can tell me if that fits the bill :-)

[sueb]

That is a great article, NeoPa!

However, I guess I still don't understand how to do what I want. I want to pick a HospitalCategory, and filter the records that contain any of the various PAT_TYPE values that translate to that HospitalCategory.

It's like I want to use PAT_TYPE as a pointer into the lookup table, retrieve HospitalCategory, and use that as the filter discriminator.

[NeoPa]

Let me get this straight (Your last paragraph seemed to contradict the previous one so I'm a bit confused). Is it right that you want to select a HospitalCategory from the main form (I assume an unbound ComboBox control) then, using this HospitalCategory value, filter the records of the form to show only those which have a Pat_Type which is associated with the selected HospitalCategory?

If I'm right then this would probably be because the underlying data of the form doesn't, itself, contain the HospitalCategory field. The most straightforward solution to that would be to amend the underlying data to include the HospitalCategory data, while still maintaining an updatable recordset if that's required.

If that's not correct then it can also be done in the code by setting up a string list of all the associated Pat_Types within the selected HospitalCategory.

[sueb]

It sounds like maybe I've finally managed to untangle my words enough to make some sense! :) Yes, that's right, that's what I want to do.

And you're absolutely right, the underlying data doesn't have HospitalCategory, and, yes, the most straightforward solution would be to amend the underlying data, but the magnitude of 6-7 thousand new records a day dictates that this be automated, and it seems to me that whether I store the HospitalCategory, or just filter on it, step 1 is to use PAT_TYPE to choose HospitalCategory for each record.

Yes? No?

That's the thing I don't know how to do.

And I really thought that a lookup table would be a much more organized and maintainable approach than just a string list, which I personally find sort of icky. If that's the only way to do it, however, then I guess I'll have to do that.

[NeoPa]

Now I'm not making myself properly clear. When I suggested amending the underlying data, I meant to say amending the design of the RecordSource providing the underlying data. That is to say to use a query that contains the existing underlying data, but also links it to the lookup table you suggested.

Yes, using a Lookup Table is by far the preferable approach. My solution was suggested as it had the benefit of being easily understood as well as not relying on intimate knowledge of your database (that I don't have).

Does this make more sense?

[sueb]

Ah, a glimmer in the darkness! :)

Okay, I modified the parent form's query to include the lookup table (which already was in relationship with the foreign data), and I added one (of the ultimate 18) Hospital Category buttons to try setting the filter. Here's the code:

Private Sub VCMC_ER_Button_Click()
On Error GoTo Err_VCMC_ER_Button_Click
 
'    Me.Filter = "[Hospital Category] = 'VCMC ER' "
    Me.Filter = "[PAT_TYPE] = 'PSY' "
    Me.FilterOn = True
 
Exit_VCMC_ER_Button_Click:
    Exit Sub
 
Err_VCMC_ER_Button_Click:
    MsgBox Err.Description
    Resume Exit_VCMC_ER_Button_Click
 
End Sub
 

I'm able to filter on the PAT_TYPE, but when I tried filtering on the now-available Hospital Category, a pop-up asked me to provide that value.

I just can not figure out how to address the lookup table! What is the correct syntax?? Why is it not available to my button in the same straightforward way it is available to display on the form?

[NeoPa]

Is it possibly as simple as the fact that the code commented out refers to [Hospital Category], whereas the OP refers to the field as [Hospital_Category]?

If not, we would probably benefit from dealing only with the correct names of all objects in all circumstances.

PS. You have attached a copy of the database to the thread already. Is what we're discussing already available in that copy? If so, how can I find it?

[sueb]

Here is the database as it stands, and the code in question is behind the button "VCMC ER" on the form "FBR220V direct import".

I realize I've been all over the map with this dumb problem, so I really appreciate your patience with me on it!

[NeoPa]

Sue, I'm afraid I came across a few things in here that didn't behave as one would normally expect. If you know any reason why opening the table [Hospital Categories] should give the option to open out the [Coders' Comments] table, then please speak up now. There was no relationship defined to link these tables (I suspect one may have been while you were looking into things earlier on and it was simply never properly deleted, even though it is not fully saved/visible either).

I deleted all the relationships I found as well as Compacted/Repaired the database, but without success. I think you may be in the situation where you need to create a new blank database and start importing the objects across, one by one, until you have a solid database to work with. Don't forget to replicate the various items that are database specific though. Not every change you've made will be associated with importable objects.

I didn't find anything wrong with your code BTW. I think you can put that error down to the database corruption. If you download and open the associated database from the article, you can play with that and see what sort of things do and don't work (If that'll help of course).

[NeoPa]

I did some extra work to clean up the dependency that I confirmed first was spurious. Unfortunately a whole bunch of other issues then came up (and the form continued to exhibit the same behaviour). At this point I would say recreating the database from scratch and importing any usable objects from the old version (as mentioned in my earlier reply) is your next step. Never good news I know, and maybe someone out there knows more about such things than I do and knows a way to fix this without such a step. It looks pretty corrupted to me though, and Access databases are known to get corrupted from time to time depending on how they're used.

[sueb]

You're right, NeoPa, that ([Hospital Category] linking to [Coder's Comments]) makes no sense. I'll do as you suggest and start with a clean slate and see what I come up with.

Be back (probably much!) later.

[NeoPa]

I'd be interested to know if that resolves your issue. I must admit I expect it should.