After Update Problem

J

James

Hello I have a form where by I select a value from a combo
box in 3 combo boxes and it produces a percentage at the
end in a text box....

Now the after update works on all three combo boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

-----------------------------------------------------------
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender & ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion & ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ") and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len(sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

-----------------------------------------------------------

How can I get around this?

Many Thanks

James
 
K

Katrina

This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len(sFilter) -
5)
Click to expand...
Len(sfilter) is going to return a number specifying the length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is specifying text values. I
have had places in code where I have had to add the text qualifier ( ' )
before and after my criteria string if it is a text value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
 
J

James

Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I just know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len (sFilter) -
5)
Click to expand...
Len(sfilter) is going to return a number specifying the length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is specifying text values. I
have had places in code where I have had to add the text qualifier ( ' )
before and after my criteria string if it is a text value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
Hello I have a form where by I select a value from a combo
box in 3 combo boxes and it produces a percentage at the
end in a text box....

Now the after update works on all three combo boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

-------------------------------------------------------- ---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender & ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion & ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ") and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len (sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

-------------------------------------------------------- ---

How can I get around this?

Many Thanks

James
Click to expand...


.
Click to expand...
 
K

Katrina

the first things that I would do for this is just put

sFilter = Left(sFilter, Len(sFilter) - 5)
then
where you have
= CalcReturnCount
put
call CalcReturnCount()


See if that helps
Kat


James said:
Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I just know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len (sFilter) -
5)
Click to expand...
Len(sfilter) is going to return a number specifying the length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is specifying text values. I
have had places in code where I have had to add the text qualifier ( ' )
before and after my criteria string if it is a text value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
Hello I have a form where by I select a value from a combo
box in 3 combo boxes and it produces a percentage at the
end in a text box....

Now the after update works on all three combo boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

-------------------------------------------------------- ---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender & ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion & ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ") and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len (sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

-------------------------------------------------------- ---

How can I get around this?

Many Thanks

James
Click to expand...


.
Click to expand...
Click to expand...
 
J

James

Still teh same thing but where I put call CalcReturnCount
I get an error saying that the database cant find a macro
called CalcReturnCount

How do I get around this?

Many THanks

James
-----Original Message-----
the first things that I would do for this is just put

sFilter = Left(sFilter, Len(sFilter) - 5)
then
where you have
= CalcReturnCount
put
call CalcReturnCount()


See if that helps
Kat


Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I just know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len (sFilter) -
5)
Len(sfilter) is going to return a number specifying the length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is specifying text values. I
have had places in code where I have had to add the
Click to expand...
text
qualifier ( ' )
before and after my criteria string if it is a text value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
Hello I have a form where by I select a value from a combo
box in 3 combo boxes and it produces a percentage at the
end in a text box....

Now the after update works on all three combo boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

-----------------------------------------------------
Click to expand...
---
---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender & ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion & ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ") and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len (sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

-----------------------------------------------------
Click to expand...
---
---
How can I get around this?

Many Thanks

James



.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Katrina

ok, leave the Len() the way I told you...
Out of your combo boxes, the ones where the data being select is a text
value, try putting text qualifier in the sql string.
example: If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID = '" & cboGender & "')
and "
End If
the single apostrophe is the text qualifier
have you tried walking through the code by going to debug mode?

what is the code for the function CalcReturnCount(), you need to make sure
that it is not a private function.

Kat

James said:
Still teh same thing but where I put call CalcReturnCount
I get an error saying that the database cant find a macro
called CalcReturnCount

How do I get around this?

Many THanks

James
-----Original Message-----
the first things that I would do for this is just put

sFilter = Left(sFilter, Len(sFilter) - 5)
then
where you have
= CalcReturnCount
put
call CalcReturnCount()


See if that helps
Kat


Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I just know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
Len(sfilter) is going to return a number specifying the
length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is
specifying text values. I
have had places in code where I have had to add the text
qualifier ( ' )
before and after my criteria string if it is a text
value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
message
Hello I have a form where by I select a value from a
combo
box in 3 combo boxes and it produces a percentage at the
end in a text box....

Now the after update works on all three combo boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

----------------------------------------------------- ---
---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender
& ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion
& ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ")
and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

----------------------------------------------------- ---
---

How can I get around this?

Many Thanks

James



.
Click to expand...


.
Click to expand...
Click to expand...
 
J

James

Would this be the same...

Ok I have values as text showing to the user but when they
go to the table at the back they are numbers....

Would this make a diffrence in my code?

Many Thanks

James
-----Original Message-----
ok, leave the Len() the way I told you...
Out of your combo boxes, the ones where the data being select is a text
value, try putting text qualifier in the sql string.
example: If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID = '" & cboGender & "')
and "
End If
the single apostrophe is the text qualifier
have you tried walking through the code by going to debug mode?

