Text Box reading ID - displaying related text? 1

[pmkieffe]

Hi,

Could someone please help me with formatting a text box?
I have a table with JobID as the primary key. JobNumber and JobDescription are both fields in that table.

I then have a combo box in a form. When selecting a JobNumber from the combobox, the JobID is stored in a different table by a recordset. I would then like a text box to read the selected JobID from the combobox, then display the corrseponding JobDescription.

The furthest I can get it for the text box to display the JobID that was selected.

Can I display the JobDescription based on that JobID?

Thanks,
pmkieffe

 

[kjv1611]

1st question: Are JobID and JobNumber the same data, or else how are they related?

 

[pmkieffe]

Oh sorry,

Each unique JobID has a corresponding unique JobNumber. JobDescription is unique to each JobNumber. So a specific JobID has one corresponding JobNumber.

Thanks,
pmkieffe

 

[kjv1611]

Ok, the JobID to JobNumber correlation is a direct 1:1 relationship? Another question is: are the the exact same number? So, like this:

JobID JobNumber
1 1
2 2
3 3

If so, then you could just use one text box for just the job description. And, actually, if you base it on the JobNumber instead of JobID - assuming they are the same, you could just set the Forma description table as the record source for the form, and drag the field from the "field chooser" or "field list" for description to the form, next to your combo box for the jobnumber, and it should automatically acheive what you are looking for..

Or, are the JobID and JubNumber not necessarily the same? So, you could have a JobID without having a corresponding JobNumber in existence, and thus not have the exact same number for each...

 

[pmkieffe]

Thanks for taking the time to help. I guess the best way to explain is to show you what I have:


JobID JobNumber JobDescription
1 200304 Fuel Costs
2 200305 Alternate Power
3 200308 Mill Processing
4 2005-4 Site Evaluation
... ... ...

Hope this helps.

Thanks again,
pmkieffe

 

[pmkieffe]

Also to clarify:
all JobID's have a different JobNumber and JobDescription

-pmkieffe

 

[PHV]

Have a look at the Column property of the ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[randy700]

Assume....
combo box name is cboJobs
combo box has 3 columns
column 0 is jobID
column 1 is jobNumber
column 2 is jobDescription
column widths are 0;1;0

text box source should be....
=select me.cboJobs.column(2)



Randy

 

[pmkieffe]

Thanks Randy,

but when I enter your code, I receive a syntax error. Am I doing something else wrong?


-pmkieffe

 

[randy700]

=me.cboJobs.column(2)

no select

doh!


Randy

 

[pmkieffe]

Now I get a display in the text box of : #Name?

Any thoughts?

-pmkieffe

 

[pmkieffe]

I have tried

=me.cboJobs.column(2)

and

=me.cboJobs.col(2)

in the control source for the text box. I continually get a #Name? display in the text box. Am I doing something wrong?

Thanks,
-pmkieffe

 

[kjv1611]

Well, you might want to consider this:

col(2) or column(2) should read that way for the 3rd column. If you want the first column, do col(0) or column(0). It could be that you only have 2 columns instead of 3 in that table.

Also, just try col(0) to see what it gives you, to make sure that's the issue.

 

[pmkieffe]

Good suggestion but I still get the #Name? error with col(0)

Are my data types mismatching somehow?

-pmkieffe

 

[pmkieffe]

I dont know if this helps, but with

=cboJobs

the first column is displayed correctly.

-pmkieffe

 

[Eupher]

AFAIK, the "Me" form self-identifier works only in VBA code, not in a control's property sheet. So...

=cboJobs.Column(x)

...is the correct syntax, where x is the 0-based number of the column you want to display.

HTH,

Ken S.

 

[pmkieffe]

Perfect. Thank You very much. That problem was starting to annoy me haha. Works great now.


Thanks
-pmkieffe