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.
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
I've just returned to work after kids and started managing a large Access database related to health, back-tracking over many years.
Currently in filling a form we physically enter: Apples 2.2 red apple 2.4 red apple cut 2.45 Oranges 5.6 Cucumbers 8.5
Is it possible to get field 2 to automatically fill with a number code due to the text typed in field 1?
FWIW, I'm confident at more basic Access e.g making follow on default value = Dlast("field""table") type stuff but the more complex stuff I haven't touched since Uni over a decade ago and you will need to be gentle while I blow away the cobwebs
I have a Main form that has button that loads a new Pop Up form for entering new data that will display in the Main form. When all the data is entered I click on a button that Saves the data and exits the form and then runs Re-query on the main form returning to the record that was current before the requery.
I have the following code:
Private Sub cmdSaveTradeAndExit_Click() DoCmd.RunCommand acCmdSaveRecord 'Save the current record DoCmd.Close 'Close the current form Dim CrId As Integer CrId = Forms!frmTransactionMainActivePopUp.CurrentRecord Forms!frmTransactionMainActivePopUp.Requery DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId End Sub
But I am getting the following error:
Run-time error '2498':
An expression you entered is the wrong data type for one of the arguments
I have a user who would like the value from the last entry into the form's title field text box to show as the default value when he clicks on enter new issue button to bring up a new record. I do not know if this is possible but I thought I would check for him.
I'm creating user form items (text boxes/combo boxes) for a user to enter data in. In this case it would be for a part. What I would like to happen is, if there is more than 1 part, then after the first part is entered, another part entry item is added below the first one but only if the first one has a value - for a virtually unlimited amount of parts. BUT if there is only say, one part to be entered, then the next section of selection appears directly below the blank next part. Sooo, something like this.......
Invoice:
Part1: (THE PART HERE) Part2: (THE PART HERE) Part3: (THE PART HERE) ... ... ... ... Partn: (THE PART HERE) - blank
(aButtonThatProcessesThis)
OR.........
Invoice:
Part1: (THE PART HERE) Part2: (THE PART HERE) - blank
My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.
I am trying to create another table that I can capture daily Out Of Stock data for products.
What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.
I have a form that looks up office names and will automatically populate a field called office number based upon their selection in the combo box. I have a submit button on click event set up to run a query.
Now, here's where I am running into issues: In this query, I need to pull selected columns of information based upon a multivalue lookup field. This multivalue lookup field is joined with the table that the values populate from.
The form will only have one value stored in the txt box field, and I need to be able to search for all records containing that one value.
This is what I have for code:
SELECT FilePlan.FPName, FilePlan.Description, FilePlan.[File Code], FilePlan.GRS, FilePlan.Schedule FROM Offices INNER JOIN FilePlan ON Offices.[Office Number] = FilePlan.OfficeNumb.Value WHERE (((FilePlan.OfficeNumb.Value) Like "*" & [Forms]![RetCutOff]![txtOffNumb] & "*"));
Do I need to string multiple queries together to make this work, or is it just not possible?
Silly query, think I am missing something obvious but I shall ask anyway.
I want to update certain lists in my form do the user can select from this list to update a field, so i click on a button and open a table, update a record and then close it to return to the form.
the List does not update unless I close the form and re-open it again.
Is there a property that automatically updates the list on exiting the query / table you are updating?
It's easiest if I take you through step by step. I open a form with a list of students and I have to enter the condtion for a Tutor Group (say I type in 13EN). The form then loads up with all students in 13EN. Then I have a 'Add Student' button which loads up a form with an indivudual students information qith a blank record. Then I press a 'Done' button which closes that form and requeries the Student List form.
My problem is when I requery the form I have to re-enter the condition for 'Tutor Group' i.e. I have to type it in again. Is there any way I can refresh the data from the conditions I have already entered (i.e. for the Tutor group, 13EN) without having to type it in again?
When designing a table I've created a field and set its lookup properties to display a combobox with a row source that returns a DISTINCT set of values already entered into the field.
After a row insert or row update the combobox needs to be required to ensure its list is complete.
If I create a form to display my datasheet this is easy. But I'd prefer to enter data directly into the table datasheet directly. I need to enter simple data into about 20 different tables and I'd prefer not to create 20 forms unless it's really necessary.
The lookup wizard generated entries similar to those I'd previously created manually, except the wizard generated a couple of extra settings that appeared briefly that were not part of the regular set of lookup tab properties. These additional settings referred to 'update propagation'. Once they'd disappeared I couldn't see any way to get them back..
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
I've developed a multi user DB. When a user is keying in a record I want to be able to give them the ability to click on a button and the system automatically looks at two value from the previous record they keyed. There is a text box that contains their user ID so I can use this as a reference to look up their last record (I don't want them to take other users last records).
The fields are StartTime and EndTime. So I want the startTime and EndTime from their previous record to be dropped into the current record(without physically going backwards on screen).
I currently have a table where we are logging a task number against a date and time for each employees to track activity.What I am looking to do is in a query have access lookup the previous reference number for the employee before the date/time of the individual record but on the same day
to show something like Staff 1 08/08/2015 12:00 Ref:1234 Previous Ref: Null Staff 1 09/08/2015 14:00 Ref: 3456 Previous Ref: Null Staff 1 09/08/2015 15:00 Ref: 7891 Previous Ref: 3456
Fields are Employee DTActStart - this is when task started DTActEnd - this is when activity ended Task ID - Reference for task (unique field)
I've tried seaching forums (Used DMax and Lookup) but doesnt find the most recent record before the in view record/I'm thinking a query is the best way so that when Print a report of tasks run of tasks will then show
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.
I've got stuck in preparation of this sales query. The primary sales table contains a mix of Canadian and the US detail invoice sales records. All sales records are in their native currencies The secondary lookup table contains daily US/CAN foreign exchange rates (FX).
I need to multiply every $US sales record (marked with U) with the FX rate in order to convert it to the Canadian currency. Unfortunately, this FX lookup table is missing over 50 daily rates in the last two years and as a result I cannot match them date-by-date with the sales table.
As a workaround, it is acceptable to use the previous FX rate in the table. The previous rate can be one or more days before the transaction occurred.
Attached is a sample database with the query that I've already built. The query contains two sample US records that are missing the FX rate on Jan 6. The FX rate of $1.0639 that needs to be applied to it should come from Jan 3rd entry.
New Datesales matcode curtype trans newrate 1/3/20141225.61281173224U R187611.0639 1/3/2014344.70361173260U R181731.0639 1/6/20142520 0022691U R19841 1/6/20145400 0022692U R19841
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.
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.
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
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.")?
I'm new to this forum and I'm sorry if this is a repeat question...I looked in previous threads, but could not find my particular questions.
Here's the situation: I enter a bird name in my form (I've set up a combo box of 200 choices). Each bird species has an max and min possible wing measurement. So when I enter a newly captured bird in my form, the wing measurement must fall between the minimum and maximum for that particular species. I've done similar things with cascading combo boxes, but can't figure out how to the do a between-type statement in this situation.
I would like the text from the previous field Invoice_Contact copied into the field Invoice_Contact of each new record created. I think I can use CTRL ' - but would like it done automatically if possible.
Field location: Form = PatientUpdate - Subform = F_Invoice - Field = Invoice_Contact
The form and subform are linked through: Pat_ID
The subform is based on the table called Invoice with Invoice_ID as the primary key.I tried several variations of this DLookup from examples I found on the web but the field comes out with "error" in it and its flashing!
I have a company that has a field for MailingAddress and PhysicalAddresses for shipping. When I enter the mailing address information I'd like the shipping to automatically populate with the information; however, I want to be able to change the physical address, if it differs. Is there a way I can do this without using a combo or list box? I tried changing the control source, which works, but I can't go in and make a change on the physical address without it re-populating the mailing address.
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
