Making Edit in Form Changes All..But Shouldn't

[mcupito]

I am outputting Hours worked on a specific line of business of a specific company on a certain date, and sending them to a 'Submitted Hours' form. The Line of Business box is editable only by an Admin, however, when it is changed, it changes all of the records, not just that single instance.

I've attached 2 images, one before the change and one after.

Keep in mind, I only want 1 LOB to change.

If I can provide anything else, let me know.

I feel as though it might be something with the pairing keys, or a query, however everything else works in the database aside from this, so I am hesitant to make changes.

Thanks.

[nico5038]

Looks like there's no unique identifier and in a continues form Access doesn't limit such a selection to the current record.....
As a workaround, when updating, you can right-click the date and then the company to apply a filter so only one row is displayed.
Then update the field.

Or ask the programmer to correct his design :-)

Nic;o)

[mcupito]

I am the 'programmer' .. I took over a partially completed database and didn't even know this form was editable. I am thinking that there is a mismatched connection, however, other than the editing - all of the output is fine. There is an append query that goes to the form, so I am questioning if that could be a source of issue.

[TheSmileyCoder]

A append query cannot "go" to a form. An append query adds records to a table.

A form can show a table or a SELECT query. The table could have been created by an append query, or had records added to it by an append query, but a form cannot show an append query.

I am guessing that you have 2 or more tables joined together and instead of editing the master table, you are accidentally editing the lookup table.

Try to google for some help on how to create a combobox using a lookup table, and see if that doesn't clarify it for you.

[nico5038]

I would start with making this form `read only` by setting the property to allow no edits.
Somewhere there must be a form to update Company and ServiceDate and enter the LOB. There this should be maintained.

Nico

[jimatqsi]

mcupito,you've given precious little information to help us understand your problem. Are you working with a form and a sub-form? Or do you have an unbound text box on a continuous form?

Show us what you're working with, not just the result of what you're working with. Give us a screen shot of the work area. Maybe even you can zip the .mdb and let us look at the process first-hand.

Jim

[mcupito]

I have taken a few new screen shots in hopes of giving you all better information. I wish I could zip the .mdb, however it contains lawyer's names, which I will not disclose.

When editing the LoB for a row in the ServiceHoursSbform (Yes, it's a subform), it changes all of the LOB's that are the same, to the one that was changed - AND it changes them in the tables also. That is why I believe it might be some kind of relationship issue.

[mcupito]

Nico, this form IS the form where editing is to be done. It is the output from an 'InputHoursFrm' where all of this information is entered and this form is where it can be recalled/is being stored.

Thanks all,
Mark

[mcupito]

All, I am not sure if this is possible, but can I make the LOB column a ComboBox, BUT** have it populated with the result (before ANY editing is done) and have the other LOB's listed in the ComboBox? I will post a screen shot.

E.g: For the first entry, I want it to read CORP in the LoB, but I want there to be a list of other possible LoB's in the ComboBox, and if one is selected it would replace CORP only for that row (entry) of data.

Basically, I want the ComboBox in the LoB column to say 'CORP' because that is potentially the correct information, however if someone entered it wrong and it was supposed to be "XYZ", I want "XYZ" to be an option in the ComboBox.

I really hope this helps - I am so close to finishing this segment.

Thanks all - very, very much.

[nico5038]

I see your modelling is wrong.
You'll need to link the service to the unique LOB ID and drop the relation between service and company.
Otherwise you're changing the relation between Company and LOB instead of assigning another LOB to a service.

The company for a service can always be found by linking from the LOB to the Company.

Getting the idea ?

Nic;o)

[zmbd]

mcupito,

Your Combobox for LOB in the form should have the
"control source" property set to be equal to the field in the table that you are storing the data in... appears to be ServiceTempTble![ServiceTempLOB]
With the way it is currently, LOB_tbl![LOB_Abbr] you are editing the LOB_tbl and not the value in the ServiceTempTble![ServiceTempLOB] field. This is why when you changed the value in one record all of the others changed.


Now, You have to change the rowsource of combobox so that is uses the related data.

From what I figure out from your post, you could use the LOB_tbl as the rowsorce or write a query that is either stored or entered directly into the Rowsorce property.
You need to then set the bound column to be equal to the related field (looks like [LOB_ID]), set the number of columns to match the record sorce and the column widths to 0;1;1 (the first needs to be zero if you want to hide the column the remaining values should match the number of columns)

So now the bound column is what is stored in the table.

[mcupito]

Thank you all for your contributions. I believe zmbd was correct (not in the combobox aspect) in the connectivity. I do not believe it should have been the LOB_ID in the field.

Thank you all, again. You've been great