autofill on a form

[tess]

I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in my
head.

[Steve]

Your tables are not correct. The following are recommended:
TblSalesman
SalesmanID
FirstName
LastName
<<other salesman contact fields>>

TblZipcode
ZipcodeID
ZipState
ZipCode

TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID

TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>

Note that with the above tables, you do not enter the salesman into
TblLeadInfo. In any form or report displaying LeadInfo, you can get salesman
for any zipcode from TblSalesmanAssignment.

Your form needs a query named QryMyForm for the recordsource. The query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo. The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

You need a combobox to enter ZipcodeID. For the rowsource of the combobox
you need a query based on
TblZipcode and TblSalesmanAssignment. The fields need to be:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

Set the bound column property of the combobox to 1, column count to 2 and
column width to 0;1".

Put the following code in the AfterUpdate event of the combobox:
Me.Requery


<te**@conwaycorp.netwrote in message
news:11**********************@n2g2000hse.googlegro ups.com...

>I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in my
head.

[tess]

On Jul 10, 1:18 pm, "Steve" <[e-mail]:

Your tables are not correct. The following are recommended:
TblSalesman
SalesmanID
FirstName
LastName
<<other salesman contact fields>>

TblZipcode
ZipcodeID
ZipState
ZipCode

TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID

TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>

Note that with the above tables, you do not enter the salesman into
TblLeadInfo. In any form or report displaying LeadInfo, you can get salesman
for any zipcode from TblSalesmanAssignment.

Your form needs a query named QryMyForm for the recordsource. The query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo. The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

You need a combobox to enter ZipcodeID. For the rowsource of the combobox
you need a query based on
TblZipcode and TblSalesmanAssignment. The fields need to be:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

Set the bound column property of the combobox to 1, column count to 2 and
column width to 0;1".

Put the following code in the AfterUpdate event of the combobox:
Me.Requery

<[e-mail] in message

news:11**********************@n2g2000hse.googlegro ups.com...

I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in my
head.- Hide quoted text -

- Show quoted text -

Thanks, that was of some help. But, if I base the form on a query, I
can't update it. I'll be using this form to enter information on
leads, preferably selecting a salesman from the drop down based on zip
code, then having a command button to email all the entered data to
him (i have the email part done, along with a Dial command if I want
to call him).

[Steve]

<<if I base the form on a query, I can't update it. >>

That's not true! When you select Zipcode in the combobox and enter dasta in
the LeadInfo fields, it all gets saved to TblLeadInfo.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

<te**@conwaycorp.netwrote in message
news:11**********************@p39g2000hse.googlegr oups.com...

On Jul 10, 1:18 pm, "Steve" <[e-mail]:

>Your tables are not correct. The following are recommended:
TblSalesman
SalesmanID
FirstName
LastName
<<other salesman contact fields>>

TblZipcode
ZipcodeID
ZipState
ZipCode

TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID

TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>

Note that with the above tables, you do not enter the salesman into
TblLeadInfo. In any form or report displaying LeadInfo, you can get
salesman
for any zipcode from TblSalesmanAssignment.

Your form needs a query named QryMyForm for the recordsource. The query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo. The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

You need a combobox to enter ZipcodeID. For the rowsource of the combobox
you need a query based on
TblZipcode and TblSalesmanAssignment. The fields need to be:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

Set the bound column property of the combobox to 1, column count to 2 and
column width to 0;1".

Put the following code in the AfterUpdate event of the combobox:
Me.Requery

<[e-mail] in message

news:11**********************@n2g2000hse.googlegr oups.com...

>I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in my
head.- Hide quoted text -

- Show quoted text -

Thanks, that was of some help. But, if I base the form on a query, I
can't update it. I'll be using this form to enter information on
leads, preferably selecting a salesman from the drop down based on zip
code, then having a command button to email all the entered data to
him (i have the email part done, along with a Dial command if I want
to call him).

[tess]

On Jul 10, 2:44 pm, "Steve" <[e-mail]:

<<if I base the form on a query, I can't update it. >>

That's not true! When you select Zipcode in the combobox and enter dasta in
the LeadInfo fields, it all gets saved to TblLeadInfo.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[e-mail]

