forms not behaving. 1

[abbottboi]

Hi there,

I have a 2 tables and 2 forms and while the tables are behaving the way i want them too.. ie. with the validation tables relationship etc.. when i try to use the forms to enter the same data there's no pull down menus etc.

Also, when i type in the id in the form i would love the name to auto populate.. any ideas on that relationship?

thanks

 

[scking]

Your explanation is not clear. Please clarify or you will not get many responses.

---------------------
scking@arinc.com
---------------------

 

[JoeAtWork]

there's no pull down menus
Place a dropdown control on the form and that should start a Wizard that you can use to configure the dropdown.

type in the id in the form i would love the name to auto populate
In the AfterUpdate event of the textbox where you type in the ID, populate the textbox that has the name using a DLookup function, something like:

txtName = DLookup("Name","TableName","ID = " & txtID.Text)

 

[abbottboi]

k, i have two tables, one for prospects and one for donations(transactions)

now. since i'm setting this up for someone who won't be that computer savvy so i created two froms that are linked to the two tables. This way the person entering data will not have to be in the tables but only the forms.

Since donations are based on a prospect, i have my donorID field fromt he prospects table in the donations form so that when i pull down the donorID or enter it, i would love for it to populate the fields Fname, Lname automatically from excisting data in the prospects table. Each donation will have a prospect assigned to it.

 

[abbottboi]

JOEATWORK

For the second part (autopopulate)

my field names are first_name and last_name and the table containing that info is called Propects

Could you write that bit of code again using that thanks.

Also, should my new autopopulate fields be fields in a new table or can they be fields from the existing table?

 

[JoeAtWork]

Actually, I've figured out a different way.

On your form, place an unbound textbox and name it "txtName". Set it's Locked property to Yes so users can't edit it (should be read-only).

Place a combo-box on your form, which should start a Wizard. Go through following steps on the wizard:
(1) Choose the first option "...look up values in a table or query"
(2) Choose your Propects table as the table that provides your values
(3) Choose which fields you want in the dropdown. You will want your ID field, first_name and last_name
(4) Next step lets you say how to sort - I would guess you would sort by last_name
(5) At this step you get a preview of the list. The "Hide key column" should be checked by default
(6) On this step choose the option "store that value in the field" - and choose the foreign key to the Prospect table that you have in your Donations table
(7) Last step is just to give a label to the drop down

In the properties of the combo-box, make the Name="cboProspect".

Now you need to add some code for the form's Current event, the Dropdown's AfterUpdate event, plus a custom subroutine for displaying the name, as follows:

Private Sub cboProspect_AfterUpdate()
    DisplayName
End Sub

Private Sub Form_Current()
    If Me.NewRecord Then
        txtName = ""
    Else
        DisplayName
    End If
End Sub

Private Sub DisplayName()
    If Not (IsNull(cboProspect)) Then
        txtName = cboProspect.Column(1) & " " & cboProspect.Column(2)
    End If
End Sub

 

[abbottboi]

This worked great, the thing is that this way i can select the persons name from the combo box and it filles the donor_ID field and the first name and last name. which is great.

The small modification i would be awesome is that i would like to select donor_ID and then populate the first name and last name fields.. this would be more practical.

Could you change the code a little to accomodate that? thanks so much for all your help!

 

[JoeAtWork]

You really shouldn't store the first and last names in both the Prospects and Donations tables. That breaks normalization rules.

If you need a report or form that shows both the names and the donation information, you should base it on a query that joins Prospects and Donations by donor_ID.

 

[abbottboi]

Hi there,

It doesn't need to store those names at all. It would just be used as a reference to ensure that i am entering the donor information with the right ID.

I have reports that pull information based on ID. this is how i had planned it.

what you think?