Searching

  • Thread starter gjameson via AccessMonster.com
  • Start date
G

gjameson via AccessMonster.com

I have 3 text boxes that I want to be able to use for searching.

srcID
scrCustomer
srcAddress

I have a list box below these text boxes that I want to dynamically update as
the user types in the text boxes. I thought I had this worked out early but I
have sleep since then. :)

Here is my sql for my list box.

SELECT DISTINCTROW Customer.ID AS ID, Customer.[Cust Name] AS Customer, [St
Nbr] & " " & [St Name] AS Address, Customer.MODEM_PHNE AS [Modem #], Customer.
IT_CUST AS IT, Customer.EVC_BRAND AS EVC, Customer.CHT_BRAND AS Chart,
Customer.[Mtr Size] AS [Mtr Size], Customer.[Mtr #] AS [Mtr #], Customer.
[Prem #] AS [Prem Code]

FROM Customer
WHERE (((Customer.ID) Like [Forms]![Main]![srcID] & "*"))
OR (((Customer.[Cust Name]) Like [Forms]![Main]![scrCustomer] & " *"))
OR ((([St Nbr] & " " & [St Name]) Like [Forms]![Main]![srcAddress] & "*"))
ORDER BY Customer.ID, Customer.[Cust Name];

Here is my vb for the form

Private Sub srcID_Change()

On Error GoTo Err_srcID_Change

Me.Refresh
Me.srcID.SelStart = Len(Me.srcID)

Err_srcID_Change:
Exit Sub

End Sub

Private Sub srcAddress_Change()

On Error GoTo Err_srcAddress_Change

Me.Refresh
Me.srcAddress.SelStart = Len(Me.srcAddress)

Err_srcAddress_Change:
Exit Sub

End Sub

Private Sub scrCustomer_Change()
On Error GoTo Err_scrCustomer_Change

Me.Refresh
Me.scrCustomer.SelStart = Len(Me.scrCustomer)

Err_scrCustomer_Change:
Exit Sub

End Sub

I can get this to work when I only use one textbox in my query. What am I
missing here?

TIA
Gerald
 
S

strive4peace

Hi Gerald,

this example is for filtering a form, but the logic is the same as
building the WHERE clause of the SQL statement for the RowSource of your
listbox...

'~~~~~~~~~~~~~~~

Set Form Filter

It would be best to build a filter string for the form (as opposed to
imbedding a parameter in a query)

put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~

dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery

'~~~~~~~~~~~~~~~



me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you ;))

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have 3 text boxes that I want to be able to use for searching.

srcID
scrCustomer
srcAddress

I have a list box below these text boxes that I want to dynamically update as
the user types in the text boxes. I thought I had this worked out early but I
have sleep since then. :)

Here is my sql for my list box.

