Textbox record source for datasheet subform view

[mdewell]

Hi,



I've got a form with a subform that is filtered by combobox selections in the main form. Yet, that's not the problem. The problem is with my subform trying to update the data it has.

I've set the record source for the subform's form to a table that has relational links to several other tables. I'm wanting to update the data for a table in the realtional link, and it doesn't seem to be letting me. I know I can get the subform to display ID's from the tables that the data has for each entry in the subform, as that is what is stored in the field for each item in the main table that is linked to the other ones. I also know I can update the ID by using comboboxes tied to the linked tables.

But here's my problem: in my subform it seems I can only update the ID of the computer it is looking up from the computer table that is linked to the main database. I want to be able to change the computer name that is linked to the ID. I would like to update the computer name by using a textbox to change the data. I've got it to show the computer name by doing =DLookUp() and not show the ID number, but then it gives me the common "Control can't be edited; it's bound to the expression 'DLookUp ...", as that is not legal. So, I want to be able to change another table's data that is not bound to the form (subform). I know you can do a query that might have better luck, but I had trouble getting the query to allow any updates to the data. Yes, perhaps my SQL is too complicated (too many JOINs and what not).

Oh, I do know that I could do a form with a subform with a subform ..., but I need the data displayed in a datasheet format, and a subform within a subform can't be done, and I've had no luck with trying to do all the records in a continuous form. Access wouldn't let me set the form in a continuous form format.

Thanks in advance for any help I can get.

[June7]

I am looking at your project downloaded from other thread (BTW, I just added a new post to that thread). Can you be specific about which forms and fields you are referencing? I just tested changing the OU and Computer values in those respective textboxes of the HIPAA_datasheet subform and it allowed the changes. The changes cascaded to all records.

EDIT: Above doesn't apply after all, was using version 4 of the project.

[mdewell]

Hello June7,

I think I'm running into a problem since I'm binding the subform to tblHIPAArelational, and I think I should be binding it to a query. The problem I seem to be having is that when I bind it to the query, I have a problem with making a selection in a combo box and having it change the data for the related table, tblOU, and not for the ID of the row's OU.

I've attached my current version and look at the frmHIPAAdatasheet and thefrmHIPAAquerydatasheet. In the frmHIPAAdatasheet, if I select the OU it will change it for just the record I am on, whereas, if I use the frmHIPAAqueryDatasheet it changes all the entries for the group that's in the OU, or more exactly, it changes the value of the lookup table's entry.

The advantages I've found with using the frmHIPAAqueryDatasheet is that I can alter the ComputerName easily, whereas with the frmHIPAAdatasheet, I can't get the ComputerName field to display the computer's name without binding it to a control source that locks the edit of it.

I believe that perhaps the query is going to be the right way to go, but how can I get the combobox to only alter the single record and not a whole group of them?
Thanks

[June7]

Here is technique to link two subforms http://www.fmsinc.com/microsoftacces...edsubforms.asp but I don't think this really pertains to what you want.

I would like to update the computer name by using a textbox to change the data.

Yes, will need to bind the form to join query. Textbox bound to the Computer field of tblComputer. Also need to set Relationship between tables with Cascade Update Related Fields checked.

how can I get the combobox to only alter the single record and not a whole group of them

The combobox should be bound to the tblComputer_ID from tblHIPAArelational.

Are you the only user of this project? Accomplishing the above will result in duplicate display of data on the form if you use lookup for the combobox. I think you will create confusion for users.

I never set lookup on tables. I want to see the real value when I look at tables. Since users shouldn't work with tables the only purpose for setting this in table is when you create forms with wizard or drag controls from field list to a form, those properties will carry forward. I manually set these properties on forms as needed.

[mdewell]

Hello June7,

Right now I am the only user, but in the future, others will be using it. I'm trying to make it user friendly, and that the user can select the OU form the list by name and not by number, even though the tblHIPAArelational stores it under ID. But I do only want there to be a given list of OU's to choose from. So, if I'm understanding you correctly, you think I should only use the query and no the table, or did you have another idea in mind?

Thanks

[mdewell]

