Problems in Access (error 2185) creating custom form to build dynamic query.

[StarfishJoe]

I am working on a project for a client.
He has an Access 97 database, (about to be upgraded to Access 2000), that
needs enhancing.
One of the features he wants is a form of text boxes and parameters to sort
and filter on.
The form should build a query that can be stored or used to view the
filtered data with a form, or query, or report.
This parameter selection query should be a pop-up form with several controls
on it. Once parameters have been selected, a command button will begin the
magic.

I can build the query in text form in a text box based on the selections on
the form, but I am not sure how to execute it as a query in Access, but I
guess that is another issue for after I get over this one. I will have
several issues which may require several postings over time, but the first
one I am dealing with today is this:

The sort and filter form may look to the user like this.

1. Priority Level between [_____] and [_____] '// an integer value from
two comboboxes linked to the same table field.
2. Commercial or residential (O) (O) '//choose one of two grouped
option buttons
3. Date Range between [__/__/__] and [__/__/__]
4. (there are more options but lets just use this for now)

In order to test for syntax, I am building the query statement by
concatenating strings to it and displaying it in a text box on the form.
In its simplest form it is:
"SELECT * from [Project List]"
As the user selects the parameters this will grow with a where clause
"SELECT * from [Project List] WHERE .AND..ORDER BY.."

I did this exact same thing on another application for another user using a
pure VB6 form connected to an Access 2000 MDB database. On that
application, all forms were created in Visual Studio as a stand alone
application.

On THIS application, The user already has the database and a few forms
created in Access that would take too much time to reconstruct in Visual
Basic as a whole new program. I am having to work with what I have. But I
am getting bogged down with the basic differences in Access controls and VB
forms and controls:

When I click the command button to display the query with just one parameter
option ( Priority from _x_ to _y_ ),
In my VB from it works just fine. In the Access Form I get an Error Message
#2185 "You can't reference a property or method for a control unless the
control has the focus".

I guess that means that I have cant just write the code in the module, but I
have to cut it up and place into each "gotfocus" event, and I need to
"setfocus" on each control one at a time just get the values to build the
same query. And once I set focus on another control I can no longer
reference the previous control to make a decision or calculation??? Sounds
like a lot of got focus events and maybe copying the values into New
variables created in code on the modular level.

Questions:
1. Is there a better way? Is there an easier way (less coding) to get
around this?
2. Can I create a single POP-Up form in Visual Basic and call it from
another form created in Access, and then using the resulting query, display
on an Access Form or Datasheet view or report? I guess I am talking active
X control but that "sounds" over my level of skill.
3. Know of a "low cost/No cost" add in that already exists out there?

I wuz supposed to be dun. I am under the gun. I ain't havin' no fun.

Sincerely,
StarfishJoe

 

[Allen Browne]

Joe, the error message is because you are using the Text property of the
control. In pure VB, that's the normal approach. The default property in
Access is Value. Text applies only while the control has focus, and refers
to the characters in the control. They have not yet become the Value -- in
fact, they may not be able to. For example, if you are entering a date, and
have so far typed:
6/1/
then that's the Text of the control, but it could not become the Value. The
difference is because Access is data-centric.

Having sorted that out, run a web/newsgroup search for "Query by Form" to
hit topics like yours.

The basic thing is to build up a string from the values in the controls to
use in the WHERE clause of the SQL string. Once you have the SQL string, you
can assign it to the RecordSource of a form or report to display the
results, (or you could assign it to the SQL property of a QueryDef.)

The basic logic to concatenate the values from the non-null controls into
the WHERE clause runs like this. It adds an AND to the end of each one, so
you can easily add as many as you need and chop off the last one at the end:

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.PriorityMin) Then
strWhere = strWhere & "([PriorityLevel] >= " & Me.PriorityMin & ")
AND "
End If
If Not IsNull(Me.PriorityMax) Then
strWhere = strWhere & "([PriorityLevel] <= " & Me.PriorityMax & ")
AND "
End If

Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "([PropertyType] = 'Commercial') AND "
Case 2
strWhere = strWhere & "([PropertyType] = 'Residential') AND "
End Select

'etc

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = " WHERE " & Left(strWhere, lngLen)
End If

One further suggestion: these controls are typically unbound, so the user
can enter anything. For non-text values, set the Format property to General
Number or Short Date so Access knows the intended data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working on a project for a client.
He has an Access 97 database, (about to be upgraded to Access 2000), that
needs enhancing.
One of the features he wants is a form of text boxes and parameters to
sort
and filter on.
The form should build a query that can be stored or used to view the
filtered data with a form, or query, or report.
This parameter selection query should be a pop-up form with several
controls
on it. Once parameters have been selected, a command button will begin the
magic.

I can build the query in text form in a text box based on the selections
on
the form, but I am not sure how to execute it as a query in Access, but I
guess that is another issue for after I get over this one. I will have
several issues which may require several postings over time, but the first
one I am dealing with today is this:

The sort and filter form may look to the user like this.

1. Priority Level between [_____] and [_____] '// an integer value from
two comboboxes linked to the same table field.
2. Commercial or residential (O) (O) '//choose one of two grouped
option buttons
3. Date Range between [__/__/__] and [__/__/__]
4. (there are more options but lets just use this for now)

In order to test for syntax, I am building the query statement by
concatenating strings to it and displaying it in a text box on the form.
In its simplest form it is:
"SELECT * from [Project List]"
As the user selects the parameters this will grow with a where clause
"SELECT * from [Project List] WHERE .AND..ORDER BY.."

I did this exact same thing on another application for another user using
a
pure VB6 form connected to an Access 2000 MDB database. On that
application, all forms were created in Visual Studio as a stand alone
application.