<[e-mail] in message

news:11**********************@p39g2000hse.googlegr oups.com...

On Jul 10, 1:18 pm, "Steve" <[e-mail]:

Your tables are not correct. The following are recommended:
TblSalesman
SalesmanID
FirstName
LastName
<<other salesman contact fields>>

TblZipcode
ZipcodeID
ZipState
ZipCode

TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID

TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>

Note that with the above tables, you do not enter the salesman into
TblLeadInfo. In any form or report displaying LeadInfo, you can get
salesman
for any zipcode from TblSalesmanAssignment.

Your form needs a query named QryMyForm for the recordsource. The query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo. The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

You need a combobox to enter ZipcodeID. For the rowsource of the combobox
you need a query based on
TblZipcode and TblSalesmanAssignment. The fields need to be:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

Set the bound column property of the combobox to 1, column count to 2 and
column width to 0;1".

Put the following code in the AfterUpdate event of the combobox:
Me.Requery

<[e-mail] in message

>news:11**********************@n2g2000hse.googlegr oups.com...

I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in my
head.- Hide quoted text -

- Show quoted text -

Thanks, that was of some help. But, if I base the form on a query, I
can't update it. I'll be using this form to enter information on
leads, preferably selecting a salesman from the drop down based on zip
code, then having a command button to email all the entered data to
him (i have the email part done, along with a Dial command if I want
to call him).- Hide quoted text -

- Show quoted text -

I created a the query as instructed:
Your form needs a query named QryMyForm for the recordsource. The
query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo.
The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

and based my form on that query. When I try to enter data it doesn't
allow it, now error msg, just 'ding' and i can't type in or select
from the combo box.. any ideas? Note, a subform that is on this form..
I can enter data there

[Steve]

What is this about a subform? There was no previous mentin of a subform.
Nevertheless, the form should still work. Open your query in design view
then click the red exclamation in the menu to run the query. Is there a
blank line at the bottom to enter a new recprd? Is the far right navigation
button with the asterisk enabled? This will tell you if the query is
updateable. If you answered No to the questions, there is something wrong in
your query.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

<te**@conwaycorp.netwrote in message
news:11**********************@r34g2000hsd.googlegr oups.com...

On Jul 10, 2:44 pm, "Steve" <[e-mail]:

><<if I base the form on a query, I can't update it. >>

That's not true! When you select Zipcode in the combobox and enter dasta
in
the LeadInfo fields, it all gets saved to TblLeadInfo.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[e-mail]

<[e-mail] in message

news:11**********************@p39g2000hse.googleg roups.com...

On Jul 10, 1:18 pm, "Steve" <[e-mail]:
Your tables are not correct. The following are recommended:
TblSalesman
SalesmanID
FirstName
LastName
<<other salesman contact fields>>

>TblZipcode
ZipcodeID
ZipState
ZipCode

>TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID

>TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>

>Note that with the above tables, you do not enter the salesman into
TblLeadInfo. In any form or report displaying LeadInfo, you can get
salesman
for any zipcode from TblSalesmanAssignment.

>Your form needs a query named QryMyForm for the recordsource. The
query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo.
The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

>You need a combobox to enter ZipcodeID. For the rowsource of the
combobox
you need a query based on
TblZipcode and TblSalesmanAssignment. The fields need to be:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

>Set the bound column property of the combobox to 1, column count to 2
and
column width to 0;1".

>Put the following code in the AfterUpdate event of the combobox:
Me.Requery

><[e-mail] in message

>>news:11**********************@n2g2000hse.googleg roups.com...

>I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in my
head.- Hide quoted text -

>- Show quoted text -

Thanks, that was of some help. But, if I base the form on a query, I
can't update it. I'll be using this form to enter information on
leads, preferably selecting a salesman from the drop down based on zip
code, then having a command button to email all the entered data to
him (i have the email part done, along with a Dial command if I want
to call him).- Hide quoted text -

- Show quoted text -

I created a the query as instructed:
Your form needs a query named QryMyForm for the recordsource. The
query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo.
The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

