Newby question - Please help!!

[Jack Schitt]

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[Larry Linson]

If you aren't the newsgroup's resident troll and character assassin, you
should be aware that you are using an alias he has used before posting from
Supernews, as you are, and if you don't get answers, it may well be because
people think that he's just trying to start a thread on which to hang his
trolling and character assassination.

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com...

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[MacDermott]

Let's back up a moment and ask why you have two separate tables.
Do you want to store multiple addresses for a single employee?
If so, when you type in a SSN, which address should be displayed?
If not, why not put the address in the same table as the employee?

- Turtle

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com...

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[Jack Schitt]

I was afraid somebody was going to ask that!! This isn't actually
what I'm trying to accomplish - I was just trying to keep it simpler
(in my mind!!) What I'm working on is a HelpDesk type application. A
PC technician should be able to go to a user's desk, pull up this
database from that pc, and do a lookup for that user, PC Serial
number, workstation name, etc... (all resides in a DB called
Employee). The Serial# is the P. Key of that lookup table. When the
employee record is found it should populate this form, which will add
corresponding records into the HelpLog Databasem which contains
additional fields such as techician name, problem, solution, etc...
So it should create a new record for every trouble call.

Thanks!

On Wed, 05 May 2004 11:41:27 GMT, "MacDermott" <ma********@nospam.com>
wrote:

Let's back up a moment and ask why you have two separate tables.
Do you want to store multiple addresses for a single employee?
If so, when you type in a SSN, which address should be displayed?
If not, why not put the address in the same table as the employee?

- Turtle

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com.. .

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[Jack Schitt]

I assure you I'm not a troll - only a wanna-be Access programmer with
what I think is a legitamite question. I do appreciate you informing
me of this though - I hope I'm not mistaken for him (or her)!

On Wed, 05 May 2004 05:16:20 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:

If you aren't the newsgroup's resident troll and character assassin, you
should be aware that you are using an alias he has used before posting from
Supernews, as you are, and if you don't get answers, it may well be because
people think that he's just trying to start a thread on which to hang his
trolling and character assassination.

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com.. .

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[MacDermott]

Sometimes the hardest part of writing an application is getting clear
exactly what it is supposed to do. <g>

As I understand your task, instead of thinking
"bring up the user's record, then add an item to the HelpLog table",
I would design it like this:
"open a new record in the HelpLog table. Then populate the User field
by choosing from a combobox."

Does this help?
- Turtle
"Jack Schitt" <[e-mail]> wrote in message
news:7h********************************@4ax.com...

I was afraid somebody was going to ask that!! This isn't actually
what I'm trying to accomplish - I was just trying to keep it simpler
(in my mind!!) What I'm working on is a HelpDesk type application. A
PC technician should be able to go to a user's desk, pull up this
database from that pc, and do a lookup for that user, PC Serial
number, workstation name, etc... (all resides in a DB called
Employee). The Serial# is the P. Key of that lookup table. When the
employee record is found it should populate this form, which will add
corresponding records into the HelpLog Databasem which contains
additional fields such as techician name, problem, solution, etc...
So it should create a new record for every trouble call.

Thanks!

On Wed, 05 May 2004 11:41:27 GMT, "MacDermott" <ma********@nospam.com>
wrote:

Let's back up a moment and ask why you have two separate tables.
Do you want to store multiple addresses for a single employee?
If so, when you type in a SSN, which address should be displayed?
If not, why not put the address in the same table as the employee?

- Turtle

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com.. .

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[Julia Baresch]

"Me" refers to the object you're working in. For example if you're
coding an event procedure in a form, "Me" refers to the form. So you
may be able to code Me!ControlName instead of typing the whole
reference to the form.

The times I've seen and used "Me" was with an exclamation point, not a
dot, and it doesn't always work in my experience. Sometimes I still
have to type the whole reference. Access VBA has a lot of quirks,
I've often found I have to use syntax that varies slightly from
examples.

I probably won't make it back to this thread, so I don't care if you
assasinate my character. I won't see it. :-)

Julia

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com...

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[Brian]

