I have one table (tblComplete) with a listing of 30-50 company names and associated information...
"tblComplete" has fields: AutoNumber, Company, Address, City, State, Zip, Phone
On a form I created a combo box linked to AutoNumber and Company to drop down the list. Once the 'company' is selected, I would like it and the rest of the fields (address, city, state, zip, and phone) to go into the blank table (tblSelected).
Could I use a Macro to do this or should it be a query?
Does anyone have a quick sample code on how to load a combo box using ADO? Currently I set the row source type to Table/Query and the row source to a query within the program. I would like to fill my combo box using code instead.
Hi All, Totally new to access, been asked to make a database for a historical society. To make life easy, in descibing the hist. society's numerous items, I thought it best to follow the standard museum catalogue naming system, i.e everything has a primary and secondary classification. So what I have done is create two combo boxes, with the choices in the 'secondary' classification field limited by the choice in the 'primary' combo box. These controls are based on two tables that contain only the names/info on primary and secondary classification. It works nicely. However, because these controls are on a data entry form, when you make your choices in these combo boxes, the data is not recorded on the appropriate data table (presumably as the controls are linked to those other tables). Everything else you type in the form (like the item's location or autonumber) is saved in correct table. Other problem is that every time you go to add a new record, and change the primary/secondary choices, it changes all the records on the form to equal these choices. How do I fix it, or is there nothing I can do (as it may be totally impossible - I don't know!)??
Any help greatly appreciated, but please talk very simply and slowly - I am a complete novice!
I have a form "Admin" used to create new records in a table "RCJ"
I have a table called "Projects" that contains detailed information for projects.
I have a table called "Waterfall" that contains basic information and schedule information for each particular project.
I have a combo box "Contract" that selects information based on a query "Core" that gathers certain information I want to use of form "Admin"
I understand it's generally not a good idea to duplicate data in tables, but for specific reasons I am here. My combo button "Contract" has an event "On Click" that gathers information I want copied from table "Waterfall" via query "Core" is:
Private Sub Lookup1_Click()
Me![Project Description] = Lookup1.Column(1) =>goes to a text box Me![TContract] = Lookup1.Column(3) => goes to a text box Me![Requestor] = Lookup1.Column(5) => goes to a combo box Me![AEM] = Lookup1.Column(4) => goes to a combo box Me![AE] = Lookup1.Column(6) => goes to a combo box Me![Priority] = Lookup1.Column(12) => goes to a combo box End Sub
Now, the items listed above are sometimes just a text box and sometimes a combo box (because there are times, when items get entered that don't fit the original combo box.
Now, here's the rub. When I select the pull down on the combo box it lists all available projects. You find the one you want and select it. At that point, only Columns 1 (text box) and 6 (combo box) auto fill. The other information does not fill in .. BUT, if I more to the prior record and back, all information shows up OR if I click on any of the particular fields, the information shows up in that field. In theory, everything should show up right away.
I thought it might be because it doesn't like to autofill a combo box, but one of the combo boxes fills fine. And all of the information is acutally going to the various boxes, it just doesn't show up right away.
I'm stuck on a portion of a form that displays members attending and not attending a specific event. I've got some of it working but others not.
1. Removing items from a table and updating a listbox 2. Changing the Event selection from Next Record to a combo box.
The code that I have in the two list boxes are as follows:
Code: Private Sub Form_Current() Me!lstNotAttending.RowSource = "SELECT DISTINCT Members.MemberID, Members.txtLname, Members.txtFname FROM Members " & _ " LEFT JOIN MemberEvents ON Members.MemberID = MemberEvents.fk_MemberID" & _ " WHERE MemberEvents.fk_MemberID Is Null OR MemberEvents.fk_EventID <> " & Me!txtEventID & _ " ORDER BY Members.txtLName;" Me!lstAttending.RowSource = "SELECT Members.MemberID, Members.txtLname, Members.txtFname FROM Members " & _ " LEFT JOIN MemberEvents ON Members.MemberID = MemberEvents.fk_MemberID" & _ " WHERE MemberEvents.fk_EventID = " & Me!txtEventID & _ " ORDER BY Members.txtLName;" End Sub
The code I have working for adding a member:
Code: Private Sub lstNotAttending_DblClick(Cancel As Integer) CurrentDb.Execute "INSERT INTO MemberEvents(fk_MemberID, fk_EventID) " & _ "VALUES (" & lstNotAttending.Column(0) & ", " & Me!txtEventID & ");"
Me!lstNotAttending.Requery Me!lstAttending.Requery End Sub
I know the code for removing has to start with REMOVE FROM. I just don't know the syntax.The combo box has no code.I'm assuming the Requery options aren't working properly for the listboxes
I have a table [Employees] that has the field [FirstName] and [LastName]. On my form [Jobs], I have a combobox [ComboWho]. The combo box has all of the possible first names. When a first name is picked, it makes the last name fill in a textbox.
In the real version, it is a [LastName] is a lot of information, so it is kept in a memo field. I tried to use cascading combo boxes, but it cuts off my text.
I keep getting it to almost work... I've tried about 5 different methods... DLookUp, subform, etc.
Right now, I'm using this code:
Private Sub ComboWho_Change() Dim location As String Me.ComboWho.SetFocus location = DLookup("LastName", "Employee", "FirstName = '" & Me.ComboWho.SelText & "'") Me.MyTextBox.SetFocus Me.MyTextBox.Text = location End Sub
It has properly filled in the textbox. I thought it was working perfectly, but ran into one snag... We have been using a split form... and it won't let me filter that column (Column ComboWho). I think because it is saving everything as numbers instead of text.
ComboWhoDatabase.accdbI've attached a copy of the simplified database.
I have a form for user to select multiple items from a combo box, and 18 unbound text boxes , each time user selects an item in combo and hits "add" the value appears in txt box by order, I mean select1 then "add" filling txtbox1, select2 then "add" filling txtbox2 ... and so on till we reach txtbox18, then msg box appears that he filled the whole 18 boxe. I have already done this before for one txtbox which is a very simple operation, but i cannot figure out how to do it with multiple txtboxes.
I have a form with a text box. i want whatever i write in the text box to be inserted in the table Product under Code. Its like im inserting a new product.
How can i do that? Im new to Access and VB, so bare with me.
I currently am working on a small inventory project. I have a table with the fields "Part Number" which is my primary key, "Description", "Cost", and "Sale Cost". I have a second table that I would like to use to keep track of purchase orders. It has the fields "Part Number", "Description", "QTY", and "Cost". I would like to be able to open purchase orders and be able to select a part number from a combo box that pulls "Part Number" from my item list.
So I can enter items in to my item list and later when I do purchase orders I can go to purchase orders select my item and have it automatically fill in the description and cost in my purchase order. If the item does not exist I can enter in the item in to the item list table. At this point i'm not worried about a prompt to enter in new items if they don't exist in the item list table. I just want to my Purchase Orders table to be able to autofill description and cost by selecting a "Part Number".
I have a basic design question that I am not sure how to address.
I am trying to build a simple data entry database with a form to take input from the user, store the values in a table. Once the data is gathered into the table I want to use this table to print a report of each record (entered using the form before).
To achieve this objective, I made a form (frminput) with some text fields. Most of the fields on this form are Bound fields to a query (qrymaster). One of the field is a Combo box (whose value is shown from another table). I have designed the RecordSource of this Form to be a Query (qrymaster).
This is a basic Data Entry form where the user selects the Combo box item and based on what he selects, some of the fields in this Form gets pre-filled. The rest of the fields on this form are bound to the query "qrymaster" and the user has to type these fields manually.
Now, I want a Save button here that would save all the values on this form to the table "tblmaster". How do I do this efficiently keeping in mind all the normalization laws on the database?
At this time, behind the Save Button, I have included a SQL statement to insert all the field values into the table "tblmaster".
I am sure there is a better way to do this. Can someone point me in the right direction please?
I have a list of 4300 clients. The clients' personal information and ID numbers are listed in the table BasicInformation. Another table, DiseasesAndValues lists 17 diseases and a "scheduled value" (long story short - an amount of money) for each disease. Each client has one of these 17 diseases. I would like to have a column in the BasicInformation table which shows the scheduled value for the disease that a particular client has by going back to the DiseaseAndValues table and checking to see what the scheduled value of their disease is. Alternatively, I could use a query that could enter in these values with a click of a button.
I know how to have multiple columns fill a lookup in a combobox both from a table and a query. But I need to have the other fields that aren't saved by the combobox saved in the neighboring columns. So, my primary table is a master list of chemicals to be analyzed along with their respective registry numbers. I know what most of the programmers say about repetitive data being bad form etc. These names and registry numbers will NEVER change, so I'm not worried about a change causing problems later. I'm trying to build a separate tables that will have specific chemicals and the methods that they are analyzed under that effectively copy from the master list, but add their own quality control criteria. Further more, not all of my clients need all of the chemicals that are available for every method. It should be noted that not all chemicals are analyzed by the same methods and that some methods will have some of the same chemicals as others. I need the registry numbers because this is what the analysis software uses to uniquely identify each chemical and I need the name because names are easier for me. Long story short, I need both of these pieces together. It was suggested to have a macro copy the remaining columns from the dropbox in the table to the other columns in my table, but I'm not sure how to do this (I'm still very new to Access and my VB is very rusty). I understand how to do this for a form, but when client reporting lists become involved later on, this will make my database very bloated to have a form to populate each respective table.
I have a database which has been in production for quite a few years, it even made the swap from .mdb and .accdb without losing any of the functionality. Well I just had to export from access to new access to start a new file for one of our projects and now one of the subforms is not adding information into the table. The way the subform functions is it feeds information to the table Lease Tracts, at the same time it has another table Xrf Property Tracts Leases (I didn't pick the name and it drives me nuts too!)
When a user starts to add information into the subform the table lease tracts generates an auto number "tracref" which it adds onto the line where the user is inputting the rest of their information. The relationship is set as all alike between xrf and lease tracts to input in both tables, well the xrf lease num1 is being filled in by the master/child setting but the lease num1 on the lease tract is the only field not autofilling.
OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.
"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID". "Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on. "Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.
The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.
So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.
The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.
The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.
The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.
The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.
However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.
Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open Select Case Forms!frmLogin!cboUser.Column(4) Case X = 4'the employees security level' Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee. End Select
of course this is a syntax error as I do not know the correct code words.
I have a table with names of people and the cars they own. I made a form with a combobox which everytime i choose the name of the person in my combobox, it will list the cars they owned. That works fine. Problem i have is that i have to show how many cars they own. So i made a query to count the number of cars for each person. Then I made a textbox on the form to show this count. However I can't seem to link this textbox to the query since my form has a record source to the table of ppl and cars. How do i link my textbox to my query of counts of cars?
I tried changing the control source pointing to the field of the query but it doesn't work. I have linked the combobox selection so that my query filters to wat i select on the form. Anyone can help :( , its a simple process but i keep trying and doesn't work, and also dont wanna use a subform.
hi is there anyway to temporarily store the information being entered into a form before actually writing it to a table. i want information to be added to the table only once the save button is pressed. i dont want the table to be used even as the scratch pad. Thanks
I have been trying to create a database that looks after staff holidays.
Here's how it looks at present
TBL_Holidays:
Staff_Index Staff_Name (From a lookup) Start_Date Number_of_days
TBL_Holidays_Booked:
Staff_Name Dates_Booked
Once the form (FRM_Holidays) is filled out I had (until I gave up and decided to ask for help - 2 weeks) a button that went off and run a append query.
Here's my problem. Once the button is pressed I need the TBL_Holidays_Booked to fill with the Dates_Booked.
ie.
John Smith wants his holiday to start on the 1st August for 10 days.
The resulting TBL_Holidays_Booked would look like the following:
John Smith 01/08/06 John Smith 02/08/06 John Smith 03/08/06 .. John Smith 10/08/06
Is there a way to do this? or is there a simplier way of doing it?
I have a form, which is used to create proposals. The form has form headers and footers, as well as a page footer.
In the Detail area of the form there are only two fields, Description and Amount, which only take up one line. I want the detail area to determine how many pages the form will be when printed. Can a new description/amount fields line be automatically inserted after I complete one? I guess the question is, can the Detail area of the form grow automatically? If so, how is it done.
Naturally, I only want the Form Header and Footer to be printed once, regardless of the amount of pages the proposal becomes. And, likewise, I want the page footer to be printed on each page. So, if the detail area only has one line completed then only one page will be printed. However, if there are 20 lines in the detail area then two pages or more will be printed.
What I am trying to do is fill a field with a date until a new date is encountered. Basically, I have imported some csv data which only has the date when it changes. I have created a new field and have a row with only the date in it. Is there a way to fill the rows below the date row with that date until the next date row is found and then continue on with the next date and so on. Here's what the database looks like, basically:
Date Field1 Field2 Field3 01/01/01 null null null null DATA DATA DATA null DATA DATA DATA null DATA DATA DATA null DATA DATA DATA 01/02/01 null null null
Hopefully this makes sense and someone can give me a hand with this! Thanks so much!!
Main Form ID Top Assembly Part Number Top Assembly Serial Number
Sub Form (continuous form) ID Part Number Part Name Part Serial Number Main Form and Sub Form linked by ID
(There is a separate table that has sub form part numbers for each main form part number)
Based upon the part number in Main Form I need to automatically populate the sub form with Part Number and Part Name.
Some Main Form Part Numbers have 85 sub form part numbers others as few as 3 sub form part numbers
Main Form ______________________________________ | | ID [123 ] | | | | Part Number: |9999999 | | Serial Number: |ABC-9999 |
Sub Form _____________________________________ | | ID [123 ] ||Part Number | Part Name | Serial Number | || 1111 | Widget 1 | A11111 | || 2222 | Widget 2 | B1234 | |_____________________________________
Is it possible to write an event procedure attached to a command button in a Microsoft Access database that would use data from an established query to pre-fill data in to a form on a web site? Has anyone tried this?
I have a form that the user will fill, then email it to a customer. I dont want to send the whole table, just the values that the user just inserted in the form.
Any idea on how should i do that using Access?
I am aware of the SendObject method, but i never used it, and dont know how. Any help will be very apreciated.