what is the code for the function CalcReturnCount(), you need to make sure
that it is not a private function.

Kat

Still teh same thing but where I put call CalcReturnCount
I get an error saying that the database cant find a macro
called CalcReturnCount

How do I get around this?

Many THanks

James
-----Original Message-----
the first things that I would do for this is just put

sFilter = Left(sFilter, Len(sFilter) - 5)
then
where you have
= CalcReturnCount
put
call CalcReturnCount()


See if that helps
Kat


Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I just know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
Len(sfilter) is going to return a number specifying the
length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is
specifying text values. I
have had places in code where I have had to add the text
qualifier ( ' )
before and after my criteria string if it is a text
value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
message
Hello I have a form where by I select a value from a
combo
box in 3 combo boxes and it produces a percentage
Click to expand...
at
the
end in a text box....

Now the after update works on all three combo boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

--------------------------------------------------
Click to expand...
---
---
---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender
& ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion
& ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ")
and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

--------------------------------------------------
Click to expand...
---
---
---

How can I get around this?

Many Thanks

James



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
J

James

I have tried what you have asked with teh test
qualifier... I still get the Error... the only diffrence
is where I have a text box where by I calculate the
percentage... I get a !Num error....

Any Suggestions?

Many Thanks

James
-----Original Message-----
ok, leave the Len() the way I told you...
Out of your combo boxes, the ones where the data being select is a text
value, try putting text qualifier in the sql string.
example: If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID = '" & cboGender & "')
and "
End If
the single apostrophe is the text qualifier
have you tried walking through the code by going to debug mode?

what is the code for the function CalcReturnCount(), you need to make sure
that it is not a private function.

Kat

Still teh same thing but where I put call CalcReturnCount
I get an error saying that the database cant find a macro
called CalcReturnCount

How do I get around this?

Many THanks

James
-----Original Message-----
the first things that I would do for this is just put

sFilter = Left(sFilter, Len(sFilter) - 5)
then
where you have
= CalcReturnCount
put
call CalcReturnCount()


See if that helps
Kat


Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I just know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
Len(sfilter) is going to return a number specifying the
length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is
specifying text values. I
have had places in code where I have had to add the text
qualifier ( ' )
before and after my criteria string if it is a text
value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
message
Hello I have a form where by I select a value from a
combo
box in 3 combo boxes and it produces a percentage
Click to expand...
at
the
end in a text box....

Now the after update works on all three combo boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

--------------------------------------------------
Click to expand...
---
---
---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender
& ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion
& ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ")
and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

--------------------------------------------------
Click to expand...
---
---
---

How can I get around this?

Many Thanks

James



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Katrina

So waht you are saying is that the bound column on the combo box is a
number, but the selection is text? If this is the case then try this....

If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID = " & cboGender.column(0) & " and ")
endif
the column number should be whatever column your bound field is...I am
assuming here that it is the first if sqlstmt for the combo.

Kat

James said:
I have tried what you have asked with teh test
qualifier... I still get the Error... the only diffrence
is where I have a text box where by I calculate the
percentage... I get a !Num error....

Any Suggestions?

Many Thanks

James
-----Original Message-----
ok, leave the Len() the way I told you...
Out of your combo boxes, the ones where the data being select is a text
value, try putting text qualifier in the sql string.
example: If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID = '" & cboGender & "')
and "
End If
the single apostrophe is the text qualifier
have you tried walking through the code by going to debug mode?

what is the code for the function CalcReturnCount(), you need to make sure
that it is not a private function.

Kat

Still teh same thing but where I put call CalcReturnCount
I get an error saying that the database cant find a macro
called CalcReturnCount

How do I get around this?

Many THanks

James
-----Original Message-----
the first things that I would do for this is just put

sFilter = Left(sFilter, Len(sFilter) - 5)
then
where you have
= CalcReturnCount
put
call CalcReturnCount()


See if that helps
Kat


message
Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I just know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
Len(sfilter) is going to return a number specifying the
length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len(sFilter) - 5)
That does not make sense.
Another thing to check is to make sure that it is
specifying text values. I
have had places in code where I have had to add the
text
qualifier ( ' )
before and after my criteria string if it is a text
value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
message
Hello I have a form where by I select a value from a
combo
box in 3 combo boxes and it produces a percentage at
the
end in a text box....

Now the after update works on all three combo
boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

-------------------------------------------------- ---
---
---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender
& ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion
& ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ")
and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

-------------------------------------------------- ---
---
---

How can I get around this?

Many Thanks

James



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
J

James

Still no Joy I am still getting this num! error...

Plus its still having a fit with the Sure part of my
Answer Field...

Would you like to have a look at the database to get a
better idea of hwta I mean?

Many Thanks

James
-----Original Message-----
So waht you are saying is that the bound column on the combo box is a
number, but the selection is text? If this is the case then try this....

If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID = " & cboGender.column(0) & " and ")
endif
the column number should be whatever column your bound field is...I am
assuming here that it is the first if sqlstmt for the combo.

Kat

I have tried what you have asked with teh test
qualifier... I still get the Error... the only diffrence
is where I have a text box where by I calculate the
percentage... I get a !Num error....

Any Suggestions?

Many Thanks

James
-----Original Message-----
ok, leave the Len() the way I told you...
Out of your combo boxes, the ones where the data being select is a text
value, try putting text qualifier in the sql string.
example: If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID
Click to expand...
= '"
& cboGender & "')
and "
End If
the single apostrophe is the text qualifier
have you tried walking through the code by going to
Click to expand...
debug
mode?
what is the code for the function CalcReturnCount(),
Click to expand...
you
need to make sure
that it is not a private function.

Kat

Still teh same thing but where I put call CalcReturnCount
I get an error saying that the database cant find a macro
called CalcReturnCount

How do I get around this?

Many THanks

James
-----Original Message-----
the first things that I would do for this is just put

sFilter = Left(sFilter, Len(sFilter) - 5)
then
where you have
= CalcReturnCount
put
call CalcReturnCount()


See if that helps
Kat


message
Ok the thing is that I have been given this codde by
someone on these forums...

I was wondering what I need to do to make it read work?

please bear in mind that I am no VB coder... I
Click to expand...
just
know
little bits here and there...

Many Thanks

James
-----Original Message-----
This is the only thing I might see a problem with
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
Len(sfilter) is going to return a number
Click to expand...
specifying
the
length of the field
so... if the length of sfilter = 15 the code will read
if 15 then sfilter = = Left(sFilter, Len
Click to expand...
(sFilter) -
5)
That does not make sense.
Another thing to check is to make sure that it is
specifying text values. I
have had places in code where I have had to add the
text
qualifier ( ' )
before and after my criteria string if it is a text
value..

example
"where [FName] = '" & me.fname & "'"

HTH
Kat
message
Hello I have a form where by I select a value from a
combo
box in 3 combo boxes and it produces a
Click to expand...
percentage
at
the
end in a text box....

Now the after update works on all three combo
boxes...
Except on the third one when I select not sure...

It tells me it cant produce an automation object
for "Sure"...

What could the problem be??

The code I have on After Update is this:

=CalcReturnCount()

Which has the vb of:

-----------------------------------------------
Click to expand...
---
---
---
---
Private Function CalcReturnCount()
Dim sFilter As String
If Not IsNull(cboGender) Then
sFilter = sFilter & "(RespondantID=" & cboGender
& ")
and "
End If

If Not IsNull(cboQuestion) Then
sFilter = sFilter & "(QuestionID=" & cboQuestion
& ")
and "
End If

If Not IsNull(cboAnswer) Then
sFilter = sFilter & "(Answer=" & cboAnswer & ")
and "
End If
' chop off the last " and "
If Len(sFilter) Then sFilter = Left(sFilter, Len
(sFilter) -
5)
txtReturnCount = DCount("*", "tblResponses", sFilter)
End Function

-----------------------------------------------
Click to expand...
---
---
---
---

How can I get around this?

Many Thanks

James



.



.



.
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

Change Label After Update of Combo Box 1
Query Problem 1
Open Dialog Box API Call 3
Corrupt DB - VB code problem? 3
Query Problems 9
Update a date field in sub-form from dates fields on main form 1
Combo Box After Update Problem 0
After Update 3

Top