SELECT DISTINCTROW Customer.ID AS ID, Customer.[Cust Name] AS Customer, [St
Nbr] & " " & [St Name] AS Address, Customer.MODEM_PHNE AS [Modem #], Customer.
IT_CUST AS IT, Customer.EVC_BRAND AS EVC, Customer.CHT_BRAND AS Chart,
Customer.[Mtr Size] AS [Mtr Size], Customer.[Mtr #] AS [Mtr #], Customer.
[Prem #] AS [Prem Code]

FROM Customer
WHERE (((Customer.ID) Like [Forms]![Main]![srcID] & "*"))
OR (((Customer.[Cust Name]) Like [Forms]![Main]![scrCustomer] & " *"))
OR ((([St Nbr] & " " & [St Name]) Like [Forms]![Main]![srcAddress] & "*"))
ORDER BY Customer.ID, Customer.[Cust Name];

Here is my vb for the form

Private Sub srcID_Change()

On Error GoTo Err_srcID_Change

Me.Refresh
Me.srcID.SelStart = Len(Me.srcID)

Err_srcID_Change:
Exit Sub

End Sub

Private Sub srcAddress_Change()

On Error GoTo Err_srcAddress_Change

Me.Refresh
Me.srcAddress.SelStart = Len(Me.srcAddress)

Err_srcAddress_Change:
Exit Sub

End Sub

Private Sub scrCustomer_Change()
On Error GoTo Err_scrCustomer_Change

Me.Refresh
Me.scrCustomer.SelStart = Len(Me.scrCustomer)

Err_scrCustomer_Change:
Exit Sub

End Sub

I can get this to work when I only use one textbox in my query. What am I
missing here?

TIA
Gerald
Click to expand...
 
G

gjameson via AccessMonster.com

I am having a time understanding your example. First, I am a very novice
Access programmer. I know just enough to be dangerous. :)

'~~~~~~~~~~~~~~~

Set Form Filter

It would be best to build a filter string for the form (as opposed to
imbedding a parameter in a query)
------------------------------------------------------------------------------
------------------
What do I need to put in my Listbox:
Control Source: blank now
RowSourceType:Table/Query now
RowSouce:Customer Table now

I see after running my test on the form property Filter I have [ID]='xxxxx'

I only tried one field so far.
------------------------------------------------------------------------------
----------------
put comboboxes, textboxes, listboxes, etc on the form (i put then in the <----
---------------- Mine are spread out on the form. Do they have to go header).
Assign this to the AfterUpdate event of each one...
into the header?


=SetFormFilter() <-------------------------------------------------- Where
does this go?

then put this code behind the form

'~~~~~~~~~~~~~~~
------------------------------------------------------------------------

Here is my code.

Dim mFilter As String
mFilter = ""

If Not IsNull(Me.srcID) Then
mFilter = "[ID]= '" _
& Me.srcID & "'"
End If

If Len(mFilter) > 0 Then
Me.Filter = mFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If

Me.Requery
End Sub

Private Sub srcID_AfterUpdate()
SetFormFilter() = mFilter
End Sub

------------------------------------------------------------------------------
------ Is this the code behind the AfterUpdate Events? That is what I did.
dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery


Nothing changed in my listbox. I am sure I did not do something right. Again
thank you for your help.

Gerald
Hi Gerald,

this example is for filtering a form, but the logic is the same as
building the WHERE clause of the SQL statement for the RowSource of your
listbox...

'~~~~~~~~~~~~~~~

Set Form Filter

It would be best to build a filter string for the form (as opposed to
imbedding a parameter in a query)

put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~

dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery

'~~~~~~~~~~~~~~~

me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you ;))

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
I have 3 text boxes that I want to be able to use for searching.
Click to expand...
[quoted text clipped - 62 lines]
TIA
Gerald
Click to expand...
Click to expand...
 
S

strive4peace

Hi Gerald,

I have answered your questions in-line

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I am having a time understanding your example. First, I am a very novice
Access programmer. I know just enough to be dangerous. :)

'~~~~~~~~~~~~~~~

Set Form Filter

It would be best to build a filter string for the form (as opposed to
imbedding a parameter in a query)
------------------------------------------------------------------------------
------------------
What do I need to put in my Listbox:
Control Source: blank now
RowSourceType:Table/Query now
RowSouce:Customer Table now
Click to expand...

click on the builder button (...) for the RowSource property of your
listbox and build the SQL -- use some criteria too. You will get
something like this:

SELECT field1, field2, field3
FROM Tablename
WHERE [ID] = 9;

copy this SQL statement so that you can construct it in your code
I see after running my test on the form property Filter I have [ID]='xxxxx'

I only tried one field so far.
Click to expand...

Do they have to go header?

no, they can be anywhere -- the example I gave you was for using unbound
combos for collecting information on how the user wants to filter the
form and I find it best to separate them from the real data so the user
does not get confused.
Assign this to the AfterUpdate event of each one...
into the header?
Click to expand...
=SetFormFilter() <-------------------------------------------------- Where
does this go?
Click to expand...

