Lookup Field Row Dependant

Mar 18, 2005

Hi Everyone,

I have a question about lookup fields. I have the tables below. The main table is [vid] and i reference other tables like [manufacturer], [model].. etc. to fill in [vid]. I have pull down lookups for all of [vid] but it is showing all models, makes, etc. I need helping making the lookup row dependant. For example when I am in [vid] and click on the [model] combo box for vid=1, I only want to see the models for that manufacturer(Ford), so it will only show mustang, escape, and navigator; not all of the models. For vid=2, when I click the pull down for model I only want to see Gm Models, corvette and cavalier. So this is what i mean be row dependant. Is this possible?

Thanks so much in advanced,
Michael

[vid]
vid,year,mft, make, model, submodel, ...
1,2005,ford,ford,Mustang,GT
2,2005,GM,Chevrolet,Cavalier, LS
3,2005,dcx,Jeep,Wrangler,base
...
30,2005,ford,Lincoln,Navigator, base

[manufacturer]
mid,mftName
1,ford
2,gm
3,dcx

[Model]
modelID,modelname,modelmft
1, Mustang,Ford
2, Escape, Ford
3, Corvette, GM
4, Cavalier, GM
5, Navigator, Ford
6, Wrangler, DCX

View Replies


ADVERTISEMENT

Forms :: Lookup Value Dependant On Previous Field Entry - Requery?

Jun 21, 2015

I'm creating a database which contains information for keeping dog grooming clients. It has 3 tables, table 1 is customer, table 2 is dog, and table 3 is booking.I want to be able to choose the customer table and within that you can see the pet linked to that customer and within the dog table you can see any bookings with that dog.What I have done is linked the pet and booking table to the customer details via mobile no. being the primary key.

I want a form called appointments which contains fields *ID, date, mobile, dog name, grooming stlye and other. What i want to happen is this, once the mobile number is entered, i then when it to lookup the mobile number in the dog table and pull the pet name from that, then in the dog name box it will then provide a drop down of dogs related to that mobile number.

I understand a query needs to be made in the dog name field, so in that field i created a query to look up the mobile no. in the appointment table, and to then lookup the pet name in the pets table. This kind of works. The two problems are this, the drop down in the dog name field shows all of the dogs in the database not just the ones assigned to the mobile no. in the field before. If i choose one of the dogs not related to that mobile it changes the mobile number in the field before to the relevant one (but i don't want to be able to see the dogs which arent related to the mobile no. in the field before.The other issue is that on one of the customers there are two dogs, now on the drop down you can see both dogs, but it will only allow you to select the dog that was entered first into the database. If i select the other dog it simply chooses the data for the one first entered.

I have read about doing Requeries on the afterupdate of the field and attempted this by choosing the dog name field and in the afterupdate telling it to requery pet name (I've also tried mobile), this hasn't worked. I have a feeling I'm getting a bit confused by which fields i'm supposed to use etc.

View 1 Replies View Related

Required Field Dependant On Another

Sep 12, 2006

Quick question for you.
I know how to set a required field, but how do I edit two field so that they are dependant on each other? IE. How do I set my form to make FIELD1 required ONLY if FIELD2 is empty and vice versa?
Thanks very much and I hope I've explained myself correctly.

View 1 Replies View Related

Make One Field Dependant On Another

Jun 2, 2006

i have a fault logging system for a school. i am trying to change a background Colour of a field to red when another field contains certain values.

any ideas much appriciated :)

View 3 Replies View Related

Form Field Dependant On DDLB

Apr 26, 2005

I have a list box containing various items. I would like to have another field return a numeric value depending on what is selected in the list box. For example, if Closed is selected from the list box, the other field would return a 1. How can I do this. The new field also needs to be linked to a table so the values are saved in the table. Thanks for the help.

View 1 Replies View Related

Change Colour Dependant On Field Value

Jun 28, 2006

Hello,

First please accept my apologies if this has been done before. In the attached database I am going to have a form which is continous what I am looking to do is change the colour of the box which is in the background to the associated colour from the quote table for example

if they choose water then it will look at the TBLQuoteType and change the box colour to the colour which has been assigned to that value.

Hope this explains it ok I will continue to try and figure it out but I thought I could also use your expert knowledge.

14030

Thanks again

View 5 Replies View Related

Make A Field Visable Dependant On Combo Box

Sep 18, 2006

I am very new to Access and all that it entails but I have really learned a lot viewing everyones posts. I cannot seem to get my current problem solved.
I am trying to have a combo box appear depending on a preivous combo box selection.

1st combo box is "ApplianceCombo"
2nd combo box is "SpeedCombo" Set to Visible = No

1st Combo Box has
Dishwasher
Vacuum
Washing Machine etc

If they choose Washing Machine I would like my 2nd combo box to appear which they can then choose
1000
1100
1200
1300

Hope I have explained this well enough.
Thank you in advance for any help
Kim

View 2 Replies View Related

Tables :: Lookup Field Using Another Multi Value Lookup Field As Data Source

Nov 23, 2012

how to do a particular thing in Access 2010 (I don't even know if it is possible).

I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")

So I can store, for each different product, none, one, or more options to let the customers choose from.

I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)

The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:

select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]

