Populating and storing Textbox Values 2

[BradCustom]

I am working with Access 2007 and in my form I have a combobox which populates an Unbound Textbox with a Specification Rev. I'd like to store the Rev in the same record as the Specification but I'm not sure how to accomplish it.

Here is what I have:
Lookup table: Specification
Store Table: CofC

My Combobox looks up the Specification in the Specification table and based on the selection the Rev Textbox is populated with the Rev.

The combobox is setup as follows:
Control Source: Process
Row Source: SELECT [Specifications].[ID], [Specifications].[Specification], [Specifications].[Rev] FROM [Specifications] ORDER BY [Specification];

This field is stored in a table called CofC

The textbox is setup as follows:
Control Source: =[Combo71].[Column](2)

This field shows the Rev for the Specification.

What I would like to do is to store the text field in the CofC table after a Specification is selected in Combo71.

Thanks for any help.

 

[dhookom]

What is the Record Source of your form? Is it CofC? If so you should be able to add some code to the after udpate event of the combo box to set a bound control to the column value.


Me.FieldNameToStoreRev = [Combo71].[Column](2)

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

Thanks for the reply Duane.
The answer to your question is yes.
Below is the Record Source for the Form.

SELECT CofC.ID, CofC.[CofC Date], CofC.[Part Number], CofC.Rev, CofC.Process, CofC.Acceptance, CofC.[Serial Number], CofC.[Operation Number], CofC.Quantity, CofC.Accept, CofC.Reject, CofC.Notes, CofC.Customer, CofC.Signature, CofC.Stamp, CofC.Job, CofC.Grade, CofC.[Process Rev], CofC.[Acceptance Rev], CofC.[Acceptance Grade] FROM CofC;

 

[dhookom]

BradCustom,
What happened when you attempted to use the code I suggested?

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

Duane,
I added the code you suggested but unfortunately it didn't save Column 2 to the CofC table.

Any thoughts as to why?

 

[dhookom]

Share your code so we can assist. Do you want to do this for new records or all existing records?

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

Duane,

This is what I added to the Combobox After Update.

Private Sub Combo71_AfterUpdate()
Me.FieldNameToStoreRev = [Combo71].[Column](2)
End Sub

Yes, I only want to save the Rev for new records. This form is for entering new certs. Once I get this working I do the same for the editing form.

Thanks,
Brad

 

[dhookom]

FieldNameToStoreRev" was a placeholder for your actual Field Name To Store Rev.

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

That's what I thought after I posted the code.

 

[BradCustom]

Ok my brain isn't working very well today due to lack of sleep so I want to make sure I understand what you're telling me.

Here is the Control and Row Source for the Combobox
Control Source: Process
Row Source: SELECT [Specifications].[ID], [Specifications].[Specification], [Specifications].[Rev] FROM [Specifications] ORDER BY [Specification];

If I understand you correctly the code for the After Update should be:

Private Sub Combo71_AfterUpdate()
Me.Rev = [Combo71].[Column](2)
End Sub

Or is my brain getting in the way?

 

[BradCustom]

Thanks Duane, That works!

 

[dhookom]

Can we assume based on your most recent answer that it is working as expected?

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

I was premature, it actually doesn't work

 

[dhookom]

it actually doesn't work" doesn't work to provide any idea what you are seeing or what's happening. What happens when you add a break point to the line of code and you hover over the code?

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

What happened is it saved the value into the field called Rev in the CofC table. Unfortunately I have 3 different Rev's in the CofC table

Fields:
Rev
Process Rev
Acceptance Rev

I want to save this field to Process Rev.
When I used the code above it saved the Rev into [CofC].[Rev]
So I changed the code to:

Private Sub Combo71_AfterUpdate()
Me.Rev = [Combo71].[Column](2)
End Sub

Unfortunately it didn't save the Rev into [CofC].[Process Rev]

 

[dhookom]

Why would your code stick the value in the Rev field if you want to stick it in the [Process Rev] field?

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

Sorry messed up the code in the last post.

Private Sub Combo71_AfterUpdate()
Me.Process Rev = [Combo71].[Column](2)
End Sub

 

[Andrzejek]

Your control's name is [tt]Process Rev [/tt]? With a Space in the name?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[BradCustom]

Yes the Field Name is "Process Rev" with a space. Obviously not the best practice but it should still work.

 

[BradCustom]

Ok, I have it working.

Andy was right, Access doesn't like the space in the field name. I changed the field name to [CofC].[Process_Rev] and it works fine now.

Thanks for your help.