ID Pickup

[Irwin109]

Ok so here's my idea, someone will no doubt say "Jus' put it in one table',
if you're that someone save your fingers the effort of typing, I don't want
to!

Here we go!

Ok, so when I make a new client the GP's ID is chosen via a subform of the
GP table, so in the client table the only GP info' that is saved is the ID.
(this is done using - Me.GP_Dentist_ID.Value = Me.GPList.Column(0))

In my client search I want it the other way around, so the ID will bring up
the name, place of work and telephone number into three, currently, unbound
boxes:

GPName
GPWork
and GPTel.

I had the idea in my head that this can be done via a query, but there's
probably a quicker/easier way in VBA. I've been playing around a bit but come
up with nothing.

I can probably explain this better after I get asked some question.
Help is appreciated!

 

[Al Campagna]

Irwin109,
Add the GPName, GPWork, and GPTel columns to your GPList.
GPID GPName GPWork GPTel

Place three unbound text controls on the form.
Give them a ControlSource of (respectively)
= GPList.Column(1)
= GPList.Column(2)
= GPList.Column(3)
When you select a GPID, the other controls will display the info from
the list columns.
List (and Combo) columns values are referred by column 0, 1, 2, 3,
etc...

Note:

(this is done using - Me.GP_Dentist_ID.Value = Me.GPList.Column(0))

This is really superfluous, since the GPList itself can be bound to the
GP_Dentist_ID field in your table, and thereby capture the GPID value
itself.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Irwin109]

This is why I have to pre-explain that my explanations are bad!

The GP Table has GPID, GPName, GPWork and GPTel

The Client Table just has the GPID.

In the New Client Form when selecting a GP it gives a list (it says unbound
when in design view) from the GP table, when a GP is selected it puts their
ID in GPID (Client Table).

The Search Clients Form uses VBA to let you type a surname of a client to
find their record (there's also a find next button) the result is from the
Client Table, which just has the GPID, can I get that ID to bring back the
results from the GP Table?

Would you still advise the same course of action or not? Sorry if I seem
infuriating but I'm only semi-competent with Access!