Full Version: why can't I set the value of a bound control to that of another
UtterAccess Forums > Microsoft® Access > Access Forms
Scroll to "I'm hung up on this" if you don't care about the "why"... wink.gif
I have a situation where I've got an existing main form where a user selects a record, and all corresponding records are pulled into a subform below. The subform is based on a join query. As such, there's one field that I want to allow the user to change, but natively it's changing the value in the joined table directly, which is bad, bad news...
The main table is "inventory", which contains a "CustomerKey" integer, which references the "Customer" table.
For a given part, I'd like the user to be able to reassign an inventory part to a different customer... but natively, through the join query, what this would do right now would be to update the "CustomerName" value in the customer table - and bam! key value 15 would no longer be the BigBadCorporation! shocked.gif
...yet I can't use an unbound control, because I need the end user to be able to retrieve multiple records in the subform, and individually manipulate values for each retrieved instance. Unbound controls don't have per-record scope, and display very weird behavior - it'll PUT a control on each instance, but when you set one, they ALL change to that value!
I almost hate to admit this, but:
HAs a workaround, I added an empty column to the Customer table (called TmpCustomerName).
On my form, I added a combobox to reference this empty column called 'CustomerName'.
Orenamed my existing combobox (which is bound to the Customer table's 'CustomerName' field) to 'CustomerNameSrc'.
When you select a record on the main form, the CustomerNameSrc control displays the associated record's value [since it is bound to CustomerName in the table].
I want to take the value that appears in CustomerNameSrc and set the CustomerName control equal to it...
...which should also set the value of the TmpCustomerName column for that record, since it is bound to it.
...not elegant, not clean, shameful really - but the only way I could think of since the controls obviously have to be bound to allow me to do per-record updating in the subform. U-G-L-Y... you ain't got no alibi...
Anyway- That lets me allow the user to manipulate the CustomerName control, and I can reference it in my VBA code to process the updates to the tables appropriately. And I also created code to clear that column in the table when the form is closed...
But now I'm hung up on this:
I can't set the default value of CustomerName to be what CustomerNameSrc is!
(both controls are bound to two different columns in one table, for those skipping the above intro, one a temporary placeholder in the scope of this particular retrieved in the subform's query)
I've tried to set the Default Values property in the control, like "=[CustomerNameSrc]" and saw a suggestion on the web to try "=[CustomerName].Column(0)" but Access doesn't like that...
I even tried to set an Event Procedure on the OnLoad event, similar to "Me![CustomerName] = Me![CustomerNameSrc].
No luck.
Otherwise the control works fine - I can manually select a value, and it updates the junk column in the table.
Why can't I ...
...No.. make that:
How can I make it so that when the form is opened, the CustomerName control gets the value of the CustomerNameSrc control shoved into it?
Thanks in advance!
I have read your post three time and still have no idea what you're trying
to do.
efault values apply to new record entries. There are no values in any
It's this simple:
I have a form bound to a query. When you select a record, all the controls on the form populate - they all position to the selected record. If it was an employee form, when I picked employee "Bob Jones", then all the other controls would populate with all the info on Bob Jones... hire date, address, department, etc.
In my case, it's an inventory query instead of an employee table - not important.
Now, I have one column in my table that has no value in some cases.
Think of it this way to make it easy: The form will query and pull just those records.
I have one combobox control bound to THAT table column - control CustomerName.
I have another control bound to another piece of customer name data - control called CustomerNameSrc.
When the form opens, CustomerNameSrc will contain a name, and CustomerName will be blank.
What I want to happen is automatically set CustomerName with the value of CustomerNameSrc.
I don't mind that the table will be updated behind the scenes, although all I really care about is that the combobox receives the value of CustomerNameSrc.
I haven't been able to do this, and I don't understand why!
HAs mentioned:
I've tried to set the Default Values property in the control, like "=[CustomerNameSrc]" and saw a suggestion on the web to try "=[CustomerName].Column(0)" but Access doesn't like that...
I even tried to set an Event Procedure on the OnLoad event, similar to "Me![CustomerName] = Me![CustomerNameSrc].
No luck.
In all cases, my CustomerName control remains blank. Why?
How do I get CustomerNameSrc's info automatically shoved in there? (as mentioned, I ultimately need to make CustomerNameSrc invisible to the end user to prevent updating THAT field - and that's why it needs to be there)
>Because default values apply to new records not, existing records.
If you do not want users to be able to edit a field using your form then
set the locked property of the control that displays the field to true.
You don't have to add a bogus field to the table in order to do that.
How about this in the OnCurrent Event
Dim varCustomerNameSrc As Variant
varCustomerNameSrc = Me.CustomerNameSrcControl
Me.CustomerNameControl = varCustomerNameSrc
Disregard my use of the word "default" - I meant that in the context of trying too use the "default value" property, I see that's caused a bit of confusion...
The catch is that I NEED the end user to be able to use that control. They need to be able to see the current value, and choose a new one if it's appropriate to reassign it...
But they can't directly edit the database from that control, because it would disassociate that value from the key value!
In other words, my inventory table has part numbers, quantities, and a key into the Customer table which contains Customer Name.
The control for Customer Name references the Customer table.
If the end user selects a new customer name, it'll update the customer table - rather than updating the key to the newly selected customer record.
So, if I had:
1 BigBadCorrp
2 LittleTinyLLC
3 IndependentGuy
If the end user changed the name with the control, since it's bound to the table to retreive the current value, I'd end up with this (say, they wanted to reassign a particular part ownership from IndependentGuy to BigBadCorp):
1 BigBadCorrp
2 LittleTinyLLC
3 BigBadCorrp
Instead, I need the Inventory table's key for that particular record to change from 3 to 1.
Ocan't provide the customer with a control direct from the inventory table, because no one would understand a control filled with numbers, when they want to assign parts to an owner. wink.gif
I have VBA code written to handle this - but the problem is, by the time I execute the code, the database write has already happeend.
This is a workaround to avoid that - but any less Rube Goldberg suggestions are certainly welcome!
Thanks, Mitch - I'll try it...
>The control will be empty when the form opens and only populate as
you cycle through the records. Not real handy if it's a continuous form
For datasheet view.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.