how can i filter my database?

[strive4peace]

Hi Jessica,

" I am afraid I'm not sure how to know if I have the subform
synchronized with LinkMasterFields and LinkChildFields. "

Read Access Basics ... it is only 100 pages and has lots of screen shots
<smile>

You will find the answer ... LinkMasterFields and LinkChildFields are
properties of a subform/subreport control

~~~

this SQL statement does not have any criteria:

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];

~~~
" As far as using "no criteria" to show all the records, I'm not sure
how to do that."

me.FilterOn = false

when you change a Filter, you need to requery after you change it...

me.Requery


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thank you so much for looking at my code! I am afraid I'm not sure how to
know if I have the subform synchronized with LinkMasterFields and
LinkChildFields. Could you explain that process/what those are? I think I
am using a query to get the info from the table onto the form. my combo
boxes have row source type set to table/query and the row sources have this
in them: (the other combo box has the same but with a different field)

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];


So my main form has two combo boxes, combo22 and combo24. It pulls up the
SNM TYPE and ICA (both fields in my table) from my "SNM Type" table. My main
form also have "Go" and "Reset" buttons which are the Command44 and
Command43.

My subform is just a subform I made in the wizard, and it took the field
names from my table and made them text boxes with labels. When I am in "form
view" it shows up as a table, not individual text boxes (This is what I want).

As far as using "no criteria" to show all the records, I'm not sure how to
do that. What would that change when I used my form?

Is there anything else I can tell you to help you? Thank you so much!

Jessica

Hi Jessica,

your code only sets a filter on the main form ... it does nothing with
the subform.

1. Do you have the subform synchronized with LinkMasterFields and
LinkChildFields?

2. Are you using a query for the RecordSource of subform that gets
criteria from the main form? (if so, this is not the best way -- #1 is)

Can you explain what type of information is in your main form and your
subform?

~~~

instead of this:

If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."

you might want to use no criteria to show all the records

~~~

Command44 :

before you write code for a control, you should give it a logical Name
like --> cmdApplyFilter

for more information, read about Properties and Methods here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*

Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?


:

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

:

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

 

[NukeEng85]

Crystal,

I'm afraid I don't understand the LinkMasterFields and LinkChildFields from
the Access Basics page. This is the information I found on the topic:

I don't have fields that are AutoNumber or Long Integer fields. Should I be
using the fields that the combo boxes use as MainID? because I have two
combo boxes, each one using a field (meaning I have two fields), should I use
the
"MainID; Fieldname_main"? If so, do if I put "[SNM Type]; [ICA]" in the
LinkMasterFields then do I put the same thing in the LinkChildFields, or
something different?

Thank you!

Jessica

Hi Jessica,

" I am afraid I'm not sure how to know if I have the subform
synchronized with LinkMasterFields and LinkChildFields. "

Read Access Basics ... it is only 100 pages and has lots of screen shots
<smile>

You will find the answer ... LinkMasterFields and LinkChildFields are
properties of a subform/subreport control

~~~

this SQL statement does not have any criteria:

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];

~~~
" As far as using "no criteria" to show all the records, I'm not sure
how to do that."

me.FilterOn = false

when you change a Filter, you need to requery after you change it...

me.Requery


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thank you so much for looking at my code! I am afraid I'm not sure how to
know if I have the subform synchronized with LinkMasterFields and
LinkChildFields. Could you explain that process/what those are? I think I
am using a query to get the info from the table onto the form. my combo
boxes have row source type set to table/query and the row sources have this
in them: (the other combo box has the same but with a different field)

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];


So my main form has two combo boxes, combo22 and combo24. It pulls up the
SNM TYPE and ICA (both fields in my table) from my "SNM Type" table. My main
form also have "Go" and "Reset" buttons which are the Command44 and
Command43.

My subform is just a subform I made in the wizard, and it took the field
names from my table and made them text boxes with labels. When I am in "form
view" it shows up as a table, not individual text boxes (This is what I want).

As far as using "no criteria" to show all the records, I'm not sure how to
do that. What would that change when I used my form?

Is there anything else I can tell you to help you? Thank you so much!

Jessica

Hi Jessica,

your code only sets a filter on the main form ... it does nothing with
the subform.

1. Do you have the subform synchronized with LinkMasterFields and
LinkChildFields?

