For the life of me, I cannot think of a way to make this work. :(
I have this continuous subform, and I need to put additional fields onto it.
The easiest way to do this would be to create single record sub-subforms, but
that won't work because Access won't let me put another subform on a
continuous subform.
My underlying tables are as follows: tblA for the subform has a TableA-ID
PK, and a RecordID FK [that's its link to the main table], plus several other
fields to hold information. Table B has a TableB-ID as a PK, TableA-ID as a
FK, TableC-ID as a FK, a Yes/No field and a text field. Table C has TableC-ID
as a PK, and a text field.
When a new record is created in table A, a number of new records are also
created in table B depending on the number of records in table C [with an
append operation]. There are 6 entries in table C, so table B will contain 6
records for every record in table A.
Subform A needs to show these records, but as I said above, I just cannot
think of a way to make it work.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
Basically I need to design form that looks very similar to either Split Form or Subform. I have attached a print screen of what I roughly need. The form is split into two parts. In the first/top part there are some List Box/Date fields that act as filters to the bottom form, so the bottom form displays only records that match values in red. The second part of the top form has some field that require input from user and then these records are added to the bottom form. I was trying to use both Split Form and Subform and none of it is working,I have attached an example of database and this form would be based on data from Query1.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
Just wondering if any one can help. Ive create a table of parts, and each part has a home line. Now the problem is each home line has different characheristics. So im wondering is there a piece of code that will prompt the user to enter a part number (through a query I know!) and then depending on the Line number open up a form that will have the predefined charcateristics.
Hi, i am hoping someone can help with designing a form for this structure or even have suggestions on changing my design. A nutritionist has 3 types of people (whom she sees seperately from one another) on a regular basis, they are clients, doctors and business contacts. She wants all 3 types of people in the database and want to track appoitnments with them. She has many doctors, clients and so forth. I created tblPerson so a person can be all 3 roles if they wanted to. I was informed that she has some doctors that are clients. But i am stuck wwith creating a form for this design. I want to be able to have a combobox so i can look up and fill the information based on my selection, but for the main table(tblPerson) i only have the name and gender, nothing else that is unique to identify a person, so i am out of luck if 2 people have the same name. As you can see the main table has an autonumber primary key the has a 1 to 1 relationship with the 3 role tables. If anyone has suggestions, let me know. Thank you very much. This is what my design looks like.
tblPerson fldPersID, autonumber, PK fldFirstN, text fldMidN, text fldLastN, text fldGender
tblDoctors fldPersID, Long, FK ClinicID, FK Specialty Email etc
tblClients fldPersID, Long, FK DOB, MaritalStatus, Occupation, HomeAddress, etc
tblContacts fldPersID, Long, FK BusinessAddress, Website, Description etc
tblMeetings fldMtgID, Autonumber, PK fldMtgTime, Date/Time fldMtgEnd, Date/Time fldMtgLoc, text etc.
tblAttendees fldMtgID, Long, FK fldPersID, Long, FK fldReason
Please help me in designing my form. I have an application that I need to design the form around. i have set up my tables and relationships. I want to have a main form with subforms. I need a push in the right direction. I can't attach my app nor my db for you to view, since they are too large though.
I have a main form and a large sub form that covers alot of the main form fields. My question is when I'm in design mode, the sub form blocks the fields I need to edit. I've tried setting the sub form "sent to rear" and the fields "sent to front", but this does not seem to solve the problem. So I end up dragging the sub form off to no-mans land and then place it back. Is there way to say hide the sub form in design view and then unhide it.
If I have a form and seversubforms in tabcontrols where the relationship between the underlying tables to which the forms and subforms are bound is one to many? In other words, once I enter the data for the record on the main form and go to the subform in the first tab can I enter data for , say, 3 seperate records all linked to the same id number in the parent record?
I have attached a copy of an excel sheet that my company has been using since the ice age. I would like to set up an access db, so as to eliminate the multiple files and make it easier to create new orders, rather that saving as a new file name all the time. However, does anyone have an idea of how I could use this same format for order entry? I need to ease our sales staff into this new idea and don't really want to disturb the form they have been using forever. Thanks for any and all suggestions, Chad
I am setting up a form for employees to enter data for 'hours' and 'rates' alongside records.
I have nominated the fields as Project, Hours and Rates
In the project field there are subgroups (records) a,b,c,d,e, etc. each with hours and rates applied.
I would like the employees to open up a form and see each unbound record and enter the data for hours and rates alongside in the bound boxes.
i.e.
Field = Project
a) Project planning |Hours| | Rate|
b) Scope |Hours| | Rate|
How do I get the records onto a form as "unbound"?
So far I can only get the fields as unbound.
I plan to have many disciplines i.e. Civil, Mechanical, Electrical, etc. each in a different table with different field headings and different subgroups so will have to make a form for each discipline.
Hi all Its my first time on this forum so please be gentle with me ! I have a list of numbers stored in a field (wieght). I have another field that is the product field. I also have a list of letters (A-D) Now, the weight can be 1kg (through to 20kg) A-D hence four different values in my product field. I need to have a form that the user can choose a letter then enter the weight to show the correct product. I can get a dllookup function to work fine for the fields involved but i need a control to filter the different letter combinations Anybody point me in the right direction
Not sure if this has been mentioned yet (I'm new, so please forgive me if it has...
I am working on a form that tracks more than one TECHNICIAN who has worked on a specific task, but I am unsure as to how I can display multiple text boxes w/out displaying what is in the 1st text box (as with other fields [START DATE, START TIME, END TIME, DURATION]). I am pulling them from a query that captured this data. How do I--->
a. not display duplicates in the other text boxes not in the first row and...
b. how can I adjust my query to show the time duration in decimal format?
If this can be explained in a step-by-step process, it would be greatly appreciated. (Also, if I can attach a screenshot to help explain my situation [how would I go about doing that?], it may speed up the process.
I've been asked to design a database to hold information from a proforma based on tracheostomy insertions. The information breaks down into two components; patients and tracheostomy details.
From this I have designed two tables: 1) tblPatientDetails - PatientID (autonumber) - [primary key] - 12 fields (text / numbers) - relating to demographics
Each patient can have more than one tracheostomy inserted over the course of their care, so there exists a one:many relationship between the tables. The two tables are therefore linked (one:many) from tblPatientDetails:tblTracheostomyDetails based on the PatientID fields in both tables.
OK here's my question; I want to make the inputting of these proformas as easy as possible for the person doing the data entry. Ideally I would like: a) a single form which collects the data and enters it into the two tables. b) a method of searching the patient details upon name entry and if they already exist in tblPatientDetails, to autopopulate the rest of their demographic information in the fields, but to leave the tracheostomy details blank (as it would be a new entry for that table).
My attempts at using the following solutions failed miserably: a) a form based on a query of both tables - this allowed me to put the fields from both tables on the same form and enter information, but when I searched for a patient's details to enter a new proforma - it populated both the patient & tracheostomy details as well (i.e. didn't really let me add a new 'many' to the 'one' record).
b) two separate forms (patient & tracheostomy), with a command button to go from the patient form to the tracheostomy form. This didn't work because I couldn't pass on the PatientID variable between the forms to form a link between the tables - and there ended up being two separate tables of information. :S
c) from looking around at the various forums, I understand that a sub-form is useful in these cases but I can't seem to get the formatting right (I would like the form to appear as similar to the paper proforma as possible), but my recent attempts at a sub-form resulted in a datasheet view of the tblTracheostomyDetails, which isn't very useful as it contains so many fields.
I'm now left at a loss what to do, so any help would be gratefully received.
I have a table and a form that I designed. Now I need to copy all the fields and the design of the form I just created and give it another name so my partner can use the same design and fields, but put in his own information. Is it possible to copy just the design and the fields. If so, how do I do that? Thanks.
I created a subform on one of my forms and in design view it looks all nice with a nice background and stuff. But in the form view it looks just like a regular datagrid. I'd like it to look like it does in design view. Any idea how?
I am new to Access and not a sophisticated user of computers. I've been given the task of redesigning some forms on an existing data base. I understand how to put the new fields I'm adding into the table associated with the forms I'm working on, but I've having a hard time actually moving stuff around the screen and getting it lined up.
Is there an online tutorial or reference I can go to that will teach me the layout basics?
I have a list of about 200 crops on a column, and for each crop I have 6 information (income, amount of land, season, etc.) in the following 6 columns. I have this information for about 100 households in 6 villages.
I am now starting to put these info in the computer, and it is an enourmous task. I thought that one way to do it is design a form in Access, and have one file per village. I then want to analyse the data at the household level for each village, and also analyse the data at the village level (after calculating village averages).
I want to recreate the form I used to interview farmers in Access, but I can't figure out how to make Access automatelly have 7 columns in form view (the first with the name of the crop, the next 6 with the information: income, etc.). The idea is to reproduce as closely as possible the form I used to interview the farmers, so as to facilitate the data-entry.
Does anybody know how to let Access automatically sort all the fields into 7 columns? Of course I can design the form manually (putting each field individually in its right place), but then I have 7 x200 fields to move, and it would take a huge amount of time. I can't believe that Access can't do this automatically, but I can't figure out how.
I then plan to transfer the info for analysis to SPSS. Does anybody foresee a problem?
Do I make sense? If anybody can help, I would be very grateful. This is actually very urgent, because I am going to pay somebody to do it, but I have to tell her how to do it over the week-end. Thank you very much!!
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