Thank you Julia - really appreciate the info!! I've been looking at
several Access books, but have not yet found one that really breaks
this syntax down. Probably need to find a reference guide, instead
of these "ACCESS UNLEASHED" or "MASTERING ACCESS" books I have.
(Character assassinations - don't people have better things to do? ;-Þ

jb******@oldrepublic.com (Julia Baresch) wrote in message news:<50**************************@posting.google. com>...

"Me" refers to the object you're working in. For example if you're
coding an event procedure in a form, "Me" refers to the form. So you
may be able to code Me!ControlName instead of typing the whole
reference to the form.

The times I've seen and used "Me" was with an exclamation point, not a
dot, and it doesn't always work in my experience. Sometimes I still
have to type the whole reference. Access VBA has a lot of quirks,
I've often found I have to use syntax that varies slightly from
examples.

I probably won't make it back to this thread, so I don't care if you
assasinate my character. I won't see it. :-)

Julia

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com...

I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.

I'm getting stuck in the Data Entry form. When I type in the SSN it
does pull up the employee's record, but I can't get it to populate the
remainder of the fields from the Address table. I want to populate
employee name, etc... from the address table into the Employee table.
The Northwind database has a good example of what I'm trying to do, in
the Orders form. The key seems to be in the AfterClick Event
procedure of the bound column (I think order number, but don't
remember). But it is in a format I can't find a reference to. It
goes something like Me.OrderNum = Me.NewOrderNum.

I'm sorry about rambling - I realize this isn't too clear. Please
point me in the right direction!!

TIA!

[Brian]

Hey!

I really appreciate your post - and I know you're right. I need to "step
outside" by current strategy and see if I'm even going in the right
direction.

On to your answer - it makes perfect sense to me, and I am currently
trying to start from scratch. I know that, once I get it working, I'll
step back and wonder why I had so much trouble with this one! I've done
as you suggested, and sketched out a diagram of how this should work. I
have a form as my main focal point, with a half dozen fields coming from my
Employee (lookup?) table. But when the form is filled out and loses focus,
all the data should go to the Reviews table.

My confusion right now has to do with the implementation. For example - how
do I take the PC Serial number, Employee Name, etc... fields that I pulled
from the Employee table, and insert them into this new record in the Reviews
table? Should I use a buffer of some sort...copy these fields, and then
write to the Reviews table? It turns out the example I mentioned earlier (from
the Northwinds Database) isn't as close to this as I thought it was.

Thanks again for your posts!

Brian (aka Jack Schitt!)
bk****@hotmail.com
"MacDermott" <ma********@nospam.com> wrote in message news:<Sr*****************@newsread2.news.atl.earth link.net>...

Sometimes the hardest part of writing an application is getting clear
exactly what it is supposed to do. <g>

As I understand your task, instead of thinking
"bring up the user's record, then add an item to the HelpLog table",
I would design it like this:
"open a new record in the HelpLog table. Then populate the User field
by choosing from a combobox."

Does this help?
- Turtle
"Jack Schitt" <[e-mail]> wrote in message
news:7h********************************@4ax.com...

I was afraid somebody was going to ask that!! This isn't actually
what I'm trying to accomplish - I was just trying to keep it simpler
(in my mind!!) What I'm working on is a HelpDesk type application. A
PC technician should be able to go to a user's desk, pull up this
database from that pc, and do a lookup for that user, PC Serial
number, workstation name, etc... (all resides in a DB called
Employee). The Serial# is the P. Key of that lookup table. When the
employee record is found it should populate this form, which will add
corresponding records into the HelpLog Databasem which contains
additional fields such as techician name, problem, solution, etc...
So it should create a new record for every trouble call.

Thanks!

On Wed, 05 May 2004 11:41:27 GMT, "MacDermott" <ma********@nospam.com>
wrote:

Let's back up a moment and ask why you have two separate tables.
Do you want to store multiple addresses for a single employee?
If so, when you type in a SSN, which address should be displayed?
If not, why not put the address in the same table as the employee?

- Turtle

"Jack Schitt" <[e-mail]> wrote in message
news:4d********************************@4ax.com.. .
> I thought I was starting to get a handle on Access, until I tried
> doing something useful...now I'm stuck. I have a DB with two tables -
> to keep it simple I'll say that one is an Employee File (Employees),
> and the other is an Address File (Addresses) linked by SSN. I've set
> Addresses as a Lookup Table - If the user starts typing in the SSN it
> should pull up the Employees records.
>
> I'm getting stuck in the Data Entry form. When I type in the SSN it
> does pull up the employee's record, but I can't get it to populate the
> remainder of the fields from the Address table. I want to populate
> employee name, etc... from the address table into the Employee table.
> The Northwind database has a good example of what I'm trying to do, in
> the Orders form. The key seems to be in the AfterClick Event
> procedure of the bound column (I think order number, but don't
> remember). But it is in a format I can't find a reference to. It
> goes something like Me.OrderNum = Me.NewOrderNum.
>
> I'm sorry about rambling - I realize this isn't too clear. Please
> point me in the right direction!!
>
> TIA!

[MacDermott]

Here's another question you need to put some thought into:

What happens if the data in the Employee table changes?
For example, suppose Jane Smith gets married and is suddenly Jane Wu?
Clearly, the Employee table changes.
But what about the Reviews table?
Should older reviews still say Jane Smith?
Or should they be updated to say Jane Wu?

If you want your older records to be updated, then you will store only the
Primary Key to your Employee table in the Reviews table, and use a query to
retrieve the current values from the Employee table.
If you want to keep the older data, you'll need to write some code to copy
that data into the Reviews table.

Let me know which way you want to go, and I'll try to offer some ideas about
how to get there.

- Turtle
BTW, I see that you have a ? associated with the term "lookup table". I'm
no expert on terminology, being largely self-taught, but my experience is
that "lookup table" is usually used to describe a table used in the first
way; i.e. the current values in that table are associated with records in
the main table.
"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey!

I really appreciate your post - and I know you're right. I need to "step
outside" by current strategy and see if I'm even going in the right
direction.

On to your answer - it makes perfect sense to me, and I am currently
trying to start from scratch. I know that, once I get it working, I'll
step back and wonder why I had so much trouble with this one! I've done
as you suggested, and sketched out a diagram of how this should work. I
have a form as my main focal point, with a half dozen fields coming from my Employee (lookup?) table. But when the form is filled out and loses focus, all the data should go to the Reviews table.

My confusion right now has to do with the implementation. For example - how do I take the PC Serial number, Employee Name, etc... fields that I pulled
from the Employee table, and insert them into this new record in the Reviews table? Should I use a buffer of some sort...copy these fields, and then
write to the Reviews table? It turns out the example I mentioned earlier (from the Northwinds Database) isn't as close to this as I thought it was.

Thanks again for your posts!

Brian (aka Jack Schitt!)
bk****@hotmail.com
"MacDermott" <ma********@nospam.com> wrote in message

news:<Sr*****************@newsread2.news.atl.earth link.net>...

Sometimes the hardest part of writing an application is getting clear
exactly what it is supposed to do. <g>

As I understand your task, instead of thinking
"bring up the user's record, then add an item to the HelpLog table",
I would design it like this:
"open a new record in the HelpLog table. Then populate the User field by choosing from a combobox."

Does this help?
- Turtle
"Jack Schitt" <[e-mail]> wrote in message
news:7h********************************@4ax.com...

I was afraid somebody was going to ask that!! This isn't actually
what I'm trying to accomplish - I was just trying to keep it simpler
(in my mind!!) What I'm working on is a HelpDesk type application. A
PC technician should be able to go to a user's desk, pull up this
database from that pc, and do a lookup for that user, PC Serial
number, workstation name, etc... (all resides in a DB called
Employee). The Serial# is the P. Key of that lookup table. When the
employee record is found it should populate this form, which will add
corresponding records into the HelpLog Databasem which contains
additional fields such as techician name, problem, solution, etc...
So it should create a new record for every trouble call.

Thanks!

On Wed, 05 May 2004 11:41:27 GMT, "MacDermott" <ma********@nospam.com>
wrote:

>Let's back up a moment and ask why you have two separate tables.
>Do you want to store multiple addresses for a single employee?
>If so, when you type in a SSN, which address should be displayed?
>If not, why not put the address in the same table as the employee?
>
> - Turtle
>
>"Jack Schitt" <[e-mail]> wrote in message
>news:4d********************************@4ax.com.. .
>> I thought I was starting to get a handle on Access, until I tried
>> doing something useful...now I'm stuck. I have a DB with two tables - >> to keep it simple I'll say that one is an Employee File (Employees), >> and the other is an Address File (Addresses) linked by SSN. I've set >> Addresses as a Lookup Table - If the user starts typing in the SSN it >> should pull up the Employees records.
>>
>> I'm getting stuck in the Data Entry form. When I type in the SSN it >> does pull up the employee's record, but I can't get it to populate the >> remainder of the fields from the Address table. I want to populate
>> employee name, etc... from the address table into the Employee table. >> The Northwind database has a good example of what I'm trying to do, in >> the Orders form. The key seems to be in the AfterClick Event
>> procedure of the bound column (I think order number, but don't
>> remember). But it is in a format I can't find a reference to. It
>> goes something like Me.OrderNum = Me.NewOrderNum.
>>
>> I'm sorry about rambling - I realize this isn't too clear. Please
>> point me in the right direction!!
>>
>> TIA!
>

[Brian]

Very good point. I think it would make more sense to update
with the newer values when applicable. I keep thinking I'm
almost there, but then something doesn't work right. (That's
the reason I was trying to "simplify" my problem at the start.
When I finally figure out how to make this work, I'm sure I'll be
able to use it in more complex applications!)

