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

 

[dhookom]

You can add code to the after update of one combo box to set the enabled property of another to true. You can also trap the error and handle it by setting the focus back to the first 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,

Got it. Dipping into the reference tables and with some logic reasoning as to terminology, I figured it all out. Up until now it has been quite an eye opener as to what is possible in Access.

It wouldn't have been possible at all without your invaluable support here. Many thanks for spending the time and effort to answer my questions, no matter how inane or uninformed they were at times. Your sharing of your knowledge and skills here has made my day (and an important part of the project) and has been a great learning experience. I wouldn't hesitate to start new projects on my own after this.

Of course, after all this praise, I wouldn't be me if I did not slip in a small question at the end...

I decided I want to be able to check who entered a record, and who consequently edited (not viewed!) it last and when this was done. Just as a safety measure against possible fraud. I understand that I could use something like

Me!timestampfieldname = Now

for the time in a field of every record through BeforeUpdate. However, does it make sense to save all this data in a separate table or keep it in/add it to the table being edited or created. And should I make a table of the users that are allowed to implement changes/entries or just add those on a "current user" basis?

Regards,

Patrick

 

[jpgoossen]

Hmmm, been working with the first user input.

I currently have my lookup Comboboxes in the Form sorted on the ID-field, the first field in the records. When selecting the appropriate records, people currently select through the box. I would prefer to be able to have people select records either through browsing in the box OR by entering the first letter(s) (case independent) of the Name field in the record. The entering of a letter or two, should result in the dropping down of the combobox with the list, after which the appropriate record be selected. Since there are to be hundreds of records in the table that the records are selected from, this would be faster that just browsing. I also want te be sure that the records are browsed/selected on the Name field, instead of the ID. Whenever I try to change the order in which the fields/names are presented in the Combobox, it no longer works as a proper box. This (probably) because the ID that I need to save in the record is numeric (Unique) while the filed I want to select on is Alfanumeric. Is there any way to do this easily?
Or should I try to use the ComboBox as a separate entity(unbound to a field in the record I am editing/entering) and save the ID value to identify the record through a (hidden) text box elsewhere in the form?

Cheers,

Patrick

 

[dhookom]

The combo box has lots of properties that allow you to do what you want. Try

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]
ORDER BY QLOCATION.LOC_EMP_NAME;
Column Count: 8
Bound Column: 1
Column Widths: 0,2,0,0,0,0,0,0

There is API code at

http://www.mvps.org/access

that would allow you to grab the login of a user. This could be used similar to your Now to update a record.

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,

Been looking around a bit. Could I just use currentuser() in the same way as now() when it comes to grabbing whomever entered or last changed a record? It feels like a simple, easy way to do what I want - some minimal check on who is futzing with what records - while keeping too much overhead at bay. Since I see lots of examples that involve lots of code and fail saves, I am not certain if this will actually work. Any advice?

TIA,

Patrick

 

[dhookom]

Why don't you just try use CurrentUser() to see what happens? It would take less than 1 minute to check to see what value is returned by that function.

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,

Of course you are right. I will try as soon as I have some other issue solved. I am still struggling with some other problem. I decided to use the original combobox as unbound, where users can select from a query. That way I do not have to worry about the field that I want to save in teh table (numeric ID field) messes up the selection process through a dependant name field linked to that ID. Once they have selected based on Name/First/Birthdate combination, the values I want to save into the record of the table, is saved through a different (hidden) text box. That way I do not have to show the field I want to save to the users, and still am able to save the unique ID in my table.

However, ever since I created the new Combobox (well adapted the old one), it will not show the value of the selected field, although it does enable me to start typing the name I want to look up on. I tried using Me.CboComboboxname.Column(0) in a number of places (AfterUpdate, BeforeUpdate, OnExit) but I continually get an error that the Automation method Me is not know in the object.
How do I make sure that the field shows the name value after selecting from the ComboBox? Changing the Combobox has also messed with a different funtion I had coupled to this box. After selecting, it should enable the next field, so that users are forced to enter data in a particular order. This worked fine until I changed the box from bound to a field in the table it edits to an unbound box. Has me flabbergasted. I know I must be missing something, or am running into an idiosyncrasy, but can't seem to figure out what.

Cheers,

Patrick

 

[dhookom]

