Creating combo box or query to find multiple records

[ASSK]

Hi and thanks for your help in advance.

an example of my table as below:

WEN Name Gender
123 ABC M
354 ZYW M
8601 cwm F
1183 SAM F
1523 DEN Unknown

What I like to do is create either a form or a query to show record 123,
8601, 1523. Ultimately, if I can create a form, enter the multiple number,
click ok and it will extract those records for me.

I hope what I've asked make sense....PLEASE HELP.

 

[banem2]

Hi and thanks for your help in advance.

an example of my table as below:

WEN         Name           Gender        
123          ABC             M
354          ZYW            M
8601        cwm            F
1183        SAM            F
1523        DEN             Unknown

What I like to do is create either a form or a query to show record 123,
8601, 1523. Ultimately, if I can create a form, enter the multiple number,
click ok and it will extract those records for me.

I hope what I've asked make sense....PLEASE HELP.

You will need one form with record source written in SQL, like this:

SELECT * FROM myTable

Then create another form and put existing form as subform.

You also need one text box to enter search criteria. When you have
this form created you need to code AfterUpdate event of Text Box. For
simple filter you can code AfterUpdate event like this:

dim strWhere As String
strWhere = strWhere & " [myTableFieldName] LIKE ' " & myFormFieldName
& "'"

or, when filtering numbers:

strWhere = strWhere & " [myTableFieldName] = " & myFormFieldName

To have it filtered by multiply entries delimited with comma, code
needs to loop through string and it needs to repeat above command like
this:

strWhere = strWhere & " [myTableFieldName] LIKE ' " & myFormFieldName
& "' OR "
If Right(strWhere, 4) = " OR " Then
strWhere = Left(strWhere, Len(strWhere)-3)
End If
mySubformName.RecordSource = mySubformName.RecordSource & strWhere
mySubformName.Requery

Basically you need to find the position of comma (,) with InStr
command and remember position of last found comma and simply loop
through string until there are no more commas, i.e.

instr(1, "text, text2", ",")

will return 5.

Let me know if you have trouble with code so I will wrote it for you.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[ASSK]

Hi,

Thanks for your help. But as I am relatively new at all this, what you
wrote, I didn't understand at all. I would probably need an "IDIOT" guide.

Is there a simpler way to do this? Can you pls tell me the code or something
to make it easier..

Thanks for you patient.

Hi and thanks for your help in advance.

an example of my table as below:

WEN Name Gender
123 ABC M
354 ZYW M
8601 cwm F
1183 SAM F
1523 DEN Unknown

What I like to do is create either a form or a query to show record 123,
8601, 1523. Ultimately, if I can create a form, enter the multiple number,
click ok and it will extract those records for me.

I hope what I've asked make sense....PLEASE HELP.

You will need one form with record source written in SQL, like this:

SELECT * FROM myTable

Then create another form and put existing form as subform.

You also need one text box to enter search criteria. When you have
this form created you need to code AfterUpdate event of Text Box. For
simple filter you can code AfterUpdate event like this:

dim strWhere As String
strWhere = strWhere & " [myTableFieldName] LIKE ' " & myFormFieldName
& "'"

or, when filtering numbers:

strWhere = strWhere & " [myTableFieldName] = " & myFormFieldName

To have it filtered by multiply entries delimited with comma, code
needs to loop through string and it needs to repeat above command like
this:

strWhere = strWhere & " [myTableFieldName] LIKE ' " & myFormFieldName
& "' OR "
If Right(strWhere, 4) = " OR " Then
strWhere = Left(strWhere, Len(strWhere)-3)
End If
mySubformName.RecordSource = mySubformName.RecordSource & strWhere
mySubformName.Requery

Basically you need to find the position of comma (,) with InStr
command and remember position of last found comma and simply loop
through string until there are no more commas, i.e.

instr(1, "text, text2", ",")

will return 5.

Let me know if you have trouble with code so I will wrote it for you.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[banem2]

Hi,

Thanks for your help. But as I am relatively new at all this, what you
wrote, I didn't understand at all. I would probably need an "IDIOT" guide.

Is there a simpler way to do this? Can you pls tell me the code or something
to make it easier..

Thanks for you patient.

You will need one form with record source written in SQL, like this:

SELECT * FROM myTable

Then create another form and put existing form as subform.

You also need one text box to enter search criteria. When you have
this form created you need to code AfterUpdate event of Text Box. For
simple filter you can code AfterUpdate event like this:

dim strWhere As String
strWhere = strWhere & " [myTableFieldName] LIKE ' " & myFormFieldName
& "'"

or, when filtering numbers:

strWhere = strWhere & " [myTableFieldName] = " & myFormFieldName

To have it filtered by multiply entries delimited with comma, code
needs to loop through string and it needs to repeat above command like
this:

strWhere = strWhere & " [myTableFieldName] LIKE ' " & myFormFieldName
& "' OR "
If Right(strWhere, 4) = " OR " Then
  strWhere = Left(strWhere, Len(strWhere)-3)
End If
mySubformName.RecordSource = mySubformName.RecordSource & strWhere
mySubformName.Requery

Basically you need to find the position of comma (,) with InStr
command and remember position of last found comma and simply loop
through string until there are no more commas, i.e.

instr(1, "text, text2", ",")

will return 5.

Let me know if you have trouble with code so I will wrote it for you.

Regards,
Branislav Mihaljev
Microsoft Access MVP

OK. For the start create form with record source as:

SELECT * FROM myTableName

Then create another form and put this form as subform there. In top of
form add unbound text box.

Once when you have done that, right click text box and choose
Properties.

Change name of text box to "txtSearch".

Switch to Events tab and find AfterUpdate.

Choose [Event Procedure] and click button "...".

You will see VBA window ready to enter code which triggers when user
type data into text box and press Enter (After Update). Copy following
code and then change field names to match your database field names:

-------
dim strWhere As String

If Not IsNull(txtSearch) Then
strWhere = strWhere & " [myTableFieldName] LIKE ' " & _
txtSearch & "'"
mySubformName.RecordSource = _
mySubformName.RecordSource & strWhere
mySubformName.Requery
End If
------

Once when you make this work, you have simple - single entry search.
Subform will be filtered by single data entry. So when you complete
this part we will extend the code to accept multi-values. Let me know
if this so far is OK, so we can move to second step.

Let me know is field "WEN" number or text?

Regards,
Branislav Mihaljev
Microsoft Access MVP