Linking fields in a form!


Ok. I have used access before to hold data but have always accessed and used it via asp. I have never actually tried to use the access forms and queries! But for a current project i am having to use it.

So far its been fairly simple but im stuck on one point. I have one table that holds user info and has a foreign key from another table. On my form i want the user to be able to lookup the foreign keyes available. Only i want the lookup to show the description field in the other table not the unique id (primary key) but when they slect the relevant row its obviously need to record the primary key of the other table in the foreign key field in my user info table.

How can this be done Im not sure if i explained this very well. If anyones unsure what i mean please ask my to clarify.
Posted On: Monday 22nd of October 2012 05:57:22 AM Total Views:  498
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Display of different informaton from same linking table

First thanks in advance for any help that you can give..... I am not new to Relational Database Programming but am new to Access. I have a small application i am trying to develop and have all the databases planned and created and all the forms now done. The problem comes to reports - let me explain. Simple courier shipment program. DATABASES ------------ CONTACT table primary key on contact-id SHIPMENT-TYPE table primary key on ship-type SHIPMENT table primary key is auto-generated numeric key FORM ------- User is to key into the shipment table which contains a SHIPPER-ID that look ups into CONTACT but only stores the contact-id, BILL-TO that look ups into CONTACT but only stores the contact-id, and RECEIVER again that looks up into CONTACT but only stores the contact-id. The remainder information for # of peices, weights, time, date are all okay. REPORT ---------- In trying to design the waybill or shipment label i am finding that i can only pull the contact information of address, city, zip etc.... for one source etc...SHIPPER-ID and anytime i pull the address, city, zip for the for the report for RECEIVER or BILL-TO is still just displays/prints the SHIPPER-ID information.
VIEWS ON THIS POST

220

Posted on:

Monday 22nd October 2012
View Replies!

How to merg fields in MS Access 2003

I've got a question about SQL query in MS Access, for example I have the following data: Table1: NAME _____ CITY _____ FAV vahid ______ NY _______ A vahid ______ NY _______ B hamed _____ LA _______ A hamed _____ LA _______ C Do I able to merge them like this in MS Access 2003: NAME _____ CITY ____ FAV vahid ______ NY _____ A, B hamed _____ LA _____ A, C can anyone give any solution to it
VIEWS ON THIS POST

163

Posted on:

Monday 22nd October 2012
View Replies!

Elegantly hiding table key fields from users in form controls?