On THIS application, The user already has the database and a few forms
created in Access that would take too much time to reconstruct in Visual
Basic as a whole new program. I am having to work with what I have. But I
am getting bogged down with the basic differences in Access controls and
VB
forms and controls:

When I click the command button to display the query with just one
parameter
option ( Priority from _x_ to _y_ ),
In my VB from it works just fine. In the Access Form I get an Error
Message
#2185 "You can't reference a property or method for a control unless the
control has the focus".

I guess that means that I have cant just write the code in the module, but
I
have to cut it up and place into each "gotfocus" event, and I need to
"setfocus" on each control one at a time just get the values to build the
same query. And once I set focus on another control I can no longer
reference the previous control to make a decision or calculation???
Sounds
like a lot of got focus events and maybe copying the values into New
variables created in code on the modular level.

Questions:
1. Is there a better way? Is there an easier way (less coding) to get
around this?
2. Can I create a single POP-Up form in Visual Basic and call it from
another form created in Access, and then using the resulting query,
display
on an Access Form or Datasheet view or report? I guess I am talking
active
X control but that "sounds" over my level of skill.
3. Know of a "low cost/No cost" add in that already exists out there?

I wuz supposed to be dun. I am under the gun. I ain't havin' no fun.

Sincerely,
StarfishJoe

 

[StarfishJoe]

Thank you for your reply. I appreciate the insight on value vs. text. One
my controls is a checkbox which stores a value. I am using "value" but I
still have this error. the program halts on this line. When I change it to
using a variable to hold that value passed to it.
Dim intCkPriority
ckPriority.SetFocus
intCkPriority = ckPriority.value
Then the program executes this code and halts on the next errror. I could
add all these lines of code to make this work but I would have to set focus
on each and every control in code just to copy the text or value into the
new variable. This will bloat my module 3 times larger than a vb module. I
was hoping there was a simpler way to get the control values. Also, some of
these controls are disabled by design unless and until a user checks another
checkbox next to it.I need the values in these boxes too, but I would have
to enable them before I set focus, and then disable them on LostFocus. I
could do it, but that is not saving me anything.

Is it possible just to call a Pure VB6 form from an Access created form,
build the query and then pass the result back to the Access form? The VB6
form would (should be a pop-up form just like an unputbox or messagebox, and
closed, shifting focus back to original form or another access form or
report. I can create this form and code faster in pure VB faster than
debugging this access form.

I queried for "query by form" but so far, have not found what I am looking
for.

StarfishJoe

Joe, the error message is because you are using the Text property of the
control. In pure VB, that's the normal approach. The default property in
Access is Value. Text applies only while the control has focus, and refers
to the characters in the control. They have not yet become the Value -- in
fact, they may not be able to. For example, if you are entering a date, and
have so far typed:
6/1/
then that's the Text of the control, but it could not become the Value. The
difference is because Access is data-centric.

Having sorted that out, run a web/newsgroup search for "Query by Form" to
hit topics like yours.

The basic thing is to build up a string from the values in the controls to
use in the WHERE clause of the SQL string. Once you have the SQL string, you
can assign it to the RecordSource of a form or report to display the
results, (or you could assign it to the SQL property of a QueryDef.)

The basic logic to concatenate the values from the non-null controls into
the WHERE clause runs like this. It adds an AND to the end of each one, so
you can easily add as many as you need and chop off the last one at the end:

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.PriorityMin) Then
strWhere = strWhere & "([PriorityLevel] >= " & Me.PriorityMin & ")
AND "
End If
If Not IsNull(Me.PriorityMax) Then
strWhere = strWhere & "([PriorityLevel] <= " & Me.PriorityMax & ")
AND "
End If

Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "([PropertyType] = 'Commercial') AND "
Case 2
strWhere = strWhere & "([PropertyType] = 'Residential') AND "
End Select

'etc

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = " WHERE " & Left(strWhere, lngLen)
End If

One further suggestion: these controls are typically unbound, so the user
can enter anything. For non-text values, set the Format property to General
Number or Short Date so Access knows the intended data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working on a project for a client.
He has an Access 97 database, (about to be upgraded to Access 2000), that
needs enhancing.
One of the features he wants is a form of text boxes and parameters to
sort
and filter on.
The form should build a query that can be stored or used to view the
filtered data with a form, or query, or report.
This parameter selection query should be a pop-up form with several
controls
on it. Once parameters have been selected, a command button will begin the
magic.

I can build the query in text form in a text box based on the selections
on
the form, but I am not sure how to execute it as a query in Access, but I
guess that is another issue for after I get over this one. I will have
several issues which may require several postings over time, but the first
one I am dealing with today is this:

The sort and filter form may look to the user like this.

1. Priority Level between [_____] and [_____] '// an integer value from
two comboboxes linked to the same table field.
2. Commercial or residential (O) (O) '//choose one of two grouped
option buttons
3. Date Range between [__/__/__] and [__/__/__]
4. (there are more options but lets just use this for now)

In order to test for syntax, I am building the query statement by
concatenating strings to it and displaying it in a text box on the form.
In its simplest form it is:
"SELECT * from [Project List]"
As the user selects the parameters this will grow with a where clause
"SELECT * from [Project List] WHERE .AND..ORDER BY.."

I did this exact same thing on another application for another user using
a
pure VB6 form connected to an Access 2000 MDB database. On that
application, all forms were created in Visual Studio as a stand alone
application.

On THIS application, The user already has the database and a few forms
created in Access that would take too much time to reconstruct in Visual
Basic as a whole new program. I am having to work with what I have. But I
am getting bogged down with the basic differences in Access controls and
VB
forms and controls:

When I click the command button to display the query with just one
parameter
option ( Priority from _x_ to _y_ ),
In my VB from it works just fine. In the Access Form I get an Error
Message
#2185 "You can't reference a property or method for a control unless the
control has the focus".

