SQL- using lst box to gatehr info for a specidi field.

Devin Panchal wrote:

 > hi,
 >
 > i've got an access table called 'submission_info', and in that table i've
 > got fields with names of people, and addresses. i want to show a list of
 > papers from a specific country. e.g. China.
 >
 > this is what i've strung together so far;
 >
 > Private Sub cmdCountry_Click()
 >
 > 'strVar = txtCountry
 >
 > 'strSQL = "SELECT paper_title FROM submission_info WHERE
 > contact_author_address_5 = '"strVar'"
 >
 > sql = "SELECT paper_title FROM submission_info WHERE
 > contact_author_address_5 = 'China'"
 > txtCountry.Text = sql
 >
 > End Sub
 >
 > but it aint working correctly, it just displays the whole sql line. i know

Sure it is! The computer's doing exactly what
you're telling it to! (Sorry, I couldn't just
leave a straight line like that hanging!

 > that code is wrong!! i have a list box called 'lstCountry, where the
 > user can pick a country name, and then the rest of teh fields get updated
 > with that info. only problem is, i dont know how i want to display it? e.g.
 > as a form with fields, or as a list box. ??

I don't know the effect you're looking for, but
the fact that you want a varying amount of info
on the screen suggests you may want a
Hierarchical Flexgrid. That way you can set it
up with scroll bars in case you have like 100
papers from China. You can have separate
columns for author, title, and any other info
you want to display.

You can also set different text and background
colors in each cell.

There are a lot of fiddly little properties to
set to get it looking just right, but it's a
control you'll find a lot of uses for...

 >
 > thanx
 > devin<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field. 

hi,

i know how i want it to look now.
i've gto a cbo box, called cboCountry, and that will have all the countries
in the database. then when a country is clicked (e.g. China) the
'txtCountry' field will be filled in, with info from that specific country.
it will get this info from the database. (.e.g. contact_author_first_name ,
contact_author_last_name , contact_author_address_1 , etc...)

this is the code i've got so far.

Private Sub cmdCountry_Click()

rs.Open "SELECT paper_title FROM submission_info WHERE
contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
adOpenStatic, adLockOptimistic, adCmdText

cboCountry.AddItem rs.Fields.Item("contact_author_address_5")

End Sub

how do i get it to fill in the txtCountry field?? i've also got a button to
trigger all of this. do i really need it? the button is cmdCountry.

thanx

devin

"Randy Day" wrote in message

 > Devin Panchal wrote:
 >
  > > hi,
  > >
  > > i've got an access table called 'submission_info', and in that table
i've
  > > got fields with names of people, and addresses. i want to show a list of
  > > papers from a specific country. e.g. China.
  > >
  > > this is what i've strung together so far;
  > >
  > > Private Sub cmdCountry_Click()
  > >
  > > 'strVar = txtCountry
  > >
  > > 'strSQL = "SELECT paper_title FROM submission_info WHERE
  > > contact_author_address_5 = '"strVar'"
  > >
  > > sql = "SELECT paper_title FROM submission_info WHERE
  > > contact_author_address_5 = 'China'"
  > > txtCountry.Text = sql
  > >
  > > End Sub
  > >
  > > but it aint working correctly, it just displays the whole sql line. i
know
 >
 > Sure it is! The computer's doing exactly what
 > you're telling it to! (Sorry, I couldn't just
 > leave a straight line like that hanging!
 >
  > > that code is wrong!! i have a list box called 'lstCountry, where the
  > > user can pick a country name, and then the rest of teh fields get
updated
  > > with that info. only problem is, i dont know how i want to display it?
e.g.
  > > as a form with fields, or as a list box. ??
 >
 > I don't know the effect you're looking for, but
 > the fact that you want a varying amount of info
 > on the screen suggests you may want a
 > Hierarchical Flexgrid. That way you can set it
 > up with scroll bars in case you have like 100
 > papers from China. You can have separate
 > columns for author, title, and any other info
 > you want to display.
 >
 > You can also set different text and background
 > colors in each cell.
 >
 > There are a lot of fiddly little properties to
 > set to get it looking just right, but it's a
 > control you'll find a lot of uses for...
 >
  > >
  > > thanx
  > > devin<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field. 

Devin Panchal wrote:
 > hi,
 >
 > i know how i want it to look now.
 > i've gto a cbo box, called cboCountry, and that will have all the countries
 > in the database. then when a country is clicked (e.g. China) the
 > 'txtCountry' field will be filled in, with info from that specific country.
 > it will get this info from the database. (.e.g. contact_author_first_name ,
 > contact_author_last_name , contact_author_address_1 , etc...)
 >
 > this is the code i've got so far.
 >
 > Private Sub cmdCountry_Click()
 >
 > rs.Open "SELECT paper_title FROM submission_info WHERE
 > contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
 > adOpenStatic, adLockOptimistic, adCmdText
 >
 > cboCountry.AddItem rs.Fields.Item("contact_author_address_5")
 >
 > End Sub
 >

You call up the paper titles from china, then
add china to the country combobox again?

 > how do i get it to fill in the txtCountry field?? i've also got a button to
 > trigger all of this. do i really need it? the button is cmdCountry.

Probably not. You could get the combobox to start
the process in its Click(), DblClick() or Change()
events, That way, selecting an item in the
combobox list starts the process right off.

Read up on the combobox ListIndex() and List()
properties.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field. 

hey,

i got it to work. but i got a little problem. i got duplicated countries in
the combo box. how do i get rid of the duplicated ones?? also how do i get
'all' of the paper titles in that text field. at the moment it only shows a
paper title for that specific country, and it only shows 1 title, some
countries have more then 1. (.e.g. china has 4 different papers), but it
only shows one of them.

thanx

devin

"Randy Day" wrote in message

 > Devin Panchal wrote:
  > > hi,
  > >
  > > i know how i want it to look now.
  > > i've gto a cbo box, called cboCountry, and that will have all the
countries
  > > in the database. then when a country is clicked (e.g. China) the
  > > 'txtCountry' field will be filled in, with info from that specific
country.
  > > it will get this info from the database. (.e.g.
contact_author_first_name ,
  > > contact_author_last_name , contact_author_address_1 , etc...)
  > >
  > > this is the code i've got so far.
  > >
  > > Private Sub cmdCountry_Click()
  > >
  > > rs.Open "SELECT paper_title FROM submission_info WHERE
  > > contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
  > > adOpenStatic, adLockOptimistic, adCmdText
  > >
  > > cboCountry.AddItem rs.Fields.Item("contact_author_address_5")
  > >
  > > End Sub
  > >
 >
 > You call up the paper titles from china, then
 > add china to the country combobox again?
 >
  > > how do i get it to fill in the txtCountry field?? i've also got a button
to
  > > trigger all of this. do i really need it? the button is cmdCountry.
 >
 > Probably not. You could get the combobox to start
 > the process in its Click(), DblClick() or Change()
 > events, That way, selecting an item in the
 > combobox list starts the process right off.
 >
 > Read up on the combobox ListIndex() and List()
 > properties.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field. 

Devin Panchal wrote:
 > hey,
 >
 > i got it to work. but i got a little problem. i got duplicated countries in
 > the combo box. how do i get rid of the duplicated ones?? also how do i get

Well, as I mentioned below, you were adding
duplicates in your 'Country' button.

Beyond that, you're going to have to step
through your code to find out where
combobox.additem gets called.

If it's because your recordset returns
multiples, you'll have to explore the
'UNIQUE' keyword for your 'SELECT'
statement.

 > 'all' of the paper titles in that text field. at the moment it only shows a
 > paper title for that specific country, and it only shows 1 title, some
 > countries have more then 1. (.e.g. china has 4 different papers), but it
 > only shows one of them.

Did you add a loop to display all records
returned in your 'Country' buttion?

 >
 > thanx
 >
 > devin
 >


 >
  >>Devin Panchal wrote:
  >>
   >>>hi,
   >>>
   >>> i know how i want it to look now.
   >>>i've gto a cbo box, called cboCountry, and that will have all the
 >
 > countries
 >
   >>>in the database. then when a country is clicked (e.g. China) the
   >>>'txtCountry' field will be filled in, with info from that specific
 >
 > country.
 >
   >>>it will get this info from the database. (.e.g.
 >
 > contact_author_first_name ,
 >
   >>>contact_author_last_name , contact_author_address_1 , etc...)
   >>>
   >>>this is the code i've got so far.
   >>>
   >>>Private Sub cmdCountry_Click()
   >>>
   >>>rs.Open "SELECT paper_title FROM submission_info WHERE
   >>>contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
   >>>adOpenStatic, adLockOptimistic, adCmdText
   >>>
   >>>cboCountry.AddItem rs.Fields.Item("contact_author_address_5")
   >>>
   >>>End Sub
   >>>
  >>
  >>You call up the paper titles from china, then
  >>add china to the country combobox again?
  >>
  >>
   >>>how do i get it to fill in the txtCountry field?? i've also got a button
 >
 > to
 >
   >>>trigger all of this. do i really need it? the button is cmdCountry.
  >>
  >>Probably not. You could get the combobox to start
  >>the process in its Click(), DblClick() or Change()
  >>events, That way, selecting an item in the
  >>combobox list starts the process right off.
  >>
  >>Read up on the combobox ListIndex() and List()
  >>properties.
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field. 

the database i am using already has some records in it. i didnt make the
actual database. i have not got any loops. this is wat i've got;;

Private Sub cmdCountry_Click()

rs.Open "SELECT paper_title FROM submission_info WHERE
contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
adOpenStatic, adLockOptimistic, adCmdText

txtCountry.Text = rs.Fields.Item("paper_title")
rs.Close

End Sub

and for the load ;

Private Sub Form_Load()
Set Assignment2 = New ADODB.Connection
Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
Assignment2.Open
Set rs = New ADODB.Recordset

rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
adLockOptimistic
If rs.RecordCount = 0 Then
MsgBox "You have no records in the database.", vbExclamation, "Database"
'you have no records to process
Else
rs.MoveFirst
Do While Not rs.EOF
cboCountry.AddItem rs.Fields.Item("contact_author_address_5")
rs.MoveNext
Loop
rs.MoveFirst
rs.Close

End If

End Sub

the combo box gets called when the form is loaded. at the moment, it only
diplays 1 paper title, but some countries have more then 1.

thanx

devin

"Randy Day" wrote in message

 > Devin Panchal wrote:
  > > hey,
  > >
  > > i got it to work. but i got a little problem. i got duplicated countries
in
  > > the combo box. how do i get rid of the duplicated ones?? also how do i
get
 >
 > Well, as I mentioned below, you were adding
 > duplicates in your 'Country' button.
 >
 > Beyond that, you're going to have to step
 > through your code to find out where
 > combobox.additem gets called.
 >
 > If it's because your recordset returns
 > multiples, you'll have to explore the
 > 'UNIQUE' keyword for your 'SELECT'
 > statement.
 >
  > > 'all' of the paper titles in that text field. at the moment it only
shows a
  > > paper title for that specific country, and it only shows 1 title, some
  > > countries have more then 1. (.e.g. china has 4 different papers), but it
  > > only shows one of them.
 >
 > Did you add a loop to display all records
 > returned in your 'Country' buttion?
 >
  > >
  > > thanx
  > >
  > > devin
  > >


  > >
   > >>Devin Panchal wrote:
   > >>
   > >>>hi,
   > >>>
   > >>> i know how i want it to look now.
   > >>>i've gto a cbo box, called cboCountry, and that will have all the
  > >
  > > countries
  > >
   > >>>in the database. then when a country is clicked (e.g. China) the
   > >>>'txtCountry' field will be filled in, with info from that specific
  > >
  > > country.
  > >
   > >>>it will get this info from the database. (.e.g.
  > >
  > > contact_author_first_name ,
  > >
   > >>>contact_author_last_name , contact_author_address_1 , etc...)
   > >>>
   > >>>this is the code i've got so far.
   > >>>
   > >>>Private Sub cmdCountry_Click()
   > >>>
   > >>>rs.Open "SELECT paper_title FROM submission_info WHERE
   > >>>contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
   > >>>adOpenStatic, adLockOptimistic, adCmdText
   > >>>
   > >>>cboCountry.AddItem rs.Fields.Item("contact_author_address_5")
   > >>>
   > >>>End Sub
   > >>>
   > >>
   > >>You call up the paper titles from china, then
   > >>add china to the country combobox again?
   > >>
   > >>
   > >>>how do i get it to fill in the txtCountry field?? i've also got a
button
  > >
  > > to
  > >
   > >>>trigger all of this. do i really need it? the button is cmdCountry.
   > >>
   > >>Probably not. You could get the combobox to start
   > >>the process in its Click(), DblClick() or Change()
   > >>events, That way, selecting an item in the
   > >>combobox list starts the process right off.
   > >>
   > >>Read up on the combobox ListIndex() and List()
   > >>properties.
  > >
  > >
  > ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field. 

Devin wrote:
 > the database i am using already has some records in it. i didnt make the
 > actual database. i have not got any loops. this is wat i've got;;
 >
 > Private Sub cmdCountry_Click()
 >
 > rs.Open "SELECT paper_title FROM submission_info WHERE
 > contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
 > adOpenStatic, adLockOptimistic, adCmdText
 >
 > txtCountry.Text = rs.Fields.Item("paper_title")

Well, here you add *1* paper title to the textbox...

 > rs.Close

.... and then you wipe the recordset. The question
has to be asked: *WHY* haven't you got a loop? The
code should be similar to what you had in
form_load.

 >
 > End Sub
 >
 > and for the load ;
 >
 > Private Sub Form_Load()
 > Set Assignment2 = New ADODB.Connection
 > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
 > Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
 > Assignment2.Open
 > Set rs = New ADODB.Recordset
 >
 > rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
 > adLockOptimistic
 > If rs.RecordCount = 0 Then
 > MsgBox "You have no records in the database.", vbExclamation, "Database"
 > 'you have no records to process
 > Else
 > rs.MoveFirst
 > Do While Not rs.EOF
 > cboCountry.AddItem rs.Fields.Item("contact_author_address_5")
 > rs.MoveNext
 > Loop
 > rs.MoveFirst
 > rs.Close
 >
 > End If
 >
 > End Sub
 >
 > the combo box gets called when the form is loaded. at the moment, it only
 > diplays 1 paper title, but some countries have more then 1.
 >
 > thanx
 >
 > devin<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field. 

i got it working. thanx for the help. i was missing the loop.

dev

"Randy Day" wrote in message

 > Devin wrote:
  > > the database i am using already has some records in it. i didnt make the
  > > actual database. i have not got any loops. this is wat i've got;;
  > >
  > > Private Sub cmdCountry_Click()
  > >
  > > rs.Open "SELECT paper_title FROM submission_info WHERE
  > > contact_author_address_5 ='" & cboCountry.Text & "'", Assignment2,
  > > adOpenStatic, adLockOptimistic, adCmdText
  > >
  > > txtCountry.Text = rs.Fields.Item("paper_title")
 >
 > Well, here you add *1* paper title to the textbox...
 >
  > > rs.Close
 >
 > ... and then you wipe the recordset. The question
 > has to be asked: *WHY* haven't you got a loop? The
 > code should be similar to what you had in
 > form_load.
 >
  > >
  > > End Sub
  > >
  > > and for the load ;
  > >
  > > Private Sub Form_Load()
  > > Set Assignment2 = New ADODB.Connection
  > > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
  > > Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
  > > Assignment2.Open
  > > Set rs = New ADODB.Recordset
  > >
  > > rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
  > > adLockOptimistic
  > > If rs.RecordCount = 0 Then
  > > MsgBox "You have no records in the database.", vbExclamation, "Database"
  > > 'you have no records to process
  > > Else
  > > rs.MoveFirst
  > > Do While Not rs.EOF
  > > cboCountry.AddItem rs.Fields.Item("contact_author_address_5")
  > > rs.MoveNext
  > > Loop
  > > rs.MoveFirst
  > > rs.Close
  > >
  > > End If
  > >
  > > End Sub
  > >
  > > the combo box gets called when the form is loaded. at the moment, it
only
  > > diplays 1 paper title, but some countries have more then 1.
  > >
  > > thanx
  > >
  > > devin<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: SQL- using lst box to gatehr info for a specidi field.