this goes DIRECTLY into the the property sheet, starting with an equal
sign, then the function name, followed by parenthesis

this is not right...
Private Sub srcID_AfterUpdate()
SetFormFilter() = mFilter
End Sub

'~~~~~~~~~~~~~~~
------------------------------------------------------------------------

Here is my code.

Dim mFilter As String
mFilter = ""

If Not IsNull(Me.srcID) Then
mFilter = "[ID]= '" _
& Me.srcID & "'"
End If

If Len(mFilter) > 0 Then
Me.Filter = mFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If

Me.Requery
End Sub
Click to expand...

Because you are building the RowSource for a listbox, you should change
the function name to reflect what you are doing ... something like this:

Private Function BuildListboxRowSource()
dim strSQL as string, mWhere as string

If Not IsNull(Me.SrcID) then
mWhere = "[ID] = " & me.SrcID
End if

strSQL = "SELECT field1, field2, field3 " _
& " FROM Tablename "

if Len(mWhere) > 0 then
strSQL = strSQL & " WHERE " & mWhere
end if

strSQL = strSQL & ";"

'take this next line out after everything works ok
Debug.Print strSQL

Me.Listbox_controlname.RowSource = strSQL
Me.Listbox_controlname.Requery

End Function

So, now, the AfterUpdate event of SrcID would be -->
=BuildListboxRowSource()

type it directly into the property sheet, where you currently have
[Event Procedure]


~~~~~~~~~~~ debug.print ~~~~~~~~~~~

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

Hopefully, this clears up some of your questions!

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
Nothing changed in my listbox. I am sure I did not do something right. Again
thank you for your help.

Gerald
Hi Gerald,

this example is for filtering a form, but the logic is the same as
building the WHERE clause of the SQL statement for the RowSource of your
listbox...

'~~~~~~~~~~~~~~~

Set Form Filter

It would be best to build a filter string for the form (as opposed to
imbedding a parameter in a query)

put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~

dim mFilter as string
mFilter = ""

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if

If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if

if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery

'~~~~~~~~~~~~~~~

me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you ;))

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
I have 3 text boxes that I want to be able to use for searching.
Click to expand...
[quoted text clipped - 62 lines]
TIA
Gerald
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

Hi Gerald,

I am assuming you are using your listbox to find records. Make the
first column of your listbox the autonumber ID that you are probably
using in your table as a primary key.

Use the ColumnWidths property to set the column widths for your listbox

ie,
ColumnWidths --> 0;1;1
(the first column will be hidden)
ListWidth --> 2
(this is the sum of the column widths. For Listboxes, I often add 0.1
because sometimes you will see a horizontal scrollbar if you do not)


Put code something like this behind your form:

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

If IsNull(Me.ActiveControl) Then Exit Function

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

Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~

Then, the AfterUpdate event of your listbox to find a record would be -->
=FindRecord()

and, just as before, this is typed directy in to the property sheet,
where you would see [Event Procedure]

I like to use generic functions like this so that they can be used
behind other forms with minimal changes. The only thing you will need
to substitute is your ID fieldname (autonumbers are long integers) for
IDfield


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
G

gjameson via AccessMonster.com

I have my query working , I think. :) But I am not seeing anything in the
listbox. Before or after I enter a number.
If I enter a number I can click on the listbox and it will open the record I
choose. But what I would like is that all the records be displayed and
narrowed down when I type in my search parameters. Also my ID is my primary
key, if that makes a difference.

Gerald
Hi Gerald,

I am assuming you are using your listbox to find records. Make the
first column of your listbox the autonumber ID that you are probably
using in your table as a primary key.

Use the ColumnWidths property to set the column widths for your listbox

ie,
ColumnWidths --> 0;1;1
(the first column will be hidden)
ListWidth --> 2
(this is the sum of the column widths. For Listboxes, I often add 0.1
because sometimes you will see a horizontal scrollbar if you do not)

Put code something like this behind your form:

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