and based my form on that query. When I try to enter data it doesn't
allow it, now error msg, just 'ding' and i can't type in or select
from the combo box.. any ideas? Note, a subform that is on this form..
I can enter data there

[tess]

On Jul 10, 3:35 pm, "Steve" <[e-mail]:

What is this about a subform? There was no previous mentin of a subform.
Nevertheless, the form should still work. Open your query in design view
then click the red exclamation in the menu to run the query. Is there a
blank line at the bottom to enter a new recprd? Is the far right navigation
button with the asterisk enabled? This will tell you if the query is
updateable. If you answered No to the questions, there is something wrong in
your query.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[e-mail]

<[e-mail] in message

news:11**********************@r34g2000hsd.googlegr oups.com...

On Jul 10, 2:44 pm, "Steve" <[e-mail]:

<<if I base the form on a query, I can't update it. >>

That's not true! When you select Zipcode in the combobox and enter dasta
in
the LeadInfo fields, it all gets saved to TblLeadInfo.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[e-mail]

<[e-mail] in message

>news:11**********************@p39g2000hse.googleg roups.com...

On Jul 10, 1:18 pm, "Steve" <[e-mail]:
Your tables are not correct. The following are recommended:
TblSalesman
SalesmanID
FirstName
LastName
<<other salesman contact fields>>

TblZipcode
ZipcodeID
ZipState
ZipCode

TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID

TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>

Note that with the above tables, you do not enter the salesman into
TblLeadInfo. In any form or report displaying LeadInfo, you can get
salesman
for any zipcode from TblSalesmanAssignment.

Your form needs a query named QryMyForm for the recordsource. The
query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo.
The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

You need a combobox to enter ZipcodeID. For the rowsource of the
combobox
you need a query based on
TblZipcode and TblSalesmanAssignment. The fields need to be:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

Set the bound column property of the combobox to 1, column count to 2
and
column width to 0;1".

Put the following code in the AfterUpdate event of the combobox:
Me.Requery

<[e-mail] in message

>news:11**********************@n2g2000hse.googlegr oups.com...

I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in my
head.- Hide quoted text -

- Show quoted text -

Thanks, that was of some help. But, if I base the form on a query, I
can't update it. I'll be using this form to enter information on
leads, preferably selecting a salesman from the drop down based on zip
code, then having a command button to email all the entered data to
him (i have the email part done, along with a Dial command if I want
to call him).- Hide quoted text -

- Show quoted text -

I created a the query as instructed:
Your form needs a query named QryMyForm for the recordsource. The
query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo.
The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

and based my form on that query. When I try to enter data it doesn't
allow it, now error msg, just 'ding' and i can't type in or select
from the combo box.. any ideas? Note, a subform that is on this form..
I can enter data there- Hide quoted text -

- Show quoted text -

No blank record, new record button is disabled.. so you got it,
something is wrong with the query, I'll see if I can figure out what
it is.

I didn't mention the subform because it did't seem relevant, with or
without the subform, the form should act the same.

Thanks.. and don't go away! :-)

[Steve]

If you don't find the problem with the query, post the SQL of the query.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


<te**@conwaycorp.netwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...

On Jul 10, 3:35 pm, "Steve" <[e-mail]:

>What is this about a subform? There was no previous mentin of a subform.
Nevertheless, the form should still work. Open your query in design view
then click the red exclamation in the menu to run the query. Is there a
blank line at the bottom to enter a new recprd? Is the far right
navigation
button with the asterisk enabled? This will tell you if the query is
updateable. If you answered No to the questions, there is something wrong
in
your query.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[e-mail]

<[e-mail] in message

news:11**********************@r34g2000hsd.googleg roups.com...

On Jul 10, 2:44 pm, "Steve" <[e-mail]:
<<if I base the form on a query, I can't update it. >>

>That's not true! When you select Zipcode in the combobox and enter
dasta
in
the LeadInfo fields, it all gets saved to TblLeadInfo.

>PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[e-mail]

><[e-mail] in message

>>news:11**********************@p39g2000hse.google groups.com...

On Jul 10, 1:18 pm, "Steve" <[e-mail]:
Your tables are not correct. The following are recommended:
TblSalesman
SalesmanID
FirstName
LastName
<<other salesman contact fields>>