I guess that means that I have cant just write the code in the module, but
I
have to cut it up and place into each "gotfocus" event, and I need to
"setfocus" on each control one at a time just get the values to build the
same query. And once I set focus on another control I can no longer
reference the previous control to make a decision or calculation???
Sounds
like a lot of got focus events and maybe copying the values into New
variables created in code on the modular level.

Questions:
1. Is there a better way? Is there an easier way (less coding) to get
around this?
2. Can I create a single POP-Up form in Visual Basic and call it from
another form created in Access, and then using the resulting query,
display
on an Access Form or Datasheet view or report? I guess I am talking
active
X control but that "sounds" over my level of skill.
3. Know of a "low cost/No cost" add in that already exists out there?

I wuz supposed to be dun. I am under the gun. I ain't havin' no fun.

Sincerely,
StarfishJoe

 

[Allen Browne]

The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form.

Try:
intCkPriority = Me.ckPriority.value
or if ckPriority is a field in the form's Recordsource but is not
represented by a control on the form, try:
intCkPriority = Me!ckPriority.value

If you had declared:
Dim intCkPriority As Integer
then it is possible your line could fail with an invaid use of null error
(94, from memory). Although the Yes/No field cannot store a Null in Access
(JET), the check box could be null if unbound, at a new record, or bound to
a Number type field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your reply. I appreciate the insight on value vs. text.
One
my controls is a checkbox which stores a value. I am using "value" but I
still have this error. the program halts on this line. When I change it
to
using a variable to hold that value passed to it.
Dim intCkPriority
ckPriority.SetFocus
intCkPriority = ckPriority.value
Then the program executes this code and halts on the next errror. I could
add all these lines of code to make this work but I would have to set
focus
on each and every control in code just to copy the text or value into the
new variable. This will bloat my module 3 times larger than a vb module.
I
was hoping there was a simpler way to get the control values. Also, some
of
these controls are disabled by design unless and until a user checks
another
checkbox next to it.I need the values in these boxes too, but I would have
to enable them before I set focus, and then disable them on LostFocus. I
could do it, but that is not saving me anything.

Is it possible just to call a Pure VB6 form from an Access created form,
build the query and then pass the result back to the Access form? The VB6
form would (should be a pop-up form just like an unputbox or messagebox,
and
closed, shifting focus back to original form or another access form or
report. I can create this form and code faster in pure VB faster than
debugging this access form.

I queried for "query by form" but so far, have not found what I am
looking
for.

StarfishJoe

Joe, the error message is because you are using the Text property of the
control. In pure VB, that's the normal approach. The default property in
Access is Value. Text applies only while the control has focus, and
refers
to the characters in the control. They have not yet become the Value --
in
fact, they may not be able to. For example, if you are entering a date, and
have so far typed:
6/1/
then that's the Text of the control, but it could not become the Value. The
difference is because Access is data-centric.

Having sorted that out, run a web/newsgroup search for "Query by Form" to
hit topics like yours.

The basic thing is to build up a string from the values in the controls
to
use in the WHERE clause of the SQL string. Once you have the SQL string, you
can assign it to the RecordSource of a form or report to display the
results, (or you could assign it to the SQL property of a QueryDef.)

The basic logic to concatenate the values from the non-null controls into
the WHERE clause runs like this. It adds an AND to the end of each one,
so
you can easily add as many as you need and chop off the last one at the end:

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.PriorityMin) Then
strWhere = strWhere & "([PriorityLevel] >= " & Me.PriorityMin &
")
AND "
End If
If Not IsNull(Me.PriorityMax) Then
strWhere = strWhere & "([PriorityLevel] <= " & Me.PriorityMax &
")
AND "
End If

Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "([PropertyType] = 'Commercial') AND "
Case 2
strWhere = strWhere & "([PropertyType] = 'Residential') AND "
End Select

'etc

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = " WHERE " & Left(strWhere, lngLen)
End If

One further suggestion: these controls are typically unbound, so the user
can enter anything. For non-text values, set the Format property to General
Number or Short Date so Access knows the intended data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working on a project for a client.
He has an Access 97 database, (about to be upgraded to Access 2000), that
needs enhancing.
One of the features he wants is a form of text boxes and parameters to
sort
and filter on.
The form should build a query that can be stored or used to view the
filtered data with a form, or query, or report.
This parameter selection query should be a pop-up form with several
controls
on it. Once parameters have been selected, a command button will begin the
magic.

I can build the query in text form in a text box based on the selections
on
the form, but I am not sure how to execute it as a query in Access, but I
guess that is another issue for after I get over this one. I will have
several issues which may require several postings over time, but the first
one I am dealing with today is this:

The sort and filter form may look to the user like this.

1. Priority Level between [_____] and [_____] '// an integer value from
two comboboxes linked to the same table field.
2. Commercial or residential (O) (O) '//choose one of two grouped
option buttons
3. Date Range between [__/__/__] and [__/__/__]
4. (there are more options but lets just use this for now)

In order to test for syntax, I am building the query statement by
concatenating strings to it and displaying it in a text box on the
form.
In its simplest form it is:
"SELECT * from [Project List]"
As the user selects the parameters this will grow with a where clause
"SELECT * from [Project List] WHERE .AND..ORDER BY.."

I did this exact same thing on another application for another user using
a
pure VB6 form connected to an Access 2000 MDB database. On that
application, all forms were created in Visual Studio as a stand alone
application.

On THIS application, The user already has the database and a few forms
created in Access that would take too much time to reconstruct in
Visual
Basic as a whole new program. I am having to work with what I have.
But I
am getting bogged down with the basic differences in Access controls
and
VB
forms and controls:

When I click the command button to display the query with just one
parameter
option ( Priority from _x_ to _y_ ),
In my VB from it works just fine. In the Access Form I get an Error
Message
#2185 "You can't reference a property or method for a control unless
the
control has the focus".

