I have query that has 4 fields, borrower, estimated closing, review file, loan status.
(fields) Borrower Estimated Closing Review file Loan Status
(data type) text date date text
(sample) jim smith, 05/10/1971, blank, approved
What I was wondering is if ther is a way to auto-populate the review file date field with a date subtracting 60 days from the estimated closing date.
hope I explained clearly, let me know if you need any other information.
Oh yea, this query will be put in a report to be generated weekly, if there is a way to do it in reports.
I (will have) a form which a user fills in to enter new data.
I have two fields,
"Branch" and "Branch ID" (Branch ID will not be on the form, just in the table)
Branch will be fed by a combo box with seven choices. I would like it so that when "Branch" is populated it autopopulates Branch ID with a code which relates to the branch, so for example
I have a form in which I want to capture today's date when another field on that form is changed from active to Closed. In excel I would write an IF statement but can't get my head round what I would do in Access to achieve the same result. basically it's if (field name 'status')=(criteria 'closed') then populate field (date closed with 'today's date').
So I have a continuous form and I have a button that is pressed when a job is released and it record a date in a field. I wanted to turn that button to disabled after pressed but after lots of reading found that it couldn't be done in a continuous form due to it disabling all buttons . So I decided to just make the code populate the field only if it was a null value so that it wouldn't overwrite a value if someone pressed it more than once. Well the null is my downfall in all this. I cannot make it compare and populate the date when the button is pressed. I have tried all I can find on the net and all I can think of.
In case my other question cant be resolved, I'd like to do a work around, but dont know how to do that either.
So, I have a form (Photos) with a field (PhotoTitle) and in the form is a subform containing a continuous form a (Sales).
This form contains invoiceno, location, customer etc... but also PhotoTitle field.
I've been trying to get it to populate based on a relationship and use that data elsewhere, but cant, so I'd then like it to.. on enter (or focus) automatically populate with the value from the main form (photos), phototitle field and put it into the sales subform phototitle field.
The pop-up will show the content of the parent form field or a literal if it is blank.
But it doesn't work. The syntax is invalid but my meaning is clear to everyone but the compile.
Private Sub Form_Open(Cancel As Integer)
If [Forms]![MeetingStatus]![List2].[Column](3) = "" Then Set [Forms]![Comment]![Text2] = "No comments" Else Set [Forms]![Comment]![Text2] = [Forms]![MeetingStatus]![List2].[Column](3) End If
I have 2 forms. Information from one field links both forms (though it is not the primary key). Using a "open form" button I want the field on the second form to be populated with the contents from the same field on the first form, when a user selects the open form button. I imagine all of this is very doable in SQL, however I am using Access GUI to construct my database and have no time to learn SQL at this stage. Any ideas would be greatly appreciated.
A fellow employee of mine who is self taught in Microsoft Access currently has a database with a form that contains fields labeled name and address. She is trying to set it up so that when she begin to type, it will automatically look up to see if that name and address was previously entered to cut down on having to repeat it every time. She has tried the look up function with no success. She is using a single table as a base. Any assistance anyone can give us will be fantastic!! If I have posted this in the wrong forum or if this question was already answered, I could not locate it and I'm sorry if I am not following proper forum protocol. Thanks!!
I've been working on this issue for quite sometime. I've tried different methods, such as the DLookup function, but I think I'm doing something wrong. Let me explain what I'm trying to do:
This is a Work Order, the user will Select a Customer (Table Customer: Contains CustomerID and Customer Name, address, etc) from a drop down box. When they do this, I have another textbox which is designated for the Address. I would like the address to automatically populate. but its not quite working. I've tried it with the DLookup function (I entered it into the control source of the textbox). I entered the following syntax:
It keeps coming up with an error. I have no idea why.
Now this is just the first half. The second half I have a subform on the same main form, which lists the equipment they have on their site. The database has a number of customers with many equipment for each of them. How do I make sure that the subform only populates equipment associated with the Customer that was selected on the main form?
I have an address box that I would like to auto populate with a double click event. Basically, if the location address is the same as the main company address, and there isn't any data already in the location field, then I would like the user to be able to just double click the location address and the field gets automatically populated with the main company address.
Private Sub Address_DblClick(Cancel As Integer) If IsNull(Me.Address) Then Me!Address = Me.CompanyName.Column(1) End If
So this works fine if the main company address actually has an address in it. But if it doesn't then double clicking the location address produces a run time error 3315 for trying to populate a null value.
i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:
tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.
My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:
tblTasks
TaskID - PK Task Name Team ID - FK
tblTeam TeamID - PK Team Name
there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.
I know auto-populating has been asked before for forms, but I am looking to auto-populate a couple of fields in a table based on an ID Number that corresponds to another table. I realize it is not the best practice to duplicate data in a DB, however, I am using an ID card program that requires tables to populate the ID card information. My hope is to have two separate tables for 2 versions of ID cards.
The first table is called Firefighter info with fields of ID Number, First Name, Last Name, and Firefight Certs, EMS Certs, and emergency contact information. The second table is called EMS Personnel with fields of ID Number, First Name, Last Name, EMS Certs, and other fields that will be entered manually. I would like the First Name, Last Name, and EMS Certs fields to auto-populate in the EMS Personnel table when the ID Number is entered. I have a relationship already listed between the two table that links the ID Numbers in both.
The Firefighter Info contains the information for all members of the department, regardless of whether they are EMS, or strictly firefighters. The EMS personnel table contains information strictly regarding to the EMS members. Thus, the ID number you are entering into the EMS table will always correspond to an existing entry in the Firefighter Info table.
I need help to populate a certain selection from a drop-down (combo box).........OK I have a field named REP ID/REP NAME....(Rep stands for contractor)....Each Rep is assigned an ID and the first two numbers in the rep ID correspond to the territory in which they sell products...So for example 5830 John,Smith the 58 would be Northern California....Now what I want to create is a field named TEAM which would consist of Team A through Team D....Every Team is associated with different territory numbers to lets say I click 5830 John, Smith I would want Team A to automatically be populated in the TEAM field....Someone please helpp....And I hope I described it correctly
I have a combo where the list is populated with a bit of VB code to create a two field list. When the user selects the item they want from the list it displays the item in the combo box and should populate a text box in the same row with the value from the second column.
This combo is part of an orders subform and the user is selecting a product from the list which displays the product_name and the product_id.
The problem is that the product_id is not filled in and access returns the following error: Run-time error ‘3101’: The Microsoft Jet database cannot find a record in the table ‘product_table’ with key matching field(s) product_id.
This is the VB code that populates the list : [code] Private Sub comboCatagory_ID_AfterUpdate() Dim sProd_description As String sProd_description = "SELECT products_table.product_id, products_table.prod_description " & _ "FROM products_table " & _ "WHERE products_table.prod_catagoryID = " & "'" & Me.comboCatagory_ID.Column(0) Me.comboProd_description.RowSource = sProd_description Me.comboProd_description.Requery End Sub [end code]
So I'm working on an Access 2007 application for my company's suppliers list, but I am restricted to using a single Excel worksheet for the back-end. One of the fields, called [Files], is a list of files in .pdf format related to a supplier. The number of files can be 0, 1 or many. I currently have this field set up so that each file is separated by a semi-colon. A sample first record in [Files] is:
K:Users est user2008-12-24-ASTM D5857-08.pdf; K:Users est user2012-03-12APCPP25-Black.pdf; K:Users est userDIN_16901.pdf
The front-end contains a multi-item form. I'm trying to add a ComboBox (cboFiles) for each record, which is populated by the value in [Files]. Then, selecting an item in the ComboBox should automatically open that file.
I've tried a few approaches so far for populating the ComboBox: Setting [Files] as the Control Source results in a single item appearing as "K:Users est user2008-12-24-ASTM D5857-08.pdf;K:Users est user2012-03-12APCPP25-Black.pdf;K:Users est userDIN_16901.pdf"
I also tried setting the ComboBox Record Source Type to Value List and created a hidden TextBox (txtFiles) with the Control Source set as [Files]. Then I used VBA in Form_Load to equate the ComboBox Row Source to the TextBox. The code is simply:
Code: Private Sub Form_Load() Me.cboFiles.RowSource = Me.txtFiles End Sub
While this does create a ComboBox with the correct items for the first record, it also populates the ComboBox for all the other records with the same thing, ie. every ComboBox has the items: - K:Users est user2008-12-24-ASTM D5857-08.pdf - K:Users est user2012-03-12APCPP25-Black.pdf - K:Users est userDIN_16901.pdf
I'm thinking maybe this approach could work with some modification in the code or approach. Unfortunately, I'm not too clear on why the code goes through all the Me.cboFiles.RowSource, but only once on Me.txtFiles or how to get it to check the txtFile for each row. Otherwise, I will need to figure out some other way to fill in the ComboBoxes.
Edit: Actually, I've noticed something peculiar. Each time I select an item from a ComboBox, the other ComboBoxes for the other records also change to the same item. I think this has to do with cboFiles being unbound. Changing it to bound doesn't seem to be quite right though as it just gives the same default values as txtFiles while the items are still just copied from the first cboFiles. Also, I can't actually select any of the items when it's bound. I imagine it might have to do with the read-only attributes of a linked Excel File.
I am trying to add a new email field to my existing database of 900 employees. I have imported an excel worksheet of email addresses into a new table that has lastname, firstname, employee#, email. The primary key on the new table is email. The primary key on the employee table is employee#, and there is a new field titled email.
The email field in both tables are text fields. In running an append query, it either adds 900 to 27000 additional fields of email only or it says cannot complete because of key code violations. On the join properties I have clicked on #3 all records from source to only equal fields of destination.
I have a Pupils database where I have a form with Pupil name and ID.I have a query with a calculated field and the ID and this works great.
I can display the associated calc on the form using a sub-form (I link the ID in the calc to the ID on the form) but I dont know how to get this value to display in a listbox.
I have the following tables: Inquiry (This is the main table I want to populate through the use of a form) Programs
My question refers to two columns in the Inquiry Table Program Group
The program column is populated by a drop down menu that is linked from a programname column in the Programs table.
I I want is the Group column in the Inquiry table to autopopulate based on the selected programname (There is a group column associated in the Programs table) so the form field will be autopopulated.
What I have tried is autopopulating the form (form name is Inquiries) Field called Group by using =[Program].[Column](1) in the source code of the text box. This works well, but it then does not populate the main table.
I need to either - Learn how to populate the main table column called Group based on the form autopopulating or - Learn how to autopopulate the column Group in in the table so the form will autopopulate
I have a combo box [Description] populated with items and a corresponding cost in [PriceRetail] within the same table. I require the price to be added into the price field once the item is chosen in the Description pull down. This is what i have, but the price field never populates.
--------------- Private Sub Description_AfterUpdate() Me.Price = Me.Description.Column(3) End Sub ---------------
The source table has 4 records in this order; ID, Category, Description, PriceRetail
I'm new to access. Hope someone can offer some help!
I have created a simple database to better organise some day-to-day office data management. For instance, I have a form (just) to record event attended by staff. The data would then be output as a brief report on overall office activities. Since it's event-oriented, staff attendance is just one complementary information. I went through most of the post re multi-select. I understand that it may not be a good idea to return values from multi-select list boxes into a text field, as it would be problematic for using data-analysis in the future.
In my case, I just need to have a text box, neatly (and easy for user, too) shows who attended. As suggested in one of the posting, I follow the instruction of http://support.microsoft.com/default.aspx?scid=kb;en-us;210203. But it doesn't seem working. I attach my databsae (sorry, it's very simple, as I want to test whether the multi-select work).
Your help would be greatly appr:confused: :confused: :confused: ciate!
Also, what about if I group option button, will it allow multiple return by the way?
In the Contacts Table there are two fields, the unique Contact ID key field numeric and the Contact Name. Like this:
Contact_ID; Name 1; Tom 2; Dick 3; Harry
In a form named "Selection" there is a Combo box that references the Contact table, and lists just the three names in the drop down (showing the Contact_ID is optional - I'm fine either way):
Tom Dick Harry
When the selection is made it will populate the field Contact_Ref the table called SelectedName.
Question: When the user selects the name I'd like the data populated in the SelectedName table to be the Contact_ID Value, and not the Name, so if Harry is selected the Value "3" would be saved.
