Getting started with forms...

[Elliott3]

Hi,
I am somewhat familiar with Access(but not the programming side). The first thing I would like to figure out is how to create a window(form) with two buttons on it, one to add a new record to a single table and the other to close the window.

For argument sake, just say i have a table with a first name field, second name field and an id number auto-incremented. The window should include only three labels, three text boxes, two buttons and the title bar.

Can someone tell me either how to do this or where i could get a tutorial to show me this. I have seen a number of tutorials already but they did not show me how to do this.

Any suggestions are really appreciated!

CES

 

[BNPMike]

Use a form creation wizard. First do a query with the fields you need. You can then base the form on the query rather than the table. To add a new record you just set the appropriate form property and it will automatically start-up on a new record. So you don't need a button for that. To close the window (if you really need to duplicate Windows built-in functionality), use a command button wizard. Choose Form Operations...Close Form. It'll give you a little exit door picture free of charge. Access...wonderful

 

[Elliott3]

>>To add a new record you just set the appropriate form property and it will automatically start-up on a new record

Ok, I have a couple questions.

1. Is it better to base a form on a query rather than a table? If so, what is the benifit? (is it just so you are not in direct interaction with the table?)

2. I'm not sure I understand what property you are talking about in the above?

3. I have chosen not to show the 'navigation buttons' and the 'record selectors', for design reasons.

4. how do i look at the code that is generated? and is vba a little different than vb or can you use vb?

sorry for the mouthful lol

Craig

 

[medic133]

In the database window, click on Forms under the Objects heading on the left of the database window. Then double-click "Create form in Design View". A new form will open up. Double click the dot in the upper left corner of the form near where the word "Detail" appears. This will open the properties window of the form. Click the All tab of the properties window and click once in the Record Source field. Now click on the small arrow in the right of the field. This will drop down a list of all queries and tables on which you can base your form. Select the table that you mentioned above. This will open a Field List. (This list can also be displayed any time after the Record Source has been defined by clicking the Record Source button on the Form Design toolbar.) Now, simply click and drag the desired fields from the Field List to the design grid of the form. This will automatically create labels and textboxes for each of the fields. (Although I don't choose to use wizards, you could click "Create form by using wizard" in the database window and I think it would walk you step by step through this process. The only bad thing is you don't have the opportunity to immediately customize some things right away, although, if you do use a wizard, you can always go back and customize them later).

Now for the command buttons and title bar. The title bar is actually a label. Make sure the Toolbox toolbar is displayed when designing a form and click on the Label button of the toolbar (it looks like this: Aa). Now click in the disign grid of the form where you want the label to appear. A cursor will appear in a very tiny label and you can type what ever you want in the label. When you are done typing, just press enter on the keyboard. You can edit this label and change any of its properties through its property window. To access this window (as well as for any other objects on a form or report), right click the object (in this case, the label) and click Properties. If the properties window was displayed this will make it go away. Just right click on the object again and click Properties again.

As for the command buttons, I would encourage you to use the wizards the should open up after completing the following steps. They will allow you to do what you want in the above example, and then once you feel up to writing code, they provide a wonderful example on how to write it. In the Toolbox toolbar, click the "Command Button" button (it looks like a command button). Then click in the design grid where you want the command button to appear. (If you don't click exactly where you want your objects to appear, you can always drag them to the appropriate place or set the specific location via the Properties window.) This should launch the "Command Button Wizard". For your command button to go to a new record: click Record Operations under Categories and Add New Record under Actions, then click next. Now choose either to display text or a picture on your command button (it makes no differance what you choose, again, you can modify it all in the command button's Properties window) and click next. Now enter a name for the command button (it makes no differance what you name the button other than avoiding names that are the same as key words for Access like Name, Date, Record, ... By convention a command button's name should begin with cmd followed by whatever you want such as cmdnewname (it's kinda OK to use the word name here since it is only part of the name)) and click Finish. That should take care of that command button.

As for the command button to close the form, follow the same instructions as above. When the wizard launches, select Form Operations under Categories and Close Form under Actions. Follow the directions as above for the remainder of the process. That should take care of everything in your example!! As you might have guessed, more complex operations are merely modifications of these simpler operations. Just for fun, you might want to take a look at the code you created with the wizards. On the Form Design toolbar, click on the Code button to display the VBA code. Alternately, you can click on Tools/Macro/Visual Basic Editor to display the same thing. If you are feeling really daring, in the region where you see Private Sub Commandbuttonname_Click() (Commandbuttonname is the name of your command button) for the command button you use to go to a new record, look for the code DoCmd.GoToRecord ,,acNewRec. Under this code enter the following where controlname is the name of one of your textboxes that does not receive the focus (receiving the focus is when the textboxes gets the cursor): DoCmd.GoToControl "controlname". This tells the program to place the cursor in a particular textbox when the form advances to a new record. I only mention to do this to give you a little taste, and I do mean little, of what you can do with coding.

Hope this wasn't too long and hope it helps!!!!

 

[medic133]

Craig,

I just noticed your response to BNPMike. I think I've answered most of your questions except for your first question. Personally, I don't think basing a form on a query is any better than basing one on a table. When you base a form on a query, you actually are basing your form on a table, because a query is just the table with information you don't want inaccessable. I always thought a query was essentially a spreadsheet with information from the table on it, but when you enter data in a form based on the query, you actually modify data in the table.

Either way, feel free to base a form on a query or a table. They both work very well, and, perhaps for the seasoned developer, a query may be more efficient to base a form on, but for us rookies, I haven't ever noticed a differance!