As far as your question about my use of the word 'Lookup Table' - I'm
just not positive I'm using the correct terminology in this case.

"MacDermott" <ma********@nospam.com> wrote in message news:<KZ*****************@newsread2.news.atl.earth link.net>...

Here's another question you need to put some thought into:

What happens if the data in the Employee table changes?
For example, suppose Jane Smith gets married and is suddenly Jane Wu?
Clearly, the Employee table changes.
But what about the Reviews table?
Should older reviews still say Jane Smith?
Or should they be updated to say Jane Wu?

If you want your older records to be updated, then you will store only the
Primary Key to your Employee table in the Reviews table, and use a query to
retrieve the current values from the Employee table.
If you want to keep the older data, you'll need to write some code to copy
that data into the Reviews table.

Let me know which way you want to go, and I'll try to offer some ideas about
how to get there.

- Turtle
BTW, I see that you have a ? associated with the term "lookup table". I'm
no expert on terminology, being largely self-taught, but my experience is
that "lookup table" is usually used to describe a table used in the first
way; i.e. the current values in that table are associated with records in
the main table.
"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey!

I really appreciate your post - and I know you're right. I need to "step
outside" by current strategy and see if I'm even going in the right
direction.

On to your answer - it makes perfect sense to me, and I am currently
trying to start from scratch. I know that, once I get it working, I'll
step back and wonder why I had so much trouble with this one! I've done
as you suggested, and sketched out a diagram of how this should work. I
have a form as my main focal point, with a half dozen fields coming from

my

Employee (lookup?) table. But when the form is filled out and loses

focus,

all the data should go to the Reviews table.

My confusion right now has to do with the implementation. For example -

how

do I take the PC Serial number, Employee Name, etc... fields that I pulled
from the Employee table, and insert them into this new record in the

Reviews

table? Should I use a buffer of some sort...copy these fields, and then
write to the Reviews table? It turns out the example I mentioned earlier

(from

the Northwinds Database) isn't as close to this as I thought it was.

Thanks again for your posts!

Brian (aka Jack Schitt!)
bk****@hotmail.com
"MacDermott" <ma********@nospam.com> wrote in message

news:<Sr*****************@newsread2.news.atl.earth link.net>...

Sometimes the hardest part of writing an application is getting clear
exactly what it is supposed to do. <g>

As I understand your task, instead of thinking
"bring up the user's record, then add an item to the HelpLog table",
I would design it like this:
"open a new record in the HelpLog table. Then populate the User field by choosing from a combobox."

Does this help?
- Turtle
"Jack Schitt" <[e-mail]> wrote in message
news:7h********************************@4ax.com...
> I was afraid somebody was going to ask that!! This isn't actually
> what I'm trying to accomplish - I was just trying to keep it simpler
> (in my mind!!) What I'm working on is a HelpDesk type application. A
> PC technician should be able to go to a user's desk, pull up this
> database from that pc, and do a lookup for that user, PC Serial
> number, workstation name, etc... (all resides in a DB called
> Employee). The Serial# is the P. Key of that lookup table. When the
> employee record is found it should populate this form, which will add
> corresponding records into the HelpLog Databasem which contains
> additional fields such as techician name, problem, solution, etc...
> So it should create a new record for every trouble call.
>
> Thanks!
>
> On Wed, 05 May 2004 11:41:27 GMT, "MacDermott" <ma********@nospam.com>
> wrote:
>
> >Let's back up a moment and ask why you have two separate tables.
> >Do you want to store multiple addresses for a single employee?
> >If so, when you type in a SSN, which address should be displayed?
> >If not, why not put the address in the same table as the employee?
> >
> > - Turtle
> >
> >"Jack Schitt" <[e-mail]> wrote in message
> >news:4d********************************@4ax.com.. .
> >> I thought I was starting to get a handle on Access, until I tried
> >> doing something useful...now I'm stuck. I have a DB with two tables - > >> to keep it simple I'll say that one is an Employee File (Employees), > >> and the other is an Address File (Addresses) linked by SSN. I've set > >> Addresses as a Lookup Table - If the user starts typing in the SSN it > >> should pull up the Employees records.
> >>
> >> I'm getting stuck in the Data Entry form. When I type in the SSN it > >> does pull up the employee's record, but I can't get it to populate the > >> remainder of the fields from the Address table. I want to populate
> >> employee name, etc... from the address table into the Employee table. > >> The Northwind database has a good example of what I'm trying to do, in > >> the Orders form. The key seems to be in the AfterClick Event
> >> procedure of the bound column (I think order number, but don't
> >> remember). But it is in a format I can't find a reference to. It
> >> goes something like Me.OrderNum = Me.NewOrderNum.
> >>
> >> I'm sorry about rambling - I realize this isn't too clear. Please
> >> point me in the right direction!!
> >>
> >> TIA!
> >
>

[MacDermott]

If you want to update with newer values when available, you don't need to
copy the values from Employee to Review.
Put a field in Review to hold the Primary Key for the appropriate record in
Employee.
Put a combobox on your form, with its ControlSource set to this field in the
Review table.
Write a query which contains the Primary Key and all of the fields from
Employee that you want to display on this form. Make the Primary Key the
first field in the query.
Set this query as the combobox's RowSource.
Set its BoundColumn property to 1.
Set its ColumnCount property to the number of fields in the query.
Set all of the ColumnWidths to zero except the second one; this is the value
that will show in the combobox.

