Lookup / Edit Table
Hi all,
I am trying to learn Access form building by creating a training database. I created a form that has all of my class information and the students who are attending the class as show in the attached jpg.
If a student has already attended a previous class I don't want to enter his information again. I want to do a lookup for the student and if the student does not exist then create a new student. Is there a way of doing this without coding?
Thanks Steve
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Form Lookup For Edit
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 Replies !
View Related
Edit A Table Using DAO, PLEASE OPEN
Hi, I have a form called Edit Column, which contains two text boxes: ID and Description. The user types something in these two boxes and presses a button, and this button should take the value of ID and edit description for that particular ID in the table "Map". Here is the code I have written for it: Dim d As Database Set d = CurrentDb Dim r As Recordset Set r = d.OpenRecordset("Map") r.MoveFirst If r! = Me.Text1 Then [I]('Text1 is the text box where the user enters an ID) Do While Not r.EOF r.Edit r![Description] = Me.Text3 r.Update r.MoveNext Loop MsgBox "Successful", vbInformation Else MsgBox "This column does not exist in the Store", vbCritical End If Thanx in Advance
View Replies !
View Related
Can't Edit Data In A Table In ADP
I've created an ADP project containingnumerous tables. One (and only one!) table in the set will not open in edit mode while in Access. Here are the particulars: 1) The table was created from scratch in SQL Enterprise Manager, 2) The table is editable in SQL Enterprise Manager, 3) The table permissions appear to be set correctly (and are the same as all the other tables which all allow editting in Access) 4) I open the Access project and double-click the table. The "add record" navigation button is disabled, and any attempt to edit a field elicits a "This recordset is not updatalbe" error. 5) However, I can open the table in Design mode in Access, make changes, and save them. Any ideas? This is very frustrating! Mark
View Replies !
View Related
Edit Field In Table
I am trying to edit a specific record in my table. I want to know how to go about referencing it. I'm dealing with a table of books each having its own Book # assigned to it. Example: The table has Book#(primary key), title, author, quantity. How would i go about increasing/decreasing the the quantity by a value(i.e. 1, 2, 3, etc.) in a form that i'm in and then saving it to that specific books quantity? I'm attempting to keep track of our inventory.
View Replies !
View Related
Can I Somehow Edit A Table Through A Form Which Uses Subqueries?
Short version of question: My research has shown that Access will not allow edits through a form that 1) has subqueries in its SELECT clause, or 2) uses aggregation such as First(). Is there any tricky way around this? Long version of question: I have a database form that shows an overview of orders for products (it's based on a query that pulls all current orders from the big table). I would like to handle both of these on each row: Show where we built it last time, and Allow the user to select where it will be built this time. The problem is that I cannot figure a way to change the form/query such that it doesn't aggregate to find out where we built it last time, or doesn't rely on subqueries that do just that. But I feel like there should be some way to do it--since each row displayed does rely on one and only one record in the complete table of orders. Which obviously is the record I want to update. Is there something I can do that will accomplish this? I really do not want to have to make the user open up another form to see last time or choose this time. Thanks in advance!!
View Replies !
View Related
Unable To Edit Sql Linked Table
We are using access 97 and I have a linked table to sql 2000. A couple of weeks ago, our sql server crashed and we had to completely rebuild it / restore all of the data. Ever since then, only one of our linked sql tables in access is acting up. Whenever we go to edit a line (sometimes, not always), we get an error which states the following: This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes. Options are Save Record (greyed out), Copy to Clipboard, and Drop Changes. About all I have tried is re-linking the table...any other ideas?
View Replies !
View Related
Edit Destination Table In Visual Basic
I want an append query to add records to one of several tables, depending upon the value of a variable entered into a form. This variable is also used to select the appropriate records. The query is invoked from the visual basic code behind the form. Can anyone tell me how to edit the destination table from within the visual basic? In hope... Nell
View Replies !
View Related
Edit Data In Table Directly Through Textbox?
Hi I have a form, which displays data from a query recordsource. One of the textboxes is a "Comments" textbox, which I would like the user to write in directly, which in turn is a direct link to the data in the table so it is updated immediately. At the moment, the textbox does not seem to be editable. Is there a standard way to do something like this ?
View Replies !
View Related
Using Multiple Combo Boxes To Retrieve (not Edit) Records From A Table
I'm creating a form that has combo boxes pertaining to each of the following fields from table "Documents": DocumentID (primary key), DocumentTitle, DocumentAuthor, and DocumentYear. No combo box is used with any priority over the others. The working form will allow a user to retrieve the full document record (data for all fields) by using any combo box they want, as well as any combination of combo boxes. This means that if a selection is made in one field's combo box, the drop-down lists in the other boxes need to update based on that preliminary selection. The filtered results for each field, based on any and all combo box selections, are always shown in a single datasheet on the form. The kicker is that when a user starts filtering records by making selections from the drop down list in a combo box, but then decides to TYPE in another field's combo box, I want the combo box they typed in to do two things: 1) filter the records for that field based on what they typed, if any records meet that criteria (e.g. they typed "B" so only records beginning with B are shown), and 2) if no records match the typed criteria, the search starts all over (at the top of the cascade), filtering all library records based only on what they typed in the most recent combo box. Does that make sense? Any tips welcomed. I don't know how to cascade combo boxes in this way.
View Replies !
View Related
Lookup Table
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 Replies !
View Related
Table Lookup Function
Hi, I have a table with the following 3 fields (these are the ones i'm having the issue with but there are others). ID, Country, City The ID is an auto number. The Country Field is a lookup with the following lookup properties Display Control: Combo Box Row Source Type: Value List Row Source: "England";"Spain";"France" etc :confused: Now the problem... I also want a lookup in the City field which changes to reflect what was selected in the Country field. E.G If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc. Is this possible in the lookup of a table??? Thanks. :)
View Replies !
View Related
Field & Lookup Table
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 Replies !
View Related
Lookup Table - Numbers?
Hi all, I Have a question regarding lookup tables. I have a table called tblActivities. In this, I have created a field called ActivityBudget. I would like this to be a lookup field (select entries from a list). In order to do this, I have created a table called tblBudgetLists with all the various budgets. For the AcitvityBudget field in tblActivities, I have set it as Text with the following things: Display Control: Combo Box Row Source Type: Table/Query Row Source: tblBudgetLists Limit To List: Yes The function is half working... There are 19 entries in the tblBudgetLists table. When I select a budget from the list in tblActivities.ActivityBudget, the numbers 1 to 19 are displayed (I am assuming because there are 19 entries), rather than the names of the budgets. Any ideas on how to change this? sugar05
View Replies !
View Related
If A AND B Then C Lookup Table Needed
I am looking to set up tables in such a way that will allow for the concept: If Variable 1=A and Variable 2=B then Output of 3=C For Example: If the House is "Blue" And the Man drinks "Beer" Then his pet is a "Dog" If the House is "Green" And the Man drinks "Vodka" Then his pet is a "Bird" NOTE: There is no equational relationship between the first two variables to get the 3rd output. It is simply a lookup table. In excel you would have the color house down the left, the drink across the top and in the cells would be the type of pet. But I am struggling with how this can be made to function within Access since I will want the user to obviously be able to pick the first two variables and have the "answer" spit out.
View Replies !
View Related
Lookup Table Confusion
Hi everybody I have been unsure about this topic and want to finally be sure. When I create a lookup table I don't include a PK; I just have the one field. Therefore I don't include an FK within the Main table; I include the same field name as the field name in the lookup. "sector Details" in lookup and "sector Details" in Main. Am I better to make up these lookup tables and use a PK and then have the FK within the Main Table? Is this easier or trickier to work with? And will it cause any problems if I did include keys? for example, providing combo boxes as search criteira for forms using the FK/PK as bound fields? I know it's a bad idea to have lookup fields in tables which I'll avoid. But this means if I'm updating in the back end I'll only see a table full of keys and not the field name! Hope someone can help because I've been getting different advice since I started on Access including Professional books using lookup fields. Thanks.
View Replies !
View Related
Lookup Table Value Must Be Populated
I have a table called tbl_workorders. One of the fields on this table is optional but because the lookup table is on the one side of a 1-to-many relationship using an auto-number key it is requiring that this field be populated to create a record on the workorder table. This is a problem because the field is optional. tbl_workorders WorkOrrderID(PK) Field1 Field2 Field3 Field4 JobSubtypeID (FK-many) <---- Lookup field tbl_Subtypes JobSubtypeID (PK-one) SubtypeDesc Should I set the SubtypeDesc field to a default value of "none"? I know this is storing unnecessary data, but is there another option?
View Replies !
View Related
Adding Iif Or Maybe A Lookup Table To Db
Hello folks first post here. I tried reading through the boards to find someone with a similiar question, but not luck yet. I have been working with a basics of Access for about a year. I did take take a bit of programming in college but I don't remember much of it, so I looking for a little help. I can usually Google for answers but I cannot seem to find the write key words this time. A while back I created a table that tracks employees' data and recently I started working with the insurance data I have gathering for sometime. It goes something like Field Name Data Type SS# Number (primary key) Insurance $ Number PPO YES/NO HMO YES/NO Self YES/NO Spouse YESNO Children YES/NO Family YES/NO Dental Self YES/NO Dental Spouse.......etc. What I'm trying to do is turn the yes/no's into total dollars for each person. I have done this in Excel but the fields were differnet and I had to use a many, many if.'s or a lookup table(data array) PPO doesn't = a dollar amount by itself, but PPO + Self = $19.03 or PPO + Self + Dental Self = $23.00. Or the person can just have Dental Spouse = 8.86. Pretty much the people can take a piece and leave another. "Insurance $" is another field name and is where I am trying to get the total to go. I was thinking of a lookup table but I don't know how to do that in outside of Excel. I am thankful for any help or even a nudge in the right direction. ~Profector
View Replies !
View Related
Lookup Values In Another Table
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 Replies !
View Related
Need Help With Lookup On Table/form
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 Replies !
View Related
Lookup Or Separate Table?
While trying to "normalize" a developing database, a question has come up. I have two sets of information that will probably never change. Hair color and Eye color. Should these be put into a separate table or can I include those two fields in the table containing information about the person and then use the lookup wizard function in the table design and "type in the values I want"? I am using '97. Thanks.
View Replies !
View Related
How To Lookup Values In A Table And Add Them To Another?
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 Replies !
View Related
Default Value Lookup From Same Table
I'm using Access 2003 to write reference books. I have a field labeled Title Page and one labeled Source Page. 9 times out of 10 the Title page is the same as the source page. So, how can I put in a default value that the source page is the same as the title page, but then I'm allowed to go in and change the source page in the rare instance when they don't match. I'm fairly new to Access, though not to database programs.
View Replies !
View Related
Lookup Field In A Table
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 Replies !
View Related
LookUp Tables For Dynamic Set Of Attributes: Set A Pointer Or Use A Generic LookUp?
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 Replies !
View Related
|
TRACKER