Did you implement my suggestion from 9 May 07 10:48? This should allow you to bind the combo box to your LOC_ID field while displaying the LOC_EMP_NAME.

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 am trying the solution you suggested

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]
ORDER BY QLOCATION.LOC_EMP_NAME;
Column Count: 8
Bound Column: 1
Column Widths: 0,2,0,0,0,0,0,0

But I get an error message indicating that HOUR_LOC_ID "cannot contain a Null value, since this property Required for this filed has been set to True. Fill in a value." This is where I have selected a name from the dropdown box and it has been checked against the table it was selected from. Is this because the field HOUR_LOC_ID is a numeric field, where the value I have typed in/selected from the drop down is textual? I mean, I do indicate that the BOUND COLUMN is the Column I want to save.

FYI, I did rearrange the order of the fields in the Query QLOCATION, so that the ID field is the last field, but that should not necessarily matter, should it?

I tried the original unbounding because I wanted to SHOW the Name field in the Combobox after selecting, while SAVING the (numeric) ID in the appropriate HOUR_LOC_ID field in the table.

BTW, I cannot seem to find the place to enter the display properties of any values typed in, like starting the value in the Combobox field with a Capital letter, no matter whether the user typed in lower case or capitals...

TIA,

Patrick

 

[dhookom]

If the bound column is 1 then your drop down is not "textual". The value of your drop down/combo box is based on the bound column of the row source query. Do you have a LOC_ID in every record in QLOCATION?
Your combo box will display the first non-zero width column and bind whatever column you have set in the properties.

If you can't seem to resolve this, I would accept a very limited copy by email. You would need to email me directly to find out how to correctly send me a file. You could also put a copy out where I can download it.

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,

>> The value of your drop down/combo box is based on the bound column of the row source query. Do you have a LOC_ID in every record in QLOCATION?<<

Yes, that makes sense and is what I already had figured. The value shown in the Combobox after selection in the DropDown had to be of the type that it is bound to. That is the reason that I decided to make the combo unbound and only in a seperate field enter the value of LOC_ID into the table.

As I tried to explain in my message of May 10th 9:42, when I try this, it somehow wil no longer show any value in teh combobox. Is there a link to what it shows in the combobox and what field the (unbound!) box is linked to, uniquely, while selecting? Or does having an unbound ComboBox automatically imply that it should not be bound to anything, but just show the value of the first field in the selected DropDown record? All the values in the record will already be shown in a few unbound text fields lower in the form, as a method of visual control, but to prevent any uncertainty, I would prefer to at least show the name field in the ComboBox after selecting.

In a sense, we are back to the question of May 10th.

Any insights?

TIA,

Patrick

 

[dhookom]

I don't have a clue why you created an unbound combo box. If you want to store the ID LOC_ID value in the HOUR_LOC_ID, then use one combo box with the properties I suggested.

As I stated in my most recent post "Your combo box will display the first non-zero width column and bind whatever column you have set in the properties." If you don't find this is true than tell me your combo box doesn't seem to follow the generally accepted methods.

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,

First I must apologize for keeping you busy during the weekend, and for keeping your mother form your devoted attention on this Mothers day.

I am sorry that I seem to be so unclear in my explanations. Part of the problem may be that your invaluable advice has made me change some aspects of the project I have been working on. So what I was trying to do at first, has evolved, kind of. I mean the basics and the purpose has not changed, but the methods have been refined thanks to all the great tips.

Ok, I will try to be clearer in what I am trying to do with that ComboBox.

The users that are to enter the data are NOT trained professionals. So I am trying to prevent any confusion there. I want the users to do the following when they enter data into a new form. The form shows an empty ComboBoxform. In that box they can start typing a name, which will open the Dropdown and click on the arrow, opening the Dropdown. From that Combobox they should select a unique Username/firstname/birthdate/Location combination. That combination is defined in the Query QLOCATION by the unique ID LOC_ID. That ID is also the value I want to store in HOUR_LOC_ID, a field in the local table HOURS.

However, ifr at all possible, I DO NOT want the users to see that LOC_ID value. It is (to them anyway) just a meaninless numeric value. I only want them to see the (first) field of the Combobox that theyt made their selection on.

