Query problems | Newbie | | Join Date: Aug 2008
Posts: 5
| | |
I have a query which works perfectly but I wish to iterate through the query and select specific portions from the query such as ID, address which can then be sent to perform another function.
My current solution is to fill a listbox with the required information however I have no idea how to extract the values, I have tried a solution with combobox however, upon iterating through all the items to select them the items remain unselected and thus Comobox.text has a null value.
Any help would be appreciated.
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Query problems
We will need much more detail that that which you have provided, such as: - Table the Query is based on.
- Field Names and their Data Types that comprise the Query.
- Specific Fields you wish to extract.
- Where do you wish to extract these Field values to? List Box, Combo Box, etc.
- What is the Name of the Object that will accept these values?
- etc...
| | Newbie | | Join Date: Aug 2008
Posts: 5
| | | re: Query problems
1. Table the Query is based on.
2. Field Names and their Data Types that comprise the Query.
3. Specific Fields you wish to extract.
4. Where do you wish to extract these Field values to? List Box, Combo Box, etc.
5. What is the Name of the Object that will accept these values?
6. etc...
The table the query is based on is a generic table (tbl1) all field names are based on client listing, the ones that the comprise the query are CandidateName, phone, email. all of which are text data type.
I wish to extract the phone data from the query into a listbox/combobox then into a memo box
Since the listbox/combobox already contains the data items i wish to extract I would like to know how to extract the information from the listbox and put into the memo box, or would it be simpler to use a recordset and iterate through the set returned by the query?
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Query problems
Quote:
Originally Posted by Jaxter1 1. Table the Query is based on.
2. Field Names and their Data Types that comprise the Query.
3. Specific Fields you wish to extract.
4. Where do you wish to extract these Field values to? List Box, Combo Box, etc.
5. What is the Name of the Object that will accept these values?
6. etc...
The table the query is based on is a generic table (tbl1) all field names are based on client listing, the ones that the comprise the query are CandidateName, phone, email. all of which are text data type.
I wish to extract the phone data from the query into a listbox/combobox then into a memo box
Since the listbox/combobox already contains the data items i wish to extract I would like to know how to extract the information from the listbox and put into the memo box, or would it be simpler to use a recordset and iterate through the set returned by the query? Quote:
Since the listbox already contains the data items i wish to extract I would like to know how to extract the information from the listbox and put into the memo box
- What exactly do you mean by Memo Box?
- In what format do the wish the Phone Numbers in the List/Combo Box extracted? e.g.
- (215) 888-1234;(234) 876-8936;(679)-0018
- (215) 888-1234
-
(234) 876-8936
-
(679)-0018
- (215) 888-1234, (234) 876-8936, (679)-0018
| | Newbie | | Join Date: Aug 2008
Posts: 5
| | | re: Query problems
Quote:
1. What exactly do you mean by Memo Box?
2. In what format do the wish the Phone Numbers in the List/Combo Box extracted? e.g.
Code: ( text )
1.
(215) 888-1234;(234) 876-8936;(679)-0018
Code: ( text )
1.
(215) 888-1234
2.
(234) 876-8936
3.
(679)-0018
Code: ( text )
1.
(215) 888-1234, (234) 876-8936, (679)-0018
I would prefer the phone numbers to be extracted as your third example shows,
by memo box i mean a textual area sorry for confusion
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Query problems
Quote:
Originally Posted by Jaxter1 I would prefer the phone numbers to be extracted as your third example shows,
by memo box i mean a textual area sorry for confusion Stand by, I should have an answer for you within a couple of hours. Oops, here comes the boss, gotta go! (LOL).
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Query problems
Quote:
Originally Posted by ADezii Stand by, I should have an answer for you within a couple of hours. Oops, here comes the boss, gotta go! (LOL). OK, boss has gone. Assuming your List Box is named lstPhone and your Text Box is named txtPhoneNos, the following code will do the trick: - Dim strBuild As String
-
Dim intCounter As Integer
-
Dim intNumOfEntries As Integer
-
-
intNumOfEntries = Me![lstPhone].ListCount
-
-
If intNumOfEntries > 0 Then
-
For intCounter = 0 To intNumOfEntries - 1
-
strBuild = strBuild & Me![lstPhone].ItemData(intCounter) & ", "
-
Next
-
Me![txtPhoneNos] = Left$(strBuild, Len(strBuild) - 2) 'remove trailing ", "
-
Else
-
Me![txtPhoneNos] = vbNullString
-
End If
| | Newbie | | Join Date: Aug 2008
Posts: 5
| | | re: Query problems
thanks the help is appreciated
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Query problems
Quote:
Originally Posted by Jaxter1 thanks the help is appreciated You are quite welcome.
| | Newbie | | Join Date: Sep 2008
Posts: 1
| | | re: Query problems
Quote:
Originally Posted by ADezii OK, boss has gone. Assuming your List Box is named lstPhone and your Text Box is named txtPhoneNos, the following code will do the trick:
- Dim strBuild As String
-
Dim intCounter As Integer
-
Dim intNumOfEntries As Integer
-
-
intNumOfEntries = Me![lstPhone].ListCount
-
-
If intNumOfEntries > 0 Then
-
For intCounter = 0 To intNumOfEntries - 1
-
strBuild = strBuild & Me![lstPhone].ItemData(intCounter) & ", "
-
Next
-
Me![txtPhoneNos] = Left$(strBuild, Len(strBuild) - 2) 'remove trailing ", "
-
Else
-
Me![txtPhoneNos] = vbNullString
-
End If
Hi, this code is cool. Now, what I'm tryin is to iterate thru the second column of the combo box as my combo box has two columns. What it will do is iterate through the second column and compare values in those columns with the one passed and if matched it will return the value in the same row but from the first column. Can you please help me how I can achieve this. Thank you.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Query problems
Quote:
Originally Posted by jaeezzy Hi, this code is cool. Now, what I'm tryin is to iterate thru the second column of the combo box as my combo box has two columns. What it will do is iterate through the second column and compare values in those columns with the one passed and if matched it will return the value in the same row but from the first column. Can you please help me how I can achieve this. Thank you. I'm a little fuzzy on your request, but here is generic code that will iterate through all Values in the 2nd Column of a Combo Box named cboNames, and compare them to an Unknown Value: - Dim intCounter As Integer
-
Dim intNumOfEntries As Integer
-
-
intNumOfEntries = Me![cboNames].ListCount
-
-
If intNumOfEntries > 0 Then
-
For intCounter = 0 To intNumOfEntries - 1
-
If Me![cboNames].Column(1, intCounter) = "<some value>" Then
-
'take some action here
-
End If
-
Next
-
End If
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
