Combo box troubles

  • Thread starter Thread starter Adrienne
  • Start date Start date
A

Adrienne

I would like my combo box to do one thing:

look at my current form (from a select query) and look at
a text box entitled NEW CAN # (that changes per
transaction)also look at a similar field in a table(field
name CAN)if the two match then automatically fill in the
same four fields from the table onto the form. The auto
fill portion has to be done in Visual Basic. This is a
specific assignment. The trick to all of this is that I
have 150 different CAN's and 6,000 transactions.

Here is how the information looks:
qry:CANTEST:Form is where the changing
NEWCAN# exist (0978,5478,0927,0852,7192, etc)

tbl:CANFY2003 is where all the other fields
exist that I want to auto fill on my form
the fields are CAN,RESEARCH,DIVISION,POOL & Notes.
0978, BIOD, BIOD, RPG, Training Center

ExampleIf
qry:CANTEST:Form (NEW CAN#) = (0789)example of CAN#
tbl:CANFY2003 field(entitled CAN)(0789)
then autofield form with the following text boxes
RESEARCH BIOD (example of field)
DIVISION DAIDS (example of field)
POOL RPG (example of field)
Notes:(sometimes this field is blank)Training Projects(exp)
Keep in mind I have 150 different CAN #'s on my table
examples below:

CAN RESEARCH DIVISION POOL Notes
0789 BIOD DAIDS RPG Training Center
0960 BIOD DAIDS SELECT PAY (No Notes)
0927 BIOD DAIT CAREERS (No Notes)

Again all I'm trying to do is have my combo box look at a
form text box entitled NEW CAN# and look at a table in the
field entitled CAN if the two match then fill in the other
4 fields from the table onto my form. The other 4 fields
from the table are Research, Division, Pool and Notes.
Keep in mind again there are 150 different CAN's and about
6000 different transactions. I know there are 50 ways but
I have to use an event procedure for all 150 different
CAN's using a Visual Basic Code. Someone provided me with
and example of a zip code and I tried to use my info and
it did not work. The event procedure looks like this:

Private Sub ZipCode_AfterUpDate
'If Zip Code is entered fill in city and state
automatically.


Select Case ZipCode
Case "10000" to "10200"
City = "New York"
State ="NY"

End Select
End Sub


I need help deseparately. I have sent in several notes but
I'm not sure if the note was clear!!!
 
Adrienne said:
I would like my combo box to do one thing:

look at my current form (from a select query) and look at
a text box entitled NEW CAN # (that changes per
transaction)also look at a similar field in a table(field
name CAN)if the two match then automatically fill in the
same four fields from the table onto the form. The auto
fill portion has to be done in Visual Basic. This is a
specific assignment. The trick to all of this is that I
have 150 different CAN's and 6,000 transactions.
Click to expand...

Combo boxes are used by people to look things up.
Once looked up you could move to the record and then change things but it
does not sound like that is the right thing to to.
It soulds like you need a query relating NewCan and Can, using the fields
from the table taht contains Can to show the values you want.

There are any number of examples of this in the Northwind database.

I note that you have a table named CANFY2003
If this indicates that there might be a CANFY2004,CANFY2005 then this is a
good time to add a FS field to your CAN table, index it and base your forms
on a qquery that selects for a particular year.
If not you will have a lot of work ahead when somebody wants to compare 2004
with 2003.
 
Back
Top