2. Are you using a query for the RecordSource of subform that gets
criteria from the main form? (if so, this is not the best way -- #1 is)

Can you explain what type of information is in your main form and your
subform?

~~~

instead of this:

If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."

you might want to use no criteria to show all the records

~~~

Command44 :

before you write code for a control, you should give it a logical Name
like --> cmdApplyFilter

for more information, read about Properties and Methods here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*




NukeEng85 wrote:
Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?


:

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

:

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true

 

[strive4peace]

Hi Jessica,

"if I put "[SNM Type]; [ICA]" in the LinkMasterFields then do I put the
same thing in the LinkChildFields, or something different?"

yes (without the quotes) -- if the fieldnames are the same; if they are
different, then use the proper names

NOTE: you must have controls ON each form to hold the linking fields.
Normally, on the subform anyway, you would set Visible --> No so they do
not display

Often, I put linking fields in the form footer of the main form -- and
hidden control in the form header or footer of subforms

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

I'm afraid I don't understand the LinkMasterFields and LinkChildFields from
the Access Basics page. This is the information I found on the topic:

I don't have fields that are AutoNumber or Long Integer fields. Should I be
using the fields that the combo boxes use as MainID? because I have two
combo boxes, each one using a field (meaning I have two fields), should I use
the
"MainID; Fieldname_main"? If so, do if I put "[SNM Type]; [ICA]" in the
LinkMasterFields then do I put the same thing in the LinkChildFields, or
something different?

Thank you!

Jessica

Hi Jessica,

" I am afraid I'm not sure how to know if I have the subform
synchronized with LinkMasterFields and LinkChildFields. "

Read Access Basics ... it is only 100 pages and has lots of screen shots
<smile>

You will find the answer ... LinkMasterFields and LinkChildFields are
properties of a subform/subreport control

~~~

this SQL statement does not have any criteria:

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];

~~~
" As far as using "no criteria" to show all the records, I'm not sure
how to do that."

me.FilterOn = false

when you change a Filter, you need to requery after you change it...

me.Requery


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thank you so much for looking at my code! I am afraid I'm not sure how to
know if I have the subform synchronized with LinkMasterFields and
LinkChildFields. Could you explain that process/what those are? I think I
am using a query to get the info from the table onto the form. my combo
boxes have row source type set to table/query and the row sources have this
in them: (the other combo box has the same but with a different field)

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];


So my main form has two combo boxes, combo22 and combo24. It pulls up the
SNM TYPE and ICA (both fields in my table) from my "SNM Type" table. My main
form also have "Go" and "Reset" buttons which are the Command44 and
Command43.

My subform is just a subform I made in the wizard, and it took the field
names from my table and made them text boxes with labels. When I am in "form
view" it shows up as a table, not individual text boxes (This is what I want).

As far as using "no criteria" to show all the records, I'm not sure how to
do that. What would that change when I used my form?

Is there anything else I can tell you to help you? Thank you so much!

Jessica

:

Hi Jessica,

your code only sets a filter on the main form ... it does nothing with
the subform.

1. Do you have the subform synchronized with LinkMasterFields and
LinkChildFields?

