combo boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a combo box with 2 columns. The first column with a
value NSN from a table NSN and the second column with a value Price from
table NSN. I would like for both values to appear in the table when the NSN
is selected. At the moment I can only get the value in column one to appear.
I think I am doing something wrong in the coding. If it is any help I am
using Microsoft Access 2003.
 
Not sure if I understand correctly, but typically, you would only get one
value in a combo box. You are tying the data you retrieve to a particular
field. Access knows what is in the related table so there is no need to
pull that data (it would be redundant to store it in both fields).

You might be wanting to pull tow items from a table, squezze them into one
and store that in the field. This can be done using the & and the two
fields you want.

You might want to rethink your design or post a few more details as to why
you are trying to retrieve two fields into your current record and store
thos two fields into the current (single) field.

Hope I did not read your post wrong.

Rick B
 
Once again please disregard this message i was getting an error telling me
that my posts were not being posted. Sorry for the confusion.
 
I am trying to create a table that stores all of my invoices. I want to be
able to pick an NSN and then the NSN appears on the table along with the
Price of the NSN. I have both of those values stored in another table called
NSN. How would I go about doing that? I thought a combo box would be the way.
 
Try this in the AfterUpdate of your combo box

textbox.value=combobox.column(1)

The first column is 0 and the second column is 1

See if this is helpful.

lwells
 
I am a beginner at creating this and I do not fully understand how to use
afterupdate is there any way you would be able to help me out a little bit on
that?
 
Open your form in the design view and place your cursor in the combobox.
Right click on your mouse and from the dropdown list select properties. When
the properties window opens, locate the Event tab and click on it. This will
display all the events for this control. Find the one that says AfterUpdate
and place the cursor in the blank field next to it. This will display a
dropdown arrow and the elispe button, the one with three little dots. Click
the elipse button and select Code Builder. This will open the code window
with your cursor between the Sub ComboBox AfterUpdate and the End sub. This
is where you will add the previous statement. You will have to use the exact
name of the textbox and the exact name of the combobox in order for this
work. If you have spaces between the names you will need to enclose the name
with brackets [combobox name]. Once you have enter this information close
the code window, close and save your form and then reopen the form to test
it. This should give you a start.

lwells
 
Iwells thank you for atleast helping understand the afterupdate even
better...now I'm getting an error message. I tried it two ways
first I entered

NSN.value = NSN.column(0) .... this works and compiles but it is giving me
the same result as before

the I tried

NSN.value = ITT Price.column(1) ... and this gives me an error message

Run-time error '2465'
Microsoft Office Access can't find the field '|' referred to in your
expression





lwells said:
Open your form in the design view and place your cursor in the combobox.
Right click on your mouse and from the dropdown list select properties. When
the properties window opens, locate the Event tab and click on it. This will
display all the events for this control. Find the one that says AfterUpdate
and place the cursor in the blank field next to it. This will display a
dropdown arrow and the elispe button, the one with three little dots. Click
the elipse button and select Code Builder. This will open the code window
with your cursor between the Sub ComboBox AfterUpdate and the End sub. This
is where you will add the previous statement. You will have to use the exact
name of the textbox and the exact name of the combobox in order for this
work. If you have spaces between the names you will need to enclose the name
with brackets [combobox name]. Once you have enter this information close
the code window, close and save your form and then reopen the form to test
it. This should give you a start.

lwells

Effie said:
I am a beginner at creating this and I do not fully understand how to use
afterupdate is there any way you would be able to help me out a little bit on
that?
Click to expand...
Click to expand...
 
Effie,

You're on the right track. This can be done in the combo box's AfterUpdate
or can be done by using a "calculated control". For the latter, you would
simply put the equal sign and right side of the equation in the textbox's
Control Source.
NSN.value = NSN.column(0) .... this works and compiles but it is giving
me
the same result as before
Click to expand...

This doesn't work because you are trying to assign the value in control
NSN's first column to be the value of control NSN. You need to assign the
value of this column to another control, such as a textbox. Each item on the
form needs to have a unique name so that Access/VBA know which item you are
referring to.

The combo box will display more than one column in its drop down, but will
only display the value in the first visible column in the box once the
selection has been made. To show the values in the other columns, place a
textbox or textboxes (depending on how many of the other columns you want to
display) next to the combo box and set the Control Source of these textboxes
to the appropriate column in the combo box.

Example Row Source:
=NSN.Column(0)

The Column number is "zero based". What this means is that the first column
is zero, the second is one, etc.

--
Wayne Morgan
MS Access MVP


Effie said:
Iwells thank you for atleast helping understand the afterupdate even
better...now I'm getting an error message. I tried it two ways
first I entered

NSN.value = NSN.column(0) .... this works and compiles but it is giving
me
the same result as before

the I tried

NSN.value = ITT Price.column(1) ... and this gives me an error message

Run-time error '2465'
Microsoft Office Access can't find the field '|' referred to in your
expression





lwells said:
Open your form in the design view and place your cursor in the combobox.
Right click on your mouse and from the dropdown list select properties.
When
the properties window opens, locate the Event tab and click on it. This
will
display all the events for this control. Find the one that says
AfterUpdate
and place the cursor in the blank field next to it. This will display a
dropdown arrow and the elispe button, the one with three little dots.
Click
the elipse button and select Code Builder. This will open the code
window
with your cursor between the Sub ComboBox AfterUpdate and the End sub.
This
is where you will add the previous statement. You will have to use the
exact
name of the textbox and the exact name of the combobox in order for this
work. If you have spaces between the names you will need to enclose the
name
with brackets [combobox name]. Once you have enter this information
close
the code window, close and save your form and then reopen the form to
test
it. This should give you a start.

lwells

Effie said:
I am a beginner at creating this and I do not fully understand how to
use
afterupdate is there any way you would be able to help me out a little
bit on
that?

:

Try this in the AfterUpdate of your combo box

textbox.value=combobox.column(1)

The first column is 0 and the second column is 1

See if this is helpful.

lwells

:

I am trying to create a combo box with 2 columns. The first column
with a
value NSN from a table NSN and the second column with a value Price
from
table NSN. I would like for both values to appear in the table
when the NSN
is selected. At the moment I can only get the value in column one
to appear.
I think I am doing something wrong in the coding. If it is any
help I am
using Microsoft Access 2003.
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much!! That worked...It is doing what I wanted it to do now.

Wayne Morgan said:
Effie,

You're on the right track. This can be done in the combo box's AfterUpdate
or can be done by using a "calculated control". For the latter, you would
simply put the equal sign and right side of the equation in the textbox's
Control Source.
NSN.value = NSN.column(0) .... this works and compiles but it is giving
me
the same result as before
Click to expand...

This doesn't work because you are trying to assign the value in control
NSN's first column to be the value of control NSN. You need to assign the
value of this column to another control, such as a textbox. Each item on the
form needs to have a unique name so that Access/VBA know which item you are
referring to.

The combo box will display more than one column in its drop down, but will
only display the value in the first visible column in the box once the
selection has been made. To show the values in the other columns, place a
textbox or textboxes (depending on how many of the other columns you want to
display) next to the combo box and set the Control Source of these textboxes
to the appropriate column in the combo box.

Example Row Source:
=NSN.Column(0)

The Column number is "zero based". What this means is that the first column
is zero, the second is one, etc.

--
Wayne Morgan
MS Access MVP


Effie said:
Iwells thank you for atleast helping understand the afterupdate even
better...now I'm getting an error message. I tried it two ways
first I entered

NSN.value = NSN.column(0) .... this works and compiles but it is giving
me
the same result as before

the I tried

NSN.value = ITT Price.column(1) ... and this gives me an error message

Run-time error '2465'
Microsoft Office Access can't find the field '|' referred to in your
expression





lwells said:
Open your form in the design view and place your cursor in the combobox.
Right click on your mouse and from the dropdown list select properties.
When
the properties window opens, locate the Event tab and click on it. This
will
display all the events for this control. Find the one that says
AfterUpdate
and place the cursor in the blank field next to it. This will display a
dropdown arrow and the elispe button, the one with three little dots.
Click
the elipse button and select Code Builder. This will open the code
window
with your cursor between the Sub ComboBox AfterUpdate and the End sub.
This
is where you will add the previous statement. You will have to use the
exact
name of the textbox and the exact name of the combobox in order for this
work. If you have spaces between the names you will need to enclose the
name
with brackets [combobox name]. Once you have enter this information
close
the code window, close and save your form and then reopen the form to
test
it. This should give you a start.

lwells

:

I am a beginner at creating this and I do not fully understand how to
use
afterupdate is there any way you would be able to help me out a little
bit on
that?

:

Try this in the AfterUpdate of your combo box

textbox.value=combobox.column(1)

The first column is 0 and the second column is 1

See if this is helpful.

lwells

:

I am trying to create a combo box with 2 columns. The first column
with a
value NSN from a table NSN and the second column with a value Price
from
table NSN. I would like for both values to appear in the table
when the NSN
is selected. At the moment I can only get the value in column one
to appear.
I think I am doing something wrong in the coding. If it is any
help I am
using Microsoft Access 2003.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top