Got all that? Now...
Add a textbox to your form. Let's say you've called your combobox
cboEmployee.
set the textbox's ControlSource like this:
=cboEmployee.Column(2)
Now when you select an employee in the combobox, that name should show in
the combobox itself, and whatever's in the 3rd column should show in your
textbox.
Once you get this example working, you should be able to build the textboxes
to display the data from your other combobox columns.

HTH
- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Very good point. I think it would make more sense to update
with the newer values when applicable. I keep thinking I'm
almost there, but then something doesn't work right. (That's
the reason I was trying to "simplify" my problem at the start.
When I finally figure out how to make this work, I'm sure I'll be
able to use it in more complex applications!)

As far as your question about my use of the word 'Lookup Table' - I'm
just not positive I'm using the correct terminology in this case.

"MacDermott" <ma********@nospam.com> wrote in message

news:<KZ*****************@newsread2.news.atl.earth link.net>...

Here's another question you need to put some thought into:

What happens if the data in the Employee table changes?
For example, suppose Jane Smith gets married and is suddenly Jane Wu?
Clearly, the Employee table changes.
But what about the Reviews table?
Should older reviews still say Jane Smith?
Or should they be updated to say Jane Wu?

If you want your older records to be updated, then you will store only the Primary Key to your Employee table in the Reviews table, and use a query to retrieve the current values from the Employee table.
If you want to keep the older data, you'll need to write some code to copy that data into the Reviews table.

Let me know which way you want to go, and I'll try to offer some ideas about how to get there.

- Turtle
BTW, I see that you have a ? associated with the term "lookup table". I'm no expert on terminology, being largely self-taught, but my experience is that "lookup table" is usually used to describe a table used in the first way; i.e. the current values in that table are associated with records in the main table.
"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey!

I really appreciate your post - and I know you're right. I need to "step outside" by current strategy and see if I'm even going in the right
direction.

On to your answer - it makes perfect sense to me, and I am currently
trying to start from scratch. I know that, once I get it working, I'll step back and wonder why I had so much trouble with this one! I've done as you suggested, and sketched out a diagram of how this should work. I have a form as my main focal point, with a half dozen fields coming from

my

Employee (lookup?) table. But when the form is filled out and loses

focus,

all the data should go to the Reviews table.

My confusion right now has to do with the implementation. For

example - how

do I take the PC Serial number, Employee Name, etc... fields that I

pulled from the Employee table, and insert them into this new record in the

Reviews

table? Should I use a buffer of some sort...copy these fields, and then write to the Reviews table? It turns out the example I mentioned earlier (from

the Northwinds Database) isn't as close to this as I thought it was.

Thanks again for your posts!

Brian (aka Jack Schitt!)
bk****@hotmail.com
"MacDermott" <ma********@nospam.com> wrote in message

news:<Sr*****************@newsread2.news.atl.earth link.net>...

> Sometimes the hardest part of writing an application is getting

clear > exactly what it is supposed to do. <g>
>
> As I understand your task, instead of thinking
> "bring up the user's record, then add an item to the HelpLog table", > I would design it like this:
> "open a new record in the HelpLog table. Then populate the User

field

> by choosing from a combobox."
>
> Does this help?
> - Turtle
>
>
> "Jack Schitt" <[e-mail]> wrote in message
> news:7h********************************@4ax.com...
> > I was afraid somebody was going to ask that!! This isn't actually
> > what I'm trying to accomplish - I was just trying to keep it simpler > > (in my mind!!) What I'm working on is a HelpDesk type application. A > > PC technician should be able to go to a user's desk, pull up this
> > database from that pc, and do a lookup for that user, PC Serial
> > number, workstation name, etc... (all resides in a DB called
> > Employee). The Serial# is the P. Key of that lookup table. When the > > employee record is found it should populate this form, which will add > > corresponding records into the HelpLog Databasem which contains
> > additional fields such as techician name, problem, solution, etc... > > So it should create a new record for every trouble call.
> >
> > Thanks!
> >
> > On Wed, 05 May 2004 11:41:27 GMT, "MacDermott" <ma********@nospam.com> > > wrote:
> >
> > >Let's back up a moment and ask why you have two separate tables.
> > >Do you want to store multiple addresses for a single employee?
> > >If so, when you type in a SSN, which address should be displayed?
> > >If not, why not put the address in the same table as the employee? > > >
> > > - Turtle
> > >
> > >"Jack Schitt" <[e-mail]> wrote in message
> > >news:4d********************************@4ax.com.. .
> > >> I thought I was starting to get a handle on Access, until I tried > > >> doing something useful...now I'm stuck. I have a DB with two

tables -

> > >> to keep it simple I'll say that one is an Employee File

(Employees),

> > >> and the other is an Address File (Addresses) linked by SSN. I've set

> > >> Addresses as a Lookup Table - If the user starts typing in the

SSN it

> > >> should pull up the Employees records.
> > >>
> > >> I'm getting stuck in the Data Entry form. When I type in the

SSN it

> > >> does pull up the employee's record, but I can't get it to

populate the

> > >> remainder of the fields from the Address table. I want to

populate > > >> employee name, etc... from the address table into the Employee

table.

> > >> The Northwind database has a good example of what I'm trying to do, in

> > >> the Orders form. The key seems to be in the AfterClick Event
> > >> procedure of the bound column (I think order number, but don't
> > >> remember). But it is in a format I can't find a reference to.

It > > >> goes something like Me.OrderNum = Me.NewOrderNum.
> > >>
> > >> I'm sorry about rambling - I realize this isn't too clear. Please > > >> point me in the right direction!!
> > >>
> > >> TIA!
> > >
> >

[Brian]

Hey Turtle!

Sorry about taking so long to get back - they've got me on some other
projects over here. Anyhow....

I'm not sure if I worded my problem correctly when I posted this message.
I've done as you suggested, and things are starting to come together. But
my problem is that all records from the Employee table are coming up in the
form. What I really want is that the form only show records from the Review
table. The scenario is.....

A technician goes out on a help call. Get's to the Employees (George's)
desk, and pulls up the database. Since this is a New trouble call, he
wants to enter a new record into the Review table. But hopefully he won't
need to enter in the employee information - just pick George from the dropdown
(combo box) which is coming from the Employee table. Assuming George is
entered correctly in the Employee table, all the information (especially
his PC serial number - the primary key of the Employee Table) will show up.
Then, once I input some additional information on the form, it should update
the Review table with all that information.

I want to apologize in advance for being so hard-headed on this!! I should
be able to figure this out, but keep hitting dead ends.

