Copying values from lookup fields 1

[jpgoossen]

Hi,

I am new to Access and not very experienced in the whole dB field. I promised a friend to create a small database for them to solve some simple issues.
What I am trying to do, is create a table where some values are filled in automatically, from specific records in another table. Since the table where the values are looked up from may change over time, the value needs to be inserted as value, not as reference. The tables have a common, unique field value.

Example:
Table: Fields
WORKED_HOURS: ID_HOUR, CUSTOMER, DEFAULT_HOURS, ACTUAL_HOURS

CUSTOMERS: ID_CUST, CUST_NAME, CUST_DEF_HOURS

The field CUSTOMER in the table WORKED_HOURS is a lookup from the CUSTOMERS table, and contains the value CUSTOMERS:ID_CUST. These two fields are therefore common.
What I want to do now, is automagically insert the value CUST_DEF_HOURS (the number of hours that are scheduled for that customer) into the field DEFAULT_HOURS without giving the data entry person the chance to change that value. It has to be the value and no longer the link to the other table, since the value for CUST_DEF_HOURS may change when the cursomer decides to enter into a different working agreement. The field DEFAULT_HOURS has to be visible in the form that is used to be used for entering the hours, since it needs visual control versus the field of ACTUAL_HOURS in the same record.

I am stumped and cannot seem to find the answer to what sounds like a simple question. Tried using SQL statements, lookup tables and more, but everytime I end up with a field DEFAULT_HOURS that is either empty or gives me an error, but never filled in with the value for CUST_DEF_HOURS that I know exists in the table CUSTOMERS.

Hope that my question is not too simple -although I don't mind admitting to my newby-ness -

Thanks in advance for any pointers,

Patrick

 

[adalger]

Are you using a form for data entry? Setting a control bound to [DEFAULT_HOURS] with its default value set to SELECT CUST_DEF_HOURS FROM CUSTOMERS WHERE ID_CUST = CUSTOMER should work.

TMTOWDI - it's not just for Perl any more

 

[dhookom]

You must use a form for data entry. You should not use lookup fields defined in table designs. You should use a combo box to select the ID_Cust. Your combo box Row Source can be something like:
[tt]SELECT ID_Cust,CUST_NAME, CUST_DEF_HOURS
FROM Customers
ORDER BY CUST_NAME[/tt]

You would use the After Update event of the combo box to set the value of the Default_Hours

Me.txtDefault_Hours = Me.cboCustomer.Column(2)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

Adalger, Duane,

Thanks for your info so far. I have some more specific questions however. - It has been so long ago I looked into databases, that my main experience has been with Clipper, a dBase clone... Ancient!

Anyway, am I to understand that you suggest that I never(!) use table lookup in the table design but relegate this to the Form exclusively? That will require some restructuring, but sounds not too difficult.

As to clarifying what I need (because it is not too clear to me yet).

I want my users to select a name/birthdate/location value from a table. This value is referenced through the ID_CUST/CUSTOMER link and looked up in one field. As soon as this is selected, I want to SHOW my users the values of DEF_HOURS from the CUSTOMERS table and COPY that into the field DEFAULT_HOURS of the active table, WORKED_HOURS.
I will also want to show some other fields (start and ending dates of the service agreement actually) from the table CUSTOMERS, but not copy those. Those will be used for comparison with the work dates, to check that the work dates will fall into the service period. I had hoped that with my simplified question, I would be able to extract some simple tips.

I guess I am a bit more in over my head than planned, since it is not too clear what you meant before. It does not help that I am forced to work with a localized version of Access 2003, so that looking up the actual translations is a bit harder.

Perhaps a bit more step by step nstructions could help out more.

Hope I don't inconvenience you guys/gals too much, since any help is appreciated!

Cheers,

Patrick

 

[dhookom]

This link sums up my opinion on lookup fields defined in tables

http://www.mvps.org/access/lookupfields.htm.

I also started with dBase and then Clipper. I think the last version I used was 5.x. Clipper and dBase were procedural languages where Access is more of an Event Driven application.

All user viewing and editing of data should be done through forms and reports. Were you able to create a form with a combo box like I suggested?

Did you set the Row Source property of the combo box?

Could you find the After Update event of the combo box?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

Duane,

Read your "opinion" and totally agree. Took in the Ten Commandments for good measure, of course. You know, I did KNOW that that is the right way to do it, but you know how these things start Someone asks for a favor, you propose to "simply set up a small database" for them and before you know it... You are not only in over your head, but also find that you need to do additional work because you do want to deliver a proper, and relatively safe and foolproof product.

Anyway, removed the lookup functions from the tables themselves. Fot the simpler ones, I just kept the lookup in there, created a simple form through the Wizard and after that removed the lookup FROM the table, but kept it in the form, since I knew it worked.

