General :: Make A Msg Box In A Form Where Values Are Lookup From A Table?

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 Replies


ADVERTISEMENT

General :: Lookup All Values Of Another Table

Feb 12, 2014

II have been working in Access 2010 and by no means would I call myself an expert. I have two different tables, one is called 'JobsList' and the other one is called 'StatHolidays'. On the Jobslist form, I have a field that requires a ship date, however, I don't want to allow the user to select the dates listed in the StatHolidays table. Is there a way that when a user picks a date that is listed in StatHolidays that a pop up box will say "This date is Christmas, do not choose this as a Ship date". The 'StatHolidays' table has a field for a date and for a description of the holiday.

View 7 Replies View Related

Lookup Table - How To Make More Than 1 Field From Query Appear In The List

Dec 1, 2014

In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.

I am using

Code:
SELECT instructors.first, instructors.last, instructors.ID FROM instructors;
in my Classes table.

What am I doing wrong?

I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.

View 1 Replies View Related

How To Make A Table Lookup Field Depend On Value In Another Column

Sep 20, 2012

I have a table called Locations that lists Countries and Cities:

USA, Detroit
USA, Chicago
USA, New York
UK, London
UK, Liverpool
UK, Birmingham
France, Paris
France, Le Mans

I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).

View 5 Replies View Related

Lookup Values In Another Table

Jun 13, 2006

BACKGROUND

I am desperate to solve this problem but unfortunately I have not been able to figure it out. Below I will outline a design of a database and the desired results.

I know what I want to do but I don’t know how to do it (or whether it is impossible!)

DATABASE DESIGN

The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.

Staff Data
Contains daily data for several members of staff
Staff ID
Staff Name
Date
Data Field 1
Example records:
600-001, Bob Smith, 01/03/2006, 50
600-001, Bob Smith, 02/03/2006, 50
600-001, Bob Smith, 03/03/2006, 50
600-001, Bob Smith, 04/03/2006, 50
600-001, Bob Smith, 05/03/2006, 50
600-002, Jayne Cole, 01/03/2006, 60
600-002, Jayne Cole, 02/03/2006, 60
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
600-002, Jayne Cole, 05/03/2006, 60
600-003, Alex Winter, 01/03/2006, 20
600-003, Alex Winter, 02/03/2006, 20
600-003, Alex Winter, 03/03/2006, 20
600-003, Alex Winter, 04/03/2006, 20
600-003, Alex Winter, 05/03/2006, 20

Team Lookup
Shows what team each staff member belongs to and what date this is effective.
Staff ID
Team
Start Date

Example records:
600-001, Sales, 01/01/06
600-002, Sales, 01/01/06
600-003, Accounts, 01/01/06
600-002, Accounts, 04/03/06

Please note:

The first three records show that at the start of the year Bob (600-001) and Jayne (600-002) worked for Sales and that Alex (600-003) worked for Accounts.

The last record shows that from 04/03/06 Jayne switched teams to Accounts

Query Assign Team
Assigns the correct team to Staff ID for each date
Staff ID
Staff Name
Team
Date
Data Field 1
Desired Results:
600-001, Bob Smith, Sales, 01/03/2006, 50
600-001, Bob Smith, Sales, 02/03/2006, 50
600-001, Bob Smith, Sales, 03/03/2006, 50
600-001, Bob Smith, Sales, 04/03/2006, 50
600-001, Bob Smith, Sales, 05/03/2006, 50
600-002, Jayne Cole, Sales, 01/03/2006, 60
600-002, Jayne Cole, Sales, 02/03/2006, 60
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
600-002, Jayne Cole, Accounts, 05/03/2006, 60
600-003, Alex Winter, Accounts, 01/03/2006, 20
600-003, Alex Winter, Accounts, 02/03/2006, 20
600-003, Alex Winter, Accounts, 03/03/2006, 20
600-003, Alex Winter, Accounts, 04/03/2006, 20
600-003, Alex Winter, Accounts, 05/03/2006, 20

Query Group By Team
Summarises data by team/date
Team – Group By
Date – Group By
Data Field 1 - Sum
Desired Results:
Sales, 01/03/06, 110
Sales, 02/03/06, 110
Sales, 03/03/06, 110
Sales, 04/03/06, 50
Sales, 05/03/06, 50
Accounts, 01/03/06, 20
Accounts, 02/03/06, 20
Accounts, 03/03/06, 20
Accounts, 04/03/06, 80
Accounts, 05/03/06, 80

PROBLEM: WHAT I AM TRYING TO DO

I don’t know how to get the query “Query Assign Team” to work!!

I would like to lookup up the ‘Staff ID’ and ‘Date’ in “Team Lookup” and return the appropriate value for ‘Team’

If the only two records in Tbl Staff data were:

600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60

I want the query to return:

600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60

Can anybody help me?

Should I be using DLOOKUP? If so, how?
Is VBA the only way around my problem? If so, can you tell me what it is?
Am I attempting the impossible?

View 9 Replies View Related

How To Lookup Values In A Table And Add Them To Another?

Jan 22, 2007

I just started a new database and I'm new at this so I have a question about ID numbers for my two tables.

In Table1, I have faculty demographic information, and a FacultyID (unique ID number created by AutoNumber).

Table2 has the courses those faculty members taught - one faculty teaches many courses. But this table does not have the FacultyID that I added to Table1.

So, how do I automate the process of looking up the FacultyID number from Table1 and adding it to the currently blank FacultyID field in Table2? Theres hundreds of records and this will be done every semester, so I need an automatic way of doing this lookup.

Any help would be appreciated. Thanks.

View 1 Replies View Related

Modules & VBA :: Restrict Value In Lookup Field To Only Include Values From Table B Not In Table A?

