Forms question

[Terry]

There is probably a real easy answer to this one. Using access 2000, I have
a database with about 1000 client records in it. I am using a combo box in a
form which when I click in it, all of the client names are listed and
selectable, which is exactly what I want. When I click on a client name,
their name goes in the box, but the remaining client information doesn't
change, which of course has the potential in corrupting the entire database.
I want to be able to select a client name, and have the remaining
information for that client fill in automatically in the form. Any
suggestion on how to resolve this would be appreciated. Thanks in advance.

--
Terry

[Allen Browne]

Re: Forms question

Terry, you already have a Customers table (the row source for the combo
box). Presumably you are now talking about using that information in another
tables (such as orders for customers).

Open the Northwind sample database (installed with Access).
From the Tools menu, choose Relationships.
Notice how the Orders table has a CustomerID field, related back to the
Customers table.

Now Open the Orders form in design view.
Right-click the CustomerID combo box, and choose Properties.
On the Format tab, Column Count is 2, and Columns Widths is 0";2".
As a result the combo stores the CustomerID (the Bound Column property is
1), but displays the CustomerName.

You will also see how the customer names appear on the form. These fields
are not part of the Orders record. The RecordSource for the form is a query
that reads these from the Customers table. If a customer changes address,
you only have to do that once in the Customers table, and all the orders
automatically show the new address.

That is probably all you need, but in the case of Northwind they wanted to
allow for sending an order to a different address. As a result, they *do*
store the shipping address. If you look at the AfterUpdate event of the
CustomerID combo, you will see how they copy the current customer's address
into the "Ship To" address fields. If you really need to do that, you can
copy their approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Terry" <ve1trb@hotmail .com> wrote in message
news:QnDSb.1307 0$Ja2.74714@nnr p1.uunet.ca...[color=blue]
> There is probably a real easy answer to this one. Using access 2000, I[/color]
have[color=blue]
> a database with about 1000 client records in it. I am using a combo box in[/color]
a[color=blue]
> form which when I click in it, all of the client names are listed and
> selectable, which is exactly what I want. When I click on a client name,
> their name goes in the box, but the remaining client information doesn't
> change, which of course has the potential in corrupting the entire[/color]
database.[color=blue]
> I want to be able to select a client name, and have the remaining
> information for that client fill in automatically in the form. Any
> suggestion on how to resolve this would be appreciated. Thanks in advance.
>
> --
> Terry
>
>[/color]

[Larry Linson]

Re: Forms question

You don't mean, do you, that you intend to have redundant information
stored? If so, it'd be better to save in the record a link to the client
file, and show the client information in a subform or pick it up from
columns in the combo and move it to unbound fields on the form.

Larry Linson
Microsoft Access MVP



"Terry" <ve1trb@hotmail .com> wrote in message
news:QnDSb.1307 0$Ja2.74714@nnr p1.uunet.ca...[color=blue]
> There is probably a real easy answer to this one. Using access 2000, I[/color]
have[color=blue]
> a database with about 1000 client records in it. I am using a combo box in[/color]
a[color=blue]
> form which when I click in it, all of the client names are listed and
> selectable, which is exactly what I want. When I click on a client name,
> their name goes in the box, but the remaining client information doesn't
> change, which of course has the potential in corrupting the entire[/color]
database.[color=blue]
> I want to be able to select a client name, and have the remaining
> information for that client fill in automatically in the form. Any
> suggestion on how to resolve this would be appreciated. Thanks in advance.
>
> --
> Terry
>
>[/color]

[Terry]

Re: Forms question

Actually Larry, what I'm trying to do is to make it real simple for a user
to retrieve information on a particular customer and not save/change any new
info at all (retrieve/display only) when using this particular form. There
is no need for a report or a printout.
I think Allen's suggestion is the way I need to go.
Thank you both, I'll let you know how I make out.
--
Terry


"Larry Linson" <bouncer@localh ost.not> wrote in message
news:WYDSb.2798 $9p5.2352@nwrdd c02.gnilink.net ...[color=blue]
> You don't mean, do you, that you intend to have redundant information
> stored? If so, it'd be better to save in the record a link to the client
> file, and show the client information in a subform or pick it up from
> columns in the combo and move it to unbound fields on the form.
>
> Larry Linson
> Microsoft Access MVP
>
>
>
> "Terry" <ve1trb@hotmail .com> wrote in message
> news:QnDSb.1307 0$Ja2.74714@nnr p1.uunet.ca...[color=green]
> > There is probably a real easy answer to this one. Using access 2000, I[/color]
> have[color=green]
> > a database with about 1000 client records in it. I am using a combo box[/color][/color]
in[color=blue]
> a[color=green]
> > form which when I click in it, all of the client names are listed and
> > selectable, which is exactly what I want. When I click on a client name,
> > their name goes in the box, but the remaining client information doesn't
> > change, which of course has the potential in corrupting the entire[/color]
> database.[color=green]
> > I want to be able to select a client name, and have the remaining
> > information for that client fill in automatically in the form. Any
> > suggestion on how to resolve this would be appreciated. Thanks in[/color][/color]
advance.[color=blue][color=green]
> >
> > --
> > Terry
> >
> >[/color]
>
>[/color]

[Salad]

Re: Forms question

Terry wrote:
[color=blue]
> Actually Larry, what I'm trying to do is to make it real simple for a user
> to retrieve information on a particular customer and not save/change any new
> info at all (retrieve/display only) when using this particular form. There
> is no need for a report or a printout.
> I think Allen's suggestion is the way I need to go.
> Thank you both, I'll let you know how I make out.
> --
> Terry[/color]

Let's say I have a customer table. I create a form for customers with all th
fields for data entry. The form's recordsource is Customers. I also have a
GoTo combo box where someone can select a customer and when a new customer is
selected, the record displayed is that customer.

Let's say the combo is called ComboBox. The Combo box displays
CustomerID(hidd en) and CustName.

In the OnCurrent event, you would enter
Me.ComboBox = Me.CustomerID

In the AfterUpdate event of the combo you need some code to move to the record
selected.
You can copy this code into the event, change the name to your combobox name and
customerid field name
Dim rst As DAO.Recordset
Set rst = Me.Recordsetclo ne 'use the forms recordsource
rst.FindFirst "CustomerID = " & Me.ComboBox 'find the customer selected
If not rst.NoMatch then 'the record was found. you may have a filter where
it isn't in the list
Me.Bookmark = rst.bookmark 'found it, now set the bookmark
else
msgbox "Not found. Remove filer"
endif
rst.close
set rst = Nothing