What's The Best Way To Add A Record To A Normalized Database Utilizing Combo Boxes?
Jan 30, 2006
I have four tables with unique records as shown below. I'd like to add a record for a new item to the database by selecting the values from combo boxes for Supplier, Item and Unit. If the values do not exist the user would then type in a new value. SuppliersItemCode and Cost would always be new values. What's the best way to go about this? I am unsure how to add a record to a normalized database where you sometimes have to use / reference existing unique values in multiple tables via foreign keys for the new record.
The logic of the form would be:
1. Select existing or add new Supplier.
2. Select existing or add new Item.
3. Select existing or add new Unit.
4. Enter new SuppliersItemCode
5. Enter Cost
I currently have 3 tables within a database with student details of three different classes. I need to create a user form that has a dropdown box which I can select a student from one of these tables with a number of text boxes below which brings up all the students details, then once the student has been selected and the correct details are shown then I need to create a button which allows me to move that student from one table to another.
I have a SharePoint list that tracks the date/time of each stop of an order from its origin to its destination. The number of stops varies depending on the type of order (you can use train as a metaphor). The goal is to calculate the average time between each stop (we're trying to figure out where orders typically get held up).
Let's say there are stops A, B, C, D, and E. Every order starts at A and ends at E. If they all made every stop, I could easily get the average of datediff("d", B, A) etc.
However, since they make different numbers of stops, what I need is the difference between the stop and the last stop they actually made. I need a crosstab query (or report) that lists the OrderType as the row, the StopName as the column, and the average(datediff) as the value.
In a perfect world, this would be normalized and I'd have separate tables for the stops and the order types, but trying to do that through SharePoint is Not Fun and is going to confuse the end users.
i have 2 combo boxes on a form and a button to add a new record in a table. i am trying to add code so that if either of the boxes is 'null' that an error message displays and the code ends so that a partial record isnt entered into the table. i think the solution is adding a line somewhere to my code but im not sure where. Here is the code i have for just one of the boxes to get it working correctly:
Private Sub Command31_Click() On Error GoTo Err_Command31_Click If IsNull(Me.Combo14) Then MsgBox ("invalid selection made") End If
This code gives me the error message, ive tried putting in end sub/exit sub before and after the 'end if' but each time the record is added to the table. Can someone tell me how to end the procedure after the if statement if its condition is true?
I'm developing an Invoicing database (which I've attached a sanitized copy) where I'd like to have an invoice number combo box show only invoices relevant to the client previously selected in the client name combo box.
I downloaded an example from Microsoft's templates website (which I've also attached), and I thought this would be a simple task.
I'm not sure what I'm doing wrong, but I figure it's something simple.
I'd prefer to use vba; I really don't want to use macros.
Timesheet_T PK-TimesheetID FK - EmployeeID FK - ProjectID Sat Sun Mon Tues Wedn Thurs Fri WeekEnding
My question is on my input form I want to have the user/employee pick his name once in a header combo box as-well as the week ending date and have it populate to every new record that user input (each employee can charge to multiple projects in a week so I want to eliminate the need for them to pick their name and week ending date everytime they select a different job charge.)
John Doe Week Ending 12-20-2013
M T W TH F Landscaping 8 4 3 2 6 Roofing 3 5 2 1 Plumbing 1 4 1
I have a data entry form that is also used for editing exsisting records, upon the opening of a record the form populates with all the data of the record apart from what is previously selected in combo boxes, so I need to find a way to re-populate those comboboxes?
I'm trying to navigate to a record based on a series of combo boxes and a button. The user goes through the combo boxes, first picking a (let's say) Store, then Aisle, then Item. The Item combo box stores the Item's unique ID as its bound column.My code is as follows:
Code: Private Sub NavigateButton_Click() Me.Recordset.Clone.FindFirst "[ItemID] = " & Me![ItemComboBox] Me.Bookmark = Me.Recordset.Clone.Bookmark '(<-- Don't actually have this in yet, but I will need it) End Sub
The ItemID field in the ItemTable table is an Autonumber Long Integer. Why am I getting the error "Engine does not recognize 'ItemID' as a valid field name or expression"?There are no references within the form to the ItemID directly (no text boxes, hidden fields etc).
I am trying to get a series of combo boxes to interact and record the selections made in new records within a destination table. Here is situation with respect to the tables involved:
1 table lists the names/acronyms of various research facilities and the branch to which they belong (acronym is PK) 1 table lists Financial Points of Contact for each facility (acronym is FK to relate to earlier table) 1 table lists Technical Points of Contact for each facility (acronym is FK to relate to Facility table) 1 table lists program participants and the branch they belong to (Branch in FK to relate to Facility table)
I am trying to create a form that allows me to set the participant and, from this selection, restricts the facility choices in the Facility combo box to those that fall within the branch to which the participant is assigned. I think the next step is pretty obvious too. Once the facility is selected I have combo boxes that would only display those Financial and Technical PoCs assigned to that facility.
I have watched the 4 Data Pig tutorials and I can easily make functional cascading combo boxes as long as they only refer to the look up tables. I generated perfectly functioning cascading combo boxes on a test form. How do I generate combo boxes that will then store the info selected in my destination table? Whenever I try to set a Control Source it disrupts the functioning of the queries.
I'm very new to Access 2000. I'm working with 3 tables.
I finally got this sql statment to work: SELECT [tlkp.Language].[LangID], [tblRawData].[LangName], [tblApplication].[AppID] FROM (tlkpLanguage INNER JOIN tblRawData ON [tlkpLanguage].[LangName]=[tblRawData].[LangName]) INNER JOIN tblApplication ON [tblRawData].[AppID]=[tblApplication].[AppId]
How can I change it to UPDATE? I want to update the tblApplication.LangID = tlkpLanguage.LangID using the joins described in the select statement?
There is no LangName field in the tblApplication. I have tried everything and I keep getting syntax errors.
I am working on a hired plant database and want to limit the combo box on the hired form to only list items not on hire.I thought I could do this using the IssueDate and ReturnDate fields within a query with an IIf statement forcing the Yes/No field to choose Yes when there is a date in the IssueDate field and No when the ReturnDate field has a date entered.
And other time it wont work with On change but only with After Update Event (code is same).
Another thing this morning happens was that when I tried to add new record trough form where combo box and tex boxes are located, MS Office suddenly stopped working after selecting combo box selection (with message Microsoft Office has Stopped Working). It is 2013 version.
I got it work after deleting and re-inserting VBA code to autopopulate text box at After Update Event.
I'm trying to build an database for aircraft operators. I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.I've got following controls on my form (unbound):
1. AircraftType (combo box) from tblAircrafts 2. CompanyName (combo box) from tblListOfAircraftsOperators 3. TeailNumber (text box) from tblAircraftOperators 4. AirportNameSearch (combo box) from tblAirports 5. PassengersNumber (text box) from tblAircraftOperators 6. ManufactureYear (text box) from tblAircraftOperators 7. SourceSearch (combo box) from tblInfoSource 8. CountrySearch (combo box) from tblCountry 9. CategorySearch (combo box) from tblAircraftCategory 10. EamilToOperator (text box) from tblAircraftOperators 11. InteriorPhoto (Bound object frame) from tblAircraftOperators 12. ExteriorPhot (bound object frame) from AircraftOperators
I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
there is a way to convert multiple text boxes to combo boxes all at once, rather than right clicking on them one at a time, and selecting Change to.
I have a form with about 50 fields and most of them need to be converted to combo boxes. I'd always done it manually one at a time up to this point, but I'm trying to build up my learning and look for smarter ways to do things.
I am trying to make a search option in my form header. Right now I have two unbound combo boxes (CboAccountsfilter and cboCourseName) that I can use to filter my records. Currently, I can use the drop down for CboAccountsfilter and a list of accounts will appear. When I select one, the corresponding Course Names will appear in cboCourseName. This works fine...Code below. I would like to take the filtering a step farther and add checkboxes to filter the data. I my form, there currently exist several check boxes (yes/no)...(Priority, Rep Top Target, Manager Top Target, ect). I would like to have the option to use a check box to filter. I.E if I had a checkbox in my header called PriorityFilter, if checked it would only bring up those records that met the two combo boxes criteria and was a priority.
Below is the code I have so far...it doesnt have anything for the checkbox because I am at a lost of how to get started.
Private Sub CboAccountsfilter_Change() Me.Requery Me.cboCourseName.Requery Me.Check178.Requery End Sub
Hi guys, I have a related databes with about 20 tables.
My main table stores the data linking with most of the other tables. This main table stores football match records with player line ups. Initially I had a problem linking the 11 player fields in the main table to the player table, Icould only do it with 1, so someone at work suggested to link it using the lookup function. This worked brilliantly. However, now I am designing a GUI with forms but the forms have combo boxes where the lookup function was used and when i change these to text boxes, the players names are replaced with their ID numbers.
Is there any way of creating the form without the unsightly combo boxes, as they won't be needed, the GUI will be read-only. I look forward to your help!!
I have a table that has client names and addresses. I have designed a form to be able to invoice these clients and everything is fine however what I'd like to do is have the address of the client appear automatically.
I have set the client names in a combo box and would like their address to appear in either a text box or sunken label automatically from the table. Is this possible and how do I do it.
Please keep in mind that I've done basic programming so please be kind to this newbie :D
Hi everyone, I have been making progress with customizing a MS Access program, but one major problem is that I have been trying to make a List Box or Combo Box that I can use to enter data in the TABLE, but I find that I get a pull-down list that has the list of values from only the parameter that is primary key. More specifically, the program is set up as follows: I have two tables in this program: One that is called "invoices" and one that is called "items". In both of these tables, there is one common parameter, which is "Item Number". Item number is the primary key, and I used the "relationships" function to tie this parameter to itself between the two tables. I was successful in setting up a list-box for the "Item Number", but when I try to set up a list-box for another parameter that is supposed to display a person's initials, the pull-down list displays the list of item numbers instead of the list of people's initials. In fact, I don't know if there is an extra step I need to take so that the database stores a list of people's initials. Instead, I just fill in the initials in the field for each record for which the "Item Number" is the primary key. How can I get the list-box to pull down a selection of different people's initials, or in other words how can I get all the people's initials to be stored so that the list of initials can be looked up. Just so you know, I have tried different choices of entries in the "Lookup" tab in the Design mode of Tables, including Display Control, Row Source Type, Row Source, and Bound Column, but the outcome is that the only parameter that I get get in the pull-down list is the primary key, which is Item Number. I appreciate any help you can offer in explaining how to correct this. On a separate note, one of the parameters is "Date", and on the reports, I'm trying to figure out how to filter a specific date range so that I can limit each report to a specific month. Please advise me on this procedure as well. Thanks.
I have several comboboxes (6) on my form.How to populate these comboboxes with values depending on selected value in previous combobox.
Example.Lets say that you select value "Audi" in combobox 1, then available values in combobox 2 should be "A4","A6","TT" etc. and if you selected "BMW" in combobox 1, then available values in combobox 2 should be "3-series", "5-series" etc...
How do you merge / append records from one database into another? The old database was very poorly designed (i.e. no normalization), but much of the data is the same.
Instinct from what I have learned says do this:
- create a query in each database that joins all fields together into a flat table - run an update query that migrates the source data to the new database.
I really have no idea though, and to be honest... even if this is how it is done, I am very unsure how to proceed!
So I’ve been assigned to develop a database in Access which will house information on students who are participating in an internship program sponsored by my employer. I was given a spreadsheet with roughly 40 columns, so it is pretty detailed info about the students. For example personal student info (DOB, email, phone, etc), as well as school, major, year rank, school address, permanent address, etc.
Importing this given table is easy, just by using the import wizard. However I am faced with a challenge. The info I have is up to date at this point however in years to come there will be a need to import new student info. The only way my employer wants that to be done is by taking the new excel spreadsheet and importing that data into the already created database. Now this wouldn't be a problem if the DB is one flat, non-normalized, table because the columns would line up. However I feel that the DB needs to be normalized because of the vast amount of data that is repetitive, such as State or Major.
Is there a way to import an excel sheet into an existing normalized access database? Or even a way to automate it so that the excel columns would be broken up into the separate corresponding normalized tables?
I have a fairly complete data model, my question is, how best to create tables for entry where I do not have subform after subform, and subforms in subforms. With subforms for the subforms and subforms on top of that.
I am about to start pulling out some teeth because at this point it sounds fun compared to this!
Do I need to use a query to base my table off of to build my Customer entry form? Or is my only option to use 15 frikin' Subforms? I I use a query, does that cause problems with data integrity?
I am having trouble finding useful reference to this, both online and in books I have purchased.
Normalization is fantastic, I understand.... But besides the crumby Northwind sample DB that everyone and their brother directs you to, is there an actual useful sample to look over that illustrates the use of a normalized data model?
I have grown more than a bit irritated with this, mainly because of my own inablilty to make progress in putting this project together.
I would like to create a simple to use, streamlined systems that promotes efficiency and speed, not a super convoluted system that has 85 subforms on every form in the DB.
I would REALLY appreciate some feedback/direction here, I am about to give up on normalized data and go with a simple flat table approach.
I would rather have redundant data than indecipherable forms that I have to answer incessant questions about.