I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?

View 5 Replies View Related

Rowsource Of Lookup Field Dependent On Field Value In Same Record In Subform

Jul 6, 2014

I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?

- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.

Something like:

Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function

View 5 Replies View Related

Lookup 2 Field To Auto Fill Another Field?

Dec 30, 2012

Is it possible to look up 2 field to auto fill another field?

Field 1 is "Exposure" this autofill with "Probability" is user input.

I need to lookup Exposure and Probability and autofill a score in to "Risk Rating". This to stop incorrect data being inputted.

I have a table with all the results combination in it.

Would it be possible to use a Dlookup to look at Exposure and Probability to give me the score

I tried a Combox with autofill. But the power to be would like it done without user input.

View 11 Replies View Related

Dependant Information

Apr 14, 2006

How Do You Do Dependant Fields?

Example being:

I have a list of Stores of which there are 4 Formats (Super, Extra, Metro, Express). Each of these formats have their own specific grades.

What i want to do is when entering a new store via a form, In the Format box i would choose one of the formats from a combo box then when i progress to the Grade Entry, i would only want to see the Grades for that particular Store Format.

Would i need to have different lookup tables for each of the format grades & how do i achieve the above?

Ive seen this done on Airline web sites, ie select outgoing airport then the destinations change to only those that can be reached by flights from the Outgoing airport.

View 3 Replies View Related

Date Dependant Data

Jun 18, 2007

What's the correct syntax to search for in the forums, for this question.

Have a customer database, where the customers records are split across two tables. The second table holds the customers address; when this changes I need to record the date so that in the future, any invoices etc always have the correct address on them for that particular date.

I have a cross tab query which works quite happily and when you create the record it adds a date/time stamp. However what I cannot seem to figure out is how to save any ammendments to the address etc as a new record, whilst not adding a new record to the non "dative information" such as Name, DOB etc. The PK for table2 is made up of the Customer Number And Date/Time Stamp. At the moment any changes I make to the existing record in table2 simply over writes the edited fields..

Can I do this through the QBE, or does this have to done using recordsets?

Anyone help please?

View 9 Replies View Related

Dependant List Boxes

Feb 26, 2005

Hi, im kind of new to access and im not sure whether this is possible or not, but i have a problem which needs to be solved by 4 list boxes on the same form.

The first list box will have 4 choices in it and depending on which one is chosen the second list box will be updated. This continues on to the 4th list box, which when a choice has been made a new form will open with the relevant information from the tables.

I hope this is enough information to enable someone to aid me with my problem, thankyou very much for your time.

Craig.

View 1 Replies View Related

Combo Box Dependant On Text Box

Feb 7, 2006

Hi

I would like to know how can i reference a combo box to the value of a text box on the form it is for a purchasing system.

if i select product one i only want the the order quantity for that item to show and the same with the price field as all the reorder levels and cost information is stored in a table. i have created queries only selecting the product code and reorder quantity and the product code and the cost price.

hope someone will be able to help

regards
melanie

View 14 Replies View Related

Combo Box Value Dependant On Value In Control

Apr 22, 2014

I have a control called Pnummer (its personell number).After a user enters this number i want my combobox called Kenteken (Licenseplate number) to fill with only the licence plate numbers of the employee from the table Parkeerbeheer (Parking management).Should be simple enough, its like cascading comboboxes but then with only one combobox.So i put this code in the afterupdate event of the Pnummer control :

Code:
On Error Resume Next
kzlKenteken.RowSource = "Select parkeerbeheer.kenteken " & _
"FROM parkeerbeheer " & _
"WHERE parkeerbeheer.pnummer = '" & fldPnummer.Value & "' " & _
"ORDER BY parkeerbeheer.kenteken;"
I have left the rowsource blanc as above code handles that.

The only thing that comes to mind why it doesn't work is that the table bound to this form is NOT the source where i pull the licenceplate numbers from.

View 5 Replies View Related

Hiding Controls Dependant On User

Sep 15, 2005

Is it possible to hide text boxes/controls on forms dependant on the user ? I have a had a (quick) look at access security and it appears that it just enables read/write access to objects not controls on the objects.

Many Thanks in anticipation


Dave Smith

View 1 Replies View Related

Pictures Dependant On Current Record

Jan 17, 2006

I would like to somehow put the link to the picture in a field in a table, and then have the picture change depending on the record I am viewing (which is altered by the combo box pertaining to another field) ok thanks!

View 14 Replies View Related

Displaying Records Dependant On Combo Box

Jun 8, 2006

Hi

Any ideas on how to autofill the rest of a form when i select from a combo box?

ie when i choose a name from the list i want the rest of the form to populate with the rest of the data relevant to the selection.

Cheers

View 2 Replies View Related