2. Are you using a query for the RecordSource of subform that gets
criteria from the main form? (if so, this is not the best way -- #1 is)

Can you explain what type of information is in your main form and your
subform?

~~~

instead of this:

If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."

you might want to use no criteria to show all the records

~~~

Command44 :

before you write code for a control, you should give it a logical Name
like --> cmdApplyFilter

for more information, read about Properties and Methods here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*




NukeEng85 wrote:
Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?


:

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

:

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true

 

[NukeEng85]

I think I may be having a problem because my form and subform are unbound.
When I clicked on the "..." button I got an error message that says I can't
link unbound forms. I ignored it and entered in "[SNM Type];[ICA]" into both
the LinkMaster and LinkChild Fields anyway. When I switched to "view form" I
recieved an error message that asked me for parameters for my SNM Type and
ICA fields.

Thank you so much for helping me!

Jessica

Hi Jessica,

"if I put "[SNM Type]; [ICA]" in the LinkMasterFields then do I put the
same thing in the LinkChildFields, or something different?"

yes (without the quotes) -- if the fieldnames are the same; if they are
different, then use the proper names

NOTE: you must have controls ON each form to hold the linking fields.
Normally, on the subform anyway, you would set Visible --> No so they do
not display

Often, I put linking fields in the form footer of the main form -- and
hidden control in the form header or footer of subforms

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

I'm afraid I don't understand the LinkMasterFields and LinkChildFields from
the Access Basics page. This is the information I found on the topic:

SourceObject --> drop list and choose the name of the form you will use as a >> subform (You can also Drag a form object from the database window and drop >> it on the main form. This automatically sets The SourceObject property.)
LinkMasterFields --> MainID
LinkChildFields --> MainID

If you have multiple fields, delimit the list with semi-colon
LinkMasterFields --> MainID;Fieldname_main
LinkChildFields --> MainID;Fieldname_child
WHERE:
-- MainID is replaced with your field name holding an AutoNumber field (usually) >> in the parent table and a Long Integer field in the child table. Fieldname_main is >> the fieldname in the main RecordSet – and it is best to actually put the field on >> the main form. Fieldname_child is the name of a field in the child RecordSet – >> and, once again, it is best that this field actually be ON the related subform.

I don't have fields that are AutoNumber or Long Integer fields. Should I be
using the fields that the combo boxes use as MainID? because I have two
combo boxes, each one using a field (meaning I have two fields), should I use
the
"MainID; Fieldname_main"? If so, do if I put "[SNM Type]; [ICA]" in the
LinkMasterFields then do I put the same thing in the LinkChildFields, or
something different?

Thank you!

Jessica

Hi Jessica,

" I am afraid I'm not sure how to know if I have the subform
synchronized with LinkMasterFields and LinkChildFields. "

Read Access Basics ... it is only 100 pages and has lots of screen shots
<smile>

You will find the answer ... LinkMasterFields and LinkChildFields are
properties of a subform/subreport control

~~~

this SQL statement does not have any criteria:

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];

~~~
" As far as using "no criteria" to show all the records, I'm not sure
how to do that."

me.FilterOn = false

when you change a Filter, you need to requery after you change it...

me.Requery


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




NukeEng85 wrote:
Crystal,

Thank you so much for looking at my code! I am afraid I'm not sure how to
know if I have the subform synchronized with LinkMasterFields and
LinkChildFields. Could you explain that process/what those are? I think I
am using a query to get the info from the table onto the form. my combo
boxes have row source type set to table/query and the row sources have this
in them: (the other combo box has the same but with a different field)

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];


So my main form has two combo boxes, combo22 and combo24. It pulls up the
SNM TYPE and ICA (both fields in my table) from my "SNM Type" table. My main
form also have "Go" and "Reset" buttons which are the Command44 and
Command43.

My subform is just a subform I made in the wizard, and it took the field
names from my table and made them text boxes with labels. When I am in "form
view" it shows up as a table, not individual text boxes (This is what I want).

As far as using "no criteria" to show all the records, I'm not sure how to
do that. What would that change when I used my form?

Is there anything else I can tell you to help you? Thank you so much!

Jessica

:

Hi Jessica,

your code only sets a filter on the main form ... it does nothing with
the subform.

1. Do you have the subform synchronized with LinkMasterFields and
LinkChildFields?

