Please DO help me finding a good solution to this VBA , Thanks a million!

[Martin]

Remark: jxsjc's value is depended to a combo, this combo might be not
selcted by the user, if not selected, it would be done as included all of
this combo contents. zydjc's logic is the same as jxsjc .

Please do help me in solving the following VBA mistake! It's important!
Thanks!!!


Private Sub Command107_Click()
Dim jxsjc As String
Dim zydjc As String

If IsNull([Forms]![aab]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![aab]![Combo98]
End If

If IsNull([Forms]![aab]![Combo100]) Then
zydjc = "*"
Else
zydjc = [Forms]![aab]![Combo100]
End If

DoCmd.OpenForm "ffe", acNormal, acEdit

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& jxsjc
&"'"

[Forms]![ffe].FilterOn = True
End Sub

 

[Douglas J Steele]

PLEASE stop posting the same question into so many threads. It's very
difficult to keep track of all of the answers you've received.

Is that an actual copy-and-paste of your code?

If so, the problem may be that you've repeated jxsjc twice:

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& jxsjc
&"'"

On the other hand, your OpenForm command is incorrect:

DoCmd.OpenForm "ffe", acNormal, acEdit

The official syntax is:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]

You've got acEdit where the filter should be. If you check the Help file,
you'll see that acEdit isn't a value for any of those arguments. If you want
to allow edits on the form, then the datamode should be acFormEdit (Okay,
acEdit and acFormEdit both equal 1, so it will work in actual fact. The
point is, it contradicts the documentation.)

Your OpenForm statement should be either:

DoCmd.OpenForm "ffe", acNormal, , , acFormEdit

or

DoCmd.OpenForm "ffe", acNormal, DataMode := acEdit

Note that you can set the filter when you open the form:

strFilter = "dealera like '"& jxsjc &"' and dealerb = '"& zydjc &"'"

DoCmd.OpenForm "ffe", acNormal, strFilter, , acFormEdit

although personally I'd use a Where condition rather than a filter:

DoCmd.OpenForm "ffe", acNormal, , strFilter, acFormEdit

 

[Martin]

Mr. Douglas,

Firstly, Thank you very much for spending time answering my confusing
questions. I have been disturbing all day by this problem.

Secondly, the VBA I posted really posted wrongly --- that sentence should
be:
[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& zydjc
&"'"

Thirdly, The key problem lies in the "*" operation. I tried all the way you
and other kind persons told me. But only one way can solve the problem, it
is the sentence you told me that: [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)
up till now, ONLY THIS SENTENCE can solve the "*" problem !!!!

YET, one more step I could succeed! IF YOU COULD KINDLY TELL ME HOW TO make
the upper VBA sentence to fit for TWO CRITERIA, I would totally resolve ALL
THESE problem !!! for example:
ONE CRITERIA, this vbais very good : [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)

TWO CRITERIA ? IS IT?? >>>> [Forms]![aab].Filter = " dealera like " &
Chr$(34) & jxsjc & Chr$(34) AND " dealerb like " & Chr$(34) & zydjc &
Chr$(34) ?????????
but, I tried this sentence (to do with two criteria), can't work. I tried to
change the postition of the " and ', but to my effort, Can't work.

Please, Mr. Douglas, would you and other friends here in this newsgroup to
help me solve this big big big problem ??

Thanks you all, again

Martin

PLEASE stop posting the same question into so many threads. It's very
difficult to keep track of all of the answers you've received.

Is that an actual copy-and-paste of your code?

If so, the problem may be that you've repeated jxsjc twice:

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& jxsjc
&"'"

On the other hand, your OpenForm command is incorrect:

DoCmd.OpenForm "ffe", acNormal, acEdit

The official syntax is:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]

You've got acEdit where the filter should be. If you check the Help file,
you'll see that acEdit isn't a value for any of those arguments. If you
want
to allow edits on the form, then the datamode should be acFormEdit (Okay,
acEdit and acFormEdit both equal 1, so it will work in actual fact. The
point is, it contradicts the documentation.)

Your OpenForm statement should be either:

DoCmd.OpenForm "ffe", acNormal, , , acFormEdit

or

DoCmd.OpenForm "ffe", acNormal, DataMode := acEdit

Note that you can set the filter when you open the form:

strFilter = "dealera like '"& jxsjc &"' and dealerb = '"& zydjc &"'"

DoCmd.OpenForm "ffe", acNormal, strFilter, , acFormEdit

although personally I'd use a Where condition rather than a filter:

DoCmd.OpenForm "ffe", acNormal, , strFilter, acFormEdit



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Remark: jxsjc's value is depended to a combo, this combo might be not
selcted by the user, if not selected, it would be done as included all of
this combo contents. zydjc's logic is the same as jxsjc .

Please do help me in solving the following VBA mistake! It's important!
Thanks!!!