Thanks again!
Brian
"MacDermott" <ma********@nospam.com> wrote in message news:<kC****************@newsread3.news.atl.earthl ink.net>...

If you want to update with newer values when available, you don't need to
copy the values from Employee to Review.
Put a field in Review to hold the Primary Key for the appropriate record in
Employee.
Put a combobox on your form, with its ControlSource set to this field in the
Review table.
Write a query which contains the Primary Key and all of the fields from
Employee that you want to display on this form. Make the Primary Key the
first field in the query.
Set this query as the combobox's RowSource.
Set its BoundColumn property to 1.
Set its ColumnCount property to the number of fields in the query.
Set all of the ColumnWidths to zero except the second one; this is the value
that will show in the combobox.

Got all that? Now...
Add a textbox to your form. Let's say you've called your combobox
cboEmployee.
set the textbox's ControlSource like this:
=cboEmployee.Column(2)
Now when you select an employee in the combobox, that name should show in
the combobox itself, and whatever's in the 3rd column should show in your
textbox.
Once you get this example working, you should be able to build the textboxes
to display the data from your other combobox columns.

HTH
- Turtle

[Brian]

Hey Turtle!

Sorry about taking so long to get back - they've got me on some other
projects over here. Anyhow....

I'm not sure if I worded my problem correctly when I posted this message.
I've done as you suggested, and things are starting to come together. But
my problem is that all records from the Employee table are coming up in the
form. What I really want is that the form only show records from the Review
table. The scenario is.....

A technician goes out on a help call. Get's to the Employees (George's)
desk, and pulls up the database. Since this is a New trouble call, he
wants to enter a new record into the Review table. But hopefully he won't
need to enter in the employee information - just pick George from the dropdown
(combo box) which is coming from the Employee table. Assuming George is
entered correctly in the Employee table, all the information (especially
his PC serial number - the primary key of the Employee Table) will show up.
Then, once I input some additional information on the form, it should update
the Review table with all that information.

I want to apologize in advance for being so hard-headed on this!! I should
be able to figure this out, but keep hitting dead ends.

Thanks again!
Brian
"MacDermott" <ma********@nospam.com> wrote in message news:<kC****************@newsread3.news.atl.earthl ink.net>...

If you want to update with newer values when available, you don't need to
copy the values from Employee to Review.
Put a field in Review to hold the Primary Key for the appropriate record in
Employee.
Put a combobox on your form, with its ControlSource set to this field in the
Review table.
Write a query which contains the Primary Key and all of the fields from
Employee that you want to display on this form. Make the Primary Key the
first field in the query.
Set this query as the combobox's RowSource.
Set its BoundColumn property to 1.
Set its ColumnCount property to the number of fields in the query.
Set all of the ColumnWidths to zero except the second one; this is the value
that will show in the combobox.

Got all that? Now...
Add a textbox to your form. Let's say you've called your combobox
cboEmployee.
set the textbox's ControlSource like this:
=cboEmployee.Column(2)
Now when you select an employee in the combobox, that name should show in
the combobox itself, and whatever's in the 3rd column should show in your
textbox.
Once you get this example working, you should be able to build the textboxes
to display the data from your other combobox columns.

HTH
- Turtle

[MacDermott]

Brian -
I'm really concerned about your using the PC Serial number as the PK of
your Employees table.
Is each PC really "joined at the hip" to one employee?
Don't you ever upgrade PC's?
Move an employee from one PC to another?

But aside from that, you might try thinking of your form this way:
When you display a record from the Review table, you want to see the
Employee information for that record.
That does not mean that the Employee information must actually be in the
Review table, only that it must be displayed when a Review record is
displayed.
All you need in the Review table is the PK from the Employee record.
Then you can retrieve the Employee information whenever you need it.

Does this make sense?
- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey Turtle!

Sorry about taking so long to get back - they've got me on some other
projects over here. Anyhow....

I'm not sure if I worded my problem correctly when I posted this message.
I've done as you suggested, and things are starting to come together. But
my problem is that all records from the Employee table are coming up in the form. What I really want is that the form only show records from the Review table. The scenario is.....

A technician goes out on a help call. Get's to the Employees (George's)
desk, and pulls up the database. Since this is a New trouble call, he
wants to enter a new record into the Review table. But hopefully he won't
need to enter in the employee information - just pick George from the dropdown (combo box) which is coming from the Employee table. Assuming George is
entered correctly in the Employee table, all the information (especially
his PC serial number - the primary key of the Employee Table) will show up. Then, once I input some additional information on the form, it should update the Review table with all that information.

I want to apologize in advance for being so hard-headed on this!! I should be able to figure this out, but keep hitting dead ends.

Thanks again!
Brian
"MacDermott" <ma********@nospam.com> wrote in message

news:<kC****************@newsread3.news.atl.earthl ink.net>...

If you want to update with newer values when available, you don't need to copy the values from Employee to Review.
Put a field in Review to hold the Primary Key for the appropriate record in Employee.
Put a combobox on your form, with its ControlSource set to this field in the Review table.
Write a query which contains the Primary Key and all of the fields from
Employee that you want to display on this form. Make the Primary Key the first field in the query.
Set this query as the combobox's RowSource.
Set its BoundColumn property to 1.
Set its ColumnCount property to the number of fields in the query.
Set all of the ColumnWidths to zero except the second one; this is the value that will show in the combobox.

Got all that? Now...
Add a textbox to your form. Let's say you've called your combobox
cboEmployee.
set the textbox's ControlSource like this:
=cboEmployee.Column(2)
Now when you select an employee in the combobox, that name should show in the combobox itself, and whatever's in the 3rd column should show in your textbox.
Once you get this example working, you should be able to build the textboxes to display the data from your other combobox columns.

HTH
- Turtle

[Brian]

Hey Matt!

Just got back from a week in Mexico...back to the grind!

Anyhow - gave some thought to your suggestions, and made a few
changes. I've added a Login_ID field to both tables, and made it
the PK of the Employee table. I've reapplied your other suggestions,
and also changed the relationship of the tables (within the query
being used as the datasource for the combo box) so only records from
the Reviews table with related records in the Employees table are being
displayed.

Of course, I'm not there yet!! I've added a couple test records to the
Reviews table, then pulled up my form. When I click my combobox, I get
the two records which are in the table. But when I try selecting one of
them I get the following message:

"Records in table Reviews would have no record on the 'one' side"

Does this mean a relationship is set incorrectly?

