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]

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

 

[BradCustom]

Thanks Duane,

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

 

[dhookom]

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

 

[BradCustom]

Yes, I am aware that I have to make those changes

 

[Andrzejek]

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:[tt]
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;[/tt]

Since you are going after just one table, wouldn’t it be easier to do just:
[tt]
SELECT [ID], [Specification], [Rev] FROM [Specifications] ORDER BY [Specification];
[/tt]And since you have nice names of your fields, you may even do this:[tt]
SELECT ID, Specification, Rev FROM Specifications ORDER BY Specification;
[/tt]
And[tt]
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;
[/tt]
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.

 

[dhookom]

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

 

[BradCustom]

WOW

 

[Andrzejek]

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

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.

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP