Populate values in 3 bound txt boxes based on value from a bound c

[broncojim]

I am new to vba, and have been able to do some simple one or two line coding.
When the code has many quotes or objects I have never heard of, I get
somewhat confused. I have set up ODBC connections to Oracle tables and
created a form to input new records. I want to select a value in a bound
combo box, cmbITEMNUMBER, and have corresponding values automatically
populated in 3 bound text boxes, KVA, SECVOLT and INVLOC. With that being
said, I am hoping someone will be so nice as to help write the code for the
follwing scenario. The following data will be used and is a complete list.

ITEMNUMBER KVA SECVOLT LOCATION
28586000 10 120/240 NEW PY2
28586005 15 120/240 NEW PY2
28586010 25 120/240 NEW PY2
28586015 50 120/240 NEW PY2
28586040 50 240/120 NEW PY2
28586020 75 120/240 NEW PY2
28586025 100 120/240 NEW PY2
28586080 112.5 208/120 NEW PY4B
28586120 150 480/277 NEW PY4A
28586030 167 120/240 NEW PY2
28586095 300 208/120 NEW PY4B
28586165 225 240/120 NEW PY4B
28586160 300 240/120 NEW PY4B
28586115 112.5 480/277 NEW PY4A
28586125 300 480/277 NEW PY4A
28586100 500 208/120 NEW PY4A
28586105 750 208/120 NEW PY4A
28586110 1000 208/120 NEW PY4A
28586135 750 480/277 NEW PY4A
28586140 1000 480/277 NEW PY4A
28586155 2500 480/277 NEW PY4A
28586034 15 480/240 NEW PY3A
28586145 1500 480/277 NEW PY4A
28586130 500 480/277 NEW PY4A
28586075 75 208/120 NEW PY4B
28586085 150 208/120 NEW PY4B
28586090 225 208/120 NEW PY4B
28586035 25 240/120 NEW PY3A
28586045 75 240/120 NEW PY3B
28586050 100 240/120 NEW PY3A
28586055 167 240/120 NEW PY3B
28586001 10 240/480 NEW PY2
28586006 15 240/480 NEW PY2
28586011 25 240/480 NEW PY2
28586019 75 240/480 NEW PY2
28586026 100 240/480 NEW PY2
28586031 167 240/480 NEW PY2
28586170 50 277/480 NEW PY2
28586036 25 480/240 NEW PY3A
28586065 100 480/240 NW PY3A
28586070 167 480/240 NEW PY3A

This data is directly from a query called qryITEMNUMBER, so it can be used
as a recordsoruce. Please help!

Thanks,

Jim

 

[Bill]

Jim,
If I'm understanding you correctly then you need
3 un-bound text boxes say tbKVA, tbSecvolt and
tbLocation. Then, if the records returned in the
Oracle table ARE NOT divided into fields, then
you'll have to parse them out with something like

Dim Oracle() As String
Dim NoFlds As Int
Dim I As Int

Oracle = split(OracleRecord, " ") 'Parse record
'Expected number of fields? 4 or more
NoFlds = ubound(Oracle) ' 0 origin indexing

If NoFlds >= 3 then
tbKVA = Oracle(1)
tbSecfolt = Oracle(2)
tbLocation = Oracle(3)
If NoFlds > 3 then
Do For I = 4 to NoFlds
tbLocation = tbLocation & " " & Oracle(I)
Next
End If
Else
Msgbox "Oracle record format not recognized."
End If

If the Oracle records are already broken into the
desired fields, then you simply assign the respective
values to your text boxes. Note that I'm assuming
that you've NOT loaded the Oracle records into
Access, as that would necessarily change the way
in which the textboxes are bound.

Bill

 

[broncojim]

Bill,

Thanks for the response. The text boxes and combo box are all actually
bound controls and are divided into fields. Also, I'm not quite sure what
you mean by the Oracle records not being loaded into Access. I have set up
ODBC links to the Oracle tables, so they have not been imported but you can
see all the records when opening the tables. I hope this explains my
situation more clearly.

Thanks again,

Jim

 

[Bill]

Gordon,

Which field is the combo bound to? I'm not clear
how your form is designed. The use of a combo
box suggests, though not necessarily, that the form
IS NOT continuous. In any case, why isn't the
combo box multi-column so that the all of the fields
of any item appear as you scroll the combo?

Bill

 

[broncojim]

Bill,

The combo is bound to ITEMNUMBER and the form is not continuous. The combo
box is multi-column. Tell me if I am wrong here, but don't the text boxes
need to be unbound in order for the control source to be set to column 1, 2
and 3 for the text boxes? As far as I know, the text box controls have to be
bound so that the information is written to the table for the corresponding
fields. This form is a data entry form only.

Thanks,


Jim

 

[Bill]

Sorry Jim, I have correspondence in progress with
a Gordon and wasn't paying due attention.

If you have a multi-column combo that contains the
fields of interest, why do you then have separate text
boxes intended for the same data? Are you wanting
to duplicate the data into new records in your Access
DB?

Bill

 

[broncojim]

No problem.

Is it possible to have a multi column combo box insert data into the table
in multiple fields when inputting a new record? If so, I'm not sure how to
do this. I certainly do not want to duplicate a record.

Thanks,

Jim

 

[Bill]

Jim,
I'm beginning to sense that your wanting to create
a new record in your DB by simply entering data
into bound text boxes. If that's the case, then you
need to understand that you can't create new
table records that way. Is that what you're trying
to do?
Bill

 

[broncojim]

Bill,

Yes, that is the case. Actually it is working right now by manually
entering all the data (I have checked the table and the data entered into the
text boxes is there). I am just looking for a shortcut by selecting the data
for the item number in the cmbITEMNUMBER combo box and have the data
automatically populate the bound text boxes. Maybe I am going about this in
the wrong way?

 

[Bill]

Well Jim, you got me. Given a table with named
fields within each record, how does Access know
which table record to bind to unless a new record
is assumed, which would be a new to me. You
might want to try manually entering data into the
text box, look at the table in datasheet view, then
repeat the process to see if the same record is
being changed.
Bill