Automatically fill the Remaining Feild

[xeen20]

Hi,
i am new to access and i am stuck with a issue, hoping u guyz could help me.
i could not update my fields with reference to the value in the combo box.

I have only 1 table and 20 columns.

I created a form with 19 feilds with control sources to the table columns and one with combo box, and i made contol source with the table column and rowsource with a query for the combo box. Combo box is showing the values in the table.
But...the prob is how can i fill few feilds in the form automatically depending on the value of the combo box.
Plz Help

 

[agehoops]

probably with a bit of VB. It depends what you mean by fill the fields. Do you want it to lookup information already stored and just show that record? Or do you want it to fill in new data for that record? If the latter, then it'll be in VB. You'll need to specify what values you want for each selection. Something like this:

Private Sub ComboBox1_AfterUpdate()
If ComboBox1 = "A value from the combo box" Then
 [Field 1] = "This"
 [Field 2] = "That"
ElseIf ComboBox1 = "A different value from the combo box" Then
 [Field 1] = "THIS"
 [Field 2] = "THAT"
End If

It's really quite tedious (may be an easier way. Don't know) But that will fill in each field with the value that you specify.

 

[xeen20]

Thankyou for the solution but I want to fill with the data already stored in the record for the value in combo box

 

[agehoops]

Ah ok that should be ok. If you create a new combo box somewhere on the form and let it bring up the wizard, you should have 3 options, one of which (should be the bottom one) is what you're after. It'll load the record that you select from the combo box

 

[xeen20]

Hi,
thanks but could u explain me in detail how to do that. i am new to this access.

 

[agehoops]

Sure.

Ok in design view of the form, go to the toolbox, and find the combo box option. Then add one to your form either by just clicking or clicking and dragging to specify the size. Once you've done that, a window will pop up which is the combo box wizard. In this window, you should then have 3 options. One will be to specify values, one to look the values up, and the other to find a record on the form based on the value you select. Choose that option. Then in the next window, choose the fields you want to be displayed in the combo box. Then just keep going through the options until you reach the end. What should then happen is when you select an option from the combo box, it'll go to that record on the form. If you don't have that option in the combo box wizard then it'll be more complicated

 

[xeen20]

Hi ,
Thanks but when i add combo box , wizard is not poped up.what should i do .........

 

[agehoops]

hmm weird, should be the first thing to come up. Ok then, put this in the AfterUpdate event of the combo box but you'll have to modify it

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Room Number] = " & Str(Nz(Me![cboRoom], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ok in there, change the cboRoom to the name of the combo box, and change the [Room Number] to the name of the field you want it to use. Probably use the ID field if I were you. Hopefully that should then do what you're after.

 

[xeen20]

I Appreciate you help,

but i could not make it work

here
my table name is = Details
colums are.......... ID (Auto Generated)........First Name .......... Last Name.......MI................Phone No..............Email........

i will put my first names to combo feild.
and it should show Only the Last name and Mi.. in the form and i will enter new phone NO and email to save as new record.
i did this
Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = " & Str(Nz(Me![First Name], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

First Name is the combo feild
Last Name is the feild in the form i want to retrieve from table.

but didnt work
help me

 

[agehoops]

Ok. Is your form based on that table?

You say colums?? Is it a list box, or do you just mean fields?

 

[xeen20]

Sorry for the confusion, Its a Feilds...
Here
My table Name : Details
ID( Auto ) ---------First Name ---------Last Name---------Phone Number----------Email.
1 -------------------x------------------- x1 ---------------- xxxxxx ------------ xxxx@y
2 ------------------y------------------- y1----------------xxxxxxxx------------xx xxxx@y
3 .. .. .... ....


i made a form with all the feilds
like
First Name
Last Name
Phone Number
Email

Now i made First Name as Combo box with row source as " SELECT Details.[First Name] FROM Details" and contorl as [First Name] of Details

Now i want to display Last Name based on the First Name in combo box.

This should be helping me in adding a new record with different Phone Number and Email.

 

[agehoops]

ah right ok. Tell you what, send me a copy of you db and i'll see if I can get it running for you if you like?

 

[xeen20]

I attached my database .

 

[agehoops]

Here ya go. I've put a new combo box at the top of the form. just click it and it'll list all the records you have, then it'll load that record.


Just as a hint for the database though, I'd look into doing a relational database, or at least normalising it to save on problems further down the road. Might be alright depending on what it needs to do though

View attachment My data base.zip

 

[xeen20]

Hi
Thank u,
i required in a different way . but i learned something new and useful for me.
i wolud like to get only few feilds like Last Name and MI but not
phone no and email.

 

[agehoops]

You only want First name and MI in the combo box columns? If that's what you mean then just edit the query that is its record source. If you only want those bits of information on the form updated then it'll be harder I think.