Private Sub Command107_Click()
Dim jxsjc As String
Dim zydjc As String

If IsNull([Forms]![aab]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![aab]![Combo98]
End If

If IsNull([Forms]![aab]![Combo100]) Then
zydjc = "*"
Else
zydjc = [Forms]![aab]![Combo100]
End If

DoCmd.OpenForm "ffe", acNormal, acEdit

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"&
jxsjc
&"'"

[Forms]![ffe].FilterOn = True
End Sub

 

[Douglas J Steele]

As I said elsewhere, the And needs to be inside the quotes:

[Forms]![aab].Filter = " dealera like " & Chr$(34) & jxsjc & Chr$(34) & "
AND dealerb like " & Chr$(34) & zydjc & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Mr. Douglas,

Firstly, Thank you very much for spending time answering my confusing
questions. I have been disturbing all day by this problem.

Secondly, the VBA I posted really posted wrongly --- that sentence should
be:
[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& zydjc
&"'"

Thirdly, The key problem lies in the "*" operation. I tried all the way you
and other kind persons told me. But only one way can solve the problem, it
is the sentence you told me that: [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)
up till now, ONLY THIS SENTENCE can solve the "*" problem !!!!

YET, one more step I could succeed! IF YOU COULD KINDLY TELL ME HOW TO make
the upper VBA sentence to fit for TWO CRITERIA, I would totally resolve ALL
THESE problem !!! for example:
ONE CRITERIA, this vbais very good : [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)

TWO CRITERIA ? IS IT?? >>>> [Forms]![aab].Filter = " dealera like " &
Chr$(34) & jxsjc & Chr$(34) AND " dealerb like " & Chr$(34) & zydjc &
Chr$(34) ?????????
but, I tried this sentence (to do with two criteria), can't work. I tried to
change the postition of the " and ', but to my effort, Can't work.

Please, Mr. Douglas, would you and other friends here in this newsgroup to
help me solve this big big big problem ??

Thanks you all, again

Martin


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> дÈëÏûÏ¢ÐÂÎÅ:[email protected]...

PLEASE stop posting the same question into so many threads. It's very
difficult to keep track of all of the answers you've received.

Is that an actual copy-and-paste of your code?

If so, the problem may be that you've repeated jxsjc twice:

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& jxsjc
&"'"

On the other hand, your OpenForm command is incorrect:

DoCmd.OpenForm "ffe", acNormal, acEdit

The official syntax is:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]

You've got acEdit where the filter should be. If you check the Help file,
you'll see that acEdit isn't a value for any of those arguments. If you
want
to allow edits on the form, then the datamode should be acFormEdit (Okay,
acEdit and acFormEdit both equal 1, so it will work in actual fact. The
point is, it contradicts the documentation.)

Your OpenForm statement should be either:

DoCmd.OpenForm "ffe", acNormal, , , acFormEdit

or

DoCmd.OpenForm "ffe", acNormal, DataMode := acEdit

Note that you can set the filter when you open the form:

strFilter = "dealera like '"& jxsjc &"' and dealerb = '"& zydjc &"'"

DoCmd.OpenForm "ffe", acNormal, strFilter, , acFormEdit

although personally I'd use a Where condition rather than a filter:

DoCmd.OpenForm "ffe", acNormal, , strFilter, acFormEdit



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Remark: jxsjc's value is depended to a combo, this combo might be not
selcted by the user, if not selected, it would be done as included all of
this combo contents. zydjc's logic is the same as jxsjc .

Please do help me in solving the following VBA mistake! It's important!
Thanks!!!


Private Sub Command107_Click()
Dim jxsjc As String
Dim zydjc As String

If IsNull([Forms]![aab]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![aab]![Combo98]
End If

If IsNull([Forms]![aab]![Combo100]) Then
zydjc = "*"
Else
zydjc = [Forms]![aab]![Combo100]
End If

DoCmd.OpenForm "ffe", acNormal, acEdit

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"&
jxsjc
&"'"

[Forms]![ffe].FilterOn = True
End Sub

 

[Martin]

Succeed!!!

Mr. Doug Steele , I really thank you for your help. You are very patient,
very kind. Without your help, I can not succeed in this vba sentence
operation!

A whole day's work! I have asked a lot of persons and bbs for advice, only
your method can do!

Actually, my final VBA sentence have THREE CRITERIA, I tested the following
VBA sentence, it appears to be great success!

[Forms]![abb].Filter = " dealerA like " & Chr$(34) & jxsjc & Chr$(34) & "
And dealerB like " & Chr$(34) & zydjc & Chr$(34) & " and dealerC Like " &
Chr$(34) & ywxt & Chr$(34)
---------------------------------------------------

The program results in great success. However, I only follow your VBA to
creat a THREE CRITERIA VBA, I still very confussing about the logic of this
VBA sentence---especially the ""s and the Chr$(34) , although I know chr(34)
stands for "
[Forms]![aab].Filter = " dealera like " & Chr$(34) & jxsjc & Chr$(34) & "
AND dealerb like " & Chr$(34) & zydjc & Chr$(34)

---- in my idea, this VBA means:

....Filter = "dealera like ""jxsjc"" and dealerb like ""zydjc"

---- I am confussing: why the end of it not appears as: ""zydjc"" "
?????
and, I have tried to add two of " at the end of the sentence, however after
adding, it doesn't work out correct result.

Thanks again and also thanks to all the persons give help here.

Martin

As I said elsewhere, the And needs to be inside the quotes:

[Forms]![aab].Filter = " dealera like " & Chr$(34) & jxsjc & Chr$(34) & "
AND dealerb like " & Chr$(34) & zydjc & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Mr. Douglas,

Firstly, Thank you very much for spending time answering my confusing
questions. I have been disturbing all day by this problem.

Secondly, the VBA I posted really posted wrongly --- that sentence
should
be:
[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"&
zydjc
&"'"

Thirdly, The key problem lies in the "*" operation. I tried all the way you
and other kind persons told me. But only one way can solve the problem,
it
is the sentence you told me that: [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)
up till now, ONLY THIS SENTENCE can solve the "*" problem !!!!

YET, one more step I could succeed! IF YOU COULD KINDLY TELL ME HOW TO make
the upper VBA sentence to fit for TWO CRITERIA, I would totally resolve ALL
THESE problem !!! for example:
ONE CRITERIA, this vbais very good : [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)

TWO CRITERIA ? IS IT?? >>>> [Forms]![aab].Filter = " dealera like " &
Chr$(34) & jxsjc & Chr$(34) AND " dealerb like " & Chr$(34) & zydjc &
Chr$(34) ?????????
but, I tried this sentence (to do with two criteria), can't work. I tried to
change the postition of the " and ', but to my effort, Can't work.

Please, Mr. Douglas, would you and other friends here in this newsgroup
to
help me solve this big big big problem ??

Thanks you all, again

Martin


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> дÈëÏûÏ¢ÐÂÎÅ:[email protected]...

PLEASE stop posting the same question into so many threads. It's very
difficult to keep track of all of the answers you've received.

Is that an actual copy-and-paste of your code?

If so, the problem may be that you've repeated jxsjc twice:

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& jxsjc
&"'"

On the other hand, your OpenForm command is incorrect:

DoCmd.OpenForm "ffe", acNormal, acEdit

The official syntax is:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]

You've got acEdit where the filter should be. If you check the Help file,
you'll see that acEdit isn't a value for any of those arguments. If you
want
to allow edits on the form, then the datamode should be acFormEdit (Okay,
acEdit and acFormEdit both equal 1, so it will work in actual fact. The
point is, it contradicts the documentation.)

Your OpenForm statement should be either:

DoCmd.OpenForm "ffe", acNormal, , , acFormEdit

or

DoCmd.OpenForm "ffe", acNormal, DataMode := acEdit

Note that you can set the filter when you open the form:

strFilter = "dealera like '"& jxsjc &"' and dealerb = '"& zydjc &"'"

DoCmd.OpenForm "ffe", acNormal, strFilter, , acFormEdit

although personally I'd use a Where condition rather than a filter:

DoCmd.OpenForm "ffe", acNormal, , strFilter, acFormEdit



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Remark: jxsjc's value is depended to a combo, this combo might be not
selcted by the user, if not selected, it would be done as included all of
this combo contents. zydjc's logic is the same as jxsjc .

Please do help me in solving the following VBA mistake! It's
important!
Thanks!!!


Private Sub Command107_Click()
Dim jxsjc As String
Dim zydjc As String

If IsNull([Forms]![aab]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![aab]![Combo98]
End If

If IsNull([Forms]![aab]![Combo100]) Then
zydjc = "*"
Else
zydjc = [Forms]![aab]![Combo100]
End If

DoCmd.OpenForm "ffe", acNormal, acEdit

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"&
jxsjc
&"'"

[Forms]![ffe].FilterOn = True
End Sub

 

[Douglas J Steele]

Glad you got it working.

Let me point out that having field names like DealerA, DealerB and DealerC
is usually indicative of having a repeating group, which means that your
database has not been properly normalized.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Succeed!!!

Mr. Doug Steele , I really thank you for your help. You are very patient,
very kind. Without your help, I can not succeed in this vba sentence
operation!

A whole day's work! I have asked a lot of persons and bbs for advice, only
your method can do!

Actually, my final VBA sentence have THREE CRITERIA, I tested the following
VBA sentence, it appears to be great success!

[Forms]![abb].Filter = " dealerA like " & Chr$(34) & jxsjc & Chr$(34) & "
And dealerB like " & Chr$(34) & zydjc & Chr$(34) & " and dealerC Like " &
Chr$(34) & ywxt & Chr$(34)
---------------------------------------------------

The program results in great success. However, I only follow your VBA to
creat a THREE CRITERIA VBA, I still very confussing about the logic of this
VBA sentence---especially the ""s and the Chr$(34) , although I know chr(34)
stands for "
[Forms]![aab].Filter = " dealera like " & Chr$(34) & jxsjc & Chr$(34) & "
AND dealerb like " & Chr$(34) & zydjc & Chr$(34)

---- in my idea, this VBA means:

...Filter = "dealera like ""jxsjc"" and dealerb like ""zydjc"

---- I am confussing: why the end of it not appears as: ""zydjc"" "
?????
and, I have tried to add two of " at the end of the sentence, however after
adding, it doesn't work out correct result.

Thanks again and also thanks to all the persons give help here.

Martin




"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> дÈëÏûÏ¢ÐÂÎÅ:[email protected]...

As I said elsewhere, the And needs to be inside the quotes:

[Forms]![aab].Filter = " dealera like " & Chr$(34) & jxsjc & Chr$(34) & "
AND dealerb like " & Chr$(34) & zydjc & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Mr. Douglas,

Firstly, Thank you very much for spending time answering my confusing
questions. I have been disturbing all day by this problem.

Secondly, the VBA I posted really posted wrongly --- that sentence
should
be:
[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"&
zydjc
&"'"

Thirdly, The key problem lies in the "*" operation. I tried all the way you
and other kind persons told me. But only one way can solve the problem,
it
is the sentence you told me that: [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)
up till now, ONLY THIS SENTENCE can solve the "*" problem !!!!

YET, one more step I could succeed! IF YOU COULD KINDLY TELL ME HOW TO make
the upper VBA sentence to fit for TWO CRITERIA, I would totally resolve ALL
THESE problem !!! for example:
ONE CRITERIA, this vbais very good : [Forms]![aab].Filter = " dealera like
" & Chr$(34) & jxsjc & Chr$(34)

TWO CRITERIA ? IS IT?? >>>> [Forms]![aab].Filter = " dealera like " &
Chr$(34) & jxsjc & Chr$(34) AND " dealerb like " & Chr$(34) & zydjc &
Chr$(34) ?????????
but, I tried this sentence (to do with two criteria), can't work. I

tried
to

change the postition of the " and ', but to my effort, Can't work.

Please, Mr. Douglas, would you and other friends here in this newsgroup
to
help me solve this big big big problem ??

Thanks you all, again

Martin


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> дÈëÏûÏ¢ÐÂÎÅ:[email protected]...
PLEASE stop posting the same question into so many threads. It's very
difficult to keep track of all of the answers you've received.

Is that an actual copy-and-paste of your code?

If so, the problem may be that you've repeated jxsjc twice:

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"& jxsjc
&"'"

On the other hand, your OpenForm command is incorrect:

DoCmd.OpenForm "ffe", acNormal, acEdit

The official syntax is:

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][,
datamode][, windowmode][, openargs]

You've got acEdit where the filter should be. If you check the Help file,
you'll see that acEdit isn't a value for any of those arguments. If you
want
to allow edits on the form, then the datamode should be acFormEdit (Okay,
acEdit and acFormEdit both equal 1, so it will work in actual fact. The
point is, it contradicts the documentation.)

Your OpenForm statement should be either:

DoCmd.OpenForm "ffe", acNormal, , , acFormEdit

or

DoCmd.OpenForm "ffe", acNormal, DataMode := acEdit

Note that you can set the filter when you open the form:

strFilter = "dealera like '"& jxsjc &"' and dealerb = '"& zydjc &"'"

DoCmd.OpenForm "ffe", acNormal, strFilter, , acFormEdit

although personally I'd use a Where condition rather than a filter:

DoCmd.OpenForm "ffe", acNormal, , strFilter, acFormEdit



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Remark: jxsjc's value is depended to a combo, this combo might be not
selcted by the user, if not selected, it would be done as included

all
of

this combo contents. zydjc's logic is the same as jxsjc .

Please do help me in solving the following VBA mistake! It's
important!
Thanks!!!


Private Sub Command107_Click()
Dim jxsjc As String
Dim zydjc As String

If IsNull([Forms]![aab]![Combo98]) Then
jxsjc = "*"
Else
jxsjc = [Forms]![aab]![Combo98]
End If

If IsNull([Forms]![aab]![Combo100]) Then
zydjc = "*"
Else
zydjc = [Forms]![aab]![Combo100]
End If

DoCmd.OpenForm "ffe", acNormal, acEdit

[Forms]![ffe].Filter = "dealera like '"& jxsjc &"' and dealerb = '"&
jxsjc
&"'"

[Forms]![ffe].FilterOn = True
End Sub