form entry results in results being pulled into the form

[THE BIG O]

Hello, using Access03 and I have a form that is used for data entry of a
project. As part of the project entry we are to enter the name of natsales
rep in one field and corpsales rep in another. Right now I have a drop down
menu for both fields which pulls from two tables.

Ok, here is the issue, there is a natsales rep and corpsales rep for each of
150+ branches. Obiously each rep covers multiple branches however I nor my
co-workers are going to memorize who is with what branch. So we currently
look at either a hard copy or a spreadsheet that contains that information.

What I would like to do is have it set that when one enters the branch
number in the branch field then the natsales and corpsales rep fields
automatically fill in with the appropriate sales person's name. Is this
possible?

Thanks

Michael

 

[John W. Vinson]

What I would like to do is have it set that when one enters the branch
number in the branch field then the natsales and corpsales rep fields
automatically fill in with the appropriate sales person's name. Is this
possible?

Is there *only one possibilty* given a branch? If so, where is this
information stored in your tables? Or might a given branch have more than one
rep?

John W. Vinson [MVP]

 

[hmadyson]

Yes, you just need to set some code on the AfterUpdate event of the branch
dropdown. Once something is chosen you will change the rowsource of the
second dropdown to limit the list to Select * from

where branch =
value of the branch dropdown. Then make sure to requery that dropdown. You
may also want to disable the rep dropdown until a valid value is chosen from
the branch dropdown.

Please let me know if this helps you or if you would like more help.

 

[THE BIG O]

Hello,

This does make sense but is bit over my head. I have not written much code
before.

Both the natsales rep and corp sales rep are in the same table called
nationalcorpsalesrep. branch number is column 1, natsales is column 4 and
corpsales is column 5.

I appreciate the help.

thanks

Michael

Yes, you just need to set some code on the AfterUpdate event of the branch
dropdown. Once something is chosen you will change the rowsource of the
second dropdown to limit the list to Select * from

where branch =
value of the branch dropdown. Then make sure to requery that dropdown. You
may also want to disable the rep dropdown until a valid value is chosen from
the branch dropdown.

Please let me know if this helps you or if you would like more help.

Hello, using Access03 and I have a form that is used for data entry of a
project. As part of the project entry we are to enter the name of natsales
rep in one field and corpsales rep in another. Right now I have a drop down
menu for both fields which pulls from two tables.

Ok, here is the issue, there is a natsales rep and corpsales rep for each of
150+ branches. Obiously each rep covers multiple branches however I nor my
co-workers are going to memorize who is with what branch. So we currently
look at either a hard copy or a spreadsheet that contains that information.

What I would like to do is have it set that when one enters the branch
number in the branch field then the natsales and corpsales rep fields
automatically fill in with the appropriate sales person's name. Is this
possible?

Thanks

Michael

 

[hmadyson]

Assuming that there is just one natsales rep and corp sales rep per branch, I
am not sure why you need to store all 3 pieces of data. I would think that
you could set up a listbox that shows 3 columns worth of data that can be
chosen from. If there is more than one value per branch then you will need to
populate more dropdowns and I don't think that you can get away putting at
least refresh code in the AfterUpdate event. Rowsource should be Select
natsales from table1 where branch = me!cboBranch

the code would look like

Private Sub cboBranch_AfterUpdate()
Me.cboNatsales.Requery
End Sub

Please let me know if this was helpful or if you need more help.

 

[THE BIG O]

Hi,

There is one natsales person and one corpsale person for each branch. When
we are done with a project we have to e-mail both rep's with the results thus
the reason for populating the form with both so that the project data table
has both listed. We also have a branch name assigned to the branch number
and the branch name also must populate the form. In reality I would really
like to choose the branch number in a drop down menu and have the other three
populate the form. Right now all are in drop down list form.

thanks again

Michael

 

[John W. Vinson]

There is one natsales person and one corpsale person for each branch. When
we are done with a project we have to e-mail both rep's with the results thus
the reason for populating the form with both so that the project data table
has both listed. We also have a branch name assigned to the branch number
and the branch name also must populate the form. In reality I would really
like to choose the branch number in a drop down menu and have the other three
populate the form. Right now all are in drop down list form.

You can include all three fields in the Combo Box's RowSource, and put three
unbound textboxes on the form: use control sources like

=cboBranch.Column(n)

where cboBranch is the name of the combo and n is the *zero based* position of
the desired field in the combo's query.

The branch name and rep names should certainly *NOT* be stored in this form's
table, since they can and should be looked up in the branch table whenever
needed.

John W. Vinson [MVP]