Creating a form to query and update database

**pizlam** · 04-28-2010, 11:17 PM

Hello all,

I am very new to Excel and am very surprised about its power ( I thought the best it could do was auto sum and graphs two weeks ago!)

I am now trying to develop a simple application for a small start up company that I want to start that is engaged in transporting University students back and forth from the airport. The application's first release will be in Excel and will aim to store and update customer, payment, and reservation information. It will also print a receipt and email it to the customer.

I have attached a horrible hack of what I am trying to do. What is missing is a form to query the reservations and to update information in it (we have multiple steps in the reservation life cycle before it is fulfilled).

The main interface is a sheet called ABS.
The "database" is called reservation
The receipt is called Receipt
The customer and payment info "database: is called customer info
Info for dropdowns found on the ABS sheet is found under dropdowns sheet
The business layer sheet is doing a lot of the lookups in the database (here is where it gets really hacky)
Ambassador Smart System (2) is there just in case I mess up ABS I have another copy (another hack)

OBJECTIVE:
To create a form that will lookup and update values in the reservations sheet that will query the database based on multiple criteria: cell phone number, reservation number, first and last name, status of reservation.

I have 45 columns in the reservation "database" and I am looking to combine the solutions that I found here both by Leith attached:
1. "Database Form"
2. "Search Database"

Your help is greatly appreciated...and if you have other ways to do what I am trying to do...I am all ears.

PS...I am not really liking the fact that the database and the application are together...I would love to hear any ideas that will allow multiple people to work at the same time on the data as well as protect the data from an application crash.

**davegugg** · 04-29-2010, 11:22 AM

Well, I can't look at two of your excel workbooks because I don't have 07. However, I can tell you Access is MUCH better suited for what you are trying to do.
Advantages to using Access over excel for your project:
1. You can split out the database and application so multiple people can work at the same time.
2. Simpler VBA and less of it.
3. Access actually sets up databases, so no need to "create" one out of excel.
4. Easier to use querys in Access then write your own in Excel's vba.

If you are set on excel then ignore this post, however, you may want to simplify your request if you want help. Generally, working in multiple downloaded workbooks can be confusing and unnecessary. Otherwise take a look at Access. If you are unfamiliar, a good place to start is Microsoft's training:
http://office.microsoft.com/en-us/training/default.aspx

**pizlam** · 04-30-2010, 03:05 PM

Thanks dave,

I agree with you that access is a better choice in all likely-hood. however, I found it to be less user friendly than Excel. I have a deadline for this database to be ready for May 3rd (we are expecting about 300 reservations on the database due to students leaving for the summer break). Thereafter, we can look at an access based solution.

**royUK** · 05-01-2010, 03:11 AM

If you want to use Excel as a database then you must lay out the data in the correct format,there should be no empty rows or columns within the data as you have.

I usually have the header row in Row 5 giving some rows above for adding say Subtotal formulas etc

**pizlam** · 05-02-2010, 11:05 PM

In the real database, I have no empty spaces...for the reservations tab, I have one empty row (row) two where I do an insert there.

its purpose is so that it is a constant place where the "receipt" page can lookup values before being printed and saved.

I am open to any other ways this can be done.

Thanks for the replies folks...really appreciate it.

**royUK** · 05-03-2010, 02:37 AM

You shouldn't have the insert row there,it will mean that you can't use autofilter, etc correctly. Add data at the bottom of the database. See this MS page

The database form example is from my web site