Home > Archive > Visual Basic Syntax > March 2005 > Populating an array with values from table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Populating an array with values from table
|
|
|
| Hi everyone, I'm having a little problem which I'm stuck at. I'm designing an
application which looks up customer information based on Phone number or Last
name. I also have a list box. What I want is that when the user enters a last
name, the list box should get populated with all the names with that last
name. After that, user can choose which name they want in the list box. I
have an access database with table called CUSTOMER. I'm using a recordset
called CustRECS that is connected to the above mentioned database table. How
can I extract all the matching values from the recordset and populate the
list box. Please help.
Thanks
| |
| Al Reid 2005-03-25, 8:59 am |
| "Ricky" <Ricky@discussions.microsoft.com> wrote in message news:8052BE5E-EBD4-418A-8D67-AED916DA2D3F@microsoft.com...
> Hi everyone, I'm having a little problem which I'm stuck at. I'm designing an
> application which looks up customer information based on Phone number or Last
> name. I also have a list box. What I want is that when the user enters a last
> name, the list box should get populated with all the names with that last
> name. After that, user can choose which name they want in the list box. I
> have an access database with table called CUSTOMER. I'm using a recordset
> called CustRECS that is connected to the above mentioned database table. How
> can I extract all the matching values from the recordset and populate the
> list box. Please help.
>
> Thanks
Retrieve the records from the table using a select statement then loop thru the records and add them to the ListBox. Something like
the following air code:
strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = " & strLastName
CustRECS.ActiveConnection = cn
CustRECS.CursorLocation = adUseClient
CustRECS.CursorType = adOpenForwardOnly
CustRECS.Open strSQL
Do Until CustRECS.EOF
List1.AddItem CustRECS("LastName").Value
Loop
CustRECS.Close
I hope this helps.
--
Al Reid
| |
|
| Hi Al, thanks for your help. I tried that piece of code in my program.
PointsRecs.ActiveConnection = conn
PointsRecs.CursorLocation = adUseClient
PointsRecs.CursorType = adOpenForwardOnly
PointsRecs.Open strSQL
But once I execute, I keep getting this Run-Time error.
No value given for one or more required parameters.
After I hit DEBUG, it selects the last line of the above piece of code where
it says PointsRecs.Open strSQL
Is there anything I'm missing in the syntax? Please advise.
Thanks
"Al Reid" wrote:
> "Ricky" <Ricky@discussions.microsoft.com> wrote in message news:8052BE5E-EBD4-418A-8D67-AED916DA2D3F@microsoft.com...
>
> Retrieve the records from the table using a select statement then loop thru the records and add them to the ListBox. Something like
> the following air code:
>
> strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = " & strLastName
>
> CustRECS.ActiveConnection = cn
> CustRECS.CursorLocation = adUseClient
> CustRECS.CursorType = adOpenForwardOnly
> CustRECS.Open strSQL
>
> Do Until CustRECS.EOF
> List1.AddItem CustRECS("LastName").Value
> Loop
>
> CustRECS.Close
>
> I hope this helps.
>
> --
>
> Al Reid
>
>
>
| |
| Al Reid 2005-03-26, 3:59 pm |
| "Ricky" <Ricky@discussions.microsoft.com> wrote in message
news:1B1AE80F-F111-4755-8DE2-6B32569143B8@microsoft.com...
> Hi Al, thanks for your help. I tried that piece of code in my program.
>
> PointsRecs.ActiveConnection = conn
> PointsRecs.CursorLocation = adUseClient
> PointsRecs.CursorType = adOpenForwardOnly
> PointsRecs.Open strSQL
>
> But once I execute, I keep getting this Run-Time error.
>
> No value given for one or more required parameters.
>
> After I hit DEBUG, it selects the last line of the above piece of code
where
> it says PointsRecs.Open strSQL
>
> Is there anything I'm missing in the syntax? Please advise.
>
> Thanks
>
What is the value of strSQL? It sounds like you have a misnamed field in
the string. I really can't tell much more w/o the SQL string and the table
layout.
| |
| argusy 2005-03-26, 3:59 pm |
| Ricky,
I think the statement ends up being parsed as
strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = "
because strLastName is a null value (ergo - a missing parameter)
try using list1.text instead of strLastName, and make the code
strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = '" & list1.text & "'"
(Al forgot to enclose text with the single quotes)
If you now put in "Richardson" in the listbox, the line is parsed as
strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = 'Richardson'" (Note the single quotes)
if you have surnames with "O'connor" then you have to include the REPLACE function in your code as well, so
this now becomes
strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = '" & replace(list1.text, "'","''") & "'"
This is getting complicated.
Just try that for now, and see what happens
It means that list1 is initially empty, and Al's code should probably be put in the listbox's .validate event.
You could use the listbox's .change event as well, to display ever more shorter a selection as the name gets
longer, but I think you have to be careful with the use of the listbox text somehow
Using the .validate means your customer would type in the whole name and then press <enter> or <tab> to force
the validation, which means the customer then has to go back to the list box to get the correct name from the generated
list.
I think the .change event is more elegant, as the customer doesn't leave the textbox until he/she has made a selection.
Ricky, if you have customers with the same surname, how would you tell the difference?
Have a look at Patrick van Olderen's combobox at http://www.home.zonnet.nl/PatrickO/
Argusy
Ricky wrote:
> Hi Al, thanks for your help. I tried that piece of code in my program.
>
> PointsRecs.ActiveConnection = conn
> PointsRecs.CursorLocation = adUseClient
> PointsRecs.CursorType = adOpenForwardOnly
> PointsRecs.Open strSQL
>
> But once I execute, I keep getting this Run-Time error.
>
> No value given for one or more required parameters.
>
> After I hit DEBUG, it selects the last line of the above piece of code where
> it says PointsRecs.Open strSQL
>
> Is there anything I'm missing in the syntax? Please advise.
>
> Thanks
>
> "Al Reid" wrote:
>
>
>
| |
|
| Argusy:
Hi thanks for your help. What you said makes sense but I think what I need
is the other way around. strLastName is actually the value user types in a
textfield to search a table for the desired last name. If the table has more
than 1 matches for that last name, the list gets populated with all the names
from the table with that particular last name. Please check the piece of code
in the following message.
Al:
Hi Al, here is my code.
Dim strSQL As String, strLastName As String
' User clicks a radio button called optSearchLastName to categorize their
'search based on LAST NAME
If optSearchLastName.Value = True Then
'If the recordset is open, close it.
If PointsRecs.State = adStateOpen Then PointsRecs.Close
strLastName = CStr(UCase(txtSearchCustomer.Text))
strSQL = "SELECT LASTNAME FROM POINTS WHERE LASTNAME = " & strLastName
PointsRecs.ActiveConnection = conn
PointsRecs.CursorLocation = adUseClient
PointsRecs.CursorType = adOpenForwardOnly
PointsRecs.Open strSQL
Do Until PointsRecs.EOF
lstLastName.AddItem PointsRecs("!LASTNAME").Value
Loop
End If
Thanks
"argusy" wrote:
> Ricky,
>
> I think the statement ends up being parsed as
> strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = "
> because strLastName is a null value (ergo - a missing parameter)
>
> try using list1.text instead of strLastName, and make the code
> strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = '" & list1.text & "'"
> (Al forgot to enclose text with the single quotes)
>
> If you now put in "Richardson" in the listbox, the line is parsed as
> strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = 'Richardson'" (Note the single quotes)
> if you have surnames with "O'connor" then you have to include the REPLACE function in your code as well, so
> this now becomes
> strSQL = "SELECT LastName FROM CUSTOMER WHERE LastName = '" & replace(list1.text, "'","''") & "'"
> This is getting complicated.
> Just try that for now, and see what happens
>
> It means that list1 is initially empty, and Al's code should probably be put in the listbox's .validate event.
> You could use the listbox's .change event as well, to display ever more shorter a selection as the name gets
> longer, but I think you have to be careful with the use of the listbox text somehow
> Using the .validate means your customer would type in the whole name and then press <enter> or <tab> to force
> the validation, which means the customer then has to go back to the list box to get the correct name from the generated
> list.
>
> I think the .change event is more elegant, as the customer doesn't leave the textbox until he/she has made a selection.
> Ricky, if you have customers with the same surname, how would you tell the difference?
>
> Have a look at Patrick van Olderen's combobox at http://www.home.zonnet.nl/PatrickO/
>
> Argusy
>
>
> Ricky wrote:
>
>
| |
|
| Al:
I double checked my table. The fieldname is exactly as I typed in the SELECT
statement i.e. LASTNAME
Thanks
"Ricky" wrote:
[color=darkred]
> Argusy:
>
> Hi thanks for your help. What you said makes sense but I think what I need
> is the other way around. strLastName is actually the value user types in a
> textfield to search a table for the desired last name. If the table has more
> than 1 matches for that last name, the list gets populated with all the names
> from the table with that particular last name. Please check the piece of code
> in the following message.
>
>
> Al:
>
> Hi Al, here is my code.
>
> Dim strSQL As String, strLastName As String
>
> ' User clicks a radio button called optSearchLastName to categorize their
> 'search based on LAST NAME
>
> If optSearchLastName.Value = True Then
>
> 'If the recordset is open, close it.
>
> If PointsRecs.State = adStateOpen Then PointsRecs.Close
>
> strLastName = CStr(UCase(txtSearchCustomer.Text))
> strSQL = "SELECT LASTNAME FROM POINTS WHERE LASTNAME = " & strLastName
>
> PointsRecs.ActiveConnection = conn
> PointsRecs.CursorLocation = adUseClient
> PointsRecs.CursorType = adOpenForwardOnly
> PointsRecs.Open strSQL
>
> Do Until PointsRecs.EOF
> lstLastName.AddItem PointsRecs("!LASTNAME").Value
> Loop
>
> End If
>
>
> Thanks
>
>
>
>
>
>
>
> "argusy" wrote:
>
| |
| Al Reid 2005-03-26, 3:59 pm |
| "Ricky" <Ricky@discussions.microsoft.com> wrote in message
news:7B3CE725-AC97-494C-9B4A-9451AF76A0EE@microsoft.com...
> Argusy:
>
> Hi thanks for your help. What you said makes sense but I think what I need
> is the other way around. strLastName is actually the value user types in
a
> textfield to search a table for the desired last name. If the table has
more
> than 1 matches for that last name, the list gets populated with all the
names
> from the table with that particular last name. Please check the piece of
code
> in the following message.
>
>
> Al:
>
> Hi Al, here is my code.
>
> Dim strSQL As String, strLastName As String
>
> ' User clicks a radio button called optSearchLastName to categorize their
> 'search based on LAST NAME
>
> If optSearchLastName.Value = True Then
>
> 'If the recordset is open, close it.
>
> If PointsRecs.State = adStateOpen Then PointsRecs.Close
>
> strLastName = CStr(UCase(txtSearchCustomer.Text))
> strSQL = "SELECT LASTNAME FROM POINTS WHERE LASTNAME = " &
strLastName
>
> PointsRecs.ActiveConnection = conn
> PointsRecs.CursorLocation = adUseClient
> PointsRecs.CursorType = adOpenForwardOnly
> PointsRecs.Open strSQL
>
> Do Until PointsRecs.EOF
> lstLastName.AddItem PointsRecs("!LASTNAME").Value
> Loop
>
> End If
>
>
> Thanks
>
Ricky,
Looks like it was my mistake. The strLastName needs to be surrounded with
single quotes as shown below:
strSQL = "SELECT LASTNAME FROM POINTS WHERE LASTNAME = '" &
strLastName & "'"
Also, from your code, I assume that the Last Names are entered into the
database in all upper case.
| |
|
| Yes, it works now. Thanks for your help. The reason why i have all data from
the application going into the database in capitals is because sometimes
users can enter all lower case strings, inticaps, or all uppercase. In this
case, no matter what user types, my application converts that string to all
uppercase and matches with the data in the corresponding tables, which also
have everything uppercase. I couldn't really think of any better way to do
this. But again, thanks for your help. I really appreciate it.
"Al Reid" wrote:
> "Ricky" <Ricky@discussions.microsoft.com> wrote in message
> news:7B3CE725-AC97-494C-9B4A-9451AF76A0EE@microsoft.com...
> a
> more
> names
> code
> strLastName
> Ricky,
>
> Looks like it was my mistake. The strLastName needs to be surrounded with
> single quotes as shown below:
>
> strSQL = "SELECT LASTNAME FROM POINTS WHERE LASTNAME = '" &
> strLastName & "'"
>
> Also, from your code, I assume that the Last Names are entered into the
> database in all upper case.
>
>
>
>
| |
| argusy 2005-03-28, 4:00 am |
| OK.
I didn't know you had another text box, and were transferring the contents to strLastName
so all you needed to do was put strLastName in single quotes.
Thanks Al. I guess you read my reply
What happens when you have two or more surnames that are the same?
They will all show in the list box, but how do you pick the right one?
Here I go again, making assumptions.
I assume when a name is picked, other text boxes show a phone, address or christian name.
If it's the wrong one, go pick another.
As I suggested, take a look at Patrick's combobox - it gets around that problem very neatly
Argusy
Ricky wrote:[color=darkred]
> Argusy:
>
> Hi thanks for your help. What you said makes sense but I think what I need
> is the other way around. strLastName is actually the value user types in a
> textfield to search a table for the desired last name. If the table has more
> than 1 matches for that last name, the list gets populated with all the names
> from the table with that particular last name. Please check the piece of code
> in the following message.
>
>
> Al:
>
> Hi Al, here is my code.
>
> Dim strSQL As String, strLastName As String
>
> ' User clicks a radio button called optSearchLastName to categorize their
> 'search based on LAST NAME
>
> If optSearchLastName.Value = True Then
>
> 'If the recordset is open, close it.
>
> If PointsRecs.State = adStateOpen Then PointsRecs.Close
>
> strLastName = CStr(UCase(txtSearchCustomer.Text))
> strSQL = "SELECT LASTNAME FROM POINTS WHERE LASTNAME = " & strLastName
>
> PointsRecs.ActiveConnection = conn
> PointsRecs.CursorLocation = adUseClient
> PointsRecs.CursorType = adOpenForwardOnly
> PointsRecs.Open strSQL
>
> Do Until PointsRecs.EOF
> lstLastName.AddItem PointsRecs("!LASTNAME").Value
> Loop
>
> End If
>
>
> Thanks
>
>
>
>
>
>
>
> "argusy" wrote:
>
>
| |
|
| Thanks for the tips. I have another question. I know i'm so annoying : o)
If my table doesn't have any records, I always get this error: "Either BOF
or EOF is true or the current record has been deleted. This action requires a
current record." How can I tackle this error?
"argusy" wrote:
> OK.
> I didn't know you had another text box, and were transferring the contents to strLastName
>
> so all you needed to do was put strLastName in single quotes.
> Thanks Al. I guess you read my reply
>
> What happens when you have two or more surnames that are the same?
> They will all show in the list box, but how do you pick the right one?
> Here I go again, making assumptions.
> I assume when a name is picked, other text boxes show a phone, address or christian name.
> If it's the wrong one, go pick another.
> As I suggested, take a look at Patrick's combobox - it gets around that problem very neatly
>
> Argusy
>
>
> Ricky wrote:
>
>
| |
|
| I forgot to mention that I only get that error where it says
Recordset.movefirst
"Ricky" wrote:
[color=darkred]
> Thanks for the tips. I have another question. I know i'm so annoying : o)
> If my table doesn't have any records, I always get this error: "Either BOF
> or EOF is true or the current record has been deleted. This action requires a
> current record." How can I tackle this error?
>
> "argusy" wrote:
>
| |
| argusy 2005-03-28, 8:58 am |
| before your loop, add
If pointsRecs.bof then ' if it's here, then there's no records
<message box> ' one of two messages - no name or no phone number
Else
<your loop to add items>
Endif
Argusy
Ricky wrote:[color=darkred]
> Thanks for the tips. I have another question. I know i'm so annoying : o)
> If my table doesn't have any records, I always get this error: "Either BOF
> or EOF is true or the current record has been deleted. This action requires a
> current record." How can I tackle this error?
>
> "argusy" wrote:
>
>
| |
|
| Great..thanks =)
"argusy" wrote:
> before your loop, add
>
> If pointsRecs.bof then ' if it's here, then there's no records
> <message box> ' one of two messages - no name or no phone number
> Else
> <your loop to add items>
> Endif
>
> Argusy
>
> Ricky wrote:
>
>
| |
| argusy 2005-03-28, 8:58 am |
| Jus' experience.
It's a good idea to always check for any records after opening a recordset query.
Even if you KNOW there's records - human intervention means someone, somewhere, sometime is going to
enter a name that doesn't exist, or put in a number and select your "Surname" option (or vice versa), or any
other condition that sticks a finger in the works and blows your programming
Argusy
Ricky wrote:
> Great..thanks =)
>
> <snip>
|
|
|
|
|