Creating A Form That Allows For Value Lookup
May 20, 2005
Hi, I'll give you a little background info before I ask my question:
I have a table called "Company" with the following feilds:
Company_ID (PK), Ticker (Indexed, Unique), Company Name
I have (or at least want) a single record access form, and now for the problem:
I would like someone to be able to type a ticker into the ticker feild, and have it search for an existing feild with that ticker (it would automatically populate company name with the correct value). Now, if the ticker doesn't exist, I would like it to bring up a new record form so the user can type in that information. I know I've seen something like this before, but its been a while since I've used access, so I'm still kinda brushing up on this stuff. Can anyone help me out here? Thanks!
View Replies
ADVERTISEMENT
Mar 20, 2008
Hey all.
I'm having a few issues with creating a Lookup on an Access form. I'll recreate the scenario below as best i can to help with my explanation.
I have 2 data entries. The first is a Post Code (Zip code), and the second is an Area Rating that applies to the Post code. For example:
Post code AB1 8 is awarded a rating of A
Post code SO1 3 is awarded a rating of F
I have already created a table in Excel with all 2,922 Post Codes (AB1 1 to ZE3 9) and the corresponding Area Rates (A to F)
What i'm making is a form that holds customer details. I'd like a combo box or text box that when you type in the Post Code, another text box automatically displays it's corresponding Area Rating.
Code:Post Code: Area Rating:------------- --------------| CM2 7 | | A |------------- --------------
I apologise for my really very poor diagram :P
So the Post Code box is either typed into or a combo box with the list of Post Codes, and the Area Rate box cannot be typed in to, but it automatically displays the record dependant on what is in the Post Code box.
Ideally, these will also be recorded onto the main table (IE not the table with the list of post codes or area ratings, but a 3rd table holding the rest of the details that will be used to create statistics and graphs)
Can anyone help me with how to do this? I've been trying to work it out all afternoon!
Any help is really appreciated.
View 3 Replies
View Related
Jan 7, 2007
Hello.
I am a newbie in Access. I am making a Boostore database. Here is the
relationship in problem:
http://moryasha.narod.ru/acc.gif
I set the Data Type for BookISBN field in my OrderDetails table to a
LookupWizard. However, I dont want it to look up all the ISBN's in the Books
table, but only those whose Publisher is the same as the publisher of the
relative order.
unfortunately, I am very puzzled and haven't been able to do it by myself.
Can anyone please kindly explain me as detailed as possible? Grateful in
advance.
View 1 Replies
View Related
Oct 30, 2004
Hi, I am fairly new to ACCESS. I understand the basics, thus i am a novice user.
I am trying to create a new database for managing booking and storing info on various artists.
In my main table where bands' info will be stored I am trying to make data input as efficient as possible and to ensure consistancy.
So, it leads me to ask this question... regarding inputing info of bands' location.
I want to use a lookup table to enter data for:
1 Country (Canada/US),
2 Province/State
3 Cities?
Questions:
1 Whats the best way to organize this?
2 Do i have to create seperate tables for each country and province? Or any kind of suggestion would greatly appreciated.
Furthermore, when entering data on the form, for instance if i select:
Canada --> then I'd want only Canadian Provinces to show in the following field ---> then i select Ontario --> then i'd like only ontario cities to show in the next field....
3 What would require for me to create this kind of thing... Not sure if there are 'if' functions in ACCESS.
Could someone please guide me towards the light?
thanks.
View 2 Replies
View Related
Jan 27, 2014
I'm trying to create a field with the lookup wizard, however it's a bit more complicated. I need the field to have multiple choices(3) and when a specific option is selected I need some additional choices to appear. For example: Question - have you ever used a specific product. Options: a-yes, b -no, c-other. If option b is selected then thats it, but if option a is selected I need a few other options to appear: was the brand Option1 or Option2? Also did you use it before(option1) or after(option2) smth.
View 2 Replies
View Related
Aug 3, 2014
I am designing a database for a quarry for maintenance of their machines. I have a table that has a full list of all the parts on the machines, and a multiple value lookup field that says what machines said parts are used on. I am trying to have these values translate to another table, so I can then insert a field to say how many of each part are used on the respective machine, to display on an information form for each machine.
I would also like this table to update if the relevant information is updated, for example if a new machine is input, then have the table update to reflect what parts are used on it (an After Update function?) I have made a query that gives me the read out I want (attached) but just cannot figure out how to get that into a table so I can add the extra information.
View 1 Replies
View Related
Mar 31, 2015
I want to create a text box within a form that automatically populates a contact number based on a selection from a combo box, also in the same form.
For example, I have a Bidders Table (tblTenders), this form includes information regarding the Tendor like the company name and a main contact within that company and a phone number for that contact.
I've created a separate table for all the contacts called tblContacts. This table holds all the contact information for each contact. I have a simple form called frmTenders that asks the user to input the Customer (which is the company who are bidding) and the Main Contact, which is a combo box to select the main contact from tblContacts. Below that combo box is a text box called 'Contact Number' - I want this box to display the contact number for the main contact automatically when a main contact is selected from the combo box.
The contact number text box isn't storing that information in any tables etc. It's just for viewing purposes when we need to make a call to that specific tender.
View 5 Replies
View Related
Jul 24, 2007
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
View 4 Replies
View Related
May 29, 2012
I am creating a customer database for an independent insurance agency. Within that customer database, we have a lookup field that references another table "Carriers". This is where we select the insurance carrier we have placed the clients insurance with (ex. Geico, progressive, etc). This field allows multiple entries since clients can have policies with different companies.
We also have contact detail forms for each of our insurance carriers where we store information like web logins, contact phone numbers, and other notes. These forms are based on the same table as the lookup field.
I would like to create a hyperlink so, if we are looking at a clients form with all of their contact info we can click the insurance company in the carrier field and it will bring us to the contact details form of that carrier.
View 7 Replies
View Related
Jul 27, 2015
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
View 4 Replies
View Related
Nov 23, 2004
I need to create a form to allow the user to look up patient information by entering the patient's social security number (SSN). The form would have a blank box asking the user to "ENTER THE SSN." When the user enters the SSN, it would show the other info from the table (e.g., name, psychologist, psychiatrist, etc). Unfortunately, I can't use a list box for confidentiality reasons. Is this something I can do with Access or do I ned VB? Any advice would be helpful. Thanks much!
Dan
View 1 Replies
View Related
May 16, 2006
Hi there, please excuse my poor access skills, I've searched the forums but can't really find what I'm looking for......I have two basic tables in my db, one is an inventory listing all the stock in the company, and this is linked to a tracking table, which contains all the different movements for this stock item......this is a simple one to many relationship, what I want to do is lookup the value of the 'In/Out' field in the last tracking record for every item, and set the quantity of that item in the inventory table to 0 or 1 depending on the value......how would i best go about this and where should i put the code.....? Any help is much appreciated.
View 3 Replies
View Related
Feb 21, 2005
I want to be able to choose a record from the combo box and then the rest of the fields fill in themselves... Also some of the fields are in a subform but are all linked to the entry in the combo box... can someone help me ? :confused:
View 2 Replies
View Related
Apr 22, 2005
I have a contact dB with 2 contacts (main & alternate). I would like to be able to pull any record containing either the main or alt last name up. Right now the unbound combo box works fine pulling up the first contact name but I need to be able to pull up a record if the second contact name matches as well. I am trying to use 1 unbound combo box to look at both fields (LastName1 & LastName2). Is it possible and how should I set it up?
View 14 Replies
View Related
Jul 30, 2005
I have a form with a combobox based on a query of a lookup table. If the name entered is not found in the combobox, I want a popup form to open allowing the user to enter a record for this new name in the lookup table, then close. What is the best way to this? A command button? The Not in List Event? A msgbox? I'm not sure.
View 7 Replies
View Related
Aug 7, 2005
Can anyone help?
I'm having trouble storing autofilled data from one of my forms and I was wondering if anyone has any ideas.
My data is as follows
tblProducts
ProductID
Description
Nett
tblPurchaseOrders
PurchaseOrderID
PODate
Supplier
tblPurchaseOrderDetail
PurchaseOrderID
ProductID
Description
Qty
Nett
Now I have a standard order entry form which works fine, my problem is with the subform:
frmPurchaseOrderDetail
ProductID
Description
Qty
Nett
On my form Description and Nett are auto populated referencing ProductID but this information does not transfer to the table tblPurchaseOrderDetail. The main problem is that a nett price can occasionally chanege (inflation) but I need to be able to look back to a particular order for audit purposes and be able to see what I paid.
I'm sure there is w way round this, but I've been unable to figure it out. I've gone through loads of different threads on the forum and as a result am sure it can be done, I've also looked at the examples posted by several users, but none seem to approach the lookup quite from this angle, does any one have any idea...sorry for the long post, thanks.
View 3 Replies
View Related
Sep 21, 2005
This is going to sound really stupid but I urgently need to have one form, where people can enter a product code and the description automatically shows in another field.
I have two tables, one for the form and another with all product codes and descriptions.
Presumaby I would need to make a query, which i've tried and failed miserably.
it seems so simple when you read the access help but i've missed something fundamental.
HELP!
Thank you.
PS I should have said my boss expects to enter data in one field - the product code - and the description automatically and instantly pops up in another field on the same form before leaving that particular record.
View 3 Replies
View Related
Oct 1, 2005
Hi all,
I am wandering if anyone can help me here. I have a form which is linked to a table called tbl_File. Within in this form & tbl_File there is a field called Country_Code. In the Country_Code field the user places a number that corresponds to their country they want.
Now Country_Code & the corresponding country is defined in a seperate table called tbl_CountryCodes.
What I would like to do is in my form once the user has inputed a number in the Country_Code field (it will be a combo box), a label or textbox in the SAME form will then do a search of the corresponding Country name in tbl_CountryCodes and then display it to the user.
Any ideas will be greatful :D
Karv
View 1 Replies
View Related
Jun 18, 2013
I have a form with a combo box that contains a list of Employee Names which are contained in an Employee table (column 1).Just below it I have a textbox titled Employee Number. This field should remain blank until an Employee name is chosen from the combo box at which time it should populate with the corresponding Employee number which is column 2 of the Employee table.
View 5 Replies
View Related
Mar 2, 2005
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
View 1 Replies
View Related
Jul 14, 2006
I have my main form which is connected to my main table - Query 1
I have a lookup table (actually a view from SQL Server) which holds my user name - the username used to login to the database- just one value.
In my main form I have a 'Logged By' field. I want this field to default to my login name when I process a record.
I thought it would be as easy as connecting the lookup table to my main form and then in default value typing My_login.user_name.
Apparently not. It just has the #Name? in there.
Anyone have an opinion on this ?
I can use the =environ("username") in the default value which works to good effect but different users use each others PC and I would like the field to be defaulted to thier database login and not thier windows on.
Please help ! It seems so small and its driving me ceeerazy !
View 1 Replies
View Related
Aug 23, 2006
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID
Customer Name
ProductID is a combo box.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want it to lookup that ID from tableProduct. However, how can I do this when everything is already bound to tableData?
Thanks in advance.
View 1 Replies
View Related
Aug 23, 2006
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID
Customer Name
ProductID is a combo box on the form.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want a separate textbox to lookup that ID from tableProduct and display the ProductName.
How can I accomplish this?
Thanks in advance.
View 2 Replies
View Related
May 5, 2006
I have been editing the service call management database that comes with office XP.
I have a table called "Workorders" with fields:
WorkorderID (primary key)
CustomerID
And another table called "Workorders by Customer" with fields
CustomerID (primary key)
I want to be able to have an input form where I input a workorderID which then opens the "Workorders by customer form" by customerID which relates to the WorkorderID in "Workorders" which is typed in the input box.
Im only a novice and its the code part that im struggling with.
Any help would be great.
View 2 Replies
View Related
Jul 17, 2013
I wanted to create a field lookup with values that I specify, not on the table sheet, but on the form. User can click on a text box or combo box and can select a list of value that I specify, not values that are listed on a table but ones that I type in, in the form.
View 11 Replies
View Related
Jan 11, 2014
i am working on a school database, in data base i have create two tables tblAccounts and tblTransaction and a form frmTransaction .
tblAccounts contain two fields
GLcodes
Description
and frmTransaction contain
Glcode
transaction type
debit
credit
date
narratives
in form when i enter a glcode, lookup field match the code from tblaccounts and shows the description in form against gl code.But i am facing a problem when i enter a wrong gl code my form accept it and move to the next field and when i leave blank field of glcode same problem that i am facing, i want that , when i enter a wrong glcode in a form amsgbox will apear that asking for correct glcode.
View 5 Replies
View Related
