Please help!!

[nkk]

Hey guys,
I'd like to create an access database for a vaccine clinic I am managing in a large slum here in South Asia.

So basically I want to create a form that auto-populates. Say a baby comes in: I open a new form, assign a unique id (probably the vaccination card number), enter the child's name, bio data and vaccine received today (say dose 1 of diptheria). The form then automatically stores all the information in a table. Next time the child comes in I simply search by the id and the form opens up autopopulated (with the data that was filled in at the dipth 1 visit). I just verbally confirm the name/address with the mother and then fill in/select that on this date now dose of 2 of diptheria is registered. The form automatically updates the table etc. etc.

At the end of the month I want to do some basic data analysis on the table. I want to see for example, what the average deviation in age is for each vaccine. Dose 1 of dip is supposed to be given at e.g. 2 months of age but if the average age that the child comes in for it is 3 months then I'm concerned.

I can manage the data analysis part by setting up queries (I'm pretty new to access, I only know how to create simple queries and stuff) but I have absolutely no idea how to create such a form - i.e. one that autopopulates a table, is blank when needed (i.e. for a new child) and in searching for an id will autopopulate if the child is on a followup visit..

Please help!!

[nkk]

So a quick addition:

so far I've created a split form and as I enter the data it automatically stores it in the table below the form. Also before creating the split form I created a table with a bunch of fields entered and used that table to create the form.

So now say a child comes in on a return visit, I want to put a search box on the split form so that I simply search the child's name or by his/her unique vaccination card number and the form autopopulates with all the data previously entered (possibly bio data and vaccines already administered). How would I create such a search box?

Thanks so much!

[timothybard]

To handle situations like this, I use a combobox in the header of the form.

For this purpose, the row source of the combobox would be a SQL statement that selects the id from the childrens' table.

The On Change Event of the combo box would have something similar to the following:

Dim s1 As String
  s1 = "Id = " & Me!Combo1.Column(0)
  Me.Recordset.FindFirst s1
  End If

When you select the ID from the combo box, the code will then search for the first record that has the ID and then display the data for that record on the form.

This assumes that you have the record source of the form to be the table with all the data of the children.

[nkk]

Hi timothy,
thanks so much for the help! i'm still a little confused:
1. how do i make the row source for the combo box the id from the children's table?

2. i'm not sure i understand what vb syntax is/how it works..i'm very new to access, in fact this is my first time using it lol. is it the same as SQL? if i make the form through query design can i now just make a combo box in sql?

thanks!

[timothybard]

First of all, what version of Access are you using? All versions of Access are capable of doing what you are trying to do, but the user interface of of Access may be different depending on the version you are using and I want to give you the proper instructions.

When working in Access, you are using two programming languages. One is called SQL and the other is VBA. SQL is the language that is used to retrieve, store and delete data from the database; VBA is the language MS Access uses to change to properties of the forms and objects on the forms. Both SQL and VBA can do more than what I mentioned above, but those are the basic differences. You are able make VBA run statements in SQL; when you do that, VBA basically passes the SQL statement along to SQL and SQL performs the action.

Here is an overview of what you want to do using MS Access 2002:

1) Create a new form (or edit an existing form) in design view.
2) Add the form header and footer. Right click on the part of the form that says Detail and select Form Header/Footer
3) Click on the Combo box icon on the tool bar and create a combo box in the header.
4) A wizard should pop up wanting to help you create the combo box. Click Cancel; we will set everything up manually.
5) Right click on the combo box and select properties.
6) The properties should appear for the combo box. On the data tab, click on the box next to Row Source.
7) A button with three dots should appear next to the box you clicked. Click on the three dots.
8) The SQL builder should now appear along with a list of all your tables. Double click on your children table.
9) Click close to close the list of tables. You should now see the children table in the SQL builder.
10) Drag the ID from the Children table to the bottom portion of the SQL builder. In one of the columns at the bottom, the field should show ID and the Table should show the name of the children table
11) Close the SQL builder
12) The Row Source property of the combo box should now be updated.
13) Click on the Event tab on the combo box properties.
14) Click on the event On Change
15) Click on the three dots again and choose Code Builder. The VBA screen should now appear.
16) Between the lines Private Sub combobox1() and End Sub, paste the code I gave you earlier. Please note that I am assuming combobox1 is the name of your combo box; if your combo box has a different name, then update combobox1 to the name of your combo box.
17) Lastly, make sure the Record Source of the form is the children table. To do that, view the form in design view, click on the box that is above the ruler on the left and to the left of the ruler on the top.
18) The properties box should now show the properties for the form.
19) Select Record Source from the Data tab of the form properties.
20) Click on the three dots.
21) Again, drag the children table from the list to the SQL builder.
22) Drag the * from the table to the columns at the bottom.
23) Close the SQL builder
24) Save the form and you should be done. Please note that you will need to add the fields to the form so they show up.

I believe those instructions should work; let me know if you run into any issues.

[nkk]

Hey Timothy,
Thanks again for all the help and the really thorough steps!! I'm using Access 2007 but I think the steps are the same and I got it set up. The problem is:

so all the IDs come in the combo box but when i click on one the table doesn't autopopulate. Also, I'll have about 10,000 IDs so would the combo box method maybe get too messy?

[nkk]

can i possibly email you my database?

[timothybard]

Feel free to e-mail me. I will look over anything you send.

As far as the number of records in the combo box, you can start typing in the combo box and access will look for the first record that matches what you type. Also, you can use a text box instead, which might work if you use only numbers. I use combo boxes to make sure that a user doesn't have trouble looking for a record due to misspelling; but that shouldn't be an issue with numbers.

[timothybard]

Feel free to e-mail me. I will look over anything you send.

As far as the number of records in the combo box, you can start typing in the combo box and access will look for the first record that matches what you type. Also, you can use a text box instead, which might work if you use only numbers. I use combo boxes to make sure that a user doesn't have trouble looking for a record due to misspelling; but that shouldn't be an issue with numbers.