472,365 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,365 software developers and data experts.

Newby question - Please help!!

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!
Nov 12 '05 #1
20 2190
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" <me@home.com> 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!

Nov 12 '05 #2
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" <me@home.com> 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!

Nov 12 '05 #3
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" <me@home.com> 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!


Nov 12 '05 #4
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" <me@home.com> 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!


Nov 12 '05 #5
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" <me@home.com> 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" <me@home.com> 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!

Nov 12 '05 #6
"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" <me@home.com> 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!

Nov 12 '05 #7
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" <me@home.com> 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!

Nov 12 '05 #8
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" <me@home.com> 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" <me@home.com> 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!

Nov 12 '05 #9
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" <me@home.com> 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" <me@home.com> 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!
>

Nov 12 '05 #10
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" <me@home.com> 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" <me@home.com> 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!
> >
>

Nov 12 '05 #11
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" <me@home.com> 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" <me@home.com> 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!
> > >
> >

Nov 12 '05 #12
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

Nov 12 '05 #13
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

Nov 12 '05 #14
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

Nov 12 '05 #15
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

Nov 12 '05 #16
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
>

Nov 12 '05 #17
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
> >

Nov 12 '05 #18
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
> > >

Nov 13 '05 #19
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

Nov 13 '05 #20
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

Nov 13 '05 #21

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Damien | last post by:
I have just built a simple stopwatch application, but when i f5 to get things goings i get this message, An unhandled exception of type 'System.ArithmeticException' occurred in...
1
by: Hugo de Payns | last post by:
Goodmorning. I like to put this tablecode into a stylesheet; <TABLE height=212 cellSpacing=6 cellPadding=12 width="95%" border=0> Now, i have some tutorials about css, but i just cant figure...
8
by: Ask | last post by:
G'day All, Just thought I'd drop in and say hi. I'm new to Python, but old to software development. Python is one of the languages used in my new job, so I've just bought a book, read it, and...
2
by: Dave | last post by:
I hope someone can help i have a little understanding of Access and need to create a database that will allow me to keep track of employees work hours on various processes involved in building...
10
by: Fred Nelson | last post by:
Hi: I have programmed in VB.NET for about a year and I'm in the process of learing C#. I'm really stuck on this question - and I know it's a "newby" question: In VB.NET I have several...
8
by: DavidB | last post by:
I'm trying to delete a file with File.Delete("c:\Documents and Settings\%username%\Application Data\Microsoft\Excel\excel*.xlb") On doing this I get an Exception "Illegal characters in path" -...
2
by: Simon | last post by:
I setup a Midi & Child form I can open the child form and display info from a Text file I need to enable the user to change info on the child form and when closed write the text file back to...
4
by: Fred Nelson | last post by:
Hi: I'm developing a web application that needs to have five values, each retrieved from cookies on many pages. If I have five "Request.Cookies" commands together does this cause five "round...
2
by: johnnyG | last post by:
Greetings, I'm studying for the 70-330 Exam using the MS Press book by Tony Northrup and there are 2 side-by-side examples of using the SHA1CryptoServiceProvider to create a hash value from a...
5
by: =?Utf-8?B?Q2hyaXMgRA==?= | last post by:
Hi - I was hoping to use the range validator to check that the user's age is between 10 & 65 based upon an entered DOB. I thought I could use max&min date variables in the Validator & have those...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.