Functionality for MS Access Forms

Michelle Dabney
Michelle Dabney used Ask the Experts™
on
I am not an MS Access person.  I have a 3800 row spreadsheet that I want to get into MS SQL, however, I want to see if the vision is correct before I go to the trouble.  I have the spreadsheet imported into Access.  No problem.  I created an entry form to add new ones.  I believe.  I would like the form to do a couple of things and am not sure if it's possible.  What I'd like to have happen:
User opens database and gets form.
User enters an account number.  If the number is already in the table, the form is auto-populated with the information from the table allowing the user to change and update any of the fields, as needed.  If the account number is not in the table, the user completes the form and the record is added to the table based on a field that is also populated on the form.  An example of this is account number 1234 is not present in the table.  The form remains blank.  All information is entered into the form.  One of the fields is a store location.  Instead of adding the record to the table at the bottom, it append the record to its specific location.  So if we have locations 1-12 and account 1234 is serviced out of store 5, the record does not got to the bottom of 12, but is inserted somewhere with the 5's.

Is this even possible? And if I get it to work in Access, can I import and have the same functionality in MSSql?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
All quite possible in Access, which you would use as a "front end" to mySQL.   The data would be stored in mySQL and Access would talk to it.

 mySQL in of itself is just a data store.   It doesn't have forms, reports, etc.

Jim.
Michelle DabneyIT Director

Author

Commented:
Great!  Is there a resource to assist with the field functionality that you or anyone else may know of?
Most Valuable Expert 2012
Top Expert 2014

Commented:
Instead of adding the record to the table at the bottom, it append the record to its specific location.  So if we have locations 1-12 and account 1234 is serviced out of store 5, the record does not got to the bottom of 12, but is inserted somewhere with the 5's.
One thing to note with databases is that the actual order of insertion into a table is irrelevant. Instead of trying to push that record into a table in the middle, you just insert it and let the database engine determine where it's stored. When you need to view it, you query the database using a Sort Order to order the records the way you want.

As Jim said, everything you want is doable in Access, and the Experts will be glad to help you build this. We won't build it for you, of course - that's your job - but you'll find a lot of help here.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Michelle DabneyIT Director

Author

Commented:
Thank you.
NorieAnalyst Assistant

Commented:
MIchelle

Most of this functionality is built-in to Access forms, all you need to do really is use the table with the data as a record source for the form.

You might need to add something to locate records based on the ID entered.

The only thing that won't happen in a database is the records being 'grouped' together in a table, when you add a new record to a table it's not added in a specific row/position in the table.

You can however return the data from the table grouped/ordered etc. however you want using queries.
John TsioumprisSoftware & Systems Engineer

Commented:
You need to stop thinking Access as the bulky cousin of Excel...
Excel is a spreadsheet ....think it as a piece of paper that you write down with your pen...it just has each line separated in those nice little cells...pretty much as those pink graph papers we had in school...
Access is a database...forget about the paper...is gone...think it a bit as your locker...a big locker and you have quite some boxes where you can put a lot of papers...or even smaller boxes and so on...you can arrange them anyway you like...you can push them up - down ...you can toss them away but they are not  a piece of paper...
Distinguished Expert 2017

Commented:
You have SQL Server a a topic.  Does that mean that the BE is going to be SQL Server or will you be using Jet/ACE?  3800 is a trivial number of rows so there is no pressing need to use SQL Server.  The design of the app will be affected to some degree by the answer to the question regarding the BE.

I design all my applications to be efficient as client/server apps so whether the BE is Jet/ACE or SQL Server, the app is always designed to operate efficiently with SQL Server and this means that when the time comes to upsize, I can almost always do it in less than an hour.  The heart of the design is forms bound to queries and the queries have criteria that severely limits the number of rows returned at any one time.  This actually fits right in to your request for how the form should function.  In some cases, I create complex search forms with multiple variable options.  Since this will normally result in more than one row being returned, these search forms open a list form where the user can drill down to the particular record he wants.  This form is not updateable.  To get to the update form, the user double clicks on the specified field and that opens a single record form, possibly with subforms.

For all the other situations, the edit form will have an unbound combo or text box in the header that will be used to find the record the user wants.  If the key the user is looking for is not in the list, then he can simply type into the empty record to create a new record.  For these forms, the RecordSource query has a WHERE clause something Like:
Where ClientID = Forms!myform!cboClientID
This makes the form open to a new record since the user has not yet entered anything into the combo.  In the click event of the combo, is a single line of code:

Me.Requery

This forces the form to find the single record that is requested.

So, not only does this method work well regardless of the BE, it also requires only a single line of code.
Michelle DabneyIT Director

Author

Commented:
The BE is MySql.  The keeper of the data could just enter into a datasheet view, as he does now with Excel.  Our issue with the Excel is the application that needs the data is hosted by our B2B.  They use MySql and MS SQL.  He enters both in the Excel spreadsheet for an internal process and on the B2B site. With the data being in a table, as I have it now, they can just grab the data instead of someone manually having to update on their site.  Now that I have it in a table, I'm looking for a simple way for the user to update the table.  It is modified in some manner several times per day.

Using the spreadsheet, he can CTL+F, enter the customer number and be right at his record to change what needs to be changed.  He can also delete the row if the customer is no longer part of the program.  Or he could search for a store number, add a row and enter the new customer's data.  He's comfortable with his process, but it's a lot of manual work.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
You can do similar with Access, but datasheets are limited in how much control you can exert over them.

   Instead, you would use an Access form in continuous form view, which can be made to act like a spreadsheet (i.e. up /down arrows move you up down rows).   But with that said, Access is different.

   From everything you've said though, there would be no issues in accomplishing what you've said.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial