Thread: [Basic Tools] An intro to making complex databases using simple methods.

This thread will be a multi-part thread showing some common database methods which are easy to implement into your design.

As, I go on in this thread, this database will become more and more diverse, all-the-while, I will be implementing simple methods to make the database easier to use, and giving it an asthetically pleasing appearance. In the beginning, I will be concentrating on function; I will also be cleaning up the appearance little by little with each post.

Part I - Your first VBA function (Requery)

I see requests for this all the time:

When I choose a name in a combobox, I want the rest of the fields to update with it.

How do I pull up records with a combobox?

etc. etc.

Well, if you're new to Access, this may seem like a danting task, but in reality, this is very, very simple.

In this segment, I will show you how to accomplish this. This procedure has only one line of vba code, and it will also show you how to run your query from a form control.

We're going to start with your basic demographic table:

Code:

Table1
IDnumber  - Text - Primary Key
FirstName - Text
LastName  - Text
Addr1     - Text
Addr2     - Text
City      - Text
State     - Text
Zip       - Text

You will notice a thumbnail below, it is the design view of the query and form with labels 1 - 4.

1. This is the form name (form1)
2. This is the combo box name (cboIDnum)
3. This is the builder for the after update of the combobox (cboIDnum)
3a. You will want to choose code builder when you click the 3 dots.
3b. A new window will open up. This is the VBA editor. Simply type ME.REQUERY then close this window, then save the form.
4. This is your query for the form, notice the criteria from the combobox from the form (forms!form1.cboIDnum)

The SQL view of the query would be this:

Code:

SELECT Table1.IDnumber, Table1.FirstName, Table1.LastName, Table1.Addr1, Table1.Addr2, Table1.City, Table1.State, Table1.Zip
FROM Table1
WHERE (((Table1.IDnumber)=[forms]![form1].[cboIDnum]));

In the VBA, I did a "me.requery". A simple definition is this..... the me part states that in this form (me), you want to requery (or reload - in this case with the criteria of the combobox)

That's all there is to it!

The sample database is below the thumbnail.

Part II - Your First Search Form - Unbound textboxes and command buttons

Added instructions: Trim and Nz functions, and basic concatenation "&"

Sometimes, it isn't always prudent to use a combobox to find a record. For instance, if you had a database of 10,000 names, you wouldn't want to scroll through a combobox to find the correct record.

In this exercise, we are going to combine the first and last name, and use an unbound text box to search your table, with the help of a command button.

We are going to redesign the existing query and basically rebuild the original form.

First of all, open up your query. Remember in the original query, we had the following fields:

CustID, FirstName, LastName, Addr1, Addr2, City, State, Zip

Well, we want to make this look like a traditional address label, which would include the three basic lines....name line, address line, and city state zip line. In a realistic database we would probably include phone, fax, and email, but for instruction purposes, we will keep it to the bare minimum.

In order to do this, we will create expressions based on the existing fields.

Expr1: (named FullName) we will combine first and last name separated by a space.
FullName: Trim([FirstName]) & " " & Trim([LastName])

Expr2: (named FullAddress) we will combine Addr1 and Addr2 separated by 2 spaces.
FullAddress: Trim([Addr1]) & " " & Trim(nz([Addr2]," "))

Expr3: (named CSZ) we will combine City, State, and Zip, with a comma and space between City and State, and 2 spaces between State and Zip
CSZ: Trim([City]) & ", " & Trim([State]) & " " & Trim([Zip])

Ok, what are these trim and nz functions? The Trim() function eliminates preceeding and following spaces from a string (text entry), and the Nz() function states that if an entry is null, it will then equal what you specify. In this case a space " ". The Nz() function can also return certain values for null numeric values. A null value means no data was entered as opposed to "" or 0. So for the trim function, if the data entry operator accidently entered a space after the first name, this will eliminate it.... also, if there was no secondary address, for the nz function, it will be shown as 1 space " ". The "&" concatenates the strings (adds them together)

If you have any question about string functions, make sure to visit my tutorial on string functions listed by name, it is stickied in the code and samples thread.

Once you're done creating the 3 expressions, save the query, and then you can re-open it and delete the fields, not the expressions. Now your query, which originally had 7 fields, has been simplified to 3 expressions.

Now it's time to redo you form (Form1). You can delete all of the fields/comboboxes on the original design. Now what we're going to do is insert a label, an unbound combo box, 2 command buttons, and the 3 expressions from your redesigned query. We'll break this down into 5 steps, make sure to open the thumbnail to see what we're doing.

1. Create a Label. On the thumbnail, on the right-most side you will see the number 1 as well as on the left side of the form. A label is just text, generally giving the operator more information on what to do, or what something is. Use the wizard and enter the text: "Enter Name to Search For"

2. Create an unbound textbox. Again, the wizard is on the right, and you create the textbox on the left. After you create it, you can delete the associated label, then you will want to right click on the unbound textbox and go to properties. Once there, click on the "other" tab and name it "txtFullName".

3. Create command buttons. We do not want to have the buttons do normal form functions, so after you draw the button, go directly to 3a

3a. Cancel the command button wizard. After you do this, if you left click once in the button, you can change the name (i.e. command21 to cmdsearch and command22 to cmdclearsearch)