Add Record To Second Table Dependant On Query In First

Sep 2, 2007

I am sorry to have to ask. I have been researching for the last two weeks and still can't find the solution.

Could you help me. I have a web page in asp, which has a "submit" button. When this is pressed I want to access a MS Access database called "Passliabcapture"
within this single database - I want to examine each record in one table (results), and then dependant on the results of the examination (ie if the value of a status field - "D"), add a new record to a second table (transaction file), and then continue looping through this function until the end of the first table.

Sorry if it seems simple - but I think I am - simple that is...

The code I have tried is as follows. All I get is an error saying

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/jon.asp, line 10

INSERT INTO "Transaction File" (Policy_No, Insured Name, fin_totalmnthpremium, ActionDate, Daterun, Description)
<%

[If Request.ServerVariables("REQUEST_METHOD") = "SUBMIT" Then

Set objCon = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objconn.Open Application("passliabcapture_ConnectionString")
obj_rs.Open "Results"",Transaction File", objCon, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable

INSERT INTO "Transaction File" (Policy_No, Insured Name, fin_totalmnthpremium, ActionDate, Daterun, Description)
SELECT Policy_No, Insured Name, Transaction Amount, ActionDate, Daterun, Description, FROM "Results" WHERE Status = "D"
objconn.AddNew


Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")

rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set objCon = Nothing
%>

&nbsp; <html><head><title>Debit Run Page</title></head><body style="background-image: url('_themes/expeditn/exptextb.jpg')">

<input name="Submit1" type="submit" value="run"> </body></html>]

View 2 Replies View Related

Lookup Field

Jan 11, 2008

Hi,

my only other issue!!! i have a lookup field on a table and when i view it in datasheet, i can see the actual lookup value i need however i have tried the following options:
create a form from the table
added a combo box to an existing form and gone through the wizard

both options show the id number and NOT the value i need to show. what can i do to show my actual information?

many thanks,

NS

View 8 Replies View Related

Using The Lookup Field...

May 3, 2005

Let's say that I have two tables:

Table Numbers:

ID VALUE
-- ------
1 123
2 456
3 789

Table Letters

ID VALUE NUM_LOOKUP
-- ------ -------------
1 ABC 1
2 DEF 3

Now, I'd like to use the lookup feature so that NUM_LOOKUP is a combo that displays the data in VALUE based on the relationship between ID in the two tables.

So, when you open the Letters table, you see the above. If you click in the NUM_LOOKUP field, you get a box that displays "123" for the first record and "789" for the second record.

I'm very close -- Here's what I have:

I tried the following:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [ID],[VALUE] FROM [Numbers] AS [NUM_LOOKUP];
Bound Column: 1
Column Count: 2

Problem: This selects ALL values of ID from [Numbers].

I tried adding the following to my row source:
WHERE [Letters].[NUM_LOOKUP]=[Numbers].[ID]

This just gives me the "enter parameter" dialog box for [NUM_LOOKUP] and [ID].

Any help?

Thanks!

View 2 Replies View Related

Lookup Field

Jul 31, 2006

Alright, I have a field called metrics that looks up the text in another table caled Metric Name. I want to delete the table that is being used to supply the values, but I still want a listbox with all those names. Also I want to be able to add names to that listbox. I am not sure how to do this.

View 3 Replies View Related

Help With Lookup Field

Dec 11, 2006

Hi all,

It is along since i have used access for anything and I am having problmes getting it to do what i want it to do.

I have 2 tables one which hold the data needed and the other is being used to hold a list of products with prices.

In my main talbe in which the data will be entered into I have a drop down so the product can be selected, once it is selcted i want the next field filled in automaticaly with the price. The look up looks at my products table which hold the price too. I tried with setting the look up based on a query but it get errors about using the same table

Anyone help me???

Thankyou for looking

View 11 Replies View Related

Lookup Field

Sep 25, 2006

I would like to create a lookup for a field - I want to display 2 columns, but store only one in the table.

Id City
1 Sheboygan
2 Sheboygan Falls
3 Plymouth


Show both fields in the lookup, but only store the Id in the table.

I can't seem to get the second column to display.

View 5 Replies View Related

Lookup Field Value

Mar 13, 2007

I have two columns in the look up field. When I select the lookup value in the form I need value from one column to be displayed in one box and the value from the second column displayed in another box. Is there a simple way to do it?
Thanks

View 3 Replies View Related

Lookup For Value In A Field

Apr 30, 2013

I am working on a database where i have to show the user last modification date of the files they are getting the data from. I have already completed that process. Used GetDatetime to find the files last modification date and time. And now i am moving on to the next step and Here is the scenario.

User will click the IMPORT button and first, it will check the last modified date in the form of those files(which is an unbound textbox) and compare that date with the Date Column that exist in the tbl_Import. If the Date matches it will show them a message Saying "Data cannot be imported since it already exist. If it doesnt match it will run the macro i have created to run the data import process

View 1 Replies View Related







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