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]

Re: Newby question - Please help!!

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:4dsg90h3n3 5bs7fbcctv75ucs mradlarq5@4ax.c om...[color=blue]
> 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![/color]

[MacDermott]

Re: Newby question - Please help!!

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:4dsg90h3n3 5bs7fbcctv75ucs mradlarq5@4ax.c om...[color=blue]
> 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![/color]

[Jack Schitt]

Re: Newby question - Please help!!

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 " <macdermott@nos pam.com>
wrote:
[color=blue]
>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:4dsg90h3n 35bs7fbcctv75uc smradlarq5@4ax. com...[color=green]
>> 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![/color]
>[/color]

[Jack Schitt]

Re: Newby question - Please help!!

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"
<bouncer@localh ost.not> wrote:
[color=blue]
>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:4dsg90h3n 35bs7fbcctv75uc smradlarq5@4ax. com...[color=green]
>> 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![/color]
>[/color]

[MacDermott]

Re: Newby question - Please help!!

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:7hmh90pj8k 7mh45aj1i297ubc jvc2cu713@4ax.c om...[color=blue]
> 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 " <macdermott@nos pam.com>
> wrote:
>[color=green]
> >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:4dsg90h3n 35bs7fbcctv75uc smradlarq5@4ax. com...[color=darkred]
> >> 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![/color]
> >[/color]
>[/color]

[Julia Baresch]

Re: Newby question - Please help!!

"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
[color=blue]
>
> "Jack Schitt" <me@home.com> wrote in message
> news:4dsg90h3n3 5bs7fbcctv75ucs mradlarq5@4ax.c om...[color=green]
> > 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![/color][/color]

[Brian]

Re: Newby question - Please help!!

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? ;-Þ

jbaresch@oldrep ublic.com (Julia Baresch) wrote in message news:<505e0ad2. 0405050612.591e cf21@posting.go ogle.com>...[color=blue]
> "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
>[color=green]
> >
> > "Jack Schitt" <me@home.com> wrote in message
> > news:4dsg90h3n3 5bs7fbcctv75ucs mradlarq5@4ax.c om...[color=darkred]
> > > 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![/color][/color][/color]

[Brian]

Re: Newby question - Please help!!

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!)
bk6662@hotmail. com


"MacDermott " <macdermott@nos pam.com> wrote in message news:<Sr5mc.545 8$8S1.2332@news read2.news.atl. earthlink.net>. ..[color=blue]
> 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:7hmh90pj8k 7mh45aj1i297ubc jvc2cu713@4ax.c om...[color=green]
> > 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 " <macdermott@nos pam.com>
> > wrote:
> >[color=darkred]
> > >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:4dsg90h3n 35bs7fbcctv75uc smradlarq5@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!
> > >[/color]
> >[/color][/color]

[MacDermott]

