Help! Access Registration Form

[AccessNewb11]

Hi all,


Thank you so much for reading my post. I am pretty new to Access, although I have been familiarizing myself quite a bit with the basics.
I work for a college. We conduct community classes for Law Enforcement. I am trying to create a database that I can use to register people for classes. I will run queries to act as class rosters.

Anyway, I am stuck. I wanted to create a form in which I can type (or choose from drop down) the agency an officer is from, and it would auto populate the address, county, state, and zip. Then I wanted to be able to type (or choose from drop down) an agency contact name, and it would auto populate their phone number and email. Then I wanted to be able to type in the class they are registering for, the person who is processing it and any individual notes on the officer I may need to type in.

I wanted all of this on the same form.

I have tried creating a combo box in multiple formats and I can't get it to do exactly what I want. I have attached the database here for your review (with fake data).

You will see there is a form called "frmTEST". In that one, you can choose the agency, and the county and address will auto populate. The problem is, I can't get it to do that on ONE universal form. For instance, the form "frmAgencyInfo" is also successfully auto populating, but I can't add the other information I need to add to that form. For instance, I can't add the agency contact, and have that auto populate as well.


In review, I want a registration form where I can enter Joe Schmoe into the form and register him for the class. To avoid entering the same information multiple times, I wanted parts of the form to be auto-populating.
Help?!

NOTE: I created subforms because I thought I could use those, but that wasn't working to auto populate either.

Thank you so much!

~Access Newb in California!~

[Bob Fitz]

Can you post a copy converted to A2003 mdb format

[burrina]

Hi.I did download your db but it needs some work. Allow me to make a few suggestions. Access Naming Conventions. It seems to me that most of what you are trying to do could be combined into one form with either subforms or tabs with subforms in them. Could you please explain a little more your thought process in the design and what is supposed to happen,why and when? Remember, we are not familiar with your Schema, you are.
tblAgencyInfo
qryAgencyInfo
frmAgencyInfo

[AccessNewb11]

Bob Fitz, for some reason it is not letting me save in that format. Usually it does, I'm not sure what the problem is.

Dave, yes I do need to change the names of some of those things, I agree. Thank you for the suggestion.

Yes, I would like to combine what I am doing onto one form. This is what I want:

John Doe wants to register for a Police Update class. I open the database and load the form. First I select (from a drop down box), the agency John Doe is from. The agencies' address, city, zip, etc. auto populates as soon as I select the agency. Next, I select the agency contact person and their email and phone auto populates. Then I can fill in all of the individual information for John Doe, such as the class he is taking, date of the class, etc., which would not be auto populating.

From my LIMITED understanding of Access, I could use subforms, but that won't allow me to do auto populating, correct?

Thanks.

[AccessNewb11]

I think I need help in the way I am setting up the combo box. What to select and if I need to save my values and stuff like that...

[John_G]

Hi -

A question - when you autopopulate the fields in the form, is that data just for display on the form, or is is being saved to a table as well? If you are saving the data to a table, you shouldn't be doing that - it creates redundancies and violates the rules of data normalization.

That said - to autopopulate form fields after selecting from a combo box, you will need to use VBA in the After Update event of the combo box. You will need one or more statements that look like this:

me![formfield1] = DLookup("tablefield1","tblLookup","Table_ID = " & me!ComboBoxValue)

replacing names in italics with names in your database.

for example, for to populate the Agency Address by selecting Agency from a combo box, you might have something like this:

Me![Agency_Address] = Dlookup("Address","tblAgency","Agency_ID = " & me!cboAgency)

This assumes that you have a table (tblAgency) with data for all the agencies, and that the combo box lists data from that table as its values.

Do the same for the other data you need to retrieve/display

HTH

John

[AccessNewb11]

Oh John that's a good point. How do I know if I am saving the data or simply viewing it? I don't want to violate the normalization rules, that is important to me.

OK great, I am going to try a few things, THANK YOU.

[John_G]

Hi -

A form control that is not associated with a table field will show "Unbound" in form design view, or if you look at the property sheet for the control, the "Control Source" property will be blank.

John