Feb 2, 2014

I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.

This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.

How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?

View 5 Replies View Related

Combo Box To Lookup Values In A Table

Nov 16, 2006

I have a small database with 3 tables.
tblBilltoCustomer
Fields - Key - BillCustID , companyname, address1, address2, city, state

tblOrders

tblCustomers

I have a form that has tblCustomers as the main form then tblOrders as a sub form.

I want to place a combo box on the form that will lookup a company name from the tblBilltoCustomer table then brin in the address1, address2, city,state , into the form for that record. But then I need that same info to print on a rpt.

I can get the lookup to work using =cboCompanyName.Column() but the addresses , city and state will not show on report
Is there any good samples of lookup fields

Any Ideas are greatly appreciated

Dean

View 6 Replies View Related

One Lookup Table With Two Needed Values - How To Use Both

Apr 24, 2015

The Lookup table has two fields containing values that are needed: Description and Amount ($). In the table that uses the Lookup, I'd like to have both values shown, but have a dropdown just once. In other words, when the user selects a description (the dropdown shows both the description and amount), can the amount be inserted into an Amount field as the description is done currently.

View 4 Replies View Related

Multiple Lookup Values Into One Table Field

Apr 4, 2006

Hi,

Is there anyway to have a single field in a table which is populated via the use of a lookup onto another table, but allowing multiple value selection out of the lookup table and populating those into the field...

For example

Table 1 is customer details
Table 1 field 3 = areas of interest

Table 1 field 3 is populated via a lookup into Table 2 interests

Table 2 has 4 records

Sport
Household
Motoring
Family

I want to be able to select 1 or more of the Table 2 values and populate them into Table 1 Field 3....

Help my head hurts....

View 3 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Queries :: IIF With Null Included Field - Lookup Table Values

Jul 1, 2013

I am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.

On the same criteria row for these fields, I have

Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2])
Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2])
Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])

qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.

This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.

View 3 Replies View Related

How To Make Values In One Table Automatically Show In Another Table?

May 22, 2007

Hi, I have a problem, I have a table were I list networkservices and their logical ports and I have another table were I list IPaddresses used by different machines. I'm using a multivalued lookupfield to pick which services I'm using for each Ipaddress, and thats working fine, but now I want to make the ports which is listed in another column, to automatically show in it's own column in the IPaddresses table depending on which services I'm choosing for the different IPaddresses. Is this possible?, and if it is how can I make this happen?

View 1 Replies View Related

Linking Result From Lookup Column In A Form To The Values Form?

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

Tables :: Split Database - Table Field To Lookup Values From A Query?

Jul 6, 2015

I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?

View 3 Replies View Related

Modules & VBA :: Wildcard Search Of Combobox - Lookup Values In Table Column

Nov 19, 2014

I have a form which has a combobox called Task_Ref which looks up values in a table column.

I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-

Test number 1
Yesterdays Test

As long as the word "Test" appears I would like the above to happen?

I was thinking of something along the lines of:-

If InStr(Task_Ref.Value, "Test") > 0 Then
P1.Value = True
Else
P1.Value = False
End If
End Sub

But this hasn't worked

View 4 Replies View Related

Forms :: Lookup With Values From Another Table - Populate County Once Zip Code Entered

Mar 22, 2013

I want my form frmintake to lookup with values from another table and populare county once the zip codes has been entered.

I have a table called tblintake and another tblcounty.

tblintake has zip codes entered manually

tblcounty has zip codes and counties for all regions

how does this work out in dlookup.

=DLOOKUP[Zip Code],[tblCounty],[zip Code]=[County])

View 1 Replies View Related

Tables :: Converting Excel Table For Lookup Of Values Based On Row And Column Headers

Sep 3, 2014

how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?

View 12 Replies View Related

Filtering Lookup Value Based On Other Lookup Values

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

General :: Lookup Into A Table - Populate Same Field

May 28, 2014

I have a table holding a list of post codes, and their servicing depot EG -

ID Postcode Depot
1 AB10 Edinburgh
2 AB11 Edinburgh

Then a form, which has a field for depot ( Fld_Depot )

What i would like is, when the user clicks on the field, a msg box prompts, asking for them to input a postcode

Once the postcode is entered, it populates that field with the relevant depot from the table....

View 3 Replies View Related

General :: Lookup List In Table From Query In Split Database

Sep 14, 2012

i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?

View 2 Replies View Related

General :: Creating Entries In A Table Based On Multivalue Lookup

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

General :: Make One Row In Table A Become Row In B And Rows In C

Nov 7, 2012

I have an existing database being used for for order processing (normalized, working). The order table houses the general order info and a separate detail table holds 1 or more orderdetail records for each order in the order table, related by orderID.

A user has asked that I find a way to import her data into the system from an excel spreadsheet. She has individual columns for each type of order detail record, so for each "order" row, there may be 1 or more columns of "orderdetail"s that I will need to parse into the correct tables.

I imported the raw excel into a table, but I need to append that data into the order and orderdetail table rows (i.e. I have to create the order and orderdetail records that match every other record in the system).

It looks like I need to somehow perform a looped INSERT INTO [ORDER] (field1, field2....) VALUE (val1, val2) but there are those related detail records to contend with (the orderdetail table entries) which may be more than one insert....

Test set of data is attached.

View 5 Replies View Related

General :: How To Make Structure Of Table

May 20, 2014

I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?

View 2 Replies View Related

General :: Can't Run Make Table Because Mode Is Disabled

May 17, 2013

How do you establish the Mode. I can't run a Make Table because the Mode is Disabled and blocking the action

View 1 Replies View Related

General :: Create A Field Lookup With Value That Specify In A Form That TYPE In

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







Copyrights 2005-15 www.BigResource.com, All rights reserved