Re: Newby question - Please help!!

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" <bk6662@hotmail .com> wrote in message
news:3dc3699c.0 405060602.12474 a95@posting.goo gle.com...[color=blue]
> 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[/color]
my[color=blue]
> Employee (lookup?) table. But when the form is filled out and loses[/color]
focus,[color=blue]
> all the data should go to the Reviews table.
>
> My confusion right now has to do with the implementation. For example -[/color]
how[color=blue]
> 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[/color]
Reviews[color=blue]
> 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[/color]
(from[color=blue]
> the Northwinds Database) isn't as close to this as I thought it was.
>
> Thanks again for your posts!
>
> Brian (aka Jack Schitt!)
> bk6662@hotmail. com
>
>
> "MacDermott " <macdermott@nos pam.com> wrote in message[/color]
news:<Sr5mc.545 8$8S1.2332@news read2.news.atl. earthlink.net>. ..[color=blue][color=green]
> > 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[/color][/color]
field[color=blue][color=green]
> > by choosing from a combobox."
> >
> > Does this help?
> > - Turtle
> >
> >
> > "Jack Schitt" <me@home.com> wrote in message
> > news:7hmh90pj8k 7mh45aj1i297ubc jvc2cu713@4ax.c om...[color=darkred]
> > > 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 " <macdermott@nos pam.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:4dsg90h3n 35bs7fbcctv75uc smradlarq5@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[/color][/color][/color]
tables -[color=blue][color=green][color=darkred]
> > > >> to keep it simple I'll say that one is an Employee File[/color][/color][/color]
(Employees),[color=blue][color=green][color=darkred]
> > > >> and the other is an Address File (Addresses) linked by SSN. I've[/color][/color][/color]
set[color=blue][color=green][color=darkred]
> > > >> Addresses as a Lookup Table - If the user starts typing in the SSN[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > >> should pull up the Employees records.
> > > >>
> > > >> I'm getting stuck in the Data Entry form. When I type in the SSN[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > >> does pull up the employee's record, but I can't get it to populate[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > >> remainder of the fields from the Address table. I want to populate
> > > >> employee name, etc... from the address table into the Employee[/color][/color][/color]
table.[color=blue][color=green][color=darkred]
> > > >> The Northwind database has a good example of what I'm trying to do,[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > >> 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!
> > > >
> > >[/color][/color][/color]

[Brian]

Re: Newby question - Please help!!

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 " <macdermott@nos pam.com> wrote in message news:<KZymc.716 5$8S1.3095@news read2.news.atl. earthlink.net>. ..[color=blue]
> 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" <bk6662@hotmail .com> wrote in message
> news:3dc3699c.0 405060602.12474 a95@posting.goo gle.com...[color=green]
> > 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[/color]
> my[color=green]
> > Employee (lookup?) table. But when the form is filled out and loses[/color]
> focus,[color=green]
> > all the data should go to the Reviews table.
> >
> > My confusion right now has to do with the implementation. For example -[/color]
> how[color=green]
> > 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[/color]
> Reviews[color=green]
> > 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[/color]
> (from[color=green]
> > the Northwinds Database) isn't as close to this as I thought it was.
> >
> > Thanks again for your posts!
> >
> > Brian (aka Jack Schitt!)
> > bk6662@hotmail. com
> >
> >
> > "MacDermott " <macdermott@nos pam.com> wrote in message[/color]
> news:<Sr5mc.545 8$8S1.2332@news read2.news.atl. earthlink.net>. ..[color=green][color=darkred]
> > > 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[/color][/color]
> field[color=green][color=darkred]
> > > by choosing from a combobox."
> > >
> > > Does this help?
> > > - Turtle
> > >
> > >
> > > "Jack Schitt" <me@home.com> wrote in message
> > > news:7hmh90pj8k 7mh45aj1i297ubc jvc2cu713@4ax.c om...
> > > > 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 " <macdermott@nos pam.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:4dsg90h3n 35bs7fbcctv75uc smradlarq5@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[/color][/color]
> tables -[color=green][color=darkred]
> > > > >> to keep it simple I'll say that one is an Employee File[/color][/color]
> (Employees),[color=green][color=darkred]
> > > > >> and the other is an Address File (Addresses) linked by SSN. I've[/color][/color]
> set[color=green][color=darkred]
> > > > >> Addresses as a Lookup Table - If the user starts typing in the SSN[/color][/color]
> it[color=green][color=darkred]
> > > > >> should pull up the Employees records.
> > > > >>
> > > > >> I'm getting stuck in the Data Entry form. When I type in the SSN[/color][/color]
> it[color=green][color=darkred]
> > > > >> does pull up the employee's record, but I can't get it to populate[/color][/color]
> the[color=green][color=darkred]
> > > > >> remainder of the fields from the Address table. I want to populate
> > > > >> employee name, etc... from the address table into the Employee[/color][/color]
> table.[color=green][color=darkred]
> > > > >> The Northwind database has a good example of what I'm trying to do,[/color][/color]
> in[color=green][color=darkred]
> > > > >> 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!
> > > > >
> > > >[/color][/color][/color]

[MacDermott]

Re: Newby question - Please help!!

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.Co lumn(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" <bk6662@hotmail .com> wrote in message
news:3dc3699c.0 405071050.5a687 f01@posting.goo gle.com...[color=blue]
> 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 " <macdermott@nos pam.com> wrote in message[/color]
news:<KZymc.716 5$8S1.3095@news read2.news.atl. earthlink.net>. ..[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > Primary Key to your Employee table in the Reviews table, and use a query[/color][/color]
to[color=blue][color=green]
> > retrieve the current values from the Employee table.
> > If you want to keep the older data, you'll need to write some code to[/color][/color]
copy[color=blue][color=green]
> > that data into the Reviews table.
> >
> > Let me know which way you want to go, and I'll try to offer some ideas[/color][/color]
about[color=blue][color=green]
> > how to get there.
> >
> > - Turtle
> > BTW, I see that you have a ? associated with the term "lookup table".[/color][/color]
I'm[color=blue][color=green]
> > no expert on terminology, being largely self-taught, but my experience[/color][/color]
is[color=blue][color=green]
> > that "lookup table" is usually used to describe a table used in the[/color][/color]
first[color=blue][color=green]
> > way; i.e. the current values in that table are associated with records[/color][/color]
in[color=blue][color=green]
> > the main table.
> >
> >
> > "Brian" <bk6662@hotmail .com> wrote in message
> > news:3dc3699c.0 405060602.12474 a95@posting.goo gle.com...[color=darkred]
> > > Hey!
> > >
> > > I really appreciate your post - and I know you're right. I need to[/color][/color][/color]
"step[color=blue][color=green][color=darkred]
> > > 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,[/color][/color][/color]
I'll[color=blue][color=green][color=darkred]
> > > step back and wonder why I had so much trouble with this one! I've[/color][/color][/color]
done[color=blue][color=green][color=darkred]
> > > as you suggested, and sketched out a diagram of how this should work.[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > have a form as my main focal point, with a half dozen fields coming[/color][/color][/color]
from[color=blue][color=green]
> > my[color=darkred]
> > > Employee (lookup?) table. But when the form is filled out and loses[/color]
> > focus,[color=darkred]
> > > all the data should go to the Reviews table.
> > >
> > > My confusion right now has to do with the implementation. For[/color][/color][/color]
example -[color=blue][color=green]
> > how[color=darkred]
> > > do I take the PC Serial number, Employee Name, etc... fields that I[/color][/color][/color]
pulled[color=blue][color=green][color=darkred]
> > > from the Employee table, and insert them into this new record in the[/color]
> > Reviews[color=darkred]
> > > table? Should I use a buffer of some sort...copy these fields, and[/color][/color][/color]
then[color=blue][color=green][color=darkred]
> > > write to the Reviews table? It turns out the example I mentioned[/color][/color][/color]
earlier[color=blue][color=green]
> > (from[color=darkred]
> > > the Northwinds Database) isn't as close to this as I thought it was.
> > >
> > > Thanks again for your posts!
> > >
> > > Brian (aka Jack Schitt!)
> > > bk6662@hotmail. com
> > >
> > >
> > > "MacDermott " <macdermott@nos pam.com> wrote in message[/color]
> > news:<Sr5mc.545 8$8S1.2332@news read2.news.atl. earthlink.net>. ..[color=darkred]
> > > > Sometimes the hardest part of writing an application is getting[/color][/color][/color]
clear[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
table",[color=blue][color=green][color=darkred]
> > > > I would design it like this:
> > > > "open a new record in the HelpLog table. Then populate the User[/color]
> > field[color=darkred]
> > > > by choosing from a combobox."
> > > >
> > > > Does this help?
> > > > - Turtle
> > > >
> > > >
> > > > "Jack Schitt" <me@home.com> wrote in message
> > > > news:7hmh90pj8k 7mh45aj1i297ubc jvc2cu713@4ax.c om...
> > > > > 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[/color][/color][/color]
simpler[color=blue][color=green][color=darkred]
> > > > > (in my mind!!) What I'm working on is a HelpDesk type[/color][/color][/color]
application. A[color=blue][color=green][color=darkred]
> > > > > 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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > employee record is found it should populate this form, which will[/color][/color][/color]
add[color=blue][color=green][color=darkred]
> > > > > corresponding records into the HelpLog Databasem which contains
> > > > > additional fields such as techician name, problem, solution,[/color][/color][/color]
etc...[color=blue][color=green][color=darkred]
> > > > > So it should create a new record for every trouble call.
> > > > >
> > > > > Thanks!
> > > > >
> > > > > On Wed, 05 May 2004 11:41:27 GMT, "MacDermott "[/color][/color][/color]
<macdermott@nos pam.com>[color=blue][color=green][color=darkred]
> > > > > 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[/color][/color][/color]
employee?[color=blue][color=green][color=darkred]
> > > > > >
> > > > > > - Turtle
> > > > > >
> > > > > >"Jack Schitt" <me@home.com> wrote in message
> > > > > >news:4dsg90h3n 35bs7fbcctv75uc smradlarq5@4ax. com...
> > > > > >> I thought I was starting to get a handle on Access, until I[/color][/color][/color]
tried[color=blue][color=green][color=darkred]
> > > > > >> doing something useful...now I'm stuck. I have a DB with two[/color]
> > tables -[color=darkred]
> > > > > >> to keep it simple I'll say that one is an Employee File[/color]
> > (Employees),[color=darkred]
> > > > > >> and the other is an Address File (Addresses) linked by SSN.[/color][/color][/color]
I've[color=blue][color=green]
> > set[color=darkred]
> > > > > >> Addresses as a Lookup Table - If the user starts typing in the[/color][/color][/color]
SSN[color=blue][color=green]
> > it[color=darkred]
> > > > > >> should pull up the Employees records.
> > > > > >>
> > > > > >> I'm getting stuck in the Data Entry form. When I type in the[/color][/color][/color]
SSN[color=blue][color=green]
> > it[color=darkred]
> > > > > >> does pull up the employee's record, but I can't get it to[/color][/color][/color]
populate[color=blue][color=green]
> > the[color=darkred]
> > > > > >> remainder of the fields from the Address table. I want to[/color][/color][/color]
populate[color=blue][color=green][color=darkred]
> > > > > >> employee name, etc... from the address table into the Employee[/color]
> > table.[color=darkred]
> > > > > >> The Northwind database has a good example of what I'm trying to[/color][/color][/color]
do,[color=blue][color=green]
> > in[color=darkred]
> > > > > >> 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.[/color][/color][/color]
It[color=blue][color=green][color=darkred]
> > > > > >> goes something like Me.OrderNum = Me.NewOrderNum.
> > > > > >>
> > > > > >> I'm sorry about rambling - I realize this isn't too clear.[/color][/color][/color]
Please[color=blue][color=green][color=darkred]
> > > > > >> point me in the right direction!!
> > > > > >>
> > > > > >> TIA!
> > > > > >
> > > > >[/color][/color][/color]

[Brian]

Re: Newby question - Please help!!

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 " <macdermott@nos pam.com> wrote in message news:<kCWmc.157 4$KE6.492@newsr ead3.news.atl.e arthlink.net>.. .[color=blue]
> 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.Co lumn(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
>[/color]

[Brian]

Re: Newby question - Please help!!

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 " <macdermott@nos pam.com> wrote in message news:<kCWmc.157 4$KE6.492@newsr ead3.news.atl.e arthlink.net>.. .[color=blue]
> 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.Co lumn(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
>[/color]

[MacDermott]

Re: Newby question - Please help!!

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" <bk6662@hotmail .com> wrote in message
news:3dc3699c.0 405121427.3a4c4 619@posting.goo gle.com...[color=blue]
> 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[/color]
the[color=blue]
> form. What I really want is that the form only show records from the[/color]
Review[color=blue]
> 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[/color]
dropdown[color=blue]
> (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[/color]
up.[color=blue]
> Then, once I input some additional information on the form, it should[/color]
update[color=blue]
> the Review table with all that information.
>
> I want to apologize in advance for being so hard-headed on this!! I[/color]
should[color=blue]
> be able to figure this out, but keep hitting dead ends.
>
> Thanks again!
> Brian
>
>
> "MacDermott " <macdermott@nos pam.com> wrote in message[/color]
news:<kCWmc.157 4$KE6.492@newsr ead3.news.atl.e arthlink.net>.. .[color=blue][color=green]
> > If you want to update with newer values when available, you don't need[/color][/color]
to[color=blue][color=green]
> > copy the values from Employee to Review.
> > Put a field in Review to hold the Primary Key for the appropriate record[/color][/color]
in[color=blue][color=green]
> > Employee.
> > Put a combobox on your form, with its ControlSource set to this field in[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
value[color=blue][color=green]
> > 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.Co lumn(2)
> > Now when you select an employee in the combobox, that name should show[/color][/color]
in[color=blue][color=green]
> > the combobox itself, and whatever's in the 3rd column should show in[/color][/color]
your[color=blue][color=green]
> > textbox.
> > Once you get this example working, you should be able to build the[/color][/color]
textboxes[color=blue][color=green]
> > to display the data from your other combobox columns.
> >
> > HTH
> > - Turtle
> >[/color][/color]