Filling in 2 text boxes from 1 value

[Guest]

Hi all, what I am trying to do is this:

On my form I have a combo box that is a pull down to get the name of a
product, and the product has a unique number code with it. I also have a box
for the Product unique number right next to it. What I would like to do is
when the first Product Name is chosen from the pull-down, the Product unique
number box is filled in automatically with the corresponding number.

I hope I explained it ok, can someone help me with this if it is possible?

Thanks a million,
Steph.

 

[Guest]

Hi Steph,

Include the unique product number in the row source of your combo box. The
row source is typically a SQL statement or saved query. Something like this,
where ProductID represents the number:

Example Row Source:
SELECT ProductID, ProductName FROM Products ORDER BY ProductName;

In this query, ProductID is the first column and ProductName is the second
column. However, when referring to columns in VBA code, the columns are
zero-based. If the Bound Column is set to 1 (ie. the ProductID column) then
it becomes even easier; you don't need to worry about the zero-based business.

Use the After Update event procedure for your combo box. The following
example uses a combo box named cboProducts, and a textbox named txtProdNum.
Set the column count to 2, and the column widths to: 0";2"

The row source, as shown above for the combo box, is a valid SQL Statement
in the sample Northwind database.


Option Compare Database
Option Explicit

Private Sub cboProducts_AfterUpdate()
On Error GoTo ProcError

Me.txtProdNum = Me.cboProducts


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cboProducts_AfterUpdate..."
Resume ExitProc
End Sub


Now, lets suppose that you needed some other number, for example the
SupplierID, but you wanted to leave the ProductID as the bound column (ie.
Column 1). You could replace the above row source with this SQL statement:

SELECT ProductID, ProductName, SupplierID FROM Products ORDER BY ProductName;

Change the column count to 3, and set the column widths to 0;2";0.

The SupplierID is now the third visible column when you run the query.
However, when referring to this column in VBA code, the columns are
zero-based. Thus, you would use this line of code instead in the above
procedure:

Me.txtProdNum = Me.cboProducts.Column(2)


Hope this helps.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom, thanks for the response. I have done that for the most part and what
I have is a drop-down menu that SHOWS me the 2 things I need, a Aid Name and
a Light List Number, but when I select that item, it staill only puts in the
first thing, which is the Aid Name. I still need to have another text box be
filled with the second item, the Light List Number. That part I am stuck
on.... arggh.

Thanks for any help.

Steph

 

[Guest]

Hi Steph,

Please provide the following information:

Combo Box
1.) The name of your combo box
2.) The Row Source for your combo box (give the SQL statement, not just the
name of a saved query)
3.) The Row Source Type
4.) The Bound Column
5.) The Column Count and Column Widths, as shown on the Format tab

Text boxes
6.) The names of each text box


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Here ya go Tom:


1.) The name of your combo box - Aid
2.) The Row Source for your combo box (give the SQL statement, not just the
name of a saved query) - SELECT [Aid Assignment List_OLD].[ID], [Aid
Assignment List_OLD].[NAME], [Aid Assignment List_OLD].[LLNR] FROM [Aid
Assignment List_OLD] ORDER BY [NAME];
3.) The Row Source Type - Table/Query
4.) The Bound Column - 1
5.) The Column Count - 3
and Column Widths, as shown on the Format tab -
0";2.5834";0.5209"

Text boxes
6.) The names of each text box

The Combo Box is where I have the pull-down menu that provides the 2 items,
Aid Name and LLNR. The text box I would like the LLNR to show up when an Aid
is selected is called LLNR.

Thanks for any help!!!

 

[Guest]

Hi Steph,

This should work for you. I set up a table named Aid Assignment List_OLD,
with fields named ID, Name and LLNR. Everything works fine in my test:


Option Compare Database
Option Explicit

Private Sub Aid_AfterUpdate()
On Error GoTo ProcError

Me.LLNR = Me.Aid.Column(2)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Aid_AfterUpdate..."
Resume ExitProc
End Sub



Unsolicited advice: You really should avoid using any spaces or reserved
words in Access or JET for things that you assign a name to. It turns out
that "NAME" is a reserved word. You can usually get away with using reserved
words, as long as you are careful to use square brackets [ ], but the best
policy is to avoid using reserved words all-together. Here are some reference
articles for you:

Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

Using a Naming Convention

http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp

Reserved Words
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Here ya go Tom:


