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 Replies
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
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
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
Jan 12, 2015
I have a table (tblConversions) that I'm using as a base for replacing values in a different table. tblConversions is set up as such and is made up of nearly 100 records:
ID LABEL CODE
1 Ashaway ASH
2 Barrington BAR
3 Bristol BRI
4 Jesse Smith BUR
....
Another table (tblSysItemLoc) has nearly 1,000 records with a field for Location Names such as Ashaway New Books, Ashaway Fiction, Ashaway Non-Fiction, Barrington DVDs, Barrington Reference, etc. Other fields in this table are just statistics.
What I need to do is loop through tblConversions, and find in tblSysItemLoc where the Location field Starts With the value from tblConversion.LABEL and replace the Entire field with the CODE. For example, from tblSysItemLoc "Ashaway New Books" gets changed to "ASH", "Barrington Reference" gets changed to "BAR", etc.
I feel like I need a loop inside of a loop, but I'm not sure where to begin. Loops are not my specialty.
View 10 Replies
View Related
Jan 6, 2006
I am very new to Access and any help would be appreciated. I have been through the entire Access Bible and many forums but can't find the answer to my specific question.
The project:
I'm setting up a database for tutors and students. Tutors will, via the web, enter data about a specific tutoring session (studentName, subject, sessionLength, etc.) Then at the end of the month a report will be generated detailing hours tutored and total compensation.
The problem:
Students pay varying rates based on subject (math or verbal). So in the Session table each record for each tutoring session has an empty field called payRate. This is the rate the student will pay based on subject. So I need this field to "fill in" automatically based on the 'student' and the 'subject' from the same record. Then I can multiply the field by sessionLength and get a totalPay field for the session. Add these at month's end and pay the tutors.
I've set up a lookup table called 'subject' with three fields (student,subject, payRate) and I have the student and subject combined as the primary key. Am I on the right track here? If so, how do I link the tables to get that Session 'payRate' field to fill in automatically. This is where is breaks down for me. I've tried the lookup wizard but all I seem to get when I open the Session table is an empty PayRate field with a drop down box containing the entire subject lookup table.
Sorry for being so long winded but I need help from someone better at his than me. Thanks in advance.
View 2 Replies
View Related
Jan 12, 2008
hi, plz i need a fast help
i have 3 tables in access:
Country Table: fields:country Id, country name
City Table: fields: City ID,Country Name(lookup field to Country Table), City Name
Street Table: fields: Street Id, country name(lookup field to Country Table), City Name(must be lookup to City Table where City.Country name = Street. Country name), street name
my question is: how can i make the lookup of the City Name in the Street Table, in a way that only cities of the specific country of the record active are displayed ???
example:
Country table:
1 Lebanon
2 KSA
3 Kuwait
City Table:
1 KSA jeddah
2 KSA Riyadh
3 Lebanon Beirut
4 Lebanon Tripoli
STreet Table:
1 KSA (???) Sary (the ?? must be a combo Box that show only the cities of KSA: jeddah, Riyadh..which are in the City Table)
2 KSA (???) sondos (same as above)
3 Lebanon (???) hamra (the ??? must be a combo box that show only the cities of lebanon: Beirut and tripoli...which are in the city Table)
i have created all the fields in these tables...and when i put the field City Name as lookup, i am failing to mak eit read correctly from the city table according the the country of my active record..
plz anybody knows how to do this query???
View 2 Replies
View Related