If IsNull(Me.ActiveControl) Then Exit Function

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

Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~

Then, the AfterUpdate event of your listbox to find a record would be -->
=FindRecord()

and, just as before, this is typed directy in to the property sheet,
where you would see [Event Procedure]

I like to use generic functions like this so that they can be used
behind other forms with minimal changes. The only thing you will need
to substitute is your ID fieldname (autonumbers are long integers) for
IDfield

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Click to expand...
 
S

strive4peace

Hi Gerald,

Rather than using a query, perhaps it would be better to construct the
SQL for the listbox in your code

try something like this:

'~~~~~~~~~~~~~~~~
Private Function SetListboxSQL()

dim strSQL as string, mWhere as string
mWhere = ""

strSQL = "SELECT DISTINCTROW Customer.ID AS ID, " _
& " Customer.[Cust Name] AS Customer, " _
& " [StNbr] & ' ' & [St Name] AS Address, " _
& " Customer.MODEM_PHNE AS [Modem #], " _
& " Customer.IT_CUST AS IT, " _
& " Customer.EVC_BRAND AS EVC, " _
& " Customer.CHT_BRAND AS Chart, " _
& " Customer.[Mtr Size] AS [Mtr Size], " _
& " Customer.[Mtr #] AS [Mtr #], " _
& " Customer.[Prem #] AS [Prem Code] " _
& " FROM Customer "

if Not IsNull(Me.srcID) then

'is ID text?
'you cannot use wildcards with a number
'without converting to a string in code

mWhere = "ID Like '" & Me.srcID & "*'"
end if

if Not IsNull(Me.srcID) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[Cust Name] Like '" & Me.scrCustomer & "*'"
end if

if Not IsNull(Me.srcAddress) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[St Nbr] & ' ' & [St Name] " _
& " Like '" & Me.srcAddress& "*'"
end if

if len(mWhere) > 0 then
strSQL = strSQL & " WHERE " & mWhere
end if

strSQL = strSQL _
& " ORDER BY ID, [Cust Name];"

'remove next line after things work okay
debug.print strSQL

me.Listbox_controlname.rowsource = strSQL
me.Listbox_controlname.requery

End Function
'~~~~~~~~~~~~~~~~~

on the AfterUpdate event of each of your criteria controls -->
=SetListboxSQL()
(type this right into the property sheet, where you would see [Event
Procedure])
'~~~~~~~~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

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

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

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have my query working , I think. :) But I am not seeing anything in the
listbox. Before or after I enter a number.
If I enter a number I can click on the listbox and it will open the record I
choose. But what I would like is that all the records be displayed and
narrowed down when I type in my search parameters. Also my ID is my primary
key, if that makes a difference.

Gerald
Hi Gerald,

I am assuming you are using your listbox to find records. Make the
first column of your listbox the autonumber ID that you are probably
using in your table as a primary key.

Use the ColumnWidths property to set the column widths for your listbox

ie,
ColumnWidths --> 0;1;1
(the first column will be hidden)
ListWidth --> 2
(this is the sum of the column widths. For Listboxes, I often add 0.1
because sometimes you will see a horizontal scrollbar if you do not)

Put code something like this behind your form:

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

If IsNull(Me.ActiveControl) Then Exit Function

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

Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~

Then, the AfterUpdate event of your listbox to find a record would be -->
=FindRecord()

and, just as before, this is typed directy in to the property sheet,
where you would see [Event Procedure]

I like to use generic functions like this so that they can be used
behind other forms with minimal changes. The only thing you will need
to substitute is your ID fieldname (autonumbers are long integers) for
IDfield

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Click to expand...
Click to expand...
 
S

strive4peace

Hi Gerald,

Rather than using a query, perhaps it would be better to construct the
SQL for the listbox in your code

try something like this:

'~~~~~~~~~~~~~~~~
Private Function SetListboxSQL()

dim strSQL as string, mWhere as string
mWhere = ""

