autocomplete  
Author Message
slimjen1





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

Hi all,
I have a form with several combo boxes. I would like the users to be able to start typing in the field and it automatically completes the box. Can someone tell me how to get my form to autocomplete

Thanks


Microsoft ISV Community Center Forums1  
 
 
Mubshir Raza Ali





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

Hi,
You can use
AutoComplete* properties to accomplish this task.
If you want to give your own list for suggestion use AutoCompleteCustomSource, You can also set mode either suggest or change by setting AutoCompleteMode, There are also few builtin Source you can set them by setting AutoCompleteSource.

Now for example you add two values in collection of AutoCompleteCustomSource Saml and Martin and set AutoCompleteSource to CustomSource then when you type S it will suggest you Saml if your AutoCompleteMode is suggest.


Hope this help.



 
 
Mark Betz





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

The signature of your call to the SqlCommand constructor is incorrect, so you would be getting an error from the compiler stating that it can't find an overload to match the arguments you've supplied. Try...

string cmdText = "SELECT userLogin FROM Admin WHERE userLogin = '" + match + "'";

SqlCommand sSql = new SqlCommand( cmdText, sqlConn );

However, note that this won't retrieve partial matches, which is what your post implied you were trying to do. To do that you should create the command something like this...

string cmdText = "SELECT userLogin FROM Admin WHERE userLogin LIKE '%" + match + "%'";

That will pull back strings that have the match string anywhere in them. If you want to match leading characters only do...

string cmdText = "SELECT userLogin FROM Admin WHERE userLogin LIKE '" + match + "%'";


 
 
slimjen1





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

Where's the autocomplete property. I right click on the properties of the form and field and I don't see it.

Thanks


 
 
U_T_A





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

Thank you for your help, i managed to fix my code a little bit but now i have another little problem. "Cannot implicitly convert type 'string[]' to 'string'" Thank you again for your help :)


private string GetOptions(string match)
{
this._retStr = "";
List<string> titleArList = new List<string>();
string strConnection;
strConnection = "Data Source=source;Initial Catalog=APR;User ID=test;Password=test
";
SqlConnection sqlConn = new SqlConnection();
SqlDataReader sqlDr = null;

try
{
string cmdText = "SELECT userLogin FROM Admin WHERE userLogin LIKE '" + match + "%'";
SqlCommand sSql = new SqlCommand(cmdText, sqlConn);
sqlConn.Open();
sqlDr = sSql.ExecuteReader();
}
catch (Exception e)
{

}
finally
{
sqlConn.Close();
}

if (sqlDr.Read() != false)
{
while (sqlDr.Read())
{
String param = Convert.ToString(sqlDr["match"]);
titleArList.Add(param);
}
}
return titleArList.ToArray(); <---
"Cannot implicitly convert type 'string[]' to 'string'"
}

 
 
Mark Betz





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

First, your function is typed to return a string, and you are attempting to return string[]. If you want to return an array, and it looks like you do, then you need to rewrite the signature of the function.

Second, your SQLDataReader call won't work, because your SELECT doesn't return a column named "match," it returns a column named "userLogin."


 
 
Mubshir Raza Ali





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

It's combobox property not form's.


 
 
xr280xr





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

If you're using 2.0 you should just use the textbox's autocomplete properties. AutoCompleteSource, AutoCompleteMode, AutoCompleteCustomSource.
 
 
project2n5e0o1





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

if you are not using autocomplete in 2.0 . I would first query the Db and store all information into a dataset. I would make a postback function every time a number is entered. In the postback function have it run through the items in the dataset to find the closest match. always avoid requerying the database when you can.

 
 
JDee





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

project2n5e0o1 is correct, I would also do the same, please see the code for the same. Hope it helps!

private DataTable dtData;

private List<string> GetOptions(string match)

{

List<string> lstStr = new List<string>();

if(dtData == null)

GetData();

// retrun the datarow collection if required instead of List<String>

foreach (DataRow drow in dtData.Select("LastName LIKE '" + match + "*'"))

{

lstStr.Add(drow["LastName"].ToString());

}

return lstStr;

}

private DataTable GetData()

{

SqlConnection sCon = new SqlConnection("Data Source=Sorry;Initial Catalog=Cant;User ID=Tell;Password=You");

SqlDataAdapter sda = new SqlDataAdapter("SELECT LastName FROM Employees", sCon);

dtData = new DataTable("Employees");

try

{

sda.Fill(dtData);

}

catch

{

//catch exception...

}

return dtData;

}

Thanks,
JDee.


 
 
U_T_A





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

Thank you very much for your help guys, i o u


Al.

 
 
JDee





PostPosted: Visual Basic for Applications (VBA), autocomplete Top

I didn’t know that there was a autocomplete featuer in .NET 2.0 for a textbox, that’s awsome!

In that case, The GetOptions method (from my post) could be replaced with

private void GetOptions()

{

textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;

textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;

foreach(DataRow drow in GetData().Rows)

{

textBox1.AutoCompleteCustomSource.Add(drow["LastName"].ToString());

}

}