>TblZipcode
ZipcodeID
ZipState
ZipCode

>TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID

>TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>

>Note that with the above tables, you do not enter the salesman into
TblLeadInfo. In any form or report displaying LeadInfo, you can get
salesman
for any zipcode from TblSalesmanAssignment.

>Your form needs a query named QryMyForm for the recordsource. The
query
needs to include TblSalesman, TblSalesmanAssignment and
TblLeadInfo.
The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

>You need a combobox to enter ZipcodeID. For the rowsource of the
combobox
you need a query based on
TblZipcode and TblSalesmanAssignment. The fields need to be:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

>Set the bound column property of the combobox to 1, column count to
2
and
column width to 0;1".

>Put the following code in the AfterUpdate event of the combobox:
Me.Requery

><[e-mail] in message

>>news:11**********************@n2g2000hse.googleg roups.com...

>I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a
Salesman
assigned to that area.

I have a form based on table #1 When I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.

I'm sure this is simple, I just can't seem to get it straight in
my
head.- Hide quoted text -

>- Show quoted text -

Thanks, that was of some help. But, if I base the form on a query, I
can't update it. I'll be using this form to enter information on
leads, preferably selecting a salesman from the drop down based on
zip
code, then having a command button to email all the entered data to
him (i have the email part done, along with a Dial command if I want
to call him).- Hide quoted text -

>- Show quoted text -

I created a the query as instructed:
Your form needs a query named QryMyForm for the recordsource. The
query
needs to include TblSalesman, TblSalesmanAssignment and TblLeadInfo.
The
fields need to be:
ZipcodeID (TblLeadInfo)
<<Your LeadInfo fields> (TblLeadInfo)
Salesman:[FirstName] & " " & [LastName]

and based my form on that query. When I try to enter data it doesn't
allow it, now error msg, just 'ding' and i can't type in or select
from the combo box.. any ideas? Note, a subform that is on this form..
I can enter data there- Hide quoted text -

- Show quoted text -

No blank record, new record button is disabled.. so you got it,
something is wrong with the query, I'll see if I can figure out what
it is.

I didn't mention the subform because it did't seem relevant, with or
without the subform, the form should act the same.

Thanks.. and don't go away! :-)

[tess]

Ok.. here's everything I have
tblSalesman
SalesmanID
FirstName
LastName
email
phone
tblZipCode
ZipcodeID
ZipState
ZipCode
ZipCounty
TblSalesmanAssignment
SalesmanAssignmentID
ZipcodeID
SalesmanID
TblLeadInfo
LeadInfoID
ZipcodeID
<<Your LeadInfo fields>>
qryLeadfrm includes
tblSalesman; tblSalesmanAssignment and TblLeadInfo
fields:
ZipcodeID from from tblLeadInfo) & other info fields
Salesman:[FirstName] & " " & [LastName]

I linked tblLeadInfo to tblZipcode ZipCodeID (??) and linked
tblSalesman to tblsalesmanassignment by Salesman ID

frmLeadInfo is based on above query and includes

combobox to enter ZipcodeID - rowsource of the combobox
qry based on TblZipcode and TblSalesmanAssignment
fields:
ZipcodeID
ZipCode
Sort ascending on Zipcode.

bound column property of the combobox to 1, column count to 2 and
column width to 1;0"

code in the AfterUpdate event of the combobox:
Me.Requery

all that is done and in place.. but it doesn't work, the form shows
only records already enter (2 test records) and when I selected a
different zip, the fields stay the same, here is the sql of the qry
that the form is based on.

SELECT tblLeadInfo.ZipCodeID, tblLeadInfo.leadsource,
tblLeadInfo.BZleadnumber, tblSalesman.[Full Name], tblSalesman.ID
FROM tblSalesman INNER JOIN (tblLeadInfo INNER JOIN
tblSalesmanAssignment ON tblLeadInfo.ZipCodeID =
tblSalesmanAssignment.ZipCodeID) ON tblSalesman.ID =
tblSalesmanAssignment.SMID;
thanks again...