Thanks again for all your help!
Brian
"MacDermott" <ma********@nospam.com> wrote in message news:<uB*****************@newsread2.news.atl.earth link.net>...

Brian -
I'm really concerned about your using the PC Serial number as the PK of
your Employees table.
Is each PC really "joined at the hip" to one employee?
Don't you ever upgrade PC's?
Move an employee from one PC to another?

But aside from that, you might try thinking of your form this way:
When you display a record from the Review table, you want to see the
Employee information for that record.
That does not mean that the Employee information must actually be in the
Review table, only that it must be displayed when a Review record is
displayed.
All you need in the Review table is the PK from the Employee record.
Then you can retrieve the Employee information whenever you need it.

Does this make sense?
- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey Turtle!

Sorry about taking so long to get back - they've got me on some other
projects over here. Anyhow....

I'm not sure if I worded my problem correctly when I posted this message.
I've done as you suggested, and things are starting to come together. But
my problem is that all records from the Employee table are coming up in

the

form. What I really want is that the form only show records from the

Review

table. The scenario is.....

A technician goes out on a help call. Get's to the Employees (George's)
desk, and pulls up the database. Since this is a New trouble call, he
wants to enter a new record into the Review table. But hopefully he won't
need to enter in the employee information - just pick George from the

dropdown

(combo box) which is coming from the Employee table. Assuming George is
entered correctly in the Employee table, all the information (especially
his PC serial number - the primary key of the Employee Table) will show

up.

Then, once I input some additional information on the form, it should

update

the Review table with all that information.

I want to apologize in advance for being so hard-headed on this!! I

should

be able to figure this out, but keep hitting dead ends.

Thanks again!
Brian
"MacDermott" <ma********@nospam.com> wrote in message

news:<kC****************@newsread3.news.atl.earthl ink.net>...

If you want to update with newer values when available, you don't need to copy the values from Employee to Review.
Put a field in Review to hold the Primary Key for the appropriate record in Employee.
Put a combobox on your form, with its ControlSource set to this field in the Review table.
Write a query which contains the Primary Key and all of the fields from
Employee that you want to display on this form. Make the Primary Key the first field in the query.
Set this query as the combobox's RowSource.
Set its BoundColumn property to 1.
Set its ColumnCount property to the number of fields in the query.
Set all of the ColumnWidths to zero except the second one; this is the value that will show in the combobox.

Got all that? Now...
Add a textbox to your form. Let's say you've called your combobox
cboEmployee.
set the textbox's ControlSource like this:
=cboEmployee.Column(2)
Now when you select an employee in the combobox, that name should show in the combobox itself, and whatever's in the 3rd column should show in your textbox.
Once you get this example working, you should be able to build the textboxes to display the data from your other combobox columns.

HTH
- Turtle

[MacDermott]

Well, I'd say that sounds like a reasonable conclusion from the message you
get.
Do you have a one-to-many relationship set up with another table in the
"one" position and Reviews in the "many" position?
Does it have records associated with the records in Reviews?
Are you perhaps trying to add new records?
Do you have referential integrity enforced?

So many questions!
So many concepts!

HTH
- Turtle

BTW My name is Turtle, not Matt.
"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey Matt!

Just got back from a week in Mexico...back to the grind!

Anyhow - gave some thought to your suggestions, and made a few
changes. I've added a Login_ID field to both tables, and made it
the PK of the Employee table. I've reapplied your other suggestions,
and also changed the relationship of the tables (within the query
being used as the datasource for the combo box) so only records from
the Reviews table with related records in the Employees table are being
displayed.

Of course, I'm not there yet!! I've added a couple test records to the
Reviews table, then pulled up my form. When I click my combobox, I get
the two records which are in the table. But when I try selecting one of
them I get the following message:

"Records in table Reviews would have no record on the 'one' side"

Does this mean a relationship is set incorrectly?

Thanks again for all your help!
Brian
"MacDermott" <ma********@nospam.com> wrote in message

news:<uB*****************@newsread2.news.atl.earth link.net>...

Brian -
I'm really concerned about your using the PC Serial number as the PK of your Employees table.
Is each PC really "joined at the hip" to one employee?
Don't you ever upgrade PC's?
Move an employee from one PC to another?

But aside from that, you might try thinking of your form this way:
When you display a record from the Review table, you want to see the
Employee information for that record.
That does not mean that the Employee information must actually be in the Review table, only that it must be displayed when a Review record is
displayed.
All you need in the Review table is the PK from the Employee record. Then you can retrieve the Employee information whenever you need it.

Does this make sense?
- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey Turtle!

Sorry about taking so long to get back - they've got me on some other
projects over here. Anyhow....

I'm not sure if I worded my problem correctly when I posted this message. I've done as you suggested, and things are starting to come together. But my problem is that all records from the Employee table are coming up in

the

form. What I really want is that the form only show records from the

Review

table. The scenario is.....

A technician goes out on a help call. Get's to the Employees

