Form search criteria

[Jason1983]

Hi ,

I am trying to create an access form with a search on the group name and all the related group IDs on this particular group name must be displayed in the combo box on the form . The group name is same but the group numbers are unique..

Group name: Group Number:

Carribou Coffee 12345
Carribou Coffee 63726
Carribou Coffee 19827

So when i search for Carribou Coffee ....It needs to show me all the related numbers in the list.....

This is the query i wrote for search button....Right now with this it only displays one value.....I want all values.....

Option Compare Database
Dim Db As Database
Dim RsGrpName As DAO.Recordset
 
Private Sub cmdGroupNameSearch_Click()
Set RsGrpName = Db.OpenRecordset("SELECT DISTINCT Grp_Number,Grp_Name FROM EmployerGroup Where Grp_Name Like'" & Trim(txtGroupName) & "'")
 
If RsGrpName.RecordCount > 0 Then
 
cboGroupNumbers = RsGrpName.Fields("Grp_Number")
Else
MsgBox "Data Not Available...", vbQuestion
End If
 
End Sub
 
Private Sub form_load()
Set Db = CurrentDb()
 
End Sub
 

waiting for a help from some one....
Thanks,
Jason...

[Rabbit]

It should be:

cboGroupNumbers.RowSource = "SELECT DISTINCT Grp_Number FROM EmployerGroup WHERE Grp_Name Like '" & Trim(txtGroupName) & "';"
 

[Jason1983]

It should be:

cboGroupNumbers.RowSource = "SELECT DISTINCT Grp_Number FROM EmployerGroup WHEREGrp_Name Like '" & Trim(txtGroupName) & "';"
 

Hi,

Thanks for the solution that worked for me.....

I have another question in the same GroupName field now i have to type the whole name and then i say search so that it will get the group numbers....

But is there any way that i start typing and it will display all the related names that start with those letters and allow me to pick from them.. so that my intension is that if the user miss spells one word there wont be much difference as we give him the list of all names....Is that possible....

Please help me with the code changes...
As iam learning VB and using for the first time...I dont know much tricks in it....

Thanks for ur help.....
Will be waiting to hear something......
Jason...

[NeoPa]

Check out Example Filtering on a Form for a full rundown, but Rabbit's right in that he highlighted the fundamentals of where your code goes wrong.
You don't do it with a recordset. You set the RecordSource to the SQL you want.

[NeoPa]

...
But is there any way that i start typing and it will display all the related names that start with those letters and allow me to pick from them.. so that my intension is that if the user miss spells one word there wont be much difference as we give him the list of all names....Is that possible....

Check out the AutoExpand property of the ComboBox control.

[Jason1983]

Need Help with combo box display
--------------------------------------------------------------------------------

Hi,

I have a form and i have a combo box that gets loaded with the last name and first name being concatenated at the time of loading into the combobox and i select from that list ...

So the problem is if i select any name then it is breaking up and moving into a second line in the combobox..

Example:

Jason Meyers ----- I select this


Result

Jason
Meyers ---- I dont want it to be like that


I want it to be in the same line as it was displayed and need to remain like that after selection.....


Desired Result:

Jason Meyers




Waiting for help

Thanks
Jason.

[NeoPa]

Jason,

Some of this is clear :)
What is not clear is where this name is displaying wrongly. Is it in the ComboBox itself (after selection)? Or is the value used elsewhere?
Something that would be helpful too is the source settings of the ComboBox. Does it use a query (if so post in the SQL)? Is it a value list (If so post in the value list)?