strSQL = "SELECT DISTINCTROW Customer.ID AS ID, " _
& " Customer.[Cust Name] AS Customer, " _
& " [StNbr] & ' ' & [St Name] AS Address, " _
& " Customer.MODEM_PHNE AS [Modem #], " _
& " Customer.IT_CUST AS IT, " _
& " Customer.EVC_BRAND AS EVC, " _
& " Customer.CHT_BRAND AS Chart, " _
& " Customer.[Mtr Size] AS [Mtr Size], " _
& " Customer.[Mtr #] AS [Mtr #], " _
& " Customer.[Prem #] AS [Prem Code] " _
& " FROM Customer "

if Not IsNull(Me.srcID) then

'is ID text?
'you cannot use wildcards with a number
'without converting to a string in code

mWhere = "ID Like '" & Me.srcID & "*'"
end if

if Not IsNull(Me.srcID) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[Cust Name] Like '" & Me.scrCustomer & "*'"
end if

if Not IsNull(Me.srcAddress) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[St Nbr] & ' ' & [St Name] " _
& " Like '" & Me.srcAddress& "*'"
end if

if len(mWhere) > 0 then
strSQL = strSQL & " WHERE " & mWhere
end if

strSQL = strSQL _
& " ORDER BY ID, [Cust Name];"

'remove next line after things work okay
debug.print strSQL

me.Listbox_controlname.rowsource = strSQL
me.Listbox_controlname.requery

End Function
'~~~~~~~~~~~~~~~~~

on the AfterUpdate event of each of your criteria controls -->
=SetListboxSQL()
(type this right into the property sheet, where you would see [Event
Procedure])
'~~~~~~~~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

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

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

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)



Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have my query working , I think. :) But I am not seeing anything in the
listbox. Before or after I enter a number.
If I enter a number I can click on the listbox and it will open the record I
choose. But what I would like is that all the records be displayed and
narrowed down when I type in my search parameters. Also my ID is my primary
key, if that makes a difference.

Gerald
Hi Gerald,

I am assuming you are using your listbox to find records. Make the
first column of your listbox the autonumber ID that you are probably
using in your table as a primary key.

Use the ColumnWidths property to set the column widths for your listbox

ie,
ColumnWidths --> 0;1;1
(the first column will be hidden)
ListWidth --> 2
(this is the sum of the column widths. For Listboxes, I often add 0.1
because sometimes you will see a horizontal scrollbar if you do not)

Put code something like this behind your form:

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

If IsNull(Me.ActiveControl) Then Exit Function

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

Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~

Then, the AfterUpdate event of your listbox to find a record would be -->
=FindRecord()

and, just as before, this is typed directy in to the property sheet,
where you would see [Event Procedure]

I like to use generic functions like this so that they can be used
behind other forms with minimal changes. The only thing you will need
to substitute is your ID fieldname (autonumbers are long integers) for
IDfield

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Click to expand...
Click to expand...
 
G

gjameson via AccessMonster.com

ID is numeric and my primary key. How do I convert this to a string?

Gerald
Hi Gerald,

Rather than using a query, perhaps it would be better to construct the
SQL for the listbox in your code

try something like this:

'~~~~~~~~~~~~~~~~
Private Function SetListboxSQL()

dim strSQL as string, mWhere as string
mWhere = ""

strSQL = "SELECT DISTINCTROW Customer.ID AS ID, " _
& " Customer.[Cust Name] AS Customer, " _
& " [StNbr] & ' ' & [St Name] AS Address, " _
& " Customer.MODEM_PHNE AS [Modem #], " _
& " Customer.IT_CUST AS IT, " _
& " Customer.EVC_BRAND AS EVC, " _
& " Customer.CHT_BRAND AS Chart, " _
& " Customer.[Mtr Size] AS [Mtr Size], " _
& " Customer.[Mtr #] AS [Mtr #], " _
& " Customer.[Prem #] AS [Prem Code] " _
& " FROM Customer "