1.) The name of your combo box - Aid
2.) The Row Source for your combo box (give the SQL statement, not just the
name of a saved query) - SELECT [Aid Assignment List_OLD].[ID], [Aid
Assignment List_OLD].[NAME], [Aid Assignment List_OLD].[LLNR] FROM [Aid
Assignment List_OLD] ORDER BY [NAME];
3.) The Row Source Type - Table/Query
4.) The Bound Column - 1
5.) The Column Count - 3
and Column Widths, as shown on the Format tab -
0";2.5834";0.5209"

Text boxes
6.) The names of each text box

The Combo Box is where I have the pull-down menu that provides the 2 items,
Aid Name and LLNR. The text box I would like the LLNR to show up when an Aid
is selected is called LLNR.

Thanks for any help!!!

 

[Guest]

Thanks Tom! Now a stupid question, where again do I paste that code, was it
on the Row Source?

Thanks again for all the help!

Hi Steph,

This should work for you. I set up a table named Aid Assignment List_OLD,
with fields named ID, Name and LLNR. Everything works fine in my test:


Option Compare Database
Option Explicit

Private Sub Aid_AfterUpdate()
On Error GoTo ProcError

Me.LLNR = Me.Aid.Column(2)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Aid_AfterUpdate..."
Resume ExitProc
End Sub



Unsolicited advice: You really should avoid using any spaces or reserved
words in Access or JET for things that you assign a name to. It turns out
that "NAME" is a reserved word. You can usually get away with using reserved
words, as long as you are careful to use square brackets [ ], but the best
policy is to avoid using reserved words all-together. Here are some reference
articles for you:

Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

Using a Naming Convention

http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp

Reserved Words
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Here ya go Tom:


1.) The name of your combo box - Aid
2.) The Row Source for your combo box (give the SQL statement, not just the
name of a saved query) - SELECT [Aid Assignment List_OLD].[ID], [Aid
Assignment List_OLD].[NAME], [Aid Assignment List_OLD].[LLNR] FROM [Aid
Assignment List_OLD] ORDER BY [NAME];
3.) The Row Source Type - Table/Query
4.) The Bound Column - 1
5.) The Column Count - 3
and Column Widths, as shown on the Format tab -
0";2.5834";0.5209"

Text boxes
6.) The names of each text box

The Combo Box is where I have the pull-down menu that provides the 2 items,
Aid Name and LLNR. The text box I would like the LLNR to show up when an Aid
is selected is called LLNR.

Thanks for any help!!!

 

[Guest]

Hi Steph,

With the form open in design view, click on View > Code. This should open a
code module associated with the form (also known as CBF or code behind form).
Paste the code I gave you into the new code module. Then click on Debug >
Compile ProjectName, where ProjectName represents the name of your VBA
project (likely the name of your database, but one can change it).

Note: It is a good habit to get into to click on Debug > Compile
periodically, when you are working with VBA code. That way, you can correct
compile errors as they happen, instead of letting lots of them build up.

Close, or minimize, the code module. Back in form design view, display the
Properties dialog, if it is not already displayed, by clicking on View >
Properties (or pressing the F4 button [Access 2002 and later]). Select the
combo box in question. You should see the name of the combo box in the blue
title bar of the Properties dialog. Click on the Event tab of this dialog.
You should now see [Event Procedure] listed for the After Update listing, if
all worked okay.

As an alternative to simply clicking on View > Code at the beginning, you
could have clicked into the After Update event procedure for the combo box.
You should now see a small button with three dots appear. Click on this build
button. One of two things will happen, depending on a setting that you have
under Tools > Options on the Forms/Reports tab. You will either jump directly
into the code module [ie. "Always Use Event Procedures" is checked as your
option], with the skelton of the new event procedure already created, ie.:

Option Compare Database
Option Explicit

Private Sub Aid_AfterUpdate()

End Sub