2. Are you using a query for the RecordSource of subform that gets
criteria from the main form? (if so, this is not the best way -- #1 is)

Can you explain what type of information is in your main form and your
subform?

~~~

instead of this:

If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."

you might want to use no criteria to show all the records

~~~

Command44 :

before you write code for a control, you should give it a logical Name
like --> cmdApplyFilter

for more information, read about Properties and Methods here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*




NukeEng85 wrote:
Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?


:

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

 

[strive4peace]

Hi Jessica,

there are no FIELDS with unbound forms -- and therefore, nothing to use
for the link fields, which must be in the form RecordSource (and unbound
forms don't have that).

There is a lot you cannot do with unbound forms including filtering.
Unbound forms are difficult to work with and take more time to develop.
I have some unbound controls on a form I am working with right now
because it is to collect an address -- but if the address is already in
the database, I do not want to add it again ... it has taken me tons
more time to implement and has been frustrating; happy to say, though, I
am almost done with it -- yay!

WHY are you using unbound forms? My advice is to make your forms bound!!!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I think I may be having a problem because my form and subform are unbound.
When I clicked on the "..." button I got an error message that says I can't
link unbound forms. I ignored it and entered in "[SNM Type];[ICA]" into both
the LinkMaster and LinkChild Fields anyway. When I switched to "view form" I
recieved an error message that asked me for parameters for my SNM Type and
ICA fields.

Thank you so much for helping me!

Jessica

Hi Jessica,

"if I put "[SNM Type]; [ICA]" in the LinkMasterFields then do I put the
same thing in the LinkChildFields, or something different?"

yes (without the quotes) -- if the fieldnames are the same; if they are
different, then use the proper names

NOTE: you must have controls ON each form to hold the linking fields.
Normally, on the subform anyway, you would set Visible --> No so they do
not display

Often, I put linking fields in the form footer of the main form -- and
hidden control in the form header or footer of subforms

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

I'm afraid I don't understand the LinkMasterFields and LinkChildFields from
the Access Basics page. This is the information I found on the topic:

SourceObject --> drop list and choose the name of the form you will use as a >> subform (You can also Drag a form object from the database window and drop >> it on the main form. This automatically sets The SourceObject property.)
LinkMasterFields --> MainID
LinkChildFields --> MainID
If you have multiple fields, delimit the list with semi-colon
LinkMasterFields --> MainID;Fieldname_main
LinkChildFields --> MainID;Fieldname_child
WHERE:
-- MainID is replaced with your field name holding an AutoNumber field (usually) >> in the parent table and a Long Integer field in the child table. Fieldname_main is >> the fieldname in the main RecordSet – and it is best to actually put the field on >> the main form. Fieldname_child is the name of a field in the child RecordSet – >> and, once again, it is best that this field actually be ON the related subform.
I don't have fields that are AutoNumber or Long Integer fields. Should I be
using the fields that the combo boxes use as MainID? because I have two
combo boxes, each one using a field (meaning I have two fields), should I use
the
"MainID; Fieldname_main"? If so, do if I put "[SNM Type]; [ICA]" in the
LinkMasterFields then do I put the same thing in the LinkChildFields, or
something different?

Thank you!

Jessica

:

Hi Jessica,

" I am afraid I'm not sure how to know if I have the subform
synchronized with LinkMasterFields and LinkChildFields. "

Read Access Basics ... it is only 100 pages and has lots of screen shots
<smile>

You will find the answer ... LinkMasterFields and LinkChildFields are
properties of a subform/subreport control

~~~

this SQL statement does not have any criteria:

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];

~~~
" As far as using "no criteria" to show all the records, I'm not sure
how to do that."

me.FilterOn = false

when you change a Filter, you need to requery after you change it...

me.Requery


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




NukeEng85 wrote:
Crystal,

Thank you so much for looking at my code! I am afraid I'm not sure how to
know if I have the subform synchronized with LinkMasterFields and
LinkChildFields. Could you explain that process/what those are? I think I
am using a query to get the info from the table onto the form. my combo
boxes have row source type set to table/query and the row sources have this
in them: (the other combo box has the same but with a different field)

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];


So my main form has two combo boxes, combo22 and combo24. It pulls up the
SNM TYPE and ICA (both fields in my table) from my "SNM Type" table. My main
form also have "Go" and "Reset" buttons which are the Command44 and
Command43.

My subform is just a subform I made in the wizard, and it took the field
names from my table and made them text boxes with labels. When I am in "form
view" it shows up as a table, not individual text boxes (This is what I want).

As far as using "no criteria" to show all the records, I'm not sure how to
do that. What would that change when I used my form?

Is there anything else I can tell you to help you? Thank you so much!

Jessica

:

Hi Jessica,

your code only sets a filter on the main form ... it does nothing with
the subform.

1. Do you have the subform synchronized with LinkMasterFields and
LinkChildFields?