So, even after selecting, and when they have proceeded to the next field, I want that combobox to show nothing else but the Name.
If I understand you correctly, the field in the Combobox will always show the value of the BOUND column of the query. That certainly makes sense. However, since I did not want the Combobox that users use to select the correct record to show the numeric LOC_ID value, I though it would make sense to create an UNBOUND Combobox, using the same query, but not storing any value into the current table. I would then use a seperate (hidden) text box to inbert the LOC_ID value into the appropriate HOUR_LOC_ID field of the record.
Does that make sense, or am I the victim of faulty reasoning here? Or just unclear in what I am trying to accomplish?

TIA,

Patrick

 

[PHV]

the field in the Combobox will always show the value of the BOUND column
Wrong assumption.
The first column with a width different from 0 will be shown.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpgoossen]

PH (Philip? Phrank?)

>>Wrong assumption.
The first column with a width different from 0 will be shown. <<

Shows you how easy it is, especially if one is not proficient in Access, to lose a lot of time and misunderstand other, good willing people by making the wrong assumptions. I must state that I did try to look this snippet of info up, before asking. But did not look in the right places or ask the right questions, I guess. Thanks for this info. I will now try to get things back on track before I start asking more questions!

Thanks,

Patrick

 

[jpgoossen]

Hmmm,

It seems that turning back from my wayward path of using an unbound Combobox, the previous problem came back up.

When I use an unbound cbo, Select_Location, I can easily let the user type in a beginning letter, after which the cbo will dorp down and select the first record of the query that start with the letter typed. Typing in more letters finetunes the selection process. Pressing Enter or clicking on the record selects that and closes the cbo. Just as I would want.
However, when I use the BOUND combobox, with the same Query, and the same methodes and name, it does not function this way. Typing in a letter does open the cbo Dropdown.

I have the following code to open the dropdown:
Private Sub Select_Location_Change()
If Not IsDown Then
Me!Select_Location.Dropdown
IsDown = True
End If
End Sub

But typing in a letter just keeps the selection in the textbox of the combobox. I also does not select any records in the dropdown list. I still have to scroll through the whole list (for this test only 50 records, but after implementation, a lot more) to select the right record and click that with the mouse.

Of course, ListOnly and AutoExpand are set to Yes.

Am I missing anything? Or do bound and unbound comboboxes react differently.

- As a side note, I AM trying to select the records in the Combobox on the second field in the Query. The first field being the ID_field that I am trying to bind. I have set the column width of the first field to 0, so it is not visible when selecting. Should I just rearrange the query and move the ID-field to the last field of the query?

TIA,

Patrick

 

[dhookom]

Don't move the ID field. Why are you running code in the Change event? The Change event triggers every time a letter is typed.

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,

It is not that I LIKE to move the ID field. My whole problem seems to be that I am trying to accomplish something that I cannot seem to get Access to do. And I realize that this is probably more a matter of me being a novice than of Access not being able to do what I want.

First as to using the _Change event. I want to open the dropdown of the Combobox as soon as the user starts typing. And the letter(s) that the user types, should immediately select a specific column in the list that the Combobox is referring to. That Column MUST NOT BE the ID_LOC Column, since that is just an autonumbered, Unique Column. I want the selection process in the drop down to be on the Name/First/Birthdate combo. These ARE uniquely identified by the ID_LOC Column.

SO, no matter how important that IC_LOC Column is in my table, it should not be shown in the Combobox (or elsewhere on the form). The text visible in the Combobox AFTER selecting the appropriate record, should be of the Name Column, and not of the (Bound) ID_LOC Column.

This was a few days back the reason for me to start thinking about an unbound Combobox. Which used all the fields of teh same query, but would not necessarily need to store any values in the table. I thought about using a different (hidden) text box for that.

As for "moving" the ID_LOC Column, all I did there was rearrange the Query that selects the records. I just moved the order of the columns in the query more according to the order in which they would be sorted. Is that a bad idea?

TIA,

Patrick

 

[dhookom]

If you want a combo box to drop down when it gets the focus, remove your code and use code like:

Private Sub Select_Location_GotFocus()
    Me.Select_Location.Dropdown
End Sub

PLEASE understand if the first column has a width of 0, nobody will see it or know its value. The first column with a non-zero width is what your users would see and use to search when typing. This is generally the column your row source is sorted on.


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]

 

[dhookom]

Your problem could be:
[ul square]
[li]corruption[/li]
[li]there is something we don't know about your mdb[/li]
[li]you aren't following instructions[/li]
[li]combination[/li]
[/ul]

I have placed a simple sample of what I think you need at:

http://www.access.hookom.net/samples/combodisplay.zip

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]