autofill on a form

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.

re: autofill on a form

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




<tess@conwaycorp.netwrote in message
news:1184083388.103133.101910@n2g2000hse.googlegro ups.com...

re: autofill on a form

On Jul 10, 1:18 pm, "Steve" <s...@private.emailaddresswrote: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).

re: autofill on a form

<<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
resource@pcdatasheet.com





<tess@conwaycorp.netwrote in message
news:1184095038.466693.260960@p39g2000hse.googlegr oups.com...

re: autofill on a form

On Jul 10, 2:44 pm, "Steve" <s...@private.emailaddresswrote: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

re: autofill on a form

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
resource@pcdatasheet.com





<tess@conwaycorp.netwrote in message
news:1184098666.138848.248860@r34g2000hsd.googlegr oups.com...

re: autofill on a form

On Jul 10, 3:35 pm, "Steve" <s...@private.emailaddresswrote: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! :-)

re: autofill on a form

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
resource@pcdatasheet.com




<tess@conwaycorp.netwrote in message
news:1184100072.725449.289540@k79g2000hse.googlegr oups.com...

re: autofill on a form

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...