I've got a form that's causing me some DBA-style headaches: I have a three-table query that my form is bound to. The reason for the joins is that the table being updated by the form simply holds foreign keys that reference the tables I'm joining to - those tables contain the detail data that an end user would understand. For example, I have an inventory table where each row simply contains an "CustomerID" field - an integer, and a foreign key into the Customer table. The Customer table contains the name of the customer, along with other details about that customer. This form is to allow the user to reassign ownership of inventory items (among other similar things). I need the user to be able to simply select customer names from a drop-down, but behind the scenes I'd like them to be making the CustomerID key available to the form [the one that corresponds to the newly selected Customer Name], so I don't need to go back to the database with a separate query. Of course, I don't want CustomerID to be displayed anywhere on the form (I don't mind having an invisible control though). Is this difficult to accomplish
VIEWS ON THIS POST

204

Posted on:

Monday 22nd October 2012
View Replies!

Required fields in subforms?

I have what I think would be an easy problem, but I'm new to Access (and databases in general, I have to admit), and I'm getting nowhere on my own. I have a database with a main form that corresponds to a table of contacts. This main form has a number of subforms containing secondary information for each contact: correspondence records, secondary addresses, education information, and the like. I'd like several fields in some subforms--or the subforms themselves, if possible--to be required before a new entry can be saved. In other words, I don't want users saving records without certain key information--for instance, a contact's year of college graduation (a field within an "education" subform). I've tried setting fields on the table level to Required (& setting ow Zero Length to No), but Access will only display a dialog box and require data entry if a user clicks on a subform that includes such required fields. Otherwise--if they fill out only the fields on the main form (first name, last name, company, etc)--they will not be prompted to fill out required subform fields, and the record will be saved with a number of blank fields. Is there any way to make these subforms, or certain fields within them, required
VIEWS ON THIS POST

200

Posted on:

Monday 22nd October 2012
View Replies!

Calculatino fields automatically in a form

I need to calculate a field using other fields in the query/form. Code: TARGET DAYS TOTAL 300 1 300 400 2 800 300 3 900 400 1 400 if the day=2 then target*2 and if day=3 then target*3 else target. For Day 1 the target will be entered. I need to calculate the target for the rest of the days. I'm not sure where to calculate this, in the form or at the query level.
VIEWS ON THIS POST

185

Posted on:

Monday 22nd October 2012
View Replies!

Pop up fields

Without using VBA or html or anything like that, would someone be able to tell me how to do the following please Depending on the value of a field on a form, I'd like another field to pop up. I'm creating a d-base for a friend who's a personal trainer. I have a field for the client's BMI. If it's
VIEWS ON THIS POST

161

Posted on:

Monday 22nd October 2012
View Replies!

Generating a field with parts of other fields

If I have three fields Surname - text Given name - text Dateofbirth - dd/mm/yyyy how can I generate a field consisting of surname(then)first letter of given name (then) year of birth (ie SMITHK66)
VIEWS ON THIS POST

158

Posted on:

Monday 22nd October 2012
View Replies!

Size-changing fields in a report

I've asked this question in different forums before, and though some people say it can be done, I have not found anyone who is successful yet. I have a report that puts down a person's address, however the address information is stored within the database in separate fields ('Address', 'City', 'State', 'ZIP'). The issue I have is with the City/State/ZIP. Some city names are longer than others, is it possible to have the fields adapt to the different word lengths. Say the address is, Albany, NY 10023. The database would output this alright, but then if the city name is San Francisco, CA 94143, the fields are in disarray. I have the same issue with names. Last name and first name are stored separately. I would like to display Last, First. My workaround has been to right-align the Last name, and put it adjacent to the left-aligned first name field. This works unless the person's last name is very long, disporportionately from the first name, in which case, it looks very much off center in the overall report.
VIEWS ON THIS POST

174

Posted on:

Monday 22nd October 2012
View Replies!

Calculate Yes/No fields in a query

In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.
VIEWS ON THIS POST

534

Posted on:

Monday 22nd October 2012
View Replies!

Marking fields non applicable

I am a fairly new user of access and am using it to store information on staff training. We have a core of mandatory training and then additional training that is mandatory to job role. Is there any way of marking it not applicable to that person so they don't come when I run a query on required training I am storing the data in date formatt so that I can run date specific queries for training renewal. I would be very grateful for any help with this.
VIEWS ON THIS POST

93

Posted on:

Monday 22nd October 2012
View Replies!

2 fields point at same table

I have 2 fields in my table - cityID and offcityID - I want to create a query that ties the cityID to the city table cityID and offcityID ties the offcityID to the city table cityID I am not getting the correct amount of records when I set this up. Code: SELECT [main changed].lastname, [main changed].firstname, [main changed].address, City.City, [main changed].business, [main changed].offaddress, City.City FROM [main changed] INNER JOIN City ON ([main changed].cityID = City.CityID) AND ([main changed].offcityID = City.CityID); I should have 1898 records - when I have the AND in I get 227 records and when I have the OR in I get 2176 records. Neither is correct
VIEWS ON THIS POST

229

Posted on:

Monday 22nd October 2012
View Replies!

Message box with multiple warning fields

Is there a way of making a message box appear that lists all missing fields in a form. I don't want to set them as required fields (becasue sometimes the information really isn't available). So if there (eg), surname, date of birth and doctors name are missing - then on the OnClose event (is this where it should be), a message box would come up saying "The following fields have missing data - Surname, Date of Birth, Doctors Name"
VIEWS ON THIS POST

108

Posted on:

Monday 22nd October 2012
View Replies!

Report - Two fields into One Group Header?

Here is the problem I have. I am creating a Segregation of Duties report that show users with two different roles that they are assigned to in a system, that can potentially create a conflict. Table: Username | Role1 | Role 2 Role1 and Role2 are variables. They can be any number of combinations of different roles. I am trying to create a report that shows....... Role1 (One combination instance of Role 1 and Role 2) Role2 Username#1 Username#2 Username#3...etc The only way I can think of is that the GROUP HEADER needs to be TWO fields, not just one. When I group just one field (e.g., Role1), it doesn't display ALL instanances where Role2 may be different. It groups just Role1 and displays table rows, even though Role2 may be different. I hope that makes sense. Please let me know if anyone can help!!!! Thank you!
VIEWS ON THIS POST

133

Posted on:

Monday 22nd October 2012
View Replies!

Transferring form data to different fields

I have created a database to track the number of laptop computers and radio modems that are installed in Law Enforcement applications. I use a form to enter all pertinent data (i.e., Laptop S/N, Modem S/N, etc.) into the table, and have attributed these fields to prevent duplicate entries. Each laptop and modem is associated with a vehicle number. At times, the radio modems are replaced or swapped out between vehicles without replacing the laptops. When this happens, I would like to be able to enter the new serial number for a given vehicle that already has a laptop associated with it, without having to re-enter the serial number for the modem that is being replaced. If this explanation makes sense, and someone can possibly give me some advice on how to make it happen, I would greatly appreciate it. I am very new to this program, but know that it is powerful enough to do just about anything; I just need to know how to make it happen! Thank you very much! Ol' Navy
VIEWS ON THIS POST

