database,ms-access,access-vba,relational-database,entity-relationship , Listing specific requirements on Access Database form

Listing specific requirements on Access Database form


Tag: database,ms-access,access-vba,relational-database,entity-relationship

I am trying to create a database using Microsoft Access Database, but I have a problem. I want to create a database that holds information for computer components and a form to list certain components to build a computer.

For example, I want to build computer A, and I have a list of all the hard drives: hdd1, hdd2, hdd3, hdd4, and hdd5, but only hdd2 and hdd4 is compatible.

My approach would be (where bold = primary key, and italic = foreign keys:

HDD (hhd#, size, cost,compA, compB, compC)

COMP(comp#, hdd#)

Would this be the right approach?


Please Note:

I've provided an answer here because I'm feeling nice, but in future do bear in mind that your question is a bit too broad to be useful on this site. However if you're a beginner, hopefully this will of some help.

I've mocked-up my own example based on your descriptions purely to illustrate an idea on how to approach this.

I've created 2 tables. The first, tblCompBuilds will list computer builds. Here's its structure:

enter image description here

...and here's some dummy data for it:

enter image description here

The other table I've created is tblHdds, which will list Hard Disk Drives and a few specs. Here's its structure:

enter image description here

...and here some dummy data for it:

enter image description here

I've already decided that I will be using the HddFormFactor field that appears in both tables as what determines whether a hard drive record in tblHdds is compatible with a computer record in tblCompBuilds.

This is an important question that you need to be asking yourself when designing your database and form...

"what data can I use to establish that a hdd is compatible with a computer?"*

For my example I've invented 1 field, but you may have a more complex set of criteria to apply (possibly multiple fields and varying conditions / situations to consider).

Anyway, once I've established the data and how I intend to show compatible HDDs, I need to make a form that will allow me to select computer builds and the compatible HDDs.

With a blank form created I select the form by clicking in the top-left square; a black square denotes that the form is selected:

enter image description here

In the Property Sheet for the form I go to the "Data" tab and against the Record Source property I use the drop down to find and select the tblCompBuilds table:

enter image description here

This will bind the form to this table, meaning you can add the fields from the bound table to this form and move through those records using the form to display them.

Instead of adding the fields from the bound table to the form I'm going to create a new combo box using the combo box wizard (a combo box is essentially a drop-down list of options).

First I state that I want to get the options for my combo box from a table. This is because the data listed under the ComputerBuildName field in the tblCompBuilds table will give me the list of computers to choose from:

enter image description here

I then specify the table...

enter image description here

...and then the fields:

enter image description here

In this case I'm using just the ID and ComputerBuildName fields. The ComputerBuildName field will be what the user sees as options when they use the combo box, and the ID field will help me later when I use the combo box to manipulate the form.

I then specify an order that I want the options in the combo box to appear. As the data from the field ComputerBuildName is ultimately what I want to display in my combo box, it would make sense to have this showing in alphabetical order for the user:

enter image description here

Finally you can specify the width of the columns showing in the combo box; I tend to ignore this and do it manually later (note that I've asked the wizard to hide my primary key field, which is the ID field):

enter image description here

If you go in to Form View, the combo box looks like this:

enter image description here

So we now have the ability to select our Computer Build, next we need to show the related/compatible HDDs. There's lots of different ways this can be done, but I'm going to illustrate this using a subform.

Add a subform to your form and go through the subform wizard; beginning with specifying that I want to use an existing table:

enter image description here

...I then specify the table (tblHdds) and the fields I want to display:

enter image description here

The next stage is where we specify how we relate records on our form (which we specified earlier by setting its Record Source to tblCompBuilds) to records on our sub form (which we just specified as tblHdds).

The subform wizard will attempt to provide the relationship between form and subform by looking for fields in the tables bound to the form and subform that have the same name.

When I designed the tables, I was careful to ensure that:

  1. There would be a field (HddFormFactor) that would relate both to tblCompBuilds to tblHdds, and
  2. This related field was named exactly the same in both tables

This means there's a relationship already waiting for me to choose in the subform wizard, rather than having to try and create my own:

enter image description here

After selecting that I want my form's tblCompBuilds records to relate to my subform's tblHdds records using HddFormFactor I just need to name my subform to finish the wizard:

enter image description here

In Form View, the form should now look something like this:

enter image description here

What we want to happen is that when you select a computer from the Computer Build combo box, the subform of HDDs will update to list only those that have a compatible HDD Form Factor.

If you try and do this, the subform doesn't update.

Notice at bottom of both the form and subform there is some information about the records bound to form and subform:

enter image description here

When you select any Computer Build in the combo box, notice that the form's record number does not change; it just stays on 1 of 3 and doesn't move.

The subform cannot update because the currently loaded record for the form does not change so neither can the subform that's related to it.

To change the loaded record on our form using the combo box, we'll need to employ a bit of VBA.

Go in to Design View, select the Computer Build combo box and from the Property Sheet's "Event" tab, click the [...] button against the After Update event:

enter image description here

Select "Code Builder" and you'll be taken to the VBA editor with the following code already written for you:

enter image description here

Fill-out the middle of the combo box's After Update VBA so it reads like this:

Private Sub cboCompBuild_AfterUpdate()

    Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone

    rs.FindFirst "[ID] = " & Me.cboCompBuild

    Me.Bookmark = rs.Bookmark

    Set rs = Nothing

End Sub

Note that cboCompBuild is the name I gave the combo box. You'll need to make sure that this line in the VBA refers to the name of whatever you've called your combo box; you can find the name of your combo box in the Property Sheet:

enter image description here

Go back in to Form View and hopefully, when you select different Computer Builds, the subform should update to show those that are compatible.

There's more you can do from here, like set the subform's Allow Additions property to "No" so you don't get that weird empty record at the bottom. You may also want to consider disabling edits and deletions from the subform as well if you don't want users to be able to change the data in the subform's bound table (tblHdds). It's also worth noting that for the VBA I used a watered-down version of Allen Browne's method for Using a Combo Box to Find Records; his full version is more robust than what I've used for quick illustration.

Here's a link to Access file I created for this example.

Hope this helps.


Connecting to ODBC using pyODBC

I've read all the faq pages from the python odbc library as well as other examples and managed to connect to the DSN, using the following code: cnxn = pyodbc.connect("DSN=DSNNAME") cursor = cnxn.cursor() cursor.tables() rows = cursor.fetchall() for row in rows: print row.table_name but for everything else I keep getting...

ER diagram for booking database

I'm creating a database for booking apartments app. I have a question - it is allowable to keep user's passwords in DB (for example using BCrytp)? Another question - where is the best way to keep price if it may vary during several months?

Combining two select statements

I have two statements as such: var chgAssociationQuery1 = (from a in sostenuto.PROBLEMS join b in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals b.FROMSERVICEREQNO join c in sostenuto.Changes on b.TOSERVICEREQNO equals c.SERVICEREQNO where b.FROMSERVICEID == 101001110 && b.TOSERVICEID == 101001109 && a.NAME.Contains(name) select new { ProblemReqNo = a.SERVICEREQNO, ProblemId = a.SERVICEREQID, ChangeReqNo =...

Order by count not sorting the records correctly?

I am using MySQL. I have two table i have attached the images of the both tables? When i apply ORDER by Count then the query retrieving result's from database but not sorting them correctly, i don't know what wrong i am doing? i searched some similar question in stack...

Foreign key in C#

I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

CakePHP Unable to insert to database (datetime format)

I was new to Cakephp and I actually following the tutorial of the Blog tutorial however I didn't follow their database and try my own. Currently my database: My Model: class FypCakephp extends AppModel { //Table Name public $useTable = 'Report'; public $primaryKey = 'report_id';} My Controller: public function add()...

Purging Database - Count purged/not-purged tables

So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

ODBC ISAM_EOF without any reason

I have a strange behavior with an ODBC Driver and the underlying COBOL database. The database driver is acuODBC of AcuCorp(now Microfocus), the database itself is a COBOL database. The DSN is a system DSN and works just fine. Via Access/Excel u can read/write data with the DSN successfully. In...

If I export my database with phpmyadmin will it lock my tables or take my database down?

I want to create a backup of my database using the phpmyadmin export function. the database can't have any down time so I need to know if running a database export will take the database down? I've looked on-line but all I get is instructions on how to export. No...

Creating a generic / abstract “DBContext” Class for shared functionality among different DBs

I am working on a C# project that is pulling data from SQL Server, ODBC data-sources and Oracle Databases at different times. At this point, I have created 3 different classes - Once for each type of DB access. However, I am seeing that 95% of the code is identical...

I need help setting the RecordSource of a Report within a VBA Function

Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...

MySQL: Select several rows based on several keys on a given column

I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

for-loop add columns using SQL in MS Access

I am trying to add n columns to a table, like in this example of code where n = 10: Sub toto() Dim db As Database, i As Integer Set db = CurrentDb() For i = 1 To i = 10 db.Execute " ALTER TABLE time_series " _ & "ADD...

SQL Server: checkident: “[S00014][2560] Parameter 3 is incorrect for this DBCC statement.”

Using an SQL Server database. A lookup table is having 50 new items added to it using a database versioning tool so the same script will be run on all three databases. There is a Dev, QA, and Prod version of the database and because of political reasons prod can't...

How to create a date using strings in vba?

I want to run a for-loop from sometime to some specific time. Let's say from the first day of the year to the last day: I am given the year and I need to add the month and the day to it: I am trying to concatenate into a full...

IBM Cognos _days_between function not working

I'm having an issue with Cognos 10. I'm trying to calculate the number of days between to dates so I use the _days_between( date1, date2 ) function. _days_between ([Derniere Date Changement diaphragme] , [Premiere Date Changement diaphragme]) I'm quite sure the two Dates are Date objects ( I set them...

VBA - Using Typeof … Is ADODB.Recordset Results in Compile Error

I am building a function with a set of supporting sub-functions to create ADOX.Catalog objects to help me build automation for Access database generation. I like to use late-binding for my applications because my user base doesn't always have the same version of office applications, so I can't always rely...

Difference between dba_SEGMENTS and dba_data_files

Shouldn't these two queries give the same result? First is a sum of all segments while other is the sum of all data files. They are giving different results for me. While the first one gives me 149522MB, the other gives 214973.34 MB select sum(bytes)/1024/1024 MB from dba_segments; select sum(bytes)/1024/1024...

Does Maria DB support ANSI-89 join syntax

Does Maria DB support ansi-89 join syntax? SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites, pages WHERE sites.site_id = pages.site_id; or only ansi-92 syntax? SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title FROM sites INNER JOIN pages ON sites.site_id = pages.site_id; ...

Why am getting this error?: Unknown column 'firstname' in 'field list'

if(isset($_POST["submit"])) { // Details for inserting into the database $id = htmlentities($_POST["id"]); $firstname = htmlspecialchars($_POST["firstname"]); $lastname = htmlspecialchars($_POST["lastname"]); $username = htmlspecialchars($_POST["username"]); $password = htmlspecialchars($_POST["password"]); // Dealing with inserting $query = "INSERT INTO `myDatabaseForAll`.`users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES (NULL, $firstname, $lastname,$username,$password)"; $result = mysqli_query($connection,$query);...

Desktop Database with Server without installation

i have Java-Application that growth a lot over the past month. All configurations from the programm and all the logs are stored in .txt or .log files. The programm should change now. The user could install the programm on his own pc but the programm itself should get all the...

ElasticSearch asynchronous post

I'm posting data on my ElasticSearch database. I've noticed that data is not immediately available, it requires some milliseconds to show up in a GET request. I can live with that (after all, the calls are asynchronous so this behavior is expected) but in my test code I need to...

MS Access Text Field Special Characters

I have a search button that executes this applyfilter macro code: [ContractNumber] Like "*" & [Forms]![frmContractMatch&Revenue]![Text44] & "*" So it searches text box text44 for the appropriate ContractNumber. The trouble I am having is that if a contract number is ART#45 for example, it will not find it in the...

creating stored procedure in mysql calculate profit from product table

My following code show some error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN SET @v1:= (select sum( cost_price * current_stock) from product)' at line 2 CREATE PROCEDURE sp_profit BEGIN...

Database object with different data

I'm making a web page in ASP.NET MVC which compares prices from different shops. I have a one-to-many with products and the shops, where the SHOP has one PRODUCT and a PRODUCT has many SHOPs, the problem is that the product is the same but the price is different. Example:...

How do I access website databases? [closed]

I'm trying to better understand databases and networking. Basically my question is how do websites like these access player data from the game when they're not affiliated with the company? Do you have to have permission by the website to read their statistics? Are these databases private? What are different...

Using date in CreateQueryDef

I have a table on which I run a query that I export to Excel. Here is an example: Table: Food Item | Price | Limit_Date | ------------------------------- Carrot | 0.80 | 08/07/2015 | Salmon | 4.30 | 01/07/2015 | Biscuits | 2.40 | 15/12/2015 | Milk | 1.00 |...

Photos as Varbinary(MAX) in SQL Server 2012 causes error 502753 in Access 2010

I have an Access 2010 odbc front end with a SQL Server 2012 back end. My images (.bmp) are stored as Varbinary(Max). When I insert an image using my bound object frame in an Access form, the image displays on the form and report perfectly. I had a large number...

Converting ADODB Loop into DAO

Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

SimpleMembershipProvider WebSecurity.InitializeDatabaseConnection The login from an untrusted domain,database,exception,model-view-controller
I export the database to the server and i've problem. When I start application i see this error An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. WebSecurity.InitializeDatabaseConnection("DefaultConnection",...

Get work time in minutes based on shift schedule

In production we have 3 shifts. Each Shift timing is described in table tbl_ShiftSched: WT - work time, PT - break time. ShiftTmID - schedule for 2 and for 3 shifts. I am looking for easy way to get work time in minutes having start and end time. For example,...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...

Is there a way to create a primary key and have it cascade into other tables without re-entering data into the new tables?

I am working on setting up a database and have created tables that cascade out like using the cascade on update for the foreign key. I have two tables being worked with to try to solve this. My tables are like this: create table Item(Item int(4) not null, EquipName varchar(20),...

select data according to Row in Sqlite

image i want to get the whole row i am using this code to get my desired data but its giving me null pointer exception at temp_address1 here is the code as in mentioned image i want to get the whole row matching phone number. how can i achive that...

echo both users

The code at the bottom of this post currently echoes: Name: Spongebob Squarepants Description: I live in a pineapple under the sea. Role: editor But there are two users in "wp_usermeta". It's only echoing one. The result needs to look like this: Name: wp_dev_05 Description: My name is Chris Topher!...

What type of database is the best for storing array or object like data [on hold]

I'm just curious what the best method would be if I'm trying to have a bot running on my Node server that I could play Blackjack against. But for multiple connected clients via sockets, each connected socket will have their own bot to play against but I need some way...

Pull information from SQL database and getting login errors

I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...

ms access query very slow

I have this ms access query: SELECT, suchbegriff2, menge FROM (SELECT artnr & '/' & [lfdnr-kal] AS sb, left(suchbegriff,7) & val(right(suchbegriff,4)) AS suchbegriff2 FROM kvks WHERE suchbegriff like '*/*') AS t1 INNER JOIN (SELECT artnr & '/' & [lfdnr-kal] AS sb, [artnr-hz] & '/' & val(lfdnr) AS hz, menge...

Complex SQL with Multiple Joins

I have a database of politicians, political parties and candidacies for a post in the election. The database consists of the following tables: People, Groups, Candidacies and Posts. The Candidacies table is a join relationship between People and Groups. Each record in the Candidacies table represents a candidacy in the...

In simple RESTful design, does PATCH imply mapping to CRUD's (ORM's) “update” and PUT to “destroy”+“create” (to replace a resource)?

I'm trying to create a simple REST API and map it to CRUD. I have an ORM (DataMapper) which has methods like create, update and destroy. If I get it right, given a resource {a:'foo',b:'bar',c:'baz'}, performing a PUT {b:'qux'} is supposed to replace the resource and result in the same...

Iterate Databases and Delete Query

I am trying to iterate a table with database names in it, open the database, check if a query exists, if it does delete it, then import a query from the database this code is being executed from. I keep getting a compile time error on the line of Set...

How to execute four queries once and then check success or failure?,windows,visual-studio-2010,ms-access
I need to execute four queries and then if there is success must return true otherwise false. The queries affect the database but the function returns false Private Function save_to_data() Dim success As Boolean = False Dim conn As OleDbConnection = GetDbConnection() Dim total_due As Decimal = sanitize(txt_total_due.Text) Dim amount_paid...

Id in database using qt

I am new to qt. I have created a table in sql, and what I'm trying to do is to give an id whenever user adds data. For the first thing inserted it will be 1, second 2 and so on. So how to check what number was the last...

Update query when database in ms access

My update query update tbl_Clients set [email protected], [email protected] where [email protected] When I am updating this code in giving error Syntax error in update statement...

How to use existing SQLite database in swift?

I know there are some resources available for this but none of them clearly show the way how to do it properly. I have already populated .sqlite database (MTrader.db) and i want to connect it to my swift project and load the data from the database into spinner. I tried...

Improving work with SQL DataTime

I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

Displaying MySQL results in a single table

I created an interface in which the user can choose which results are going to be displayed. The options selected by the user are saved in an array, so the MySQL query goes through the different elements of this array. So I want to display the results in a single...

Run various queries based on a combo box selection

I have a form where a user will select a value from a dropdown. Based on this selection, I would like a series of queries to execute. For example, if a user selects 'A', then queries 1, 2 and 4 would execute. If user selects 'B', then queries 4, 2,...

sql script to find index's tablespace_name only

Trying to find the specific tablespace names that were created for indexes only. I don't want to see the tablespaces names that are already used for tables. Something like below, but i couldn't have it working due to syntax error. Can someone fix it? select tablespace_name from dba_indexes where tablespace_name...

SQLite: Individual tables per user or one table for them all?

I've already designed a website which uses an SQLite database. Instead of using one large table, I've designed it so that when a user signs up, a individual table is created for them. Each user will possibly use several hundreds of records. I done this because I thought it would...