I guess that means that I have cant just write the code in the module, but
I
have to cut it up and place into each "gotfocus" event, and I need to
"setfocus" on each control one at a time just get the values to build the
same query. And once I set focus on another control I can no longer
reference the previous control to make a decision or calculation???
Sounds
like a lot of got focus events and maybe copying the values into New
variables created in code on the modular level.

Questions:
1. Is there a better way? Is there an easier way (less coding) to get
around this?
2. Can I create a single POP-Up form in Visual Basic and call it from
another form created in Access, and then using the resulting query,
display
on an Access Form or Datasheet view or report? I guess I am talking
active
X control but that "sounds" over my level of skill.
3. Know of a "low cost/No cost" add in that already exists out there?

I wuz supposed to be dun. I am under the gun. I ain't havin' no fun.

Sincerely,
StarfishJoe

 

[StarfishJoe]

(I sent that first reply to your post prematurely. I was going to add more
to it.)

You are right about the first one.
The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form.

Yes that one does work, and that is all that I have managed to get to work.
I am avoiding the use of Nulls and setting default values where I can.
Some of my fields will be text boxes and I have discovered the .value
property of those, and two of my fields will be multiple choice
listboxes(which I have not
addressed yet.)

It just seems that I have to explicitely write to "SetFocus" to each
control, and copy the value of each one into a separate Module level
variable before I can work with them. and before I do that I have to enable
all fields, and then disable them again. If there is a more efficient way
to do THAT I am all ears.

The next thing I am runing into, even if the above setfucus on each control
were not an issue, is that I want to programmatically populate the textbox
to display the SQLQuery string I am building. On the command button click
event:
txtSQLWindow.text=strSQLBuild returns errror #2185
txtSQLWindow.value=strSQLBuild returns error#2448