185

Posted on:

Monday 22nd October 2012
View Replies!

Importing consecutive rows as subfields to a previous row?

I am trying to figure out a way to import an excel into an access database but the for row A there could be 200+ rows that follow that contain the name and description of an action that is related to row A. I was wondering how i could import this information using the logic that if column A of row B is blank then row B should be imported into record A. This rule will have to work continuously for 100+ rows and not override any information (just create a list). Then record B would be created using the next row that contained information in column A. I hope I am making sense
VIEWS ON THIS POST

95

Posted on:

Monday 22nd October 2012
View Replies!

Max no of fields in form & report

sorry if this is a silly question but I'm an access beginner.. I have tried to create a form with about 125 fields using the wizard (fields selected from quieries) once I have selected all fields access goes to the Form view but no fields are displayed. if I go into design view I can see all selected fields. Why can't I see anything in form view Is there a limit to the number of fields that can be used in a form Also, is there a limit to the number of fields in a report if there is a limit.. how can I get around this as I need to show all fields
VIEWS ON THIS POST

189

Posted on:

Monday 22nd October 2012
View Replies!

Newbie Question - Repeating fields in tables

If you are going to enter data via a form, just make a combo box tied to Title, with the four choices in its source list. Ditto for the other fields you mention. I'd just let Access create an autonumber and use that as the primary key. Then put an index on the Company that allows for duplicates. Hope that helps, David
VIEWS ON THIS POST

99

Posted on:

Monday 22nd October 2012
View Replies!

Max amount fields in a table

What is the maximum amount of fields/columns a table can hold in access 2003 And what do you do if you have a form and its record source is a table that has run out of fields/columns If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fields. I'm currently waiting for my access 2003 upgrade disk to arrive and I'm anticipating issues that might occur.
VIEWS ON THIS POST

159

Posted on:

Monday 22nd October 2012
View Replies!

How to build a funciton to return primary key,alternate key and foreign key fields

Hi every body. I need to create a function to return a string containing : A) primary key fields together with primary key names . B)alternate key fields together with correspondingly alternate key names. c)foreign key fields together with correspondingly foreign key names. I be happy if some one show me how to build such funciton since i never refrenced reletionship keys via vba
VIEWS ON THIS POST

187

Posted on:

Monday 22nd October 2012
View Replies!

How to calculate cumulative values of this query fields?

Hi everybody. I got a access 2000 query that lists : 1)weekno 2)year 3)project (project number ) 4)QweekylyReportHeader (project description ) 5)customer (customer that requested this project) 6)department (department number and name that implements this project) 7)Projectleader ( project leader name and number that is responsible for this project) 8)Task (Task number that is done for this project ) 9)task description (description of task ) 10)employee ( employee number who is working in this project ) 11)name (Employee name and initial and last that works for this project ) 12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this 13)salary (amount of salary given to this employee) ===>i want cumulative for this I want to create another query that lists : A)cumulative value of hours worked on particular project task up that point. b)cumulative value for wages given for that project task up that point. http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample) The above query ONLY lists hours worked and wages gives for particular project task only during each week.But i want hours worked and wages give for particle project task up to that point in week. For example a project task might have implemented last week but not this so i want to take that in calculation as well. I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular project task. Notes: - There is a possibility that during a particular week no task been implement for particular project. - One employee can work in more then one project - One employee can have more then one salary (amount) for the same project because he might get raise in salary! - Only tasks carried this week will be printed in the weekly report http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database) http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population) http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view) query that display hourly wages of certain project during each week Code: SELECT querythisweek.weekno, querythisweek.Year, querythisweek.Project, QweeklyReportHeader.Customer, QweeklyReportHeader.Department, QweeklyReportHeader.description, QweeklyReportHeader.ProjectLeader, querythisweek.Task, dbo_Task.description, querythisweek.Employee, [lastname] & ' ' & [initials] & ' ' & [insertion] AS Name, querythisweek.hours, querythisweek.Salary FROM dbo_Task INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task; code for querythis week( calcualte the salary and hours worked) Code: SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours from dbo_Hourly_wages a where dbo_Hours_worked.Employee = a.Employee and dbo_Hours_worked.Project = a.Project and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno) from dbo_Hourly_wages b where b.Year < dbo_Hours_worked.Year or (b.Year = dbo_Hours_worked.Year and b.weekno
VIEWS ON THIS POST

389

Posted on:

Monday 22nd October 2012
View Replies!