Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 27th, 2006, 05:14 AM
Friend of Wrox
 
Join Date: Apr 2006
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default lookup function

Hi,

I have a question that is probably going to look like a stupid one, but it's the first time i'm trying to create something in access...

I have a table where I have the companies departments and their default phone number. This table is linked with another table with a combo box.
A form is made out of this second table with the department as a combo box. Now, when I open the form I would like the phone number field to look up the default phone number in the first table that belongs to the department chosen in the combo box and insert it in the phone number field. However, when the default value on the form is changed, the phone number in the first table should stay the same. (when the form is opened the next time with a new record, the phone number should be the one coming from the table, even if this phone number had been changed in the previous record because it was not the right one, and it is important that the phone number in the first table never changes...)

There are 2 scenarios possible:
1) the first one is that the phone number should be looked up when the form is opened.
2) the second one is that the phone number should be looked up when the department-field on the opened form is changed (or updated I guess).

I guess I have to create an event procedure 'on open' for the first case and another event procedure 'After update' for the second case.

The forms are opened with an action button from a main menu. Does this change anything?

What are the event procedures I have to use? Are they the same in the 2 cases? Do I have to use them in the form properties or in the action button properties?


Thanks

 
Old April 27th, 2006, 06:28 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What does the table structure look like?

Why do you need to show the default or primary phone number from a department without using a subform?

Why do you need the phone number inserted into another field if you already have it in the first table phone number field?

I don't understand why the phone number in the first table must never change.

Sorry, I think I am dense this morning.

Can you do the table structure with fields?

mmcdonal
 
Old April 28th, 2006, 05:40 AM
Friend of Wrox
 
Join Date: Apr 2006
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have a first table named 'tbl afdelingen opv TT' with the fields
    'Naam afdeling' Text type PRIMARY KEY (= department)
    'Standaard telefoonnummer'. Text type (= default phone number)
I have a second table named 'tbl opv TT' with the fields
    'Volgnummer' Autonumber type PRIMARY KEY
    'Datum' Short date type
    'Naam afdeling' Text type (= Department)
    'Naam' Text type (= Name of a person)
    'Telefoonnummer' Text type (= Phone number that belongs to the person)

These two tables are linked with a one-to-many relationship from the 'tbl afdelingen opv TT' (ONE)to the 'tbl opv TT' (MANY) by the fields 'Naam afdeling'. The field 'Naam afdeling' from the 'tbl opv TT' works with a combo box to limit the values and make sure the right values are used.

The 'tbl opv TT' is linked with another table named 'tbl Opv TT detail' and this has the fields
    'Volgnummer' Number type PRIMARY KEY
    'Nummer TT' Text type PRIMARY KEY (= internal invoice)
    'Nummer Code' Text type (= code used to know what kind of invoice it is)
    'TT Terug?' Yes/No type (= used to check if the invoice came back)

The tables 'tbl opv TT' and 'tbl Opv TT detail' are linked with the 'Volgnummer' fields. The ONE side is on the 'tbl opv TT' table.

