Hi, New to the list - Problem with Calculate Fields and Drop down box.

[Russty]

Hi People
I am new to this forum, but not new to Access. Been playing with it on and off since it was broken away from VB back in the dim dark ages of Windows for Wally's.
I go away from it and then always end up back again because it really is one of the easiest, albiet frustrating at times, database systems for entry level people.

I have pretty much retired from designing and manufacturing small waste collection vehicles for remote communities and towns in outback Australia. The family business is still operating and I like to just keep playing with the designs and drawings.

At present I am trying to create a new drawing database to store and track the drawings and changes made.

It has been a few years since I last did this and the programming idiosyncrasies are giving me a headache...

The Issue... this time

The drawing number comprises 4 sections, job number - (JobNo), Job type - (JobType), part number - (PartNo) and version number - (VerID)

The Job Number, Part Number and Version Number are all text entry fields in the table.
The Job Type is a lookup field into the Job Type Table, with the 5 letter code showing in the closed window of the drop down box.
I have created a calculated field in the table to concatenate all four fields into this field and generate the unique Drawing number.
In the Expression field is Have :- [JobNo] & "-" & [JobType] & "-" & [PartNo] & "-" & [TypeID]
The only part I can't seem to get to work is the Jobtype will only display the list index number and not the second, or colomn (1) of the dropdown.
I am sure this is just a formatting error in my expression, but every form, other than the one shown just gives errors and refuses to save.

Thanks in advance and I look forward to trawling this site for more assistance as the process continues.

Ken.

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[oleronesoftwares]

Welcome russty

 

[theDBguy]

Hi. Welcome to AWF!

Perhaps you could skip the Calculated Column and simply use a Query for that requirement. Just a thought...

 

[oleronesoftwares]

I am assuming for the sake of illustration that your drop down field has the name Combo1


In VBA Code
On the after update event of the dropdown write the following

Combo1.value=Combo1.column(1)


Then go to the properties section of the Combo box

1. Column count enter 2

2. Column widths enter "1","1"

3. In the row source of the column your select statement will be like this
Select pkey,valuetoshow from table

so two fields are been selected, but the second field is the value you want to show.

 

[arnelgp]

you can forget about the Calculated column.
you can actually get it to work with a Query (see Query1).
also you can use Query1 as the recordsource of your form.
see this demo.

 

[bastanu]

Ken, all good suggestions but I think the root cause of your problem as described in the original post is the fact that you are using a lookup field in the table:http://access.mvps.org/access/lookupfields.htm (# 1 in that list is what you have). You should avoid using them and instead use a combo control in the forms\reports as needed. Calculating it in queries is easy enough by joining in you Job Type table as it is the TypeID that is actually stored.

Cheers,