2. Are you using a query for the RecordSource of subform that gets
criteria from the main form? (if so, this is not the best way -- #1 is)

Can you explain what type of information is in your main form and your
subform?

~~~

instead of this:

If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."

you might want to use no criteria to show all the records

~~~

Command44 :

before you write code for a control, you should give it a logical Name
like --> cmdApplyFilter

for more information, read about Properties and Methods here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*




NukeEng85 wrote:
Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?


:

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

 

[Paul]

Crystal,

I was reading through your posts with dave and you are describing my exact
problem, I would like to filter my form using a combobox, but somehow I can't
get your code to work for me.

I have a very simple database I just need to know where to make the
adjustments in the code you already provided.

Can you help me?

Paul

Hi Dave,

thank you -- and you are most welcome!

As for a good 'practice' database ... most of what I develop are custom
databases that cannot be shared because they are proprietary. There is
a link to a contact database in 'Access Basics' that you can download.
Chapter 8 of Access Basics lists several links that also have sample
databases and lots of good information -- and there are links for more
essentials throughout the document as well.

The Internet has a wealth of information, Google is your fiend <smile>

~~

aah, books ... there are several good books. Go to your favorite
bookstore and plan to spend at least a few hours picking one or more
books that suit you

1. go to the shelves with your basket(s!) and pull off a copy of every
book on Access.

At each of these subsequent steps, eliminate books that don't meet your
criteria and put the books back on the shelves

2. open each book and see if you like the font it is written in -- make
sure it is big enough and clear enoug. Also ensure that the pages
aren't so thin that text from the backside shows up as darkened areas as
this will distract you while you are trying to learn.

3. check for a comprehensive index

4. now, open each book and read arbitrary sections from different places
in the book. Every author has a different writing style and you will
find some styles more condusive to the way that you learn

5. make sure diagrams are clear enough to see

6. check the pages for "white space" ... just as important as what is
written is what is not ... your brain will need to assimilate a lot of
information and good use of white space is important

You will now have a much smaller stack to evaluate! Books are different
types, some are step-by-step, some are quick-reference, and others are
more comprehensive and designed for reference.

Once you have narrowed your choices, you may want to use price to help
make the final decisions.

pick:

1. a step-by-step book that uses examples you can relate to.
Here is where you will need to sit in the bookstore and read for a few
hours to really find one with good examples you can learn from.
Personally, I do not like step-by-step books; but for you, I think it
would be a good idea. A good Table Of Contents is something to look for
too.

2. quick-reference book (if they have one, it should be a smaller and
less-expensive book -- showing things like shortcut keys)

3. a good reference book
pick one that you could comfortably read cover-cover as that is exactly
what you should do. This book should have a thorough index.

The secret is to buy books that YOU can relate to and read! The number
of pages and what they cover is not nearly as important to how well you
feel you can relate to the author(s) -- it will only be a good book if
you read it <smile>.

~~

You will find these newsgroups to be a great resource, some of the best
Access minds on the world are here. Build a database you have a need
for, and let us help guide you along the way. Read books, do examples,
then change the examples to apply to your practice database. It is ok
to do things you wouldn't really care about, like sum and average
numeric ID fields, or pretend your SaleDate needs to be verified for a
product (like a booking date for a hotel room) <smile>... practice
skills as you read about them and feel they would be useful to know.
There is no substitute for trying it yourself.

As you use Access, print out help screens you visit and put them in a
notebook

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

WOW... you're too much and so helpful. Thank you for all of your help and
suggestions. One last question. I learn and do so much better by 'seeing'
it and the code you've given will certainly help me out. But.. can you
recommend any books that you've found with little tricks / tips as you've
pointed out? Do you have a practice db with this little example?
Suggestions are appreciated.
Thanks again.
Dave

you're welcome, Dave

one more thing ... I often use a combo to FIND records instead of
filtering a form. Consider showing all the records in the form and just
filtering the combo that finds them...

Make one or more unbound combos on your form (like in the header). Let
the first column be invisible and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).

If you are searching the recordset on another form, change the
FindRecord name to be specific (like FindRecord_Order) and, substitute

Me --> forms!formname

If on a subform:
Me --> Me.subform_controlname.form

~~~

if you want to add other ways to limit the Where clause of the SQL for
your combo(s) to find records, here is some food for thought...

'~~~~~~~~~~~~~~~
dim mWhere as variant _
, strSQL as string

mWhere = Null

If not IsNull(me.text_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[TextFieldname]= '" _
& me.text_controlname & "'"
end if

If not IsNull(me.date_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

If not IsNull(me.numeric_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[NumericFieldname]= " _
& me.numeric_controlname
end if

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename] " _
& (" WHERE " + mWhere) _
& " ORDER BY [fieldname];"

me.combo_controlname.RowSource = strSQL
me.combo_controlname.Requery

'~~~~~~~~~~~~~~~~~~~~~
When I have controls to Find and to Filter, I often color-code the
background ... for instance, the background color of Find is pale yellow
and the background color of Filter is pale green or pale blue.
'~~~~~~~~~~~~~~~~~~~~~

from "Access Basics", by Crystal:

Difference between + and &
--------------------------

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

~~~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
Thanks so much Crystal. I've printed your examples and will add the textbox
in a few hours... you've been extremely helpful and I'm much appreciative.

Now, You have a good day!
Dave

:

Hi Dave,

you are welcome happy to make your day <smile>