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.

 

[Pat Hartman]

Hi Rusty,
Welcome aboard. I moved the thread to a more appropriate forum since the experts don't normally monitor the "Introduce Yourself" forum for questions.

I have extensive experience with a drawing log application. I would be happy to demo it, if you'd like to see it for some ideas but I can't post it. Send a PM and I will set up a GTM where I can do the demo.

As to the question,

:- [JobNo] & "-" & [JobType] & "-" & [PartNo] & "-" & [TypeID] == this list of fields does not match your description.

The problem you are having is caused because the JobType is an ID and you want to show the description. In this context, you would need to use a domain function to get the JobTypeText and I don't think that will work in a calculated column since I don't think you can use functions and I don't think you can reference other tables either.

That leaves two choices.
1. save the calculated value - we always hate this option since it leave you open to errors if one of the parts changes.
2. calculate the concatenated value in every query.

I lean toward #1 but you would have to add additional editing to the form to either prevent the component parts from changing or to always calculate the composite value.

These situations are always less confusing if you name your PK's ending with ID when they are autonumbers.

to store the calculated value, use the Form's BeforeUpdate Event. This means that the value will not be calculated until just before the record is changed so depending on the flow, the user may never actually see it. The alternative is to calculate it in FIVE places (call the code of course rather than repeating it). You would then put the code into the AfterUpdate event of the four controls as well as the BeforeUpdate event of the form.

Me.CalcDrawingNum = Nz(Me.JobNo, "") & Nz(DLookup("JobType", "tblJobType", "JobTypeID =" & Me.JobTypeID), "") & Nz(Me.PartNo, "") & Nz(Me.VerNo, "")

This is obviously "air code" so could be full of typos.

 

[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,