or, you will see a "Choose Builder" dialog (default setting, with "Always
Use Event Procedures" unchecked) where you need to pick from a list that
reads:

Expression Builder
Macro Builder
Code Builder

In this case, you want to click on Code Builder.

Try out the various methods I have outlined to open the code behind form,
with and without the "Always Use Event Procedures" checked. Don't add
duplicate copies of the same named event procedure, or else you will generate
a compile error.

MOST IMPORTANTLY: When you first create a new module, make sure that you see
Option Explicit as the second line of code, automatically inserted for you.
If you do not see this, then you should set the VBA option "Require Variable
Declaration". I talk about that more in this short "Gem Tip":

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


A couple more points:
Back on the Properties dialog, with the properties displayed for the Form
(you should see the word "Form" in the blue title bar), if you click on the
Other tab, you should see a property labelled "Has Module". If this is
already Yes, then you have an existing code module associated with this form.
If it is No (ie. a brand new form, or a form that has no code module), then
you should notice that this property is automatically changed to Yes, as soon
as you do anything that causes the creation of a new form module, such as
clicking on View > Code, or following the Build button and selecting Event
Procedure if you see the Choose Builder dialog.

Sometimes, it is possible for a control to become "disassociated" with an
Event Procedure. In other words, you might have the procedure pasted into the
form's code module, but you do not see [Event Procedure] when you examine the
Event tab for the control in question. This can easily happen if you cut the
control from one section of a form, and paste it into a different section. In
that case, the event in question will seem "dead". Nothing happens when you
know that something should happen, for example nothing happens when you click
on a command button, if it has lost it's On Click event procedure. The fix is
usually pretty easy: Just select Event procedure from the dropdown, and then
click on the Build Button. You will usually always "re-find" the event
procedure, unless, of course the control was renamed, in which case a new
event procedure will be created.

I hope this helps!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Tom, got it!!! You are the man!

Thanks so much, I really appreciate your patience and your teachings..

/r
Steph

Hi Steph,

With the form open in design view, click on View > Code. This should open a
code module associated with the form (also known as CBF or code behind form).
Paste the code I gave you into the new code module. Then click on Debug >
Compile ProjectName, where ProjectName represents the name of your VBA
project (likely the name of your database, but one can change it).

Note: It is a good habit to get into to click on Debug > Compile
periodically, when you are working with VBA code. That way, you can correct
compile errors as they happen, instead of letting lots of them build up.

Close, or minimize, the code module. Back in form design view, display the
Properties dialog, if it is not already displayed, by clicking on View >
Properties (or pressing the F4 button [Access 2002 and later]). Select the
combo box in question. You should see the name of the combo box in the blue
title bar of the Properties dialog. Click on the Event tab of this dialog.
You should now see [Event Procedure] listed for the After Update listing, if
all worked okay.

As an alternative to simply clicking on View > Code at the beginning, you
could have clicked into the After Update event procedure for the combo box.
You should now see a small button with three dots appear. Click on this build
button. One of two things will happen, depending on a setting that you have
under Tools > Options on the Forms/Reports tab. You will either jump directly
into the code module [ie. "Always Use Event Procedures" is checked as your
option], with the skelton of the new event procedure already created, ie.:

Option Compare Database
Option Explicit

Private Sub Aid_AfterUpdate()

End Sub


or, you will see a "Choose Builder" dialog (default setting, with "Always
Use Event Procedures" unchecked) where you need to pick from a list that
reads:

Expression Builder
Macro Builder
Code Builder

In this case, you want to click on Code Builder.

Try out the various methods I have outlined to open the code behind form,
with and without the "Always Use Event Procedures" checked. Don't add
duplicate copies of the same named event procedure, or else you will generate
a compile error.

MOST IMPORTANTLY: When you first create a new module, make sure that you see
Option Explicit as the second line of code, automatically inserted for you.
If you do not see this, then you should set the VBA option "Require Variable
Declaration". I talk about that more in this short "Gem Tip":

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


A couple more points:
Back on the Properties dialog, with the properties displayed for the Form
(you should see the word "Form" in the blue title bar), if you click on the
Other tab, you should see a property labelled "Has Module". If this is
already Yes, then you have an existing code module associated with this form.
If it is No (ie. a brand new form, or a form that has no code module), then
you should notice that this property is automatically changed to Yes, as soon
as you do anything that causes the creation of a new form module, such as
clicking on View > Code, or following the Build button and selecting Event
Procedure if you see the Choose Builder dialog.

Sometimes, it is possible for a control to become "disassociated" with an
Event Procedure. In other words, you might have the procedure pasted into the
form's code module, but you do not see [Event Procedure] when you examine the
Event tab for the control in question. This can easily happen if you cut the
control from one section of a form, and paste it into a different section. In
that case, the event in question will seem "dead". Nothing happens when you
know that something should happen, for example nothing happens when you click
on a command button, if it has lost it's On Click event procedure. The fix is
usually pretty easy: Just select Event procedure from the dropdown, and then
click on the Build Button. You will usually always "re-find" the event
procedure, unless, of course the control was renamed, in which case a new
event procedure will be created.

I hope this helps!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom! Now a stupid question, where again do I paste that code, was it
on the Row Source?

Thanks again for all the help!

 

[Guest]

Tom, got it!!! You are the man!

Thanks so much, I really appreciate your patience and your teachings..

/r
Steph

Hi Steph,

With the form open in design view, click on View > Code. This should open a
code module associated with the form (also known as CBF or code behind form).
Paste the code I gave you into the new code module. Then click on Debug >
Compile ProjectName, where ProjectName represents the name of your VBA
project (likely the name of your database, but one can change it).

Note: It is a good habit to get into to click on Debug > Compile
periodically, when you are working with VBA code. That way, you can correct
compile errors as they happen, instead of letting lots of them build up.

Close, or minimize, the code module. Back in form design view, display the
Properties dialog, if it is not already displayed, by clicking on View >
Properties (or pressing the F4 button [Access 2002 and later]). Select the
combo box in question. You should see the name of the combo box in the blue
title bar of the Properties dialog. Click on the Event tab of this dialog.
You should now see [Event Procedure] listed for the After Update listing, if
all worked okay.

As an alternative to simply clicking on View > Code at the beginning, you
could have clicked into the After Update event procedure for the combo box.
You should now see a small button with three dots appear. Click on this build
button. One of two things will happen, depending on a setting that you have
under Tools > Options on the Forms/Reports tab. You will either jump directly
into the code module [ie. "Always Use Event Procedures" is checked as your
option], with the skelton of the new event procedure already created, ie.:

Option Compare Database
Option Explicit

Private Sub Aid_AfterUpdate()

End Sub


or, you will see a "Choose Builder" dialog (default setting, with "Always
Use Event Procedures" unchecked) where you need to pick from a list that
reads:

Expression Builder
Macro Builder
Code Builder

In this case, you want to click on Code Builder.

Try out the various methods I have outlined to open the code behind form,
with and without the "Always Use Event Procedures" checked. Don't add
duplicate copies of the same named event procedure, or else you will generate
a compile error.

MOST IMPORTANTLY: When you first create a new module, make sure that you see
Option Explicit as the second line of code, automatically inserted for you.
If you do not see this, then you should set the VBA option "Require Variable
Declaration". I talk about that more in this short "Gem Tip":

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


A couple more points:
Back on the Properties dialog, with the properties displayed for the Form
(you should see the word "Form" in the blue title bar), if you click on the
Other tab, you should see a property labelled "Has Module". If this is
already Yes, then you have an existing code module associated with this form.
If it is No (ie. a brand new form, or a form that has no code module), then
you should notice that this property is automatically changed to Yes, as soon
as you do anything that causes the creation of a new form module, such as
clicking on View > Code, or following the Build button and selecting Event
Procedure if you see the Choose Builder dialog.

Sometimes, it is possible for a control to become "disassociated" with an
Event Procedure. In other words, you might have the procedure pasted into the
form's code module, but you do not see [Event Procedure] when you examine the
Event tab for the control in question. This can easily happen if you cut the
control from one section of a form, and paste it into a different section. In
that case, the event in question will seem "dead". Nothing happens when you
know that something should happen, for example nothing happens when you click
on a command button, if it has lost it's On Click event procedure. The fix is
usually pretty easy: Just select Event procedure from the dropdown, and then
click on the Build Button. You will usually always "re-find" the event
procedure, unless, of course the control was renamed, in which case a new
event procedure will be created.

I hope this helps!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom! Now a stupid question, where again do I paste that code, was it
on the Row Source?

Thanks again for all the help!

 

[Guest]

Hi Steph,

Glad I could be of help! Good luck on your project.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Ok Tom, or anyone, now I am getting greedy!!! If i have want to do the same
thing, but have TWO other text boxes filled in, what do I need to change?

In other words, what I want is when I select an item from my combo box, have
2 other text boxes automaitcally filled in with values from my table.

Thanks!

Steph

Tom, got it!!! You are the man!

Thanks so much, I really appreciate your patience and your teachings..

/r
Steph

Hi Steph,

With the form open in design view, click on View > Code. This should open a
code module associated with the form (also known as CBF or code behind form).
Paste the code I gave you into the new code module. Then click on Debug >
Compile ProjectName, where ProjectName represents the name of your VBA
project (likely the name of your database, but one can change it).

Note: It is a good habit to get into to click on Debug > Compile
periodically, when you are working with VBA code. That way, you can correct
compile errors as they happen, instead of letting lots of them build up.

Close, or minimize, the code module. Back in form design view, display the
Properties dialog, if it is not already displayed, by clicking on View >
Properties (or pressing the F4 button [Access 2002 and later]). Select the
combo box in question. You should see the name of the combo box in the blue
title bar of the Properties dialog. Click on the Event tab of this dialog.
You should now see [Event Procedure] listed for the After Update listing, if
all worked okay.

As an alternative to simply clicking on View > Code at the beginning, you
could have clicked into the After Update event procedure for the combo box.
You should now see a small button with three dots appear. Click on this build
button. One of two things will happen, depending on a setting that you have
under Tools > Options on the Forms/Reports tab. You will either jump directly
into the code module [ie. "Always Use Event Procedures" is checked as your
option], with the skelton of the new event procedure already created, ie.:

Option Compare Database
Option Explicit

Private Sub Aid_AfterUpdate()

End Sub


or, you will see a "Choose Builder" dialog (default setting, with "Always
Use Event Procedures" unchecked) where you need to pick from a list that
reads:

Expression Builder
Macro Builder
Code Builder

In this case, you want to click on Code Builder.

Try out the various methods I have outlined to open the code behind form,
with and without the "Always Use Event Procedures" checked. Don't add
duplicate copies of the same named event procedure, or else you will generate
a compile error.

MOST IMPORTANTLY: When you first create a new module, make sure that you see
Option Explicit as the second line of code, automatically inserted for you.
If you do not see this, then you should set the VBA option "Require Variable
Declaration". I talk about that more in this short "Gem Tip":

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


A couple more points:
Back on the Properties dialog, with the properties displayed for the Form
(you should see the word "Form" in the blue title bar), if you click on the
Other tab, you should see a property labelled "Has Module". If this is
already Yes, then you have an existing code module associated with this form.
If it is No (ie. a brand new form, or a form that has no code module), then
you should notice that this property is automatically changed to Yes, as soon
as you do anything that causes the creation of a new form module, such as
clicking on View > Code, or following the Build button and selecting Event
Procedure if you see the Choose Builder dialog.

Sometimes, it is possible for a control to become "disassociated" with an
Event Procedure. In other words, you might have the procedure pasted into the
form's code module, but you do not see [Event Procedure] when you examine the
Event tab for the control in question. This can easily happen if you cut the
control from one section of a form, and paste it into a different section. In
that case, the event in question will seem "dead". Nothing happens when you
know that something should happen, for example nothing happens when you click
on a command button, if it has lost it's On Click event procedure. The fix is
usually pretty easy: Just select Event procedure from the dropdown, and then
click on the Build Button. You will usually always "re-find" the event
procedure, unless, of course the control was renamed, in which case a new
event procedure will be created.

I hope this helps!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom! Now a stupid question, where again do I paste that code, was it
on the Row Source?

Thanks again for all the help!

 

[Guest]

Hi Steph,

Just include the two extra fields in the Row Source for your combo box, and
set the column count to match. Then reference them in a similar way,
remembering that fields are zero based. As an exercise, try modifying the
example I gave you, which works in Northwind, to automatically populate a
txtUnitPrice text box with the UnitPrice value that is currently stored in
the Products table. Set the control source for this text box to the Unit
Price field in the Order Details table.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Ok Tom, this is my current Row Source:

SELECT [Aid Assignment List_OLD].[ID], [Aid Assignment List_OLD].[NAME],
[Aid Assignment List_OLD].[LLNR] FROM [Aid Assignment List_OLD] ORDER BY
[NAME];

That fills in a second text box (LLNR) with that value from the table which
I still want, but would also like another text box (PRIMARY) be filled in
when something from the Combo Box is selected as well.

When I tried what you said, I then had nothing drop downin my combo box.

Sorry I am missing it. Thanks again.
Steph

 

[Guest]

Oh, I should also add that the PRIMARY is currently a combo-box, with its
values coming from another table in drop-down format.

Not sure if it mattered but wanted to let you know!!

Thanks a million.

 

[Guest]

Hi Steph,

First, I'd like to encourage you to not use special characters (ie. spaces)
or reserved words (NAME) for anything that you assign a name to in Access.
Here are some good references for you:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I especially recommend renaming your NAME field. It might be okay with the
square brackets, but why spend the time and frustration battling a problem
that could be related to the use of reserved words?

SELECT [Aid Assignment List_OLD].[ID], [Aid Assignment List_OLD].[NAME],
[Aid Assignment List_OLD].[LLNR] FROM [Aid Assignment List_OLD] ORDER BY
[NAME];

In VBA code, the ID field would correspond to Column(0), the NAME field
would be Column(1) and the LLNR field would be Column(2). Your combo box
should have the Column Count set to 3. The column widths might be something
like:

0";1";1"

in order to display the NAME and LLNR, but hide the ID.

That fills in a second text box (LLNR) with that value from the table which
I still want, but would also like another text box (PRIMARY) be filled in
when something from the Combo Box is selected as well.

What are the names of your combo box and the two textboxes (not Control
Source, but Name, as shown at the top of the Other tab in the Properties
dialog)? What is the Afterupdate event procedure for your combo box?

Oh, I should also add that the PRIMARY is currently a combo-box, with its
values coming from another table in drop-down format.

I don't really understand what you are trying to tell me here. If you are
making reference to a Lookup field defined at the table or query level, then
you should know that these are just plain evil. Don't use them. See the 2nd
Commandment here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

When I tried what you said, I then had nothing drop downin my combo box.

Are you saying that your combo box doesn't allow you to expand it, by
clicking on the dropdown arrow?



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Oh, I should also add that the PRIMARY is currently a combo-box, with its
values coming from another table in drop-down format.

Not sure if it mattered but wanted to let you know!!

Thanks a million.

Ok Tom, this is my current Row Source:

SELECT [Aid Assignment List_OLD].[ID], [Aid Assignment List_OLD].[NAME],
[Aid Assignment List_OLD].[LLNR] FROM [Aid Assignment List_OLD] ORDER BY
[NAME];

That fills in a second text box (LLNR) with that value from the table which
I still want, but would also like another text box (PRIMARY) be filled in
when something from the Combo Box is selected as well.

When I tried what you said, I then had nothing drop downin my combo box.

Sorry I am missing it. Thanks again.
Steph

 

[Guest]

Hi Tom, thanks again.

What are the names of your combo box and the two textboxes (not Control
Source, but Name, as shown at the top of the Other tab in the Properties
dialog)? What is the Afterupdate event procedure for your combo box?

They are called:

Combo Box - Aid
First Textbox to be filled in: LLNR
Second Textbox to be filled in: PRIMARY

After Update Procedure of Combo Box:
Private Sub Aid_AfterUpdate()
On Error GoTo ProcError

Me.LLNR = Me.Aid.Column(2)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Aid_AfterUpdate..."
Resume ExitProc
End Sub


Oh, I should also add that the PRIMARY is currently a combo-box, with itsI don't really understand what you are trying to tell me here. If you are
making reference to a Lookup field defined at the table or query level, then
you should know that these are just plain evil. Don't use them. See the 2nd
Commandment here:

Well, I must admit, that is what it was, meaning the PRIMARY BOX. If I can
get this to work, I will not use it ever, ever again, I promise!!! I did not
know they were so bad, I have another in my database somehwere, what is a
better alternative for my own reference?


Are you saying that your combo box doesn't allow you to expand it, by
clicking on the dropdown arrow?

Yes, it dropped down slightly, but there was nothing in it. Keep in mind,
what you helped me with before, auto-filling in the LLNR text box was working
great.

Thanks again, your a lifesaver.

 

[Guest]

Hi Steph,

Combo Box - Aid
First Textbox to be filled in: LLNR
Second Textbox to be filled in: PRIMARY

Just a suggestion, but consider using some naming conventions for the
controls on your forms and reports. A common naming convention involves three
lowercase letters:
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

So, for example, the combo box would be named cboAid, and the two text boxes
would be named txtLLNR and txtPRIMARY, respectively. You can avoid circular
reference problems in control sources that involve calculations by making
sure that the control source and the name of the control are never the same.

I think you simply need to add one line of code to your AfterUpdate event
procedure, assuming that [Aid Assignment List_OLD].[NAME] is the Primary
value:

Private Sub Aid_AfterUpdate()
On Error GoTo ProcError

Me.PRIMARY = Me.Aid.Column(1) '<---New line of code
Me.LLNR = Me.Aid.Column(2)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Aid_AfterUpdate..."
Resume ExitProc
End Sub

On the other hand, I could have that all wrong, if Primary was coming from a
table lookup. In that case, I believe you will need to add the related table
to the SQL statement that serves as the row source for your combo box. But I
don't know the name of that table, so I really can't suggest the appropriate
SQL statement.

I have another in my database somehwere, ...

You can use the CSD Tools to quickly locate these:
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

what is a better alternative for my own reference?

Combo boxes on forms are fine. Just don't try to use them in tables or
queries. You can use a value list in a table, which generally is not as bad
as table lookup fields.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________