Combo Box

[Guest]

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[Guest]

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck

 

[Guest]

Boy, that looks great. Thank you so much. I would have never figured that
out without your help.

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[Deb]

I thought the response here would answer a question I have on filling in a
text box on a form from a combo box selection, but I keep running into
errors. I entered:

Private Sub cboPartNo_AfterUpdate()
Dim db As Database, rst As Recordset, tbl As String, n2 As String
Set db = CurrentDb
Set rst = dbs.OpenRecordset(Select * from tlu_Partnumbers,
where([PartNo]) = "me.cboPartNo.Value & '")
rst.MoveFirst
n2 = rst.Fields(Nomenclature).Value
rst.Close
Set dbs = Nothing
Me.Nomenclature.Value = n2

End Sub

The current problem starts at the "Set rst" code line. As I understand it,
"n2" would refer to the column in the table connected to the combo box. The
first field (Part Number) fills in automatically, but the second field
(Nomenclature) is what I'm trying to pull from the underlying table. This
information will be used to add new records to another table.

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[John Spencer MVP]

The line should read
Set rst = dbs.OpenRecordset("Select * from tlu_Partnumbers
where([PartNo]) = '" & me.cboPartNo.Value & '")

Also, I would dim db and rst as
DIM db as DAO.Database, rst as DAO.Recordset

It seems to me it would be simpler to use the DLookup function and not open a
recordset at all.

Me.Nomenclature = DLookup("Nomenclature","tlu_PartNumbers","PartNo=' &
Me.cboPartno & "'")

And I kind of question whether you need to do any of this if Nomenclature is
always dependent on PartNo.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I thought the response here would answer a question I have on filling in a
text box on a form from a combo box selection, but I keep running into
errors. I entered:

Private Sub cboPartNo_AfterUpdate()
Dim db As Database, rst As Recordset, tbl As String, n2 As String
Set db = CurrentDb
Set rst = dbs.OpenRecordset(Select * from tlu_Partnumbers,
where([PartNo]) = "me.cboPartNo.Value & '")
rst.MoveFirst
n2 = rst.Fields(Nomenclature).Value
rst.Close
Set dbs = Nothing
Me.Nomenclature.Value = n2

End Sub

The current problem starts at the "Set rst" code line. As I understand it,
"n2" would refer to the column in the table connected to the combo box. The
first field (Part Number) fills in automatically, but the second field
(Nomenclature) is what I'm trying to pull from the underlying table. This
information will be used to add new records to another table.

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[Deb]

John:

Please explain your statement "And I kind of question whether you need to do
any of this if Nomenclature is always dependent on PartNo." I'm fairly new
to database development, and am learning a lot the hard way. I sure do
appreciate this forum.
Thank you for your kind assistance!

The line should read
Set rst = dbs.OpenRecordset("Select * from tlu_Partnumbers
where([PartNo]) = '" & me.cboPartNo.Value & '")

Also, I would dim db and rst as
DIM db as DAO.Database, rst as DAO.Recordset

It seems to me it would be simpler to use the DLookup function and not open a
recordset at all.

Me.Nomenclature = DLookup("Nomenclature","tlu_PartNumbers","PartNo=' &
Me.cboPartno & "'")

And I kind of question whether you need to do any of this if Nomenclature is
always dependent on PartNo.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I thought the response here would answer a question I have on filling in a
text box on a form from a combo box selection, but I keep running into
errors. I entered:

Private Sub cboPartNo_AfterUpdate()
Dim db As Database, rst As Recordset, tbl As String, n2 As String
Set db = CurrentDb
Set rst = dbs.OpenRecordset(Select * from tlu_Partnumbers,
where([PartNo]) = "me.cboPartNo.Value & '")
rst.MoveFirst
n2 = rst.Fields(Nomenclature).Value
rst.Close
Set dbs = Nothing
Me.Nomenclature.Value = n2

End Sub

The current problem starts at the "Set rst" code line. As I understand it,
"n2" would refer to the column in the table connected to the combo box. The
first field (Part Number) fills in automatically, but the second field
(Nomenclature) is what I'm trying to pull from the underlying table. This
information will be used to add new records to another table.

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck




:

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[John Spencer]

If you have the partNo do you then know the nomenclature? If so and
nomenclature is NOT a memo field then I would simply include the value
in the combobox as a hidden column. Then you can grab the value of the
hidden column. So if your combobox had two columns - partno and
nomenclature, you could get the value of nomenclature with a statement
like (columns are numbered starting with zero):
CboPartNo.Column(1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John:

Please explain your statement "And I kind of question whether you need to do
any of this if Nomenclature is always dependent on PartNo." I'm fairly new
to database development, and am learning a lot the hard way. I sure do
appreciate this forum.
Thank you for your kind assistance!

The line should read
Set rst = dbs.OpenRecordset("Select * from tlu_Partnumbers
where([PartNo]) = '" & me.cboPartNo.Value & '")

Also, I would dim db and rst as
DIM db as DAO.Database, rst as DAO.Recordset

It seems to me it would be simpler to use the DLookup function and not open a
recordset at all.

Me.Nomenclature = DLookup("Nomenclature","tlu_PartNumbers","PartNo=' &
Me.cboPartno & "'")

And I kind of question whether you need to do any of this if Nomenclature is
always dependent on PartNo.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I thought the response here would answer a question I have on filling in a
text box on a form from a combo box selection, but I keep running into
errors. I entered:

Private Sub cboPartNo_AfterUpdate()
Dim db As Database, rst As Recordset, tbl As String, n2 As String
Set db = CurrentDb
Set rst = dbs.OpenRecordset(Select * from tlu_Partnumbers,
where([PartNo]) = "me.cboPartNo.Value & '")
rst.MoveFirst
n2 = rst.Fields(Nomenclature).Value
rst.Close
Set dbs = Nothing
Me.Nomenclature.Value = n2

End Sub

The current problem starts at the "Set rst" code line. As I understand it,
"n2" would refer to the column in the table connected to the combo box. The
first field (Part Number) fills in automatically, but the second field
(Nomenclature) is what I'm trying to pull from the underlying table. This
information will be used to add new records to another table.


:

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck




:

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[Deb]

WOW! That sounds soooo simple!
I have a lookup table (Part Numbers) that contains about five fields. Part
Number is the first field (primary key), and Nomenclature is the second
field. I'm using the combo box to look up the part numbers and nomenclature,
and want to fill in text boxes on the form with that information. The Part
Number field worked just fine, it just would not populate the Nomenclature
field. I don't think I set up Nomenclature as a memo field, I think it's
text (that is on a different computer from this one).

"So if your combobox had two columns - partno and

nomenclature, you could get the value of nomenclature with a statement
like (columns are numbered starting with zero):
CboPartNo.Column(1)"

Where would I put the "statement" you referenced?

Many thanks!!

If you have the partNo do you then know the nomenclature? If so and
nomenclature is NOT a memo field then I would simply include the value
in the combobox as a hidden column. Then you can grab the value of the
hidden column. So if your combobox had two columns - partno and
nomenclature, you could get the value of nomenclature with a statement
like (columns are numbered starting with zero):
CboPartNo.Column(1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John:

Please explain your statement "And I kind of question whether you need to do
any of this if Nomenclature is always dependent on PartNo." I'm fairly new
to database development, and am learning a lot the hard way. I sure do
appreciate this forum.
Thank you for your kind assistance!

The line should read
Set rst = dbs.OpenRecordset("Select * from tlu_Partnumbers
where([PartNo]) = '" & me.cboPartNo.Value & '")

Also, I would dim db and rst as
DIM db as DAO.Database, rst as DAO.Recordset

It seems to me it would be simpler to use the DLookup function and not open a
recordset at all.

Me.Nomenclature = DLookup("Nomenclature","tlu_PartNumbers","PartNo=' &
Me.cboPartno & "'")

And I kind of question whether you need to do any of this if Nomenclature is
always dependent on PartNo.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Deb wrote:
I thought the response here would answer a question I have on filling in a
text box on a form from a combo box selection, but I keep running into
errors. I entered:

Private Sub cboPartNo_AfterUpdate()
Dim db As Database, rst As Recordset, tbl As String, n2 As String
Set db = CurrentDb
Set rst = dbs.OpenRecordset(Select * from tlu_Partnumbers,
where([PartNo]) = "me.cboPartNo.Value & '")
rst.MoveFirst
n2 = rst.Fields(Nomenclature).Value
rst.Close
Set dbs = Nothing
Me.Nomenclature.Value = n2

End Sub

The current problem starts at the "Set rst" code line. As I understand it,
"n2" would refer to the column in the table connected to the combo box. The
first field (Part Number) fills in automatically, but the second field
(Nomenclature) is what I'm trying to pull from the underlying table. This
information will be used to add new records to another table.


:

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck




:

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[John Spencer]

You would use that as the control source for a textbox on the form.

The control source would have an equal sign in front of it

=CboPartNo.Column(1)

That would NOT be stored. When you need the nomenclature you would
include the lookup table in your query and link the primary key in the
lookup table to the partnumber field in the other table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

WOW! That sounds soooo simple!
I have a lookup table (Part Numbers) that contains about five fields. Part
Number is the first field (primary key), and Nomenclature is the second
field. I'm using the combo box to look up the part numbers and nomenclature,
and want to fill in text boxes on the form with that information. The Part
Number field worked just fine, it just would not populate the Nomenclature
field. I don't think I set up Nomenclature as a memo field, I think it's
text (that is on a different computer from this one).

"So if your combobox had two columns - partno and

nomenclature, you could get the value of nomenclature with a statement
like (columns are numbered starting with zero):
CboPartNo.Column(1)"

Where would I put the "statement" you referenced?

Many thanks!!

If you have the partNo do you then know the nomenclature? If so and
nomenclature is NOT a memo field then I would simply include the value
in the combobox as a hidden column. Then you can grab the value of the
hidden column. So if your combobox had two columns - partno and
nomenclature, you could get the value of nomenclature with a statement
like (columns are numbered starting with zero):
CboPartNo.Column(1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John:

Please explain your statement "And I kind of question whether you need to do
any of this if Nomenclature is always dependent on PartNo." I'm fairly new
to database development, and am learning a lot the hard way. I sure do
appreciate this forum.
Thank you for your kind assistance!


:

The line should read
Set rst = dbs.OpenRecordset("Select * from tlu_Partnumbers
where([PartNo]) = '" & me.cboPartNo.Value & '")

Also, I would dim db and rst as
DIM db as DAO.Database, rst as DAO.Recordset

It seems to me it would be simpler to use the DLookup function and not open a
recordset at all.

Me.Nomenclature = DLookup("Nomenclature","tlu_PartNumbers","PartNo=' &
Me.cboPartno & "'")

And I kind of question whether you need to do any of this if Nomenclature is
always dependent on PartNo.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Deb wrote:
I thought the response here would answer a question I have on filling in a
text box on a form from a combo box selection, but I keep running into
errors. I entered:

Private Sub cboPartNo_AfterUpdate()
Dim db As Database, rst As Recordset, tbl As String, n2 As String
Set db = CurrentDb
Set rst = dbs.OpenRecordset(Select * from tlu_Partnumbers,
where([PartNo]) = "me.cboPartNo.Value & '")
rst.MoveFirst
n2 = rst.Fields(Nomenclature).Value
rst.Close
Set dbs = Nothing
Me.Nomenclature.Value = n2

End Sub

The current problem starts at the "Set rst" code line. As I understand it,
"n2" would refer to the column in the table connected to the combo box. The
first field (Part Number) fills in automatically, but the second field
(Nomenclature) is what I'm trying to pull from the underlying table. This
information will be used to add new records to another table.


:

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck




:

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.

 

[Deb]

Hi John:

That worked like a champ (except the information does need to be saved).
Based on that, I created a new query using the table I want to store the
information in as the query source and added the PartNo table. I then
created a form and generated a combo box containing all six columns from the
PartNo table. When I tried to select something from that combo box, it just
beeps.

Also, I want this to be a data entry form only, and selected that option,
but it still pulls in all of the records from the table I want to store new
records in. Obviously I'm doing something wrong AGAIN!

Thanks again!

You would use that as the control source for a textbox on the form.

The control source would have an equal sign in front of it

=CboPartNo.Column(1)

That would NOT be stored. When you need the nomenclature you would
include the lookup table in your query and link the primary key in the
lookup table to the partnumber field in the other table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

WOW! That sounds soooo simple!
I have a lookup table (Part Numbers) that contains about five fields. Part
Number is the first field (primary key), and Nomenclature is the second
field. I'm using the combo box to look up the part numbers and nomenclature,
and want to fill in text boxes on the form with that information. The Part
Number field worked just fine, it just would not populate the Nomenclature
field. I don't think I set up Nomenclature as a memo field, I think it's
text (that is on a different computer from this one).

"So if your combobox had two columns - partno and

nomenclature, you could get the value of nomenclature with a statement
like (columns are numbered starting with zero):
CboPartNo.Column(1)"

Where would I put the "statement" you referenced?

Many thanks!!

If you have the partNo do you then know the nomenclature? If so and
nomenclature is NOT a memo field then I would simply include the value
in the combobox as a hidden column. Then you can grab the value of the
hidden column. So if your combobox had two columns - partno and
nomenclature, you could get the value of nomenclature with a statement
like (columns are numbered starting with zero):
CboPartNo.Column(1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Deb wrote:
John:

Please explain your statement "And I kind of question whether you need to do
any of this if Nomenclature is always dependent on PartNo." I'm fairly new
to database development, and am learning a lot the hard way. I sure do
appreciate this forum.
Thank you for your kind assistance!


:

The line should read
Set rst = dbs.OpenRecordset("Select * from tlu_Partnumbers
where([PartNo]) = '" & me.cboPartNo.Value & '")

Also, I would dim db and rst as
DIM db as DAO.Database, rst as DAO.Recordset

It seems to me it would be simpler to use the DLookup function and not open a
recordset at all.

Me.Nomenclature = DLookup("Nomenclature","tlu_PartNumbers","PartNo=' &
Me.cboPartno & "'")

And I kind of question whether you need to do any of this if Nomenclature is
always dependent on PartNo.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Deb wrote:
I thought the response here would answer a question I have on filling in a
text box on a form from a combo box selection, but I keep running into
errors. I entered:

Private Sub cboPartNo_AfterUpdate()
Dim db As Database, rst As Recordset, tbl As String, n2 As String
Set db = CurrentDb
Set rst = dbs.OpenRecordset(Select * from tlu_Partnumbers,
where([PartNo]) = "me.cboPartNo.Value & '")
rst.MoveFirst
n2 = rst.Fields(Nomenclature).Value
rst.Close
Set dbs = Nothing
Me.Nomenclature.Value = n2

End Sub

The current problem starts at the "Set rst" code line. As I understand it,
"n2" would refer to the column in the table connected to the combo box. The
first field (Part Number) fills in automatically, but the second field
(Nomenclature) is what I'm trying to pull from the underlying table. This
information will be used to add new records to another table.


:

You can. You don't need 3 combo boxes - just one for the customer selection
and 2 text boxes (that you can set "Enabled" to no and "Locked" to yes in
their properties menu if you want - that way they are for display only i.e.
the user can't change them)

Use the After_Update event of your customers combo box to get the
correspoding address data from your underlying table.

Something like:

Sub CustCombo_After_Update()
dim dbs as database, rst as recordset, addr1 as string, addr2 as string
Set dbs = currentdb
Set rst = dbs.openRecordset("Select * from YourTable where [customer] =
'" _
me.custcombo.value & "'")
rst.movefirst
addr1 = rst.fields("Address1_field").value
addr2 = rst.fields("Address2_field").value
rst.close
set dbs = nothing
me.textbox1.value = addr1
me.textbox2.value = addr2
end sub

Check this syntax and watch for wordwrapping (watch for the single
quotations in the Recordset call) - but that ought to do it.

Good Luck




:

I want to select a customer's name from a drop down menu and have Addr1 and
Addr2 fields in my sales order table fill in automatically from my customer
table. My customer table is CustName, Addr1, and Addr2. I can get a drop
down menu in each of these three fields in my sales order table and make
three selections. But, I feel certain I should be able to get all three
fields to fill by only making the selection in CustName.