3b. Add VBA to the buttons. Right click on each of the buttons after you have renamed them, go to properties, and then click on the "events" tab. In the "On Click" event, right click, go to build, and choose code. For the cmdsearch button, type in: Me.Requery(this reloads the form).....for the cmdclearsearch button type: Me.txtfullname = "", then hit enter, and then on the next line type Me.Requery (this last part sets the unbound textbox to "" and then reloads the form)

4. Open your query back up (Query1). In the criteria for the expression "FullName" type this: Like forms!form1.txtfullname & "*". Save the query.

5. Go back to your form (Form1). Click View, then Field List, then after the field list window comes up, drag each of the expressions to your form (FullName, FullAddress, CSZ)

Save your form. This is it! You now have your very first user entry search form. I know what your asking, "How does this benefit me?" Well in the next lesson, we're going to add a subform. The subform will be related to this basic search form (we will be re-implementing the custid). What is a subform? Well it could be a sales invoice, it could be a library book record, it could be just about anything that you want related to a certain customer. For now though, we know how to search the database for a certain record. In this case, for a certain person, and that person's ID number will come into play in the next lesson. Everything we've pulled up here will be represented by that ID number.

Again, the sample database is located below the thumbnail. Enjoy!

Part III - Creating your first subform (The beginning of a point of sale database)

Added Functions: Str(), Int(), Format(), Date(), DatePart()

In this lesson, we're going to tie in your customer search form with a very basic point of sale form. When you choose the customer, then you will have the option of adding information as to what the customer purchased. In order to do this, we will have to create another table for the purchases (table2) and relate that to the customer making the purchase (table1). We will also create an invoice number based on the date, the customer number, and the hour of purchase, all added together.

There will be 23 steps in this tutorial (don't worry, it's not as bad as it sounds)

1. Create a table in design view (table2) Include the fields that identify the purchase: invoicenumber, transactionNumber, CustIDFK, DateofInvoice, LineItemQty, LineItemInfo, LineItemPrice. InvoiceNumber will come from form1, which I will show you later, transactionNumber is an autonumber to keep each record unique, CustIDFK is what will relate to CustID in Table1, and the rest is the purchase info (Qty, Description, Price). Save as Table2

2. If you click on tools and then relationships, you will get the relationships window, add both table1 and table2 to the view. Now from table 1, click on IDNUmber in table1, and drag to CustIDFK in table2.

3. Then you will get a relationship window, click on Join Type.

4. We want the relationship to be one customer to many invoices, since a customer can make multiple purchases. It's a little misleading, but, choosing all records from table1 and only those that are equal in table2, means that when you choose a specific customer from table1, then it will only show the records from table2 with the same customer ID. Save your relationship.

4a. Base a query directly on table2, and include all fields (this will be for the subform)

5. Open Form1, then click on the subform wizard.

6. Draw a box for your subform (you will be resizing this later)

7. In the subform wizard, choose existing tables or queries.

8. Click Next

9. The next part of the wizard will ask you to choose the query (Query2 based directly on table2)

10. Hit the double right arrows to select all records.

11. Click Next

12. Click Choose From a List, as the relationship made earlier will define how records are chosen

13. Choose the default link between forms displayed (linked by IDNumber)

14. Click Next

15. Rename the subform to "Subform1"

16. Click Finish. Your Subform is now linked to the main form. Save your forms.

17. Open Form1 (Main Form), then click the textbox wizard.

18. Draw your textbox, go to properties, then the other tab, rename it txtinvnum, click on the data tab, then for control source, click the 3 dots, then expression builder.

19. For control source, type this:
=Trim(Str(Int(Format(Date(),"Standard")))) & Trim([IDnumber]) & Trim(Str(DatePart("h",Now())))
Let's start with this part "Trim(str(int(format(date(),"standard"))))"
working from the inside out...
date() means todays date - 6/26/08
format(#6/26/08#, "Standard") returns the numeric value for todays date "39625.00"
Int(39625.00) returns the whole number "39625"
str(39625) makes that value a string (text)
trim("39625") gets rid of any spaces, if any
So we have "39625", now we will concatenate(add) the customer id to the end "101a" giving us: "39625101a"
Next we will add the hour, for example 8pm = 20
Now() is the exact date and time, datepart("h",now()) returns the hour for the exact date and time.
Now our textbox will equal "39625101a20" which is sufficient for an invoice number. Save your form.

20. go into design view of your subform

21. right click on invoicenumber of your subform, go to properties, then click on the data tab. Enter for the default value, the value of the unbound textbox in your main form.

21a. the unbound textbox is named forms!form1.txtinvnum put that as the default value

22. right click on DateOfInvoice of your subform, go to properties, then click on the data tab. Enter for the default value, todays date

22a. the default value will equal date()

23. Run your form, then in the subform, hide the fields that autopopulate by sliding the ruler for each field to the left. Now when you see the subform, you will only see the fields in which you will be entering data, Qty, Description, and Price

There! You're done! You now have a main form with a subform which gets some of its data from the main form. The screenshots are below and in the next post, and numbered to reflect the steps listed above.

In the next lesson, we will be adding a combobox to lookup the types of items we can sell. Also, we will be generating subtotals and totals.

As always, the sample database is below the thumbnails in the next post due to attachment limits. Enjoy!

Part III - continued