(George's) desk, and pulls up the database. Since this is a New trouble call, he
wants to enter a new record into the Review table. But hopefully he won't need to enter in the employee information - just pick George from the

dropdown

(combo box) which is coming from the Employee table. Assuming George is entered correctly in the Employee table, all the information (especially his PC serial number - the primary key of the Employee Table) will show up.

Then, once I input some additional information on the form, it should

update

the Review table with all that information.

I want to apologize in advance for being so hard-headed on this!! I

should

be able to figure this out, but keep hitting dead ends.

Thanks again!
Brian
"MacDermott" <ma********@nospam.com> wrote in message

news:<kC****************@newsread3.news.atl.earthl ink.net>...

> If you want to update with newer values when available, you don't

need to

> copy the values from Employee to Review.
> Put a field in Review to hold the Primary Key for the appropriate

record in

> Employee.
> Put a combobox on your form, with its ControlSource set to this

field in the

> Review table.
> Write a query which contains the Primary Key and all of the fields

from > Employee that you want to display on this form. Make the Primary Key the

> first field in the query.
> Set this query as the combobox's RowSource.
> Set its BoundColumn property to 1.
> Set its ColumnCount property to the number of fields in the query.
> Set all of the ColumnWidths to zero except the second one; this is

the value

> that will show in the combobox.
>
> Got all that? Now...
> Add a textbox to your form. Let's say you've called your

combobox > cboEmployee.
> set the textbox's ControlSource like this:
> =cboEmployee.Column(2)
> Now when you select an employee in the combobox, that name should

show in

> the combobox itself, and whatever's in the 3rd column should show in

your

> textbox.
> Once you get this example working, you should be able to build the

textboxes

> to display the data from your other combobox columns.
>
> HTH
> - Turtle
>

[Brian]

Let me see....

The only relationship is the one between these two tables (Employees
and Review) and it's set as one employee to many review records. I
created this by going into Tools --> Relationships. The relationship
is also showing in the Query Wizard.

The two records I created in Reviews each have an associated record in
the Employee table. This is proved by running the Review Query, which
return both records.

I haven't reached the point of trying to add records yet...only trying to
work with the two existing records.

I have not set it to enforce referential integrity.

Guess this is why there's a learning curve, huh?

Thanks!!
Brian
"MacDermott" <ma********@nospam.com> wrote in message news:<hw******************@newsread2.news.atl.eart hlink.net>...

Well, I'd say that sounds like a reasonable conclusion from the message you
get.
Do you have a one-to-many relationship set up with another table in the
"one" position and Reviews in the "many" position?
Does it have records associated with the records in Reviews?
Are you perhaps trying to add new records?
Do you have referential integrity enforced?

So many questions!
So many concepts!

HTH
- Turtle

BTW My name is Turtle, not Matt.
"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey Matt!

Just got back from a week in Mexico...back to the grind!

Anyhow - gave some thought to your suggestions, and made a few
changes. I've added a Login_ID field to both tables, and made it
the PK of the Employee table. I've reapplied your other suggestions,
and also changed the relationship of the tables (within the query
being used as the datasource for the combo box) so only records from
the Reviews table with related records in the Employees table are being
displayed.

Of course, I'm not there yet!! I've added a couple test records to the
Reviews table, then pulled up my form. When I click my combobox, I get
the two records which are in the table. But when I try selecting one of
them I get the following message:

"Records in table Reviews would have no record on the 'one' side"

Does this mean a relationship is set incorrectly?

Thanks again for all your help!
Brian
"MacDermott" <ma********@nospam.com> wrote in message

news:<uB*****************@newsread2.news.atl.earth link.net>...

Brian -
I'm really concerned about your using the PC Serial number as the PK of your Employees table.
Is each PC really "joined at the hip" to one employee?
Don't you ever upgrade PC's?
Move an employee from one PC to another?

But aside from that, you might try thinking of your form this way:
When you display a record from the Review table, you want to see the
Employee information for that record.
That does not mean that the Employee information must actually be in the Review table, only that it must be displayed when a Review record is
displayed.
All you need in the Review table is the PK from the Employee record. Then you can retrieve the Employee information whenever you need it.

Does this make sense?
- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...
> Hey Turtle!
>
> Sorry about taking so long to get back - they've got me on some other
> projects over here. Anyhow....
>
> I'm not sure if I worded my problem correctly when I posted this message. > I've done as you suggested, and things are starting to come together. But > my problem is that all records from the Employee table are coming up in
the > form. What I really want is that the form only show records from the Review > table. The scenario is.....
>
> A technician goes out on a help call. Get's to the Employees (George's) > desk, and pulls up the database. Since this is a New trouble call, he
> wants to enter a new record into the Review table. But hopefully he won't > need to enter in the employee information - just pick George from the dropdown > (combo box) which is coming from the Employee table. Assuming George is > entered correctly in the Employee table, all the information (especially > his PC serial number - the primary key of the Employee Table) will show
up. > Then, once I input some additional information on the form, it should update > the Review table with all that information.
>
> I want to apologize in advance for being so hard-headed on this!! I should > be able to figure this out, but keep hitting dead ends.
>
> Thanks again!
> Brian
>
>
> "MacDermott" <ma********@nospam.com> wrote in message news:<kC****************@newsread3.news.atl.earthl ink.net>... > > If you want to update with newer values when available, you don't need
to > > copy the values from Employee to Review.
> > Put a field in Review to hold the Primary Key for the appropriate record
in > > Employee.
> > Put a combobox on your form, with its ControlSource set to this field in
the > > Review table.
> > Write a query which contains the Primary Key and all of the fields from > > Employee that you want to display on this form. Make the Primary Key
the > > first field in the query.
> > Set this query as the combobox's RowSource.
> > Set its BoundColumn property to 1.
> > Set its ColumnCount property to the number of fields in the query.
> > Set all of the ColumnWidths to zero except the second one; this is the
value > > that will show in the combobox.
> >
> > Got all that? Now...
> > Add a textbox to your form. Let's say you've called your combobox > > cboEmployee.
> > set the textbox's ControlSource like this:
> > =cboEmployee.Column(2)
> > Now when you select an employee in the combobox, that name should show
in > > the combobox itself, and whatever's in the 3rd column should show in your > > textbox.
> > Once you get this example working, you should be able to build the textboxes > > to display the data from your other combobox columns.
> >
> > HTH
> > - Turtle
> >

[MacDermott]

What is the recordsource for your form?
If it's a query, could you post the SQL for it?

What are the fields in the two tables which are joined in the relationship?

- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Let me see....

The only relationship is the one between these two tables (Employees
and Review) and it's set as one employee to many review records. I
created this by going into Tools --> Relationships. The relationship
is also showing in the Query Wizard.

The two records I created in Reviews each have an associated record in
the Employee table. This is proved by running the Review Query, which
return both records.

I haven't reached the point of trying to add records yet...only trying to
work with the two existing records.

I have not set it to enforce referential integrity.

Guess this is why there's a learning curve, huh?

Thanks!!
Brian
"MacDermott" <ma********@nospam.com> wrote in message

news:<hw******************@newsread2.news.atl.eart hlink.net>...

Well, I'd say that sounds like a reasonable conclusion from the message you get.
Do you have a one-to-many relationship set up with another table in the
"one" position and Reviews in the "many" position?
Does it have records associated with the records in Reviews?
Are you perhaps trying to add new records?
Do you have referential integrity enforced?

So many questions!
So many concepts!

HTH
- Turtle

BTW My name is Turtle, not Matt.
"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey Matt!

Just got back from a week in Mexico...back to the grind!

Anyhow - gave some thought to your suggestions, and made a few
changes. I've added a Login_ID field to both tables, and made it
the PK of the Employee table. I've reapplied your other suggestions,
and also changed the relationship of the tables (within the query
being used as the datasource for the combo box) so only records from
the Reviews table with related records in the Employees table are being displayed.

