INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Populating and storing Textbox Values

Populating and storing Textbox Values

(OP)
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.

RE: Populating and storing Textbox Values

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

RE: Populating and storing Textbox Values

(OP)
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;

RE: Populating and storing Textbox Values

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

Duane
Hook'D on Access
MS Access MVP

RE: Populating and storing Textbox Values

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

Any thoughts as to why?

RE: Populating and storing Textbox Values

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

RE: Populating and storing Textbox Values

(OP)
Duane,

This is what I added to the Combobox After Update.

CODE

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

RE: Populating and storing Textbox Values

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

Duane
Hook'D on Access
MS Access MVP

RE: Populating and storing Textbox Values

(OP)
That's what I thought after I posted the code. :)

RE: Populating and storing Textbox Values

(OP)
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:

CODE

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

Or is my brain getting in the way?

RE: Populating and storing Textbox Values

(OP)
Thanks Duane, That works!

RE: Populating and storing Textbox Values

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

Duane
Hook'D on Access
MS Access MVP

RE: Populating and storing Textbox Values

(OP)
I was premature, it actually doesn't work

RE: Populating and storing Textbox Values

"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

RE: Populating and storing Textbox Values

(OP)
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:

CODE

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

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

RE: Populating and storing Textbox Values

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

RE: Populating and storing Textbox Values

(OP)
Sorry messed up the code in the last post.

CODE

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

RE: Populating and storing Textbox Values

Your control's name is Process Rev ? 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.

RE: Populating and storing Textbox Values

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

RE: Populating and storing Textbox Values

(OP)
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.

RE: Populating and storing Textbox Values

BradCustom,
You might also want to consider giving your significant controls significant names. IMO, [Combo71] should have a better, more meaningful name.

Duane
Hook'D on Access
MS Access MVP

RE: Populating and storing Textbox Values

(OP)
Thanks Duane,

I usually do I just haven't done it yet. I'm still building this database.

RE: Populating and storing Textbox Values

I would name the object/control prior to writing any code referencing it. Otherwise you have to change you control name and then your code.

Duane
Hook'D on Access
MS Access MVP

RE: Populating and storing Textbox Values

(OP)
Yes, I am aware that I have to make those changes

RE: Populating and storing Textbox Values

I would like to make a few points:
Just because Access allows you to name your fields with spaces, it doesn’t mean you should do it. It is a huge headache later on (as you can see)

dhookom did a lot of help with your problem. I think you should award him with the star – click on ‘Great post? Star it’ link to do so on his post(s).

And just out of curiosity, you showed a couple SQLs like these:
SELECT [Specifications].[ID], [Specifications].[Specification], [Specifications].[Rev] FROM [Specifications] ORDER BY [Specification];

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;


Since you are going after just one table, wouldn’t it be easier to do just:

SELECT [ID], [Specification], [Rev] FROM [Specifications] ORDER BY [Specification];
And since you have nice names of your fields, you may even do this:
SELECT ID, Specification, Rev FROM Specifications ORDER BY Specification;

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

And again, if you would not have spaces in your fields’ names you would not need to use [ ]
I use underscore _ as a space.

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.

RE: Populating and storing Textbox Values

Since we are doing some coaching I would recommend never using a generic field name like [ID]. I much prefer SpecID and CofCID. I use some underscores but primarily use CamelCase like CofCDate, PartNumber, SerialNumber, OperationNumber, etc.

To go even further the naming convention I use adds a prefix to the start of every field name so there are no two fields in my applications with the same name and I know which table the field came from.

Every field in the CofC table might be named like:
cocDate
cocPartNumber
cocSerialNumber
cocOperationNumber

I use autonumber primary keys with the primary key field always named like cocCoCID and speSpeID (for Specifications table). Foreign key field names are then really simple: cocSpeID or possibly speCoCID.

Your experience with this type of convention may vary but I have never been sorry I have used this naming system.

Duane
Hook'D on Access
MS Access MVP

RE: Populating and storing Textbox Values

(OP)
WOW

RE: Populating and storing Textbox Values

I agree with you dhookom as long as we are talking about Access (Yes, I know this is an Access forum). But if you ever move to something else, like Oracle, CamelCase field becomes CAMELCASE because all fields are capitalized. There are some additional differences, but I don’t want to split hairs here. I just want to state that – even thou this is a great approach in Access - it may not work so nice in other data bases.

Also, adding the prefixes pretty much prevents you from using reserved words in Access which is another big 'no-no' IMHO smile

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.

RE: Populating and storing Textbox Values

Great points Andy. I haven't used Oracle in years so wasn't considering its naming.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close