I already normalized my database, since I do know how to do that, so it is the initial getting to run of the forms that needs to be done. Finetuning and perhaps splitting up or joining of forms can be done later. I am sure that during Beta, we will run into new questions, fine tune requests and perhaps even inconsistencies in the User Friendliness. Some of the forms need to be filled in by some relatively mentally challenged people (not dissing the users, but the work is on a not-for-profit organisation that provides occupational therapy and susidized jobs among others.

Found the Row Source and After Update fields in the Properties of the Form fields, but somehow have not been able to get the after update function to work.

Of course, since I just used some fictional field names in my examples (in my local language they may look a bit less meaningfull to the casual reader), I might make a mistake trying to determine what is code and what is a variable name.

For instance, in the code snippet:
Me.txtDefault_Hours = Me.cboCustomer.Column(2)

I took the parts Default_Hours and Customer to be names of a field and table respectively, while Column(2) looked like a part of the function that pointed to column 2 of that particular table. So I left Column untranslated, and only changed the value of 2 into 4 (the position of the required field in my actual table). Am I correct in my assumptions?

TIA,

Patrick

 

[dhookom]

These are the controls...
Me.txtDefault_Hours = Me.cboCustomer.Column(2)
txtDefault_Hours the control on your form that you want to put the default hours into
cboCustomer the combo box control
Column(2) the 3rd column/field in the combo box control row source

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

Duane,

Did some more serious looking into the <harumph> Northwind database. It made clear how the Me. functions work. Remains the question if I should just use that code in the After Update field of each and every individual field during designing the forms (each code snippet adjusted to the particular field and data required of course) or if it makes more sense to compile all those functions in lists that accompany the specific forms, in sub procedures, like

Private Sub Customernumber_AfterUpdate() and
Private Sub Customernumber_BeforeUpdate(Cancel as Integer)

In the Orders form of that example database. And how is that coupling done.

BTW, I now see that it makes more sense to develop different foirms for each function. Like different forms for Querying a table of customers and a seperate one for entering that table (since both will be probably doen byu different people). Some sort of checking oin existing customers will have to be done, of course, to prevent double entries and other redundancies.

Hope you are not fed up with my questions yet. I really appreciate all the help you are giving me. Hope to be able to reciprocate in the future.

Cheers,

Patrick

 

[dhookom]

I don't understand why you would need to use this type of code in more than a few places. I also don't understand what you would be attempting with "coompile all those functions in lists that accompany the specific forms".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

Duane,

Sorry if I have been unclear. I realise that one can only expect usefull answers when asking great questions, accompanied by usefull information.

What I was trying to ask was the following:
I want to lookup a certain value from an external table, based on a unique ID. Once I have done that, I want the form to automagically fill in some other fields with other field data from that same external table. I understand that I can do that with code like
Me!Customer_hours = Me![Customer].Column(3)
in the line AfterUpdate of the Event tab, in field properties. I however have several fields that I wantt/need to fill out in this way, since they require visual checking by the data entry person to determine we are handling the right customer.

Looking at that example database, Northwind, I see that the code for filling in several fields, is placed together in a sub function. Is that the way I should do it, or would just typing in the correct code in each AfterUpdate field be a better and easier way to achieve the requested results. And, after filling in, I want some of the data to be copied into a field in the table, for storing, while other will only need to be shown on the form, but not stored.

Have I made myself clearer now?

Thanks in advance,

Patrick

 

[jpgoossen]

As an update to myself and those reading along...

How do I make it so that filling in a value in the lookup field, will immediately fill in the automated fields? Is simply using the codee in AfterUpadet suffucient? Or does that require another step?

Oh, how I miss dBase III <gggg>

Cheers,

Patrick

 

[dhookom]

I assume you only want to give a "visual checking by the data entry person" and don't/shouldn't store these values. There are several different methods for doing this.

The most popular method requires no code. You simply add these "several fields" to the Row Source property of your combo box. Then create text boxes with control sources like:
=[Customer].Column(2)
=[Customer].Column(3)
=[Customer].Column(4)
Try to do that in dbase without writing a line of code ;-)

If you have a lot more information you want to display, you could use a subform based on your customer table. Set the Link Master/Child properties of your subform control to the customerID fields.




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

OK, have been learning a lot over the past few days. And just started working on the table again. Took me a while to figure some of the issues out, but still have questions.
I tried using a sub form, and although I did specify tht the contents of the subform should be directly related to the value of teh chosen unique ID field through its unique ID, it will not show the correct values. It only shows the values of the first record in the query that is found and seems to ignore the parent/child link through the ID fields.
As an alternate solution I tried the
=[Customer].Column(2)
solution in separate text boxes, but those only show #NAME? as content of the fields.

No matter what I seem to do, there is no update of the values based on the selected lookup fields in the start of the form.

Is this a matter of where I place the text boxes or sub forms? Is theer a difference between placing them in the Details area of the form or in the Header area?

Thanks in advance,

Patrick

 

[dhookom]

You need to provide more information such as the properties of your combo box:
Name:
Control Source:
Row SOurce:
Column Count:
Bound Column:

and then your text boxes:
Name:
Control Source:

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

Duane,


I tried the sub form method First. For that purpose I created a new form EXTENDED_LOCATION based on a query QLOCATION, which combines all the fields of the two tables that create a unique location. The fields are
LOC_ID unique, key field
LOC_EMP_NAME
LOC_EMP_FIRST_NAME
LOC_EMP_BIRTHDATE
LOC_WP_WORKPLACE
LOC_WP_STARTDATE
LOC_WP_ENDDATE
LOC_WP_DEFAULTHOURS

In the table HOURS, I only want to save a copy of LOC_ID and LOC_WP_DEFAULTHOURS

All the fields are required for the visual check.

What happens is that I lookup the LOC_ID through a Combo Box. That Works fine. However, it does not automagically show the contents of the bound fields in the sub form. At least not when entering the data, which is essential. Going to a new record and then browsing back does show the correct content, although I do not want the person entering data tob e able to browse back at all, or change the data after entering, the record, fort hat matter.

As an alternative, I tried your simple method of creating separate text boxes. Those just give the #NAME? Value in the fields. Those are set up as follows.

Name: Select Workplace
Control Source: HOUR_LOC_ID
Row Source: SELECT QLOCATION.LOC_ID,
QLOCATION.LOC_EMP_NAME,
QLOCATION.LOC_EMP_FIRST_NAME,
QLOCATION.LOC_EMP_BIRTHDATE,
QLOCATION.LOC_WP_WORKPLACE,
QLOCATION.LOC_WP_STARTDATE,
QLOCATION.LOC_WP_ENDDATE,
QLOCATION.LOC_WP_DEFAULTHOURS
FROM QLOCATION ORDER BY [LOC_EMP_NAME],
[LOC_EMP_FIRST_NAME],
[LOC_EMP_BIRTHDATE],
[LOC_WP_WORKPLACE];
Column Count: 8
Bound Column: 1

and then my text boxes:
Name: Show Name
Control Source: =QLOCATION.Column(1)
Name: Show First Name
Control Source: =QLOCATION.Column(2)
Name: Show Birth Date
Control Source: =QLOCATION.Column(3)
Name: Show Workplace
Control Source: =QLOCATION.Column(4)

As far as I am concerned, either method is fine, as long as it works. The essence is that a “simple” data entry person should be able to fill in the form, creating a record once. Changes should only be made by authorized personell. That requires that it should be very clear for which person/location combo the record is entered. Thus, after selecting the person/location in the combo box (First field in the table, and which will save the value of a unique location/person/hours ID) the contents of the other fields of the table/query from which data is selected should immediately be filled in with the appropriate data. One value should be copied into a field of the new table, but the rest should only be shown.

Regards,

Patrick

 

[jpgoossen]

While at it, another one I cannot seem to find in the Access documentation. I want to prevent users from automagically saving a record by entering Tab or Enter and thus going to a new record, until they specifically click on OK, after the visual checks or something. Is there an easy way to do that?

Regards,

Patrick

 

[dhookom]

The control sources of your text boxes must reference the name of the combo box. In my suggestions, I first referred to the combo box as "cboCustomer". You should find and use a good naming convention so that you and others will understand your application. Your current control names look more like the Caption property of the associated labels.

If your combo box name is [Select Workplace] then your text box control sources would be:
=[Select Workplace].Column(1)
=[Select Workplace].Column(2)
=[Select Workplace].Column(3)
=[Select Workplace].Column(4)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

Duh,

I guess I never made the click to see a difference between (user readable) field names in a FORM and the associated field name in the corresponding table. This seems to work.

Thanks a lot for helping me get that straight. Just kinda figured at first that the "Name" field in de properties was just desciptive and wasn't/couldn't/shouldn't be used in references.

Now if I can figure the last part of my question out, I am almost done and shopuld be able to create all the forms and tables as required.

Thank you so much so far!

Cheers,

Patrick

 

[dhookom]

I generally don't fight with saving records or not. Let users get into and out of their own trouble.

If it is important to not save a record until a button is clicked, I would create an unbound form and use code to add the record to the table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpgoossen]

Duane,

Guess you are right to at least leave some trouble spots for your users. I decided to just add special Command buttons for the Nav command and remove the default mini-record selectors at the bottom. Combine this with letting the tab order end on the command buttons, and making Save the default, instead of New, and all seems to be fine.

I am however running in to one last (?) hitch. I have two combo boxes, and I want to force the user to select the first before the second can be selected (some of the calculated fields give me errors if the order is reversed). I can't seem to find any options for forcing the user into taking the steps in a particular order. I mean, the Tab order is set, but if the users skips the first combo box or fields, or accidentely does a double tab press, there is an error. I want to try not to have to add too many instructions or text labels to the form. Is there a way of being able to enter a next field or combo box conditional on selecting a valid value for a previous combo box?

TIA,

Patrick