Automatic Address Entry

[yari130]

Hello, I am new at this and wandered if someone could help me with a little problem that I can only figure out part way.

I am helping out a charitable organization by setting up for them a small Access database that would print receipts for donations we receive. I have a Donors table with and would like my receipt entry form (which will store the information in receipt table) to have one list combo box (for the donorID field) for which it would list a few columns of my donors table, and when I choose a specific donor, their first name, last name and all the address fields would automatically be seen. I am able to only get one field to work like that but not the rest. I looked through the Northwind database that comes with Access and tried to follow what is seen in the orders form (that is exactly what i want but without the shiplabels) I looked at the Me!coding in VBA and trying to apply it, but I have a feeling that I am missing something.

Anyways, if anyone can help and point me to the right direction I would really appreciate it. We need to send out almost a years worth of receipts by next week and right now I am using combo boxes for each field and have to check the address with the names.

Thanks and hope to hear something soon,

Yari.

 

[kphu]

Hi Yari,

What your seeing in the northwind db are records from the orders table, eahc of those fields are linked via the orderid.

If your trying to create a form that inputs data into the other fields based on another field that has data then you'll need to do some vb coding behind it.

I can help you with this tommorrow as I'll be leaving for the night.

Regards,

Ken


Currently seeking for position around Boston, MA. kenphu@yahoo.com

 

[cghoga]

This is just a suggestion, but I would probably look at using a subform for what you're trying to do.

You would have 2 tables, Donors and Receipts.

In the Donors table make sure you have a unique ID.
In the Receipts table make sure you have a unique ID, and
that you create a foreign key and set its format to Number.

Now, create a main form and make its record source the Donors table.

Drop a subform on the bottom of the main form, choose the
Receipts table to populate it. Make sure to link the main form and subform, by linking the unique ID in the Donors table with the foreign key ID in the Receipts table.

Now you should be able to open the main form and in the receipts subform enter the donation amounts, etc. for the records in the Donors table.

Hope this helps you.

 

[yari130]

Ken - I was looking into the vb programming and actually got something going but I have a feeling that something is missing. I looked through the relationships and all, but can only get for example:

I enter date, (the receipt number is auto), the amount of the donation, then when I click on Donor ID and choose the donor, I get only the Last Name updated and nothing happens to the others. I am using the same Me! function that Northwind shows within the orders form in vb.

Thanks for your help, it is really appreciated.

cghoga - I thank you for your input but I had already thought of using a sub form for receipts as you had put it. My problem is that if I already have 1000 donors, it would be very tiedious to look for a specific one and then enter the receipt info. That is why I prefer the Northwind version, I can open the Donor ID in the Receipt form and have a combo box showing a few of the fields side by side for which I can be sure I choose the right Smith (since I will have the address within the combo box)and rightaway all that person's info will show up.

Thank you anyways,but if you can convince me of an easier way, I'll gladly look through it.

 

[kphu]

hi Yari,

I took a look at northwind db again and noticed that what you were referring to happens only when your adding a new record. Its basically the vb code in the After_Update event that controls the values of the other fields.

I also noticed the SQL script on the rowsource that looks up the values for the other fields and the After_Update event VB code places the valid information.

The Before_Update just checks to make sure that there is a value in the Customer ID. If it is then it tells you to select an ID from the drop down list.

There is a relationship that was defined as a 1 to many from the Customer Info table to Orders table. This relationship was used in a query to allow the customer info to appear within the forms.

If your database isn't large and confidential then send it to me as a zip file and I can have it back to you in a little bit. If not then send me an email and we'll work it out. It'll probably take some explaining to get you on the right track.

kennethp@webmpt.com




Currently seeking for position around Boston, MA. kenphu@yahoo.com

 

[VOGEL24]

This is the simplest way that I could find to do this.
Your Table Needs to have the fields that you are wanting to "fill in". Also combo box fields you are "filling in from" have to have the same propertys as the table fields.

In DonorID AfterUpdate

Insert Code:

Me!NameOfYourField = Me!DonorID.Column(1)
Me!NameOfYourField = Me!DonorID.Column(2)

'You can have several of these based on your Combo Box'

'Column numbers are from your combo box columns
Column After DonorID is Column (1)'

I have used this in several applications and is simple.