A form has been made for the 'tbl afdelingen opv TT' table to be able to had a new department, because it has to be able to evolve... (and then the default phone number can easily be changed if the person who takes these internal invoices changes his or her phone number, it is almost always the same person who takes these invoices, but somethimes someone else from the same department takes them (if the person is sick or on holiday for example), that's why it is important that the number stays the same in the table, because when the persons gets back, the phone number does not have to be inserted again. It then gives less information to add manuelly on the forms).

Different forms have been made out of the 'tbl opv TT' table, with the 'tbl opv tt detail' table as a subfrom on these forms.

On one of them I would like when I choose a department out of the combo box (limited list!!!), that the 'phone number' field would automatically be updated with the default phone number that the form can find in the 'afdelingen opv tt' table, and that if another person than the usual one takes these invoices that I can change this number in the occasional persons phone number, but without changing the phone number in the table (because it's the number that is used 90% of the time). The phone number is later used to call the person back if they do not return their invoices. The phone number should always be 'suggested', but it has to be able to be changed if it is incorrect.

On the second form it is the same scenario, but the number should be suggested when the form is opened and not when the department field is updated, because the department field is locked on this form.

I don't know if this makes it clear for you to understand now... I know it will probably seem complicated, but this is the way I would like it to work. If you think it is impossible or if you know what I'm talking about, but it's the wrong approach according to you (and that you have a better idea) just let me know...


Thanks

 
Old April 28th, 2006, 06:47 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Okay, it is a little clearer. I am not sure when you say "that the 'phone number' field would automatically be updated with the default phone number that the form can find in the 'afdelingen opv tt' table," what field you want to update. And where you want this data to show up. If you just want it to be displayed on the form, then create another subform that displays the 'Standaard telefoonnummer' of each 'Naam afdeling' (hide this field on the form) and set the properties of the form for no border, and drop it on the main form and link it by the 'Naam afdeling' field. Then whenever you select a department, the 'Standaard telefoonnummer' field will be displayed. Change the text box and form formats to flat.

If you want another field to be populated with the 'Standaard telefoonnummer', you can do this at the table design level and set the default [Forms]![etc], but then you may throw an error if you use this form anywhere else for data entry.

You can also put in a button to transfer the number being displayed into the table (I am not sure what field it would go in). The better thing it to display the number where needed from the department table, and not move it to another table. I had a similar situation in a database I just did where there was a primary contact, and a secondary contact, and the user would check a check box if they wanted to use the secondary, and that number and contact info would be displayed where needed instead of the primary. Actually, there were n number of contacts and any one of them could be displayed, but I never moved the data to another table since this would not be normalized, and queries worked fine using the check box value.



mmcdonal
 
Old April 29th, 2006, 02:35 AM
Friend of Wrox
 
Join Date: Apr 2006
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'll make it easier with another example...

If I have a table named "A" and that this table contains the fields "B" (department) and "C" (default phone number) and I have a second table named "D" with the fields "E" (autonumber), "F" (department) and "G" (used phone number).

A form is made on table "A" and is called "A2". This form contains the fields "B2" (department) and "C2" (default phone number). This form is made only for data entry or to change the default phone number, but will not be used very much...

A second from is made on table "D" and is called "D2". This form contains the fields "E2" (autonumber), "F2" (department) and "G2" (used phone number).

Table "A" and "D" are linked with a one-to-many relationship for the "B" and "F" department field, so the "F" field from table "D" have to be chosen from a combo box (the list prevent to enter other values). On the "A2" and "D2" forms the same combo box is used (pretty standard I know).

Now what I would like to happen is that when on the "D2" form I choose a department in the "F2" combo box, that access automatically looks up in table "A" what the value is in field "C" for this department that off course is equal to a value in "B". However, the value "C" in table "A" has not to be changed, but only 'looked up' by access.

The tables are only used to store the data, all data entry and looking up with queries will be done with forms or shown on reports, because the application will be build with a main menu and command buttons for navigation. (I will work with users that never used access, so I don't take risks on this, that's why I use the menu, the 'navigation buttons' and that all data will work with forms and reports... I'm just making sure no accidents happen :-))

This is a basic example for you for what I would like to have. Is it possible to do something like this or not?

Could you explain this in some 'easy' language to me, because it's the first one I'm making something in access and I don't know how to use VBA either... I'm trying to learn here :-) Because I only partially understand the 'solution' with the 'hidden subform' you're giving me...


Thanks very much mmcdonal

 
Old May 1st, 2006, 06:45 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   Okay, the subform solution will work.

   First, create another form on Table A. Just select Table A and click the autoform button. Then format the form like this:

1. Set the background color to the same color as the form D2.
2. Set the properties of the form to:
   Default View: Single Form
   Allow Form View: Yes
   Allow Datasheet View: No
   Allow PivotTable View: No
   Allow PivotChart View: No
   Scroll Bars: Neither
   Record Selectors: No
   Nav Buttons: No
   Dividing Lines: No
   Auto Resize: No
   Auto Center: No
   Border Style: None
   Control Box: No
   Min Max Buttons: No
   Close Button: No
3. Set the Dept field Visible Property to No.
4. Format the Phone Number field as you want it to appear on the form, then change the Field Data properties to Enabled: No, Locked: Yes. (This will make the phone number show up as just flat print on the form when you drop the subform, and it won't be available for editing.)
NOTE: You will have to switch back to design view to close this form if you look at it in form view since there is no border or close button.
5. Save this subform.
6. Open the Form D2 in Design view, and drag and drop this subform where you want it on D2.
7. Resize (very small since there is only one line to be displayed) and set the form's "Special Effect" to flat so it blends in with the background.
8. Set the Link CHild Fields to Dept, and Link Master Fields to Dept (if Access hasn't already done this.)

Now when someone opens Form D2, they will see the phone number of the department they select as they select it. It will look like flat print, and it will not be available for editing.

If you really must force the displayed phone number into field G, then I can show you how to do that as well. This will mean that in any subsequent queries you will have to check for a value in this field G first, and if it is empty, take the primary phone number from C.

HTH

mmcdonal
 
Old May 3rd, 2006, 11:32 AM
Friend of Wrox
 
Join Date: Apr 2006
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm rebuilding my database to correct some errors I made while building it and improving some features that will make it easier to use, so I can't try this solution now.

I'll try it when I'm in the same situation again and if I have some question, I'll let you know.

Thanks for trying to help me.

 
Old May 17th, 2006, 04:43 AM
Friend of Wrox
 
Join Date: Apr 2006
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi mmcdonal,

my database has almost been rebuild. I see what you mean now with the solution you gave me, but could you tell me how I can 'force' the phone number to appear in the G-field???

thanks

 
Old May 17th, 2006, 06:20 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You wouldn't force it to appear, but force it into the field as the value stored.

You will have to determine the event you want to use to copy the data from the first phone number field to the second. You may want to use a check box that states "Check to use Primary Phone Number" or something like that.

If you use a check box, you can add an After Update event to the check box, and if it is checked, make the secondary phone number field disappear on the form or subform.

Then when you run reports, you check for the value in this check box, and if it is Yes, you display the primary number, and if it is No, you display only the secondary number.

This solution is preferable to moving the number around and storing it twice.

How do you want to handle the solution? With the check box, or actually moving the number and storing it twice?



mmcdonal
 
Old May 18th, 2006, 04:52 AM
Friend of Wrox
 
Join Date: Apr 2006
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I know it sounds stupid, but I would like to store it twice....

So what I was thinking is to create an after update event when the department is chosen from the combo box. It then has to 'copy' the value form the subform into the table...

I already tried to use some code but it didn't work, I propably used a wrong one, but I can't remember what it was...






Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorporating lookup function in macro mikelever Excel VBA 12 November 13th, 2007 01:25 PM
LookUp and Hyperlink frankjuel Excel VBA 1 January 12th, 2007 03:03 AM
lookup using VB karebear VB How-To 1 August 2nd, 2006 04:32 PM
Lookup Tables mossimo Access 4 December 5th, 2003 11:27 AM
File name lookup acdsky Classic ASP Basics 3 November 22nd, 2003 11:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.