OK, I wasn't sure what form you wanted me to work with for the join query. I set up a query on the subform, sbfHIPAAdatasheet, that bound the computer field to the tblComputer. When I look at the subform by itself (without it in a subform view but as a whole form itself, so just double clicking on sbfHIPAAdatasheet, not frmHIPAAdatasheet) and it appears the way I want, with the txtComputerName editing the computer's name. When I view it with it as a subform in frmHIPAAdatasheet, it gives me the #Name? identity. Your thoughts?

[June7]

No, I was saying you have to use query as recordsource to accomplish what I think you are wanting to do as indicated by: "I would like to update the computer name by using a textbox to change the data."

I would not design this form to allow changing the computer name in the OU table.

[mdewell]

Thanks for the clarification. So, I need to use the HIPAA query for the recordsource for my subform.

Sorry if you misunderstood me, about the OU and computer name. THe computer name changes correctly when I use the query, but if I use a combobox for a selection of the OU for the record, all the other records that are in that OU selection are changed. I'm pretty sure I've somehow designed the OU combobox worng, but I can't decipher what went wrong with it. It will come up if you open the form, frmHIPAAqueryDatashhet. If you select a different OU in the dropdown list of the subform for the OU, it does what I said, and in reality it changes the OU name for the OU ID in tblOU.

Maybe you can give me some direction there.

[June7]

See post 4. Same as with the computer combobox, bind to field that is from the HIPAArelational table, for OU data you have named it tblOU_ID. Doesn't matter which version of the form, same for both. Need to add these fields to the query. Again, RecordSource of table is fine if you don't want to cascade name changes.

[mdewell]

I'm sorry, I, and perhaps you, keep getting confused as to what form we are refering to. I have problem with the OU combobox with the frmHIPPAqueryDatasheet, and I have the computer name probem with the frmHIPAAdatasheet. I only want one form, but I'm experimenting with either one of these. The frmHIPAAqueryDatasheet, where it references the qryHIPAA works fine for the computername problem, but gives me an error when I try to bind a combobox to the OU field, of changing the OU (name) in the tblOU. The frmHIPAAdatasheet works fine for the OU, allowing me to edit individual OUs, but gives me the #Name? problem with the computer name field. I would like a form that allows me to select an OU from tblOU through a combobox and lets me change the computer name through a textbox.

Thanks

[June7]

The HIPAA form/subform (either version) has purpose to enter and edit records for tblHIPAArelational, right? You also want it to serve to edit data in tblOU and/or tblComputer? If this is what you are aiming for, asking for trouble. Think I already said I wouldn't do this.

The data is relational. Change the computer id or the MAC address for record #2 in tblComputer and this change will be reflected in all records in tblHIPAArelational that are associated with record #2. Same goes for the OU field in tblOU.

[mdewell]

Oh, no, the frmHIPAAdatasheet's subform is tied to the HIPAArelational. The frmHIPAAqueryDatasheet is tied to a query called HIPAA. I get how the edits in tblComputer or tblOU would effect all the records. There are one entry per computer name in the tbl computer, yet there are only a few entries in OU as the an OU can have several computers in it. Does that make sense?

[June7]

I am aware that the two subforms have the different RecordSources. Doesn't matter. The forms are still purposed for editing and adding records for tblHIPAArelational. The comboboxes ControlSources would be the same. Adding the other tables to the subform RecordSource (the HIPPA query) makes the related info available for display but do not advise using these links as conduit to edit the data in those tables. If you understand the cascading impact, is this what you want? If not, I guess I don't understand the issue you are having.

[mdewell]

Maybe I don't understand the "Relationship between tables with Cascade Update Related Fields checked" impact. Where is that at?

[June7]

Check out this article http://support.microsoft.com/kb/304466

and tutorial http://etutorials.org/Microsoft+Prod...Relationships/

On second thought, the cascade update is not relevant to your situation because you use autonumber primary key. Check this article http://www.granite.ab.ca/access/cascadeupdatedelete.htm
Your situation would not technically be a cascade, you are changing the value in the related table so of course the change would be reflected through to all records that have this record ID as foreign key.