Error #2448 "You can't assign a value to this Object" "The object may
be a control on a read only form." ( No, form is not read only.) "The
Object may be on a form that is open in design view" ( No) "The value may
be too large for this field.(The test sample I did was the simple SQL
string: The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form. "Select *
from ProjectList" Just how can you tell the size of an unbound textbox
control on an Access form?

This is starting to make me want to go flip hamburgers
StarfishJoe

The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form.

Try:
intCkPriority = Me.ckPriority.value
or if ckPriority is a field in the form's Recordsource but is not
represented by a control on the form, try:
intCkPriority = Me!ckPriority.value

If you had declared:
Dim intCkPriority As Integer
then it is possible your line could fail with an invaid use of null error
(94, from memory). Although the Yes/No field cannot store a Null in Access
(JET), the check box could be null if unbound, at a new record, or bound to
a Number type field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your reply. I appreciate the insight on value vs. text.
One
my controls is a checkbox which stores a value. I am using "value" but I
still have this error. the program halts on this line. When I change it
to
using a variable to hold that value passed to it.
Dim intCkPriority
ckPriority.SetFocus
intCkPriority = ckPriority.value
Then the program executes this code and halts on the next errror. I could
add all these lines of code to make this work but I would have to set
focus
on each and every control in code just to copy the text or value into the
new variable. This will bloat my module 3 times larger than a vb module.
I
was hoping there was a simpler way to get the control values. Also, some
of
these controls are disabled by design unless and until a user checks
another
checkbox next to it.I need the values in these boxes too, but I would have
to enable them before I set focus, and then disable them on LostFocus. I
could do it, but that is not saving me anything.

Is it possible just to call a Pure VB6 form from an Access created form,
build the query and then pass the result back to the Access form? The VB6
form would (should be a pop-up form just like an unputbox or messagebox,
and
closed, shifting focus back to original form or another access form or
report. I can create this form and code faster in pure VB faster than
debugging this access form.

I queried for "query by form" but so far, have not found what I am
looking
for.

StarfishJoe

Joe, the error message is because you are using the Text property of the
control. In pure VB, that's the normal approach. The default property in
Access is Value. Text applies only while the control has focus, and
refers
to the characters in the control. They have not yet become the Value --
in
fact, they may not be able to. For example, if you are entering a date, and
have so far typed:
6/1/
then that's the Text of the control, but it could not become the Value. The
difference is because Access is data-centric.

Having sorted that out, run a web/newsgroup search for "Query by Form" to
hit topics like yours.

The basic thing is to build up a string from the values in the controls
to
use in the WHERE clause of the SQL string. Once you have the SQL

string,
you

can assign it to the RecordSource of a form or report to display the
results, (or you could assign it to the SQL property of a QueryDef.)

The basic logic to concatenate the values from the non-null controls into
the WHERE clause runs like this. It adds an AND to the end of each one,
so
you can easily add as many as you need and chop off the last one at the end:

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.PriorityMin) Then
strWhere = strWhere & "([PriorityLevel] >= " & Me.PriorityMin &
")
AND "
End If
If Not IsNull(Me.PriorityMax) Then
strWhere = strWhere & "([PriorityLevel] <= " & Me.PriorityMax &
")
AND "
End If

Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "([PropertyType] = 'Commercial') AND "
Case 2
strWhere = strWhere & "([PropertyType] = 'Residential') AND "
End Select

'etc

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = " WHERE " & Left(strWhere, lngLen)
End If

One further suggestion: these controls are typically unbound, so the user
can enter anything. For non-text values, set the Format property to General
Number or Short Date so Access knows the intended data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working on a project for a client.
He has an Access 97 database, (about to be upgraded to Access 2000), that
needs enhancing.
One of the features he wants is a form of text boxes and parameters to
sort
and filter on.
The form should build a query that can be stored or used to view the
filtered data with a form, or query, or report.
This parameter selection query should be a pop-up form with several
controls
on it. Once parameters have been selected, a command button will

begin
the

magic.

I can build the query in text form in a text box based on the selections
on
the form, but I am not sure how to execute it as a query in Access,

but
I

guess that is another issue for after I get over this one. I will have
several issues which may require several postings over time, but the first
one I am dealing with today is this:

The sort and filter form may look to the user like this.

1. Priority Level between [_____] and [_____] '// an integer value from
two comboboxes linked to the same table field.
2. Commercial or residential (O) (O) '//choose one of two grouped
option buttons
3. Date Range between [__/__/__] and [__/__/__]
4. (there are more options but lets just use this for now)

In order to test for syntax, I am building the query statement by
concatenating strings to it and displaying it in a text box on the
form.
In its simplest form it is:
"SELECT * from [Project List]"
As the user selects the parameters this will grow with a where clause
"SELECT * from [Project List] WHERE .AND..ORDER BY.."

I did this exact same thing on another application for another user using
a
pure VB6 form connected to an Access 2000 MDB database. On that
application, all forms were created in Visual Studio as a stand alone
application.

On THIS application, The user already has the database and a few forms
created in Access that would take too much time to reconstruct in
Visual
Basic as a whole new program. I am having to work with what I have.
But I
am getting bogged down with the basic differences in Access controls
and
VB
forms and controls:

When I click the command button to display the query with just one
parameter
option ( Priority from _x_ to _y_ ),
In my VB from it works just fine. In the Access Form I get an Error
Message
#2185 "You can't reference a property or method for a control unless
the
control has the focus".

I guess that means that I have cant just write the code in the

module,
but

I
have to cut it up and place into each "gotfocus" event, and I need to
"setfocus" on each control one at a time just get the values to build the
same query. And once I set focus on another control I can no longer
reference the previous control to make a decision or calculation???
Sounds
like a lot of got focus events and maybe copying the values into New
variables created in code on the modular level.

Questions:
1. Is there a better way? Is there an easier way (less coding) to get
around this?
2. Can I create a single POP-Up form in Visual Basic and call it from
another form created in Access, and then using the resulting query,
display
on an Access Form or Datasheet view or report? I guess I am talking
active
X control but that "sounds" over my level of skill.
3. Know of a "low cost/No cost" add in that already exists out there?

I wuz supposed to be dun. I am under the gun. I ain't havin' no fun.

Sincerely,
StarfishJoe

 

[Allen Browne]

Joe, I've no idea what you are doing, but you are making this much harder
than it needs to be.

First, verify you are working in the module of the form.

If so, type:
Me.
and the Intellisense will supply you a list of valid choices, including the
names of the controls on the form. Choose the name, and you can assign or
read the value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

(I sent that first reply to your post prematurely. I was going to add
more
to it.)

You are right about the first one.
The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form.

Yes that one does work, and that is all that I have managed to get to
work.
I am avoiding the use of Nulls and setting default values where I can.
Some of my fields will be text boxes and I have discovered the .value
property of those, and two of my fields will be multiple choice
listboxes(which I have not
addressed yet.)

It just seems that I have to explicitely write to "SetFocus" to each
control, and copy the value of each one into a separate Module level
variable before I can work with them. and before I do that I have to
enable
all fields, and then disable them again. If there is a more efficient way
to do THAT I am all ears.

The next thing I am runing into, even if the above setfucus on each
control
were not an issue, is that I want to programmatically populate the textbox
to display the SQLQuery string I am building. On the command button click
event:
txtSQLWindow.text=strSQLBuild returns errror #2185
txtSQLWindow.value=strSQLBuild returns error#2448

Error #2448 "You can't assign a value to this Object" "The object may
be a control on a read only form." ( No, form is not read only.) "The
Object may be on a form that is open in design view" ( No) "The value may
be too large for this field.(The test sample I did was the simple SQL
string: The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form. "Select
*
from ProjectList" Just how can you tell the size of an unbound textbox
control on an Access form?

This is starting to make me want to go flip hamburgers
StarfishJoe

The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form.

Try:
intCkPriority = Me.ckPriority.value
or if ckPriority is a field in the form's Recordsource but is not
represented by a control on the form, try:
intCkPriority = Me!ckPriority.value

If you had declared:
Dim intCkPriority As Integer
then it is possible your line could fail with an invaid use of null error
(94, from memory). Although the Yes/No field cannot store a Null in
Access
(JET), the check box could be null if unbound, at a new record, or bound to
a Number type field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your reply. I appreciate the insight on value vs. text.
One
my controls is a checkbox which stores a value. I am using "value" but I
still have this error. the program halts on this line. When I change it
to
using a variable to hold that value passed to it.
Dim intCkPriority
ckPriority.SetFocus
intCkPriority = ckPriority.value
Then the program executes this code and halts on the next errror. I could
add all these lines of code to make this work but I would have to set
focus
on each and every control in code just to copy the text or value into the
new variable. This will bloat my module 3 times larger than a vb module.
I
was hoping there was a simpler way to get the control values. Also, some
of
these controls are disabled by design unless and until a user checks
another
checkbox next to it.I need the values in these boxes too, but I would have
to enable them before I set focus, and then disable them on LostFocus. I
could do it, but that is not saving me anything.

Is it possible just to call a Pure VB6 form from an Access created
form,
build the query and then pass the result back to the Access form? The VB6
form would (should be a pop-up form just like an unputbox or
messagebox,
and
closed, shifting focus back to original form or another access form or
report. I can create this form and code faster in pure VB faster than
debugging this access form.

I queried for "query by form" but so far, have not found what I am
looking
for.

StarfishJoe
Joe, the error message is because you are using the Text property of the
control. In pure VB, that's the normal approach. The default property in
Access is Value. Text applies only while the control has focus, and
refers
to the characters in the control. They have not yet become the
Value --
in
fact, they may not be able to. For example, if you are entering a
date,
and
have so far typed:
6/1/
then that's the Text of the control, but it could not become the
Value.
The
difference is because Access is data-centric.

Having sorted that out, run a web/newsgroup search for "Query by Form" to
hit topics like yours.

The basic thing is to build up a string from the values in the
controls
to
use in the WHERE clause of the SQL string. Once you have the SQL string,
you
can assign it to the RecordSource of a form or report to display the
results, (or you could assign it to the SQL property of a QueryDef.)

The basic logic to concatenate the values from the non-null controls into
the WHERE clause runs like this. It adds an AND to the end of each
one,
so
you can easily add as many as you need and chop off the last one at
the
end:

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.PriorityMin) Then
strWhere = strWhere & "([PriorityLevel] >= " & Me.PriorityMin
&
")
AND "
End If
If Not IsNull(Me.PriorityMax) Then
strWhere = strWhere & "([PriorityLevel] <= " & Me.PriorityMax
&
")
AND "
End If

Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "([PropertyType] = 'Commercial') AND "
Case 2
strWhere = strWhere & "([PropertyType] = 'Residential') AND "
End Select

'etc

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = " WHERE " & Left(strWhere, lngLen)
End If

One further suggestion: these controls are typically unbound, so the user
can enter anything. For non-text values, set the Format property to
General
Number or Short Date so Access knows the intended data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working on a project for a client.
He has an Access 97 database, (about to be upgraded to Access 2000),
that
needs enhancing.
One of the features he wants is a form of text boxes and parameters to
sort
and filter on.
The form should build a query that can be stored or used to view the
filtered data with a form, or query, or report.
This parameter selection query should be a pop-up form with several
controls
on it. Once parameters have been selected, a command button will begin
the
magic.

I can build the query in text form in a text box based on the
selections
on
the form, but I am not sure how to execute it as a query in Access, but
I
guess that is another issue for after I get over this one. I will have
several issues which may require several postings over time, but the
first
one I am dealing with today is this:

The sort and filter form may look to the user like this.

1. Priority Level between [_____] and [_____] '// an integer
value
from
two comboboxes linked to the same table field.
2. Commercial or residential (O) (O) '//choose one of two
grouped
option buttons
3. Date Range between [__/__/__] and [__/__/__]
4. (there are more options but lets just use this for now)

In order to test for syntax, I am building the query statement by
concatenating strings to it and displaying it in a text box on the
form.
In its simplest form it is:
"SELECT * from [Project List]"
As the user selects the parameters this will grow with a where
clause
"SELECT * from [Project List] WHERE .AND..ORDER BY.."

I did this exact same thing on another application for another user
using
a
pure VB6 form connected to an Access 2000 MDB database. On that
application, all forms were created in Visual Studio as a stand
alone
application.

On THIS application, The user already has the database and a few forms
created in Access that would take too much time to reconstruct in
Visual
Basic as a whole new program. I am having to work with what I have.
But
I
am getting bogged down with the basic differences in Access controls
and
VB
forms and controls:

When I click the command button to display the query with just one
parameter
option ( Priority from _x_ to _y_ ),
In my VB from it works just fine. In the Access Form I get an Error
Message
#2185 "You can't reference a property or method for a control unless
the
control has the focus".

I guess that means that I have cant just write the code in the module,
but
I
have to cut it up and place into each "gotfocus" event, and I need
to
"setfocus" on each control one at a time just get the values to
build
the
same query. And once I set focus on another control I can no longer
reference the previous control to make a decision or calculation???
Sounds
like a lot of got focus events and maybe copying the values into New
variables created in code on the modular level.

Questions:
1. Is there a better way? Is there an easier way (less coding) to get
around this?
2. Can I create a single POP-Up form in Visual Basic and call it
from
another form created in Access, and then using the resulting query,
display
on an Access Form or Datasheet view or report? I guess I am talking
active
X control but that "sounds" over my level of skill.
3. Know of a "low cost/No cost" add in that already exists out there?

I wuz supposed to be dun. I am under the gun. I ain't havin' no fun.

Sincerely,
StarfishJoe

 

[StarfishJoe]

Yes I am working with the Module of a form. (Form_SortFilterForm:Class
Module)
Me. Produces a list of valid choices. All of my choices have been valid
and are in this list.

The Big Picture:
I am trying to create a pop-up form that contains a list of controls (
Parameters) the user can choose; a single criterea from each field or a
multiple selection or range of choices ( such as between (earlier date) and
(later date) from the same date field.
Based on the criteria the user selects, a query is to be built in code that
queries the Main Access Table and returns the fitered results to a form,
view or in datasheet view.
Basically the user (my client) could use the built in Access Query builder
to do this, but he doesn't know enough to do this and is not willing or does
not have the time to learn.
I created a similar pop-up form in another application using pure VB6 that
does exactly what I want to do in this Access project using the vb code in
Access. My Pure VB form built ( concatenated added to) a string value that
became the SQLstring. I then executed that sql string and got what I
wanted. In order to test the string to make sure there were no syntax
errors, I had it display in a label or textbox on the VB form. (This Test
window is what I am trying to do now--Unsuccessfully in Access but was
successful in VB6.

I thought I could write the code pretty much the same way in access, but
Access requires steps or returns errors where VB did not.

[Main Data Form] ---> [pop-up query/filter criteriaform] ---> {Query
results} ---> Display on [Main Data Form].

If user then wants to change the criteria and select different parameters,
He calls the pop-up and requeries with new parameters.

I am just trying to give the user a form to look at and select from instead
him looking at the built in Query designer.

Does this clarify what I am doing?

StarfishJoe
----------------------------------------------------------------------------
---------------------

Joe, I've no idea what you are doing, but you are making this much harder
than it needs to be.

First, verify you are working in the module of the form.

If so, type:
Me.
and the Intellisense will supply you a list of valid choices, including the
names of the controls on the form. Choose the name, and you can assign or
read the value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

(I sent that first reply to your post prematurely. I was going to add
more
to it.)

You are right about the first one.
The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form.

Yes that one does work, and that is all that I have managed to get to
work.
I am avoiding the use of Nulls and setting default values where I can.
Some of my fields will be text boxes and I have discovered the .value
property of those, and two of my fields will be multiple choice
listboxes(which I have not
addressed yet.)

It just seems that I have to explicitely write to "SetFocus" to each
control, and copy the value of each one into a separate Module level
variable before I can work with them. and before I do that I have to
enable
all fields, and then disable them again. If there is a more efficient way
to do THAT I am all ears.

The next thing I am runing into, even if the above setfucus on each
control
were not an issue, is that I want to programmatically populate the textbox
to display the SQLQuery string I am building. On the command button click
event:
txtSQLWindow.text=strSQLBuild returns errror #2185
txtSQLWindow.value=strSQLBuild returns error#2448

Error #2448 "You can't assign a value to this Object" "The object may
be a control on a read only form." ( No, form is not read only.) "The
Object may be on a form that is open in design view" ( No) "The value may
be too large for this field.(The test sample I did was the simple SQL
string: The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form. "Select
*
from ProjectList" Just how can you tell the size of an unbound textbox
control on an Access form?

This is starting to make me want to go flip hamburgers
StarfishJoe

The line:
intCkPriority = ckPriority.value
should work if ckPriority is the name of a check box on the form.

Try:
intCkPriority = Me.ckPriority.value
or if ckPriority is a field in the form's Recordsource but is not
represented by a control on the form, try:
intCkPriority = Me!ckPriority.value

If you had declared:
Dim intCkPriority As Integer
then it is possible your line could fail with an invaid use of null error
(94, from memory). Although the Yes/No field cannot store a Null in
Access
(JET), the check box could be null if unbound, at a new record, or

bound
to

a Number type field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your reply. I appreciate the insight on value vs. text.
One
my controls is a checkbox which stores a value. I am using "value"

but
I

still have this error. the program halts on this line. When I

change
it

to
using a variable to hold that value passed to it.
Dim intCkPriority
ckPriority.SetFocus
intCkPriority = ckPriority.value
Then the program executes this code and halts on the next errror. I could
add all these lines of code to make this work but I would have to set
focus
on each and every control in code just to copy the text or value into the
new variable. This will bloat my module 3 times larger than a vb module.
I
was hoping there was a simpler way to get the control values. Also, some
of
these controls are disabled by design unless and until a user checks
another
checkbox next to it.I need the values in these boxes too, but I would have
to enable them before I set focus, and then disable them on

LostFocus.
I

could do it, but that is not saving me anything.

Is it possible just to call a Pure VB6 form from an Access created
form,
build the query and then pass the result back to the Access form?

The
VB6

form would (should be a pop-up form just like an unputbox or
messagebox,
and
closed, shifting focus back to original form or another access form or
report. I can create this form and code faster in pure VB faster than
debugging this access form.

I queried for "query by form" but so far, have not found what I am
looking
for.

StarfishJoe
Joe, the error message is because you are using the Text property of the
control. In pure VB, that's the normal approach. The default

property
in

Access is Value. Text applies only while the control has focus, and
refers
to the characters in the control. They have not yet become the
Value --
in
fact, they may not be able to. For example, if you are entering a
date,
and
have so far typed:
6/1/
then that's the Text of the control, but it could not become the
Value.
The
difference is because Access is data-centric.

Having sorted that out, run a web/newsgroup search for "Query by

Form"
to

hit topics like yours.

The basic thing is to build up a string from the values in the
controls
to
use in the WHERE clause of the SQL string. Once you have the SQL string,
you
can assign it to the RecordSource of a form or report to display the
results, (or you could assign it to the SQL property of a QueryDef.)

The basic logic to concatenate the values from the non-null controls into
the WHERE clause runs like this. It adds an AND to the end of each
one,
so
you can easily add as many as you need and chop off the last one at
the
end:

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.PriorityMin) Then
strWhere = strWhere & "([PriorityLevel] >= " & Me.PriorityMin
&
")
AND "
End If
If Not IsNull(Me.PriorityMax) Then
strWhere = strWhere & "([PriorityLevel] <= " & Me.PriorityMax
&
")
AND "
End If

Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "([PropertyType] = 'Commercial') AND "
Case 2
strWhere = strWhere & "([PropertyType] = 'Residential') AND "
End Select

'etc

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = " WHERE " & Left(strWhere, lngLen)
End If

One further suggestion: these controls are typically unbound, so the user
can enter anything. For non-text values, set the Format property to
General
Number or Short Date so Access knows the intended data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am working on a project for a client.
He has an Access 97 database, (about to be upgraded to Access 2000),
that
needs enhancing.
One of the features he wants is a form of text boxes and

parameters
to

sort
and filter on.
The form should build a query that can be stored or used to view the
filtered data with a form, or query, or report.
This parameter selection query should be a pop-up form with several
controls
on it. Once parameters have been selected, a command button will begin
the
magic.

I can build the query in text form in a text box based on the
selections
on
the form, but I am not sure how to execute it as a query in

Access,
but

I
guess that is another issue for after I get over this one. I will have
several issues which may require several postings over time, but the
first
one I am dealing with today is this:

The sort and filter form may look to the user like this.

1. Priority Level between [_____] and [_____] '// an integer
value
from
two comboboxes linked to the same table field.
2. Commercial or residential (O) (O) '//choose one of two
grouped
option buttons
3. Date Range between [__/__/__] and [__/__/__]
4. (there are more options but lets just use this for now)

In order to test for syntax, I am building the query statement by
concatenating strings to it and displaying it in a text box on the
form.
In its simplest form it is:
"SELECT * from [Project List]"
As the user selects the parameters this will grow with a where
clause
"SELECT * from [Project List] WHERE .AND..ORDER BY.."

I did this exact same thing on another application for another user
using
a
pure VB6 form connected to an Access 2000 MDB database. On that
application, all forms were created in Visual Studio as a stand
alone
application.

On THIS application, The user already has the database and a few forms
created in Access that would take too much time to reconstruct in
Visual
Basic as a whole new program. I am having to work with what I have.
But
I
am getting bogged down with the basic differences in Access controls
and
VB
forms and controls:

When I click the command button to display the query with just one
parameter
option ( Priority from _x_ to _y_ ),
In my VB from it works just fine. In the Access Form I get an Error
Message
#2185 "You can't reference a property or method for a control unless
the
control has the focus".

I guess that means that I have cant just write the code in the module,
but
I
have to cut it up and place into each "gotfocus" event, and I need
to
"setfocus" on each control one at a time just get the values to
build
the
same query. And once I set focus on another control I can no longer
reference the previous control to make a decision or calculation???
Sounds
like a lot of got focus events and maybe copying the values into New
variables created in code on the modular level.

Questions:
1. Is there a better way? Is there an easier way (less coding) to get
around this?
2. Can I create a single POP-Up form in Visual Basic and call it
from
another form created in Access, and then using the resulting query,
display
on an Access Form or Datasheet view or report? I guess I am talking
active
X control but that "sounds" over my level of skill.
3. Know of a "low cost/No cost" add in that already exists out there?

I wuz supposed to be dun. I am under the gun. I ain't havin' no fun.

Sincerely,
StarfishJoe

 

[Dirk Goldgar]

[...]

It just seems that I have to explicitely write to "SetFocus" to each
control, and copy the value of each one into a separate Module level
variable before I can work with them. and before I do that I have to
enable all fields, and then disable them again. If there is a more
efficient way to do THAT I am all ears.

Read again what Allen wrote. You absolutely *do not* have to SetFocus
to any control to read its Value property. Therefore you don't have to
do any of those things you describe above. Just refer to the control
itself in your code, because for any data control the Value property is
its default property. For example,

If Not IsNull(Me.txtFoo) Then
strSQL = strSQL & ", " & Me.txtFoo
End If

The next thing I am runing into, even if the above setfucus on each
control were not an issue, is that I want to programmatically
populate the textbox to display the SQLQuery string I am building.
On the command button click event:
txtSQLWindow.text=strSQLBuild returns errror #2185
txtSQLWindow.value=strSQLBuild returns error#2448

Error #2448 "You can't assign a value to this Object" "The
object may be a control on a read only form." ( No, form is not read
only.) "The Object may be on a form that is open in design view" (
No) "The value may be too large for this field.(The test sample I did
was the simple SQL string:

I don't know offhand why you'd get error 2448 when assigning to the
control's Value property. Can you type in that control?

Just how can you tell the size of an
unbound textbox control on an Access form?

Size in characters? That would depend on the font and the exact text
you wanted to put in it. The width and height of the control, of
course, are available as properties of the control.

This is starting to make me want to go flip hamburgers

That doesn't pay as well.

 

[StarfishJoe]

OK. Now I think understand.
I was still thinking in terms of txtField1.VALUE =txtField2.VALUE or
cboField3.VALUE.
rather than just txtField1=txtField2 , etc.

I also discovered the reason I could not write to the control txtSQLWindow.
I apparently had it bound to an unkown control. It is now unbound and I am
now able to type in it and write to it.

My thanks to both of you gentlemen.

Now, it is on to the "next big thing".

StarfishJoe
----------------------------------------------------------------------------
---------------------

[...]

It just seems that I have to explicitely write to "SetFocus" to each
control, and copy the value of each one into a separate Module level
variable before I can work with them. and before I do that I have to
enable all fields, and then disable them again. If there is a more
efficient way to do THAT I am all ears.

Read again what Allen wrote. You absolutely *do not* have to SetFocus
to any control to read its Value property. Therefore you don't have to
do any of those things you describe above. Just refer to the control
itself in your code, because for any data control the Value property is
its default property. For example,

If Not IsNull(Me.txtFoo) Then
strSQL = strSQL & ", " & Me.txtFoo
End If

The next thing I am runing into, even if the above setfucus on each
control were not an issue, is that I want to programmatically
populate the textbox to display the SQLQuery string I am building.
On the command button click event:
txtSQLWindow.text=strSQLBuild returns errror #2185
txtSQLWindow.value=strSQLBuild returns error#2448

Error #2448 "You can't assign a value to this Object" "The
object may be a control on a read only form." ( No, form is not read
only.) "The Object may be on a form that is open in design view" (
No) "The value may be too large for this field.(The test sample I did
was the simple SQL string:

I don't know offhand why you'd get error 2448 when assigning to the
control's Value property. Can you type in that control?

Just how can you tell the size of an
unbound textbox control on an Access form?

Size in characters? That would depend on the font and the exact text
you wanted to put in it. The width and height of the control, of
course, are available as properties of the control.

This is starting to make me want to go flip hamburgers

That doesn't pay as well.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

OK. Now I think understand.
I was still thinking in terms of txtField1.VALUE =txtField2.VALUE or
cboField3.VALUE.
rather than just txtField1=txtField2 , etc.

There's nothing wrong with explicitly referring to the Value property,
and some people believe that you always should. But you don't have to,
because that's the default property of all data controls.

I also discovered the reason I could not write to the control
txtSQLWindow. I apparently had it bound to an unkown control. It is
now unbound and I am now able to type in it and write to it.

My thanks to both of you gentlemen.

Now, it is on to the "next big thing".

Have fun!