Good luck!!

 

[Elliott3]

Thanks for the fast and very descript tutorial medic133, i really appreciate it.

Just to let you know that if you have any suggestions that involve programming, i am all ears as I have programmed in VB C and other languages.

The one thing I am still having a problem with is adding the record(which i know how to do using the navigation buttons that access provides but would like a different method). I now have a window that contains all the text boxes and labels i need. As well as the close button. I have turned off the 'navigation buttons' and 'record selectors' for design purposes.

So how do i go about adding a new record? Is it better to add a button then manually add the code? If so could you give me an example? If not how do I go about it?

CES

 

[Edski]

Elliott,
Tables vs Queries?
Mike would tell you to use a query as the source of all your forms but I suggest making a decision on a case-by-case basis. I would recommend using queries for complex forms, especially ones that are based on many tables.

VB vs VBA? Here's a link that may offer some advice.

http://www.softwarepolish.com/vb/vbavsvb.htm

 

[medic133]

Craig,

Regarding adding a new record: there are a host of ways in which you could do this. One is with the wizard. Create a command button as you did before by clicking on the Command Button button in the Toolbox toolbar, then clicking in the design grid where you want the button to appear. The wizard then launches and select Record Operations under Categories and Add New Record under Actions and click Next. Then choose either a text or a picture for your button (you can modify the text to say anything you want, including putting an ampersand (&) in front of a letter you want underscored to be used in combination with the Alternate key as a keyboard shortcut) and click Next. Choose a name for your command button and click Finish. By clicking on this button, you will add the information from the textboxes on the form to the table as a record and move to a new record. If you don't move to a new record, make sure that your form is bound to a table. Switch to design of the form and double click the square in the upper left corner to open the form's Properties box. Make sure under the All tab Record Source has a table or query name in it. If not, click on the arrow to the right of the field and select one. Then click on the textboxes and look in their Properties box and make sure there is something in the Control Source field. This is the field that the textbox is linked to within the table or query that the form is linked to. If not, again click on the arrow on the right and select a field.

If you want to try code rather than using the wizard (code gives you much more flexibility) try the following. In design view of the form, click the Command Button button of the Toolbox toolbar and then click in the disign grid where you want the command button to appear. When the wizard launches, click Cancel. Then right click on the command button and click Properties. This opens the Properties box for the command button. Here is where you can change or enter the caption to appear on the command button as well as many other attributes. For information on any of these attributes, place the cursor in the field next to the attribute you want information on and press F1. This should display the help menu for that attribute. Now, scroll down in the command button's Properties box until you see On Click. Click in the blank field next to On Click and then click on the ellipse (...) to the far right. This will open a small box called Choose Builder. Here you can click on the Expression Builder if you so choose which many peoply swear by. I've never found it beneficial, but I'm not real bright! Rather, choose Code Builder. This will launch the Visual Basic Editor where you can enter code. In the large window in the right half of the screen is the area you will enter the code. Above this window you should see two smaller boxes with drop down arrows to the right of each box. These boxes should be immediately below the toolbar. In the left smaller box, click on the arrow and select the name, not the caption, of the command button you created. Make sure Click is selected in the right smaller box if it isn't already by clicking on the drop down arrow and selecting Click. This puts your cursor below text that says Private Sub commandbuttonname_Click() and above End Sub. The Private Sub denotes the beginning of the code (I believe referred to as a module) for the command button and the End Sub denotes the end. Where your cursor is type the following:

DoCmd.GoToRecord acDataForm,"formname",acNewRec

Make sure to include the quotes. That is all that is needed to move to a new record. The focus moves to the textbox, or other control (textbox, command button, etc.), with the lowest tab index, I think. To check the tab index for any control, look in their Properties box. You can change them to occur in any order and even remove them from the order. Alternately, after the above line of code you could put:

DoCmd.GoToControl "controlname"

Again, make sure to include the quotes.

Just for fun, determine which of your textboxes is the last in the tab order. In the Properties box for that textbox, this time find the On Lost Focus event and click in the field to the right of it and then click on the elipse. Choose Code Builder from the Choose Builder window. When the Visual Basic Editor (VBA) opens make sure your textbox name is in the small left box and LostFocus is in the right small box. A similar Private Sub appears in the large window. Enter the above code, making sure that if you use the GoToControl statement, you don't set the focus back to the same field you are working with. (For some reason, Access won't let you set the focus to the same control that just lost the focus. To do this you must first set the focus to another control and then set the focus where you want.) Now, after you enter data in the textbox that you have just entered the code for and then either tab out of it or click out of it with the mouse, the code that you entered will be executed and the old record will be added to the table and a new record will appear for data entry.

One thing I forgot to mention, which should have happened, but may not have. When you have code for an event for a control, make sure that [Event Procedure] appears in the field next to the event in the Properties box. I've spent many a frustrating time trying to figure out why simple code doesn't work, only to find [Event Procedure] wasn't selected in the Properties box.

These are just three ways in which you can move to new records without using navigation buttons. You can take it from here to figure out many other creative ways in which you can accomplish the same thing depending on your needs. Hope this helps, and best of luck!!

Phil

 

[BNPMike]

My recommendation to use queries is not relevant to this discussion, to be fair, and has no impact on your application. It's just that I like to preach good practice on this channel. Using a query places a level of abstraction between your forms etc and the underlying base tables. This may help in the future when you change the design of your database.