I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
I am looking to find out if I can build a "on the fly" pass-through query based on user predefined data.
Basically I am linking to a massive database. Access link to DB2 table. In order to make the system useable for the users they need to edit the query before it's ran. If not the query over the network takes around 5 minutes every time it's accessed and options such as filters and sorting are chosen. So if a user sorts one column and filters on another the query has now ran 3 times. Once to open, once to filter, and once to sort. That just took 15 minutes.
So If there is a way to build macro or form that asks them for specific information first and then modify's the query so that it only gets ran once that would be fantastic.
Example:Table has 7 fields/columns. Usually they will do 3 things. Pull back data from either a month or quarter. Then filter a column by it's content. Then sort by date of another date/timestamp column.
Hello,
My Access application consists of two MDB files: the application file and the database file. The application file has links to tables from the database file.
Now, I want to define relationships to optimize my database performance. Where should I define them? In the database file? In the application file? Both?
Thank you!
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
I am having to develop a form that we can use in case in our web based inventory systems crash. I am not good with forms at all.
I'd like the data entered to go into a table I've created with 4 fields.
So Field1 and Field2 will stay constant until we hit a button that does a whole new person we are issuing equipment to.
Field 3 and 4 though will need to generate a new record each time we scan data into both those fields.
For example the data table would look like this
Field 1 Field 2 Field 3 Field 4
Person1 IDNumber SerialA SerialB
Person1 IDNumber SerialC SerialD
Person1 IDNumber SerialE SerialF
PERSON2 TheirID SerialG SerialH
Person2 TheirID SeriaiI SerialJ
If it makes a difference this form will be done in Access 2010. Once I get the data into the table, I can do whatever I need to do with it.
I have a form called frmStartTimeEntry where a user inputs data using a barcode scanner. In this form there is a field called Part_No where after a value is inserted, I'd like the form to check if table_lines_per_part includes this part. If not found, then I'd like the form to open another form called frmLinesPerPart where the form would pull the same Part_No inserted in the previous form to fill in the Part_No field (which is hidden) and then the user would type in a qty for the LinesPerOrder. A user would then click a button btnOk to append this new record to table_lines_per_part and be returned to the frmStartTimeEntry to continue filling out the rest of the form.
This is the idea I have but I don't know how to code the part where the form checks after update if the part_no exists in the other table, nor how to capture the part_no to the other form and then append both the part_no and the lines per part to the other table.
I have 5 tables that I would like to input data in. It can only be done with a single form. The fields I want to input in have the same names in all 5 tables, for example:
Table 1:
Name
Age
DOB
Table 2:
Name
Age
DOB
Table 3:
Name
Age
DOB
Table 4:
Name
Age
DOB
Table 5:
Name
Age
DOB
Is it possible to input data into all of these fields in each table using one textbox for each field?
Preferably without having to use code but if it cannot be done without it then that would be fine.
I have an unbound control in data input form requiring to input a 6-digit number. I have put a validation rule restricting more than 6 digits. Most users prefer to enter, say 123 and the system can enter the 3 leading zero for them.
View 6 Replies View RelatedMaking a small database, Got 1 Table.
1. ContactDetailTable
Got 3 forms.
1. ContactIDForm
2. ContactInfoForm
3. NewContactFrom
In ContactIDForm it contain 1 textbox name 'TextBox' with Button Name 'Btn'
In ContactIDForm there is only 1 Text Box ContactIDTextBox and 1 Button. User Enters ID in TextBox and On Button Click Event it should check data from TextBox in Table name (ContactDetailTable) in field ContactID and if there is record matching, ContactInfoForm should Open else NewContactForm should open with ContactIDTextBox value in it.
I have two tables linked by ID (Table1,Table2)
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10
1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
I'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.
How can i make some form of a loop that when I'm attempting to update data, i pull the correct column?
strSQL = "SELECT * FROM tblImportTable"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rst
Do While .EOF = False
If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then
[code]....
Is it possible, to input information from my continuous form into a web control form. at my job we a required to tract our jobs by equipmentid and job control number(jcn). When a job is done we have to upload the id and jcn into a website to tell it is cleared. I am looking for a way for that info to automatically be filled in when i load the website based on the job i have selected in my form?
View 1 Replies View RelatedI'm creating a database for work, and one of my forms currently has 8 subforms on it. For each individual account there can be one, none, or many of any combination of these subforms that will have information on them. I need the form to be able to show all of them, as someone else will be inputting all the information when the database is complete, but to make it easier I have set it up with checkboxes so that when a checkbox is checked, a subform shows, if not checked it doesn't show. I have very limited skills with VBA, so that in itself was an accomplishment for me.
My questions is: is there anyway in access to do it so that if a subform doesn't show, all the ones beneath it that do show will get bumped up so that it is easier to read/input data?
I have attached my DB and the appropriate form is "ICinfoT."
I'm using I have a secondary control being populated by an entry from another control. I was able to find the code I needed to do this using a combo box here in this forum.
me.txtDependent=me.cboPrimary.column(#)
(specifics changed to protect my company's paranoid view of privacy)
the code itself was showing up in the secondary control instead of any value. I found though that moving the code from the On Change event for the primary control to the On Current event for the form itself everything is working beautifully.
However, I have a different task now that I need to be able to accomplish with the same two tables but in the opposite direction. I suspect that I'm going to need to create a second set of controls and maybe even a second relationship or table to do this. To use a set of specifics that I think will get this point across, if I had a master table which includes a field for "City" and a secondary table, CityState, which contains both "City" and "State", and two controls, a combo box cboCityState to look up the specific City and a text box, txtState to display the State, putting the following code in the City combo box's On Change event populates a text box with the State when the specific City is selected, or putting it in the form's On Current event will be sure that State shows up in all the copies of a form when the form is used in a search and the City is not changed :
me.txtState=me.cboCityState.column(2)
What I need to do now is to set up a control which allows me to type in the State and have the results show all records that include that State.
I'm using this in a Filter by Form environment. I know the objections to this method and the benefits to creating a dedicated search form, but this method appears to be working well in all other ways for us for now. I have several situations within this form where I have one control showing up at the initial load of the form and at the beginning of the filtering process, allowing selection of multiple values from a long list of values in a list box, for instance, (using the On Filter event) and other controls showing up as a result of the filtering process, to display the selected criteria only, for instance (using the On Apply Filter event), so I'm aware of how to make this work if that's required. I'm thinking I may need to create a second control, something like txtStateInput, to accept the State search criteria and another, something like txtCityResults, to show the cities that are part of the resultant records.
I have a TABLE with the following data:
- EMPID
- LASTNAME
I have a form with the folliwing data:
- text box for user to enter EMPID, txtEMPID
- LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field...
- if the user enters an EMPID that is in the TABLE...display "Y"
- if the user enters an EMPID that is NOT in the table...display "N"
- a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
Can you update a text box on a form based on input of a date control? Here is what I have. I have several date controls on my form, and I want to update a status textbox based on which of these date boxes is filled out, but I need the status to be filled into the table as well. Do I update the field on the table, and then requery the text box? Is there some other method for accomplishing this?
View 9 Replies View RelatedI have a database that has several tables, each table has account numbers in
them. I also have queries set for each table to bring in desired
information. Is it possible to set up a form so the user can input an
account number and depending on which table the account number is in, that
query will run?
I have form where I will show the details of one table(member details table). In same form I have subform where I can get the member family details (here I am using the relationship table), now my problem is when Member detail form open based on the member id, the below subform data should be fetched...
View 6 Replies View RelatedI have created a form with two subforms. The context of this is a litigation database. The main form is for testimony. Among other things, it includes a field for a question and an answer. Any given piece of testimony may relate to one or more documents, and any document may be referenced in one or more pieces of testimony. So have two subforms, one for documents produced by the Plaintiff, and one for documents produced by the Defendant. It worked out easier that way because I originally created indices of those documents in separate Excel tables which I then imported to Access.
So the relationship for both document tables relative to the testimony table is many to many, which is established using two junction tables.
I also have a combo box in each subform where I can autofill fields based on selection of the document number.
My problem is that while the combo-box works great, once I select a document in the subform, that selection is carried over into the next entry of the master form, but also the selection is not retained in any of the master forms. So I can browse the document listing from any entry in the master form, but I can't get the document selection in the subform to stick and create a lasting relationship to a given record in the master form.
Is my problem that it is just impossible to have a subform based on a many to many relationship, or is it in the parent child relationship of the form and subform, or is it in the properties of the subform?
Ok this might be basic stuff i'm a bit dumb
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
Please help the dumb
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance
I need to be able to query the service dates for the bill. So I can query 1509 for the .billYYMM and .readdate and it will give me the TO Date. How do I in the same query tell the query to subtract 1 from the BillYYMM that I just entered and give me the read date for that field in that query? So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE. So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?
View 5 Replies View RelatedI have a form that request the User to enter a Parcel ID number (99-9a-99-99aaa-aaa-aaaa). The sub form asks for the subdivision number, block number, and lot number. The subdivision number, block number and lot number are normally part of the Parcel ID number but sometimes they can be different. I want the sub form to fill in the Subdivision number, block number, and lot number as a default but allow the user to change the value if needed. currently the sub form will only show what is entered into the table itself if a change is made on the main form the sub form does not reflect the change.
View 3 Replies View RelatedI would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
I am building a very comprehensive Membership Management Database with extremely useful facilities initiated by some 20 or 25 Queries..
The database includes 5 Tables which store data relevant to members, another which stores details of Interest Groups and a 3rd which stores which members are affiliated to which Group or Groups.
Currently the database is accessible only by a very limited number of persons and data can be entered/edited by only one person. The database, using Access 2010, is maintained on a desktop computer and synchronized to a copy on a Cloud.
My requirement is to permit some 50 Group Leaders to input/edit data on a Password protected Group Members Form such that that is the only element of the database that they can access. The Forms would have only 2 fields from which the user would select entries from drop down lists.
My query is ; is such a procedure feasible in principle and would I be correct in assuming that the user would require to have available a copy of Access.
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.