Of course, I'm not there yet!! I've added a couple test records to the Reviews table, then pulled up my form. When I click my combobox, I get the two records which are in the table. But when I try selecting one of them I get the following message:

"Records in table Reviews would have no record on the 'one' side"

Does this mean a relationship is set incorrectly?

Thanks again for all your help!
Brian
"MacDermott" <ma********@nospam.com> wrote in message

news:<uB*****************@newsread2.news.atl.earth link.net>...

> Brian -
> I'm really concerned about your using the PC Serial number as the PK

of

> your Employees table.
> Is each PC really "joined at the hip" to one employee?
> Don't you ever upgrade PC's?
> Move an employee from one PC to another?
>
> But aside from that, you might try thinking of your form this way:
> When you display a record from the Review table, you want to see

the > Employee information for that record.
> That does not mean that the Employee information must actually be in the

> Review table, only that it must be displayed when a Review record is
> displayed.
> All you need in the Review table is the PK from the Employee

record.

> Then you can retrieve the Employee information whenever you need it.
>
> Does this make sense?
> - Turtle
>
> "Brian" <bk****@hotmail.com> wrote in message
> news:3d**************************@posting.google.c om...
> > Hey Turtle!
> >
> > Sorry about taking so long to get back - they've got me on some

other > > projects over here. Anyhow....
> >
> > I'm not sure if I worded my problem correctly when I posted this

message.

> > I've done as you suggested, and things are starting to come together. But

> > my problem is that all records from the Employee table are coming

up in
the

> > form. What I really want is that the form only show records from

the Review

> > table. The scenario is.....
> >
> > A technician goes out on a help call. Get's to the Employees

(George's)

> > desk, and pulls up the database. Since this is a New trouble

call, he > > wants to enter a new record into the Review table. But hopefully he won't

> > need to enter in the employee information - just pick George from

the dropdown

> > (combo box) which is coming from the Employee table. Assuming

George is

> > entered correctly in the Employee table, all the information

(especially

> > his PC serial number - the primary key of the Employee Table) will

show
up.

> > Then, once I input some additional information on the form, it

should update

> > the Review table with all that information.
> >
> > I want to apologize in advance for being so hard-headed on this!!

I should

> > be able to figure this out, but keep hitting dead ends.
> >
> > Thanks again!
> > Brian
> >
> >
> > "MacDermott" <ma********@nospam.com> wrote in message

news:<kC****************@newsread3.news.atl.earthl ink.net>...

> > > If you want to update with newer values when available, you

don't need
to

> > > copy the values from Employee to Review.
> > > Put a field in Review to hold the Primary Key for the

appropriate record
in

> > > Employee.
> > > Put a combobox on your form, with its ControlSource set to this

field in
the

> > > Review table.
> > > Write a query which contains the Primary Key and all of the

fields from

> > > Employee that you want to display on this form. Make the

Primary Key
the

> > > first field in the query.
> > > Set this query as the combobox's RowSource.
> > > Set its BoundColumn property to 1.
> > > Set its ColumnCount property to the number of fields in the

query. > > > Set all of the ColumnWidths to zero except the second one; this

is the
value

> > > that will show in the combobox.
> > >
> > > Got all that? Now...
> > > Add a textbox to your form. Let's say you've called your

combobox

> > > cboEmployee.
> > > set the textbox's ControlSource like this:
> > > =cboEmployee.Column(2)
> > > Now when you select an employee in the combobox, that name

should show
in

> > > the combobox itself, and whatever's in the 3rd column should

show in your

> > > textbox.
> > > Once you get this example working, you should be able to build

the textboxes

> > > to display the data from your other combobox columns.
> > >
> > > HTH
> > > - Turtle
> > >

[Brian]

Hey Turtle,

First off sorry about the name - don't know where I got Matt. As for
your
questions...

- RecordSource is a query called Review Qry
- Joined field is named Login_ID in both tables; is the primary key in
the
Employees Table
- Here's the SQL for the Review Qry:

SELECT Employees.Login_ID, Employees.FirstName, Employees.LastName,
Employees.PC, Employees.Department, Employees.[Drop Location]
FROM Employees RIGHT JOIN Reviews ON Employees.Login_ID =
Reviews.Login_ID;

Thanks!
Brian

"MacDermott" <ma********@nospam.com> wrote in message news:<6L*******************@newsread3.news.atl.ear thlink.net>...

What is the recordsource for your form?
If it's a query, could you post the SQL for it?

What are the fields in the two tables which are joined in the relationship?

- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Let me see....

The only relationship is the one between these two tables (Employees
and Review) and it's set as one employee to many review records. I
created this by going into Tools --> Relationships. The relationship
is also showing in the Query Wizard.

The two records I created in Reviews each have an associated record in
the Employee table. This is proved by running the Review Query, which
return both records.

I haven't reached the point of trying to add records yet...only trying to
work with the two existing records.

I have not set it to enforce referential integrity.

Guess this is why there's a learning curve, huh?

Thanks!!
Brian

[MacDermott]

Hi, Brian!

Is [Review Qry] also the RowSource for the combobox?

Perhaps you could start by creating a form based just on your Employees
table, with a combobox to bring up the record you want.
Once you get that going, we can look at adding Reviews.

HTH
- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hey Turtle,

First off sorry about the name - don't know where I got Matt. As for
your
questions...

- RecordSource is a query called Review Qry
- Joined field is named Login_ID in both tables; is the primary key in
the
Employees Table
- Here's the SQL for the Review Qry:

SELECT Employees.Login_ID, Employees.FirstName, Employees.LastName,
Employees.PC, Employees.Department, Employees.[Drop Location]
FROM Employees RIGHT JOIN Reviews ON Employees.Login_ID =
Reviews.Login_ID;

Thanks!
Brian

"MacDermott" <ma********@nospam.com> wrote in message

news:<6L*******************@newsread3.news.atl.ear thlink.net>...

What is the recordsource for your form?
If it's a query, could you post the SQL for it?

What are the fields in the two tables which are joined in the relationship?
- Turtle

"Brian" <bk****@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Let me see....

The only relationship is the one between these two tables (Employees
and Review) and it's set as one employee to many review records. I
created this by going into Tools --> Relationships. The relationship
is also showing in the Query Wizard.

The two records I created in Reviews each have an associated record in
the Employee table. This is proved by running the Review Query, which
return both records.

I haven't reached the point of trying to add records yet...only trying to work with the two existing records.

I have not set it to enforce referential integrity.

Guess this is why there's a learning curve, huh?

Thanks!!
Brian