if Not IsNull(Me.srcID) then

'is ID text?
'you cannot use wildcards with a number
'without converting to a string in code

mWhere = "ID Like '" & Me.srcID & "*'"
end if

if Not IsNull(Me.srcID) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[Cust Name] Like '" & Me.scrCustomer & "*'"
end if

if Not IsNull(Me.srcAddress) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[St Nbr] & ' ' & [St Name] " _
& " Like '" & Me.srcAddress& "*'"
end if

if len(mWhere) > 0 then
strSQL = strSQL & " WHERE " & mWhere
end if

strSQL = strSQL _
& " ORDER BY ID, [Cust Name];"

'remove next line after things work okay
debug.print strSQL

me.Listbox_controlname.rowsource = strSQL
me.Listbox_controlname.requery

End Function
'~~~~~~~~~~~~~~~~~

on the AfterUpdate event of each of your criteria controls -->
=SetListboxSQL()
(type this right into the property sheet, where you would see [Event
Procedure])
'~~~~~~~~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

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

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

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
I have my query working , I think. :) But I am not seeing anything in the
listbox. Before or after I enter a number.
Click to expand...
[quoted text clipped - 63 lines]
Click to expand...
 
S

strive4peace

Hi Gerald,

try this for your first where clause IF statement

if Not IsNull(Me.srcID) then
mWhere = "cStr(ID) Like '" & Me.srcID & "*'"
end if


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


ID is numeric and my primary key. How do I convert this to a string?

Gerald
Hi Gerald,

Rather than using a query, perhaps it would be better to construct the
SQL for the listbox in your code

try something like this:

'~~~~~~~~~~~~~~~~
Private Function SetListboxSQL()

dim strSQL as string, mWhere as string
mWhere = ""

strSQL = "SELECT DISTINCTROW Customer.ID AS ID, " _
& " Customer.[Cust Name] AS Customer, " _
& " [StNbr] & ' ' & [St Name] AS Address, " _
& " Customer.MODEM_PHNE AS [Modem #], " _
& " Customer.IT_CUST AS IT, " _
& " Customer.EVC_BRAND AS EVC, " _
& " Customer.CHT_BRAND AS Chart, " _
& " Customer.[Mtr Size] AS [Mtr Size], " _
& " Customer.[Mtr #] AS [Mtr #], " _
& " Customer.[Prem #] AS [Prem Code] " _
& " FROM Customer "

if Not IsNull(Me.srcID) then

'is ID text?
'you cannot use wildcards with a number
'without converting to a string in code

mWhere = "ID Like '" & Me.srcID & "*'"
end if

if Not IsNull(Me.srcID) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[Cust Name] Like '" & Me.scrCustomer & "*'"
end if

if Not IsNull(Me.srcAddress) then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = "[St Nbr] & ' ' & [St Name] " _
& " Like '" & Me.srcAddress& "*'"
end if

if len(mWhere) > 0 then
strSQL = strSQL & " WHERE " & mWhere
end if

strSQL = strSQL _
& " ORDER BY ID, [Cust Name];"

'remove next line after things work okay
debug.print strSQL

me.Listbox_controlname.rowsource = strSQL
me.Listbox_controlname.requery

End Function
'~~~~~~~~~~~~~~~~~

on the AfterUpdate event of each of your criteria controls -->
=SetListboxSQL()
(type this right into the property sheet, where you would see [Event
Procedure])
'~~~~~~~~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

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

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

from the menu in a module window: Debug, Compile
fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
I have my query working , I think. :) But I am not seeing anything in the
listbox. Before or after I enter a number.
Click to expand...
[quoted text clipped - 63 lines]
strive4peace2006 at yahoo.com
*
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Listbox Headers 2
Dcount without a specific query 2
how to stop search in the middle 2
Search Combo Box on Form 4
Access crashes with report preview command button 1
Passing Parameters 2
Change a report to